[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRSCCR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRSCCR_XMLP_PKG AS
2 /* $Header: CSTRSCCRB.pls 120.0.12020000.2 2012/07/11 13:17:37 vkatakam ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_STMT_NUM NUMBER(15);
5 O_ERROR_CODE NUMBER(15);
6 O_ERROR_MSG VARCHAR2(2000);
7 L_EXCLUDE_UNIMP_ECO NUMBER(15);
8 L_EXCLUDE_ENG NUMBER(15);
9 L_TIMESTAMP DATE;
10 L_RERUN_FLAG NUMBER(15);
11 L_STR_POS NUMBER(15);
12 L_ORGANIZATION_ID NUMBER(15);
13 CONC_STATUS BOOLEAN;
14 L_USER_ID NUMBER(15) := -1;
15 L_LOGIN_ID NUMBER(15) := -1;
16 L_REQUEST_ID NUMBER(15) := -1;
17 L_PROG_APPL_ID NUMBER(15) := -1;
18 L_PROG_ID NUMBER(15) := -1;
19 L_ROWS_UNEXPLODED NUMBER(15);
20 L_NO_BOM_ORG VARCHAR2(4);
21 L_NO_ALT_ORG VARCHAR2(4);
22 L_SNAPSHOT_DESIGNATOR VARCHAR2(20);
23 CURSOR NO_BOM_ORGS IS
24 SELECT
25 DISTINCT
26 CSLLC.ORGANIZATION_ID
27 FROM
28 CST_SC_LOW_LEVEL_CODES CSLLC
29 WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
30 AND not exists (
31 SELECT
32 'x'
33 FROM
34 BOM_PARAMETERS BP
35 WHERE CSLLC.ORGANIZATION_ID = BP.ORGANIZATION_ID );
36 CURSOR NO_ALT_ORGS(I_SNAPSHOT_DESIGNATOR IN VARCHAR2) IS
37 SELECT
38 DISTINCT
39 CSLLC.ORGANIZATION_ID
40 FROM
41 CST_SC_LOW_LEVEL_CODES CSLLC
42 WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
43 AND not exists (
44 SELECT
45 'Alternate designator exists'
46 FROM
47 BOM_ALTERNATE_DESIGNATORS BAD
48 WHERE BAD.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID
49 AND BAD.ALTERNATE_DESIGNATOR_CODE = I_SNAPSHOT_DESIGNATOR );
50 CURSOR LOOP_ROWS(I_ROLLUP_ID IN NUMBER) IS
51 SELECT
52 CSBE.ASSEMBLY_ITEM_ID,
53 CSBE.ASSEMBLY_ORGANIZATION_ID,
54 CSBE.COMPONENT_ITEM_ID,
55 CSBE.COMPONENT_ORGANIZATION_ID
56 FROM
57 CST_SC_BOM_EXPLOSION CSBE
58 WHERE CSBE.ROLLUP_ID = I_ROLLUP_ID
59 AND CSBE.DELETED_FLAG <> 'Y';
60 BEGIN
61 LP_CATEGORY_SET_ID:=P_CATEGORY_SET_ID;
62 LP_REPORT_LEVEL:=P_REPORT_LEVEL;
63 LP_EXPLOSION_LEVEL:=P_EXPLOSION_LEVEL;
64 LP_RANGE_TYPE:=P_RANGE_TYPE;
65 LP_ASSIGNMENT_SET_ID:=P_ASSIGNMENT_SET_ID;
66 LP_BUY_COST_TYPE_ID:=P_BUY_COST_TYPE_ID;
67 LP_DESCRIPTION:=P_DESCRIPTION;
68 LP_REVISION_DATE:=P_REVISION_DATE;
69 LP_COST_TYPE_ID:=P_COST_TYPE_ID;
70 LP_ORGANIZATION_ID:=P_ORGANIZATION_ID;
71 LP_INC_UNIMP_ECN_FLAG:=P_INC_UNIMP_ECN_FLAG;
72 LP_ALT_BOM_DESG:=P_ALT_BOM_DESG;
73 LP_ENG_BILL_FLAG:=P_ENG_BILL_FLAG;
74 lp_default_org_id:=p_default_org_id;
75 LP_REPORT_TYPE_TYPE:=P_REPORT_TYPE_TYPE;
76 LP_REPORT_OPTION_TYPE:=P_REPORT_OPTION_TYPE;
77 QTY_PRECISION:=bom_common_xmlp_pkg.get_precision(P_qty_precision);
78 IF (LP_ALT_BOM_DESG IS NOT NULL) THEN
79 SELECT
80 DISPLAY_NAME
81 INTO P_ALT_BOM_DESG_DSP
82 FROM
83 BOM_ALTERNATE_DESIGNATORS_VL
84 WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
85 ,lp_default_org_id)
86 AND ALTERNATE_DESIGNATOR_CODE = LP_ALT_BOM_DESG;
87 END IF;
88 IF (P_ALT_RTG_DESG IS NOT NULL) THEN
89 SELECT
90 DISPLAY_NAME
91 INTO P_ALT_RTG_DESG_DSP
92 FROM
93 BOM_ALTERNATE_DESIGNATORS_VL
94 WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
95 ,lp_default_org_id)
96 AND ALTERNATE_DESIGNATOR_CODE = P_ALT_RTG_DESG;
97 END IF;
98 L_STMT_NUM := 10;
99 IF P_CONC_REQUEST_ID IS NOT NULL THEN
100 SELECT
101 NVL(MIN(REQUESTED_BY)
102 ,-1),
103 NVL(MIN(CONC_LOGIN_ID)
104 ,-1),
105 NVL(MIN(REQUEST_ID)
106 ,-1),
107 NVL(MIN(PROGRAM_APPLICATION_ID)
108 ,-1),
109 NVL(MIN(CONCURRENT_PROGRAM_ID)
110 ,-1)
111 INTO L_USER_ID,L_LOGIN_ID,L_REQUEST_ID,L_PROG_APPL_ID,L_PROG_ID
112 FROM
113 FND_CONCURRENT_REQUESTS
114 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
115 END IF;
116 BEGIN
117 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
118 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
119 EXCEPTION
120 WHEN OTHERS THEN
121 /*SRW.MESSAGE(999
122 ,'FND SRWINIT >X')*/NULL;
123 RAISE;
124 END;
125 L_STMT_NUM := 20;
126 L_STMT_NUM := 25;
127 SELECT
128 MIN(TO_CHAR(MCV.CATEGORY_ID))
129 INTO P_CATEGORY_ID
130 FROM
131 MTL_CATEGORIES_KFV MCV,
132 MTL_CATEGORY_SETS MCS
133 WHERE SUBSTRB(MCV.CONCATENATED_SEGMENTS
134 ,1
135 ,2000) = P_CATEGORY_ID
136 AND MCS.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
137 AND MCV.STRUCTURE_ID = MCS.STRUCTURE_ID;
138 L_STMT_NUM := 26;
139 SELECT
140 NVL(FND_PROFILE.VALUE('CST_RU_PHANTOM_MATERIAL')
141 ,1)
142 INTO P_PHANTOM_MAT
143 FROM
144 DUAL;
145 L_STMT_NUM := 30;
146 IF P_ROLLUP_ID IS NOT NULL THEN
147 L_RERUN_FLAG := 1;
148 L_STMT_NUM := 40;
149 SELECT
150 ROLLUP_ID,
151 DESCRIPTION,
152 COST_TYPE_ID,
153 BUY_COST_TYPE_ID,
154 ORGANIZATION_ID,
155 ASSIGNMENT_SET_ID,
156 CONVERSION_TYPE,
157 LP_REPORT_LEVEL,
158 EXPLOSION_LEVEL,
159 ROLLUP_OPTION_TYPE,
160 1,
161 RANGE_TYPE,
162 TO_CHAR(REVISION_DATE
163 ,'YYYY/MM/DD HH24:MI:SS'),
164 INC_UNIMP_ECN_FLAG,
165 ENG_BILL_FLAG,
166 QTY_PRECISION,
167 ITEM_ID,
168 CATEGORY_SET_ID,
169 TO_CHAR(CATEGORY_ID),
170 ALT_BOM_DESG,
171 ALT_RTG_DESG
172 INTO P_ROLLUP_ID,LP_DESCRIPTION,LP_COST_TYPE_ID,LP_BUY_COST_TYPE_ID,LP_ORGANIZATION_ID
173 ,LP_ASSIGNMENT_SET_ID,P_CONVERSION_TYPE,LP_REPORT_LEVEL,LP_EXPLOSION_LEVEL
174 ,P_ROLLUP_OPTION_TYPE,LP_REPORT_OPTION_TYPE,LP_RANGE_TYPE,LP_REVISION_DATE,LP_INC_UNIMP_ECN_FLAG,LP_ENG_BILL_FLAG,P_QTY_PRECISION,P_ITEM_ID,LP_CATEGORY_SET_ID,P_CATEGORY_ID,LP_ALT_BOM_DESG,P_ALT_RTG_DESG
175 FROM
176 CST_SC_ROLLUP_HISTORY CSRH
177 WHERE CSRH.ROLLUP_ID = P_ROLLUP_ID;
178 ELSE
179 L_RERUN_FLAG := 2;
180 L_STMT_NUM := 50;
181 SELECT
182 CST_LISTS_S.NEXTVAL
183 INTO P_ROLLUP_ID
184 FROM
185 DUAL;
186 L_STMT_NUM := 60;
187 IF (LP_REPORT_OPTION_TYPE <> -1) THEN
188 INSERT INTO CST_SC_ROLLUP_HISTORY
189 (ROLLUP_ID
190 ,DESCRIPTION
191 ,COST_TYPE_ID
192 ,BUY_COST_TYPE_ID
193 ,ORGANIZATION_ID
194 ,ASSIGNMENT_SET_ID
195 ,CONVERSION_TYPE
196 ,REPORT_LEVEL
197 ,EXPLOSION_LEVEL
198 ,ROLLUP_OPTION_TYPE
199 ,REPORT_OPTION_TYPE
200 ,RANGE_TYPE
201 ,REVISION_DATE
202 ,INC_UNIMP_ECN_FLAG
203 ,ENG_BILL_FLAG
204 ,QTY_PRECISION
205 ,ITEM_ID
206 ,CATEGORY_SET_ID
207 ,CATEGORY_ID
208 ,ALT_BOM_DESG
209 ,ALT_RTG_DESG
210 ,LAST_UPDATE_DATE
211 ,LAST_UPDATED_BY
212 ,LAST_UPDATE_LOGIN
213 ,CREATION_DATE
214 ,CREATED_BY
215 ,REQUEST_ID
216 ,PROGRAM_APPLICATION_ID
217 ,PROGRAM_ID
218 ,PROGRAM_UPDATE_DATE)
219 VALUES (P_ROLLUP_ID
220 ,LP_DESCRIPTION
221 ,LP_COST_TYPE_ID
222 ,LP_BUY_COST_TYPE_ID
223 ,LP_ORGANIZATION_ID
224 ,LP_ASSIGNMENT_SET_ID
225 ,P_CONVERSION_TYPE
226 ,LP_REPORT_LEVEL
227 ,LP_EXPLOSION_LEVEL
228 ,P_ROLLUP_OPTION_TYPE
229 ,LP_REPORT_OPTION_TYPE
230 ,LP_RANGE_TYPE
231 ,TO_DATE(LP_REVISION_DATE
232 ,'YYYY/MM/DD HH24:MI:SS')
233 ,LP_INC_UNIMP_ECN_FLAG
234 ,LP_ENG_BILL_FLAG
235 ,P_QTY_PRECISION
236 ,P_ITEM_ID
237 ,LP_CATEGORY_SET_ID
238 ,TO_NUMBER(P_CATEGORY_ID)
239 ,LP_ALT_BOM_DESG
240 ,P_ALT_RTG_DESG
241 ,SYSDATE
242 ,L_USER_ID
243 ,L_LOGIN_ID
244 ,SYSDATE
245 ,L_USER_ID
246 ,L_REQUEST_ID
247 ,L_PROG_APPL_ID
248 ,L_PROG_ID
249 ,SYSDATE);
250 END IF;
251 END IF;
252 IF (LP_REPORT_OPTION_TYPE = -1 AND P_ITEM_FROM IS NULL AND P_ITEM_TO IS NULL AND (P_CATEGORY_FROM IS NOT NULL OR P_CATEGORY_TO IS NOT NULL)) THEN
253 LP_RANGE_TYPE := 5;
254 END IF;
255 L_STMT_NUM := 62;
256 SELECT
257 DEFAULT_COST_TYPE_ID,
258 ORGANIZATION_ID
259 INTO P_DEFAULT_COST_TYPE_ID,L_ORGANIZATION_ID
260 FROM
261 CST_COST_TYPES
262 WHERE COST_TYPE_ID = LP_COST_TYPE_ID;
263 IF (LP_ASSIGNMENT_SET_ID IS NOT NULL AND L_ORGANIZATION_ID IS NOT NULL) THEN
264 /*SRW.MESSAGE(0
265 ,FND_MESSAGE.GET_STRING('BOM'
266 ,'CST_SC_ASSIGN_SET_COST_TYPE'))*/NULL;
267 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR'
268 ,FND_MESSAGE.GET_STRING('BOM'
269 ,'CST_SC_ASSIGN_SET_COST_TYPE'));
270 RETURN FALSE;
271 END IF;
272 L_STMT_NUM := 63;
273 IF LP_BUY_COST_TYPE_ID IS NULL THEN
274 SELECT
275 PRIMARY_COST_METHOD
276 INTO LP_BUY_COST_TYPE_ID
277 FROM
278 MTL_PARAMETERS
279 WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
280 ,lp_default_org_id);
281 END IF;
282 L_STMT_NUM := 70;
283 IF LP_REPORT_OPTION_TYPE = 2 THEN
284 /*SRW.SET_MAXROW('Q_ASSEMBLY'
285 ,0)*/NULL;
286 /*SRW.SET_MAXROW('Q_COMPONENTS'
287 ,0)*/NULL;
288 /*SRW.SET_MAXROW('Q_COSTS'
289 ,0)*/NULL;
290 /*SRW.SET_MAXROW('Q_SR_RCV'
291 ,0)*/NULL;
292 /*SRW.SET_MAXROW('Q_SR_SRC'
293 ,0)*/NULL;
294 /*SRW.SET_MAXROW('Q_SUMMARY'
295 ,0)*/NULL;
296 LP_REPORT_LEVEL := NULL;
297 END IF;
298 L_STMT_NUM := 80;
299 IF LP_CATEGORY_SET_ID IS NULL THEN
300 SELECT
301 CATEGORY_SET_ID
302 INTO LP_CATEGORY_SET_ID
303 FROM
304 MTL_DEFAULT_CATEGORY_SETS MDCS
305 WHERE MDCS.FUNCTIONAL_AREA_ID = 5;
306 END IF;
307 L_STMT_NUM := 90;
308 IF LP_REVISION_DATE IS NULL THEN
309 SELECT
310 TO_CHAR(SYSDATE
311 ,'YYYY/MM/DD HH24:MI:ss')
312 INTO LP_REVISION_DATE
313 FROM
314 DUAL;
315 END IF;
316 L_STMT_NUM := 100;
317 SELECT
318 CCT1.COST_TYPE,
319 DECODE(LP_ASSIGNMENT_SET_ID
320 ,NULL
321 ,' '
322 ,CCT2.COST_TYPE),
323 GDCT.USER_CONVERSION_TYPE
324 INTO P_COST_TYPE_NAME,P_BUY_COST_TYPE_NAME,P_CONVERSION_TYPE_NAME
325 FROM
326 CST_COST_TYPES CCT1,
327 CST_COST_TYPES CCT2,
328 GL_DAILY_CONVERSION_TYPES GDCT
329 WHERE CCT1.COST_TYPE_ID = LP_COST_TYPE_ID
330 AND CCT2.COST_TYPE_ID = LP_BUY_COST_TYPE_ID
331 AND GDCT.CONVERSION_TYPE = P_CONVERSION_TYPE;
332 L_STMT_NUM := 110;
333 IF LP_ASSIGNMENT_SET_ID IS NOT NULL THEN
334 SELECT
335 MAS.ASSIGNMENT_SET_NAME
336 INTO P_ASSIGNMENT_SET_NAME
337 FROM
338 MRP_ASSIGNMENT_SETS MAS
339 WHERE MAS.ASSIGNMENT_SET_ID = LP_ASSIGNMENT_SET_ID;
340 END IF;
341 L_STMT_NUM := 120;
342 IF TO_NUMBER(P_CATEGORY_ID) IS NOT NULL THEN
343 SELECT
344 SUBSTRB(MCV.CONCATENATED_SEGMENTS
345 ,1
346 ,2000)
347 INTO P_CATEGORY_NAME
348 FROM
349 MTL_CATEGORIES_KFV MCV
350 WHERE CATEGORY_ID = TO_NUMBER(P_CATEGORY_ID);
351 P_CATEGORY_FROM := P_CATEGORY_NAME;
352 P_CATEGORY_TO := P_CATEGORY_NAME;
353 END IF;
354 IF P_ITEM_ID IS NOT NULL THEN
355 L_STMT_NUM := 130;
356 SELECT
357 SUBSTRB(CONCATENATED_SEGMENTS
358 ,1
359 ,2000)
360 INTO P_ITEM_NAME
361 FROM
362 MTL_SYSTEM_ITEMS_KFV MSIV
363 WHERE MSIV.INVENTORY_ITEM_ID = P_ITEM_ID
364 AND MSIV.ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
365 ,lp_default_org_id);
366 END IF;
367 L_STMT_NUM := 140;
368 IF LP_REPORT_LEVEL IS NOT NULL THEN
369 LP_REPORT_LEVEL := LP_REPORT_LEVEL + 1;
370 END IF;
371 IF NVL(P_ROLLUP_OPTION_TYPE
372 ,2) = 2 THEN
373 LP_EXPLOSION_LEVEL := NULL;
374 ELSIF LP_REPORT_LEVEL IS NOT NULL AND LP_REPORT_LEVEL > LP_EXPLOSION_LEVEL + 1 THEN
375 LP_REPORT_LEVEL := LP_EXPLOSION_LEVEL + 1;
376 END IF;
377 L_STMT_NUM := 150;
378 IF L_RERUN_FLAG = 1 THEN
379 L_STMT_NUM := 160;
380 CSTPSCEX.SNAPSHOT_SC_BOM_STRUCTURES(P_ROLLUP_ID
381 ,LP_COST_TYPE_ID
382 ,LP_REPORT_LEVEL
383 ,TO_DATE(LP_REVISION_DATE
384 ,'YYYY/MM/DD HH24:MI:SS')
385 ,L_USER_ID
386 ,L_LOGIN_ID
387 ,L_REQUEST_ID
388 ,L_PROG_ID
389 ,L_PROG_APPL_ID
390 ,O_ERROR_CODE
391 ,O_ERROR_MSG
392 ,LP_REPORT_TYPE_TYPE);
393 ELSE
394 /*SRW.MESSAGE(0
395 ,'rollup_id = ' || P_ROLLUP_ID)*/NULL;
396 L_STMT_NUM := 170;
397 IF (LP_RANGE_TYPE = 2 AND P_ITEM_ID IS NOT NULL) THEN
398 L_STMT_NUM := 110;
399 INSERT INTO CST_SC_LISTS
400 (ROLLUP_ID
401 ,INVENTORY_ITEM_ID
402 ,ORGANIZATION_ID
403 ,LAST_UPDATE_DATE
404 ,LAST_UPDATED_BY
405 ,CREATION_DATE
406 ,CREATED_BY
407 ,LAST_UPDATE_LOGIN
408 ,REQUEST_ID
409 ,PROGRAM_APPLICATION_ID
410 ,PROGRAM_ID
411 ,PROGRAM_UPDATE_DATE)
412 SELECT
413 DISTINCT
414 P_ROLLUP_ID,
415 MSI.INVENTORY_ITEM_ID,
416 MSI.ORGANIZATION_ID,
417 sysdate,
418 L_USER_ID,
419 sysdate,
420 L_USER_ID,
421 L_LOGIN_ID,
422 L_REQUEST_ID,
423 L_PROG_APPL_ID,
424 L_PROG_ID,
425 sysdate
426 FROM
427 MTL_SYSTEM_ITEMS MSI,
428 BOM_PARAMETERS BP,
429 CST_ITEM_COSTS CIC,
430 MTL_PARAMETERS MP
431 WHERE MSI.ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
432 ,MSI.ORGANIZATION_ID)
433 AND MSI.INVENTORY_ITEM_ID = P_ITEM_ID
434 AND MSI.COSTING_ENABLED_FLAG = 'Y'
435 AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
436 AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
437 AND CIC.INVENTORY_ITEM_ID = P_ITEM_ID
438 AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
439 OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
440 AND not exists (
441 SELECT
442 'X'
443 FROM
444 CST_ITEM_COSTS CIC2
445 WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
446 AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
447 AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
448 OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
449 AND not exists (
450 SELECT
451 'X'
452 FROM
453 CST_ITEM_COSTS CIC3
454 WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
455 AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
456 AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
457 AND CIC.BASED_ON_ROLLUP_FLAG = 1
458 AND BP.organization_id (+) = MSI.ORGANIZATION_ID
459 AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
460 'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
461 ELSIF (LP_RANGE_TYPE = 5) THEN
462 L_STMT_NUM := 180;
463 INSERT INTO CST_SC_LISTS
464 (ROLLUP_ID
465 ,INVENTORY_ITEM_ID
466 ,ORGANIZATION_ID
467 ,LAST_UPDATE_DATE
468 ,LAST_UPDATED_BY
469 ,CREATION_DATE
470 ,CREATED_BY
471 ,LAST_UPDATE_LOGIN
472 ,REQUEST_ID
473 ,PROGRAM_APPLICATION_ID
474 ,PROGRAM_ID
475 ,PROGRAM_UPDATE_DATE)
476 SELECT
477 P_ROLLUP_ID,
478 MIC.INVENTORY_ITEM_ID,
479 MIC.ORGANIZATION_ID,
480 sysdate,
481 L_USER_ID,
482 sysdate,
483 L_USER_ID,
484 L_LOGIN_ID,
485 L_REQUEST_ID,
486 L_PROG_APPL_ID,
487 L_PROG_ID,
488 sysdate
489 FROM
490 MTL_ITEM_CATEGORIES MIC,
491 MTL_SYSTEM_ITEMS MSI,
492 BOM_PARAMETERS BP,
493 MTL_CATEGORIES_KFV MCV,
494 CST_ITEM_COSTS CIC,
495 MTL_PARAMETERS MP
496 WHERE LP_ORGANIZATION_ID is not null
497 AND MIC.ORGANIZATION_ID = LP_ORGANIZATION_ID
498 AND MIC.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
499 AND MIC.CATEGORY_ID = MCV.CATEGORY_ID
500 AND MCV.CONCATENATED_SEGMENTS >= DECODE(P_CATEGORY_FROM
501 ,NULL
502 ,MCV.CONCATENATED_SEGMENTS
503 ,P_CATEGORY_FROM)
504 AND MCV.CONCATENATED_SEGMENTS <= DECODE(P_CATEGORY_TO
505 ,NULL
506 ,MCV.CONCATENATED_SEGMENTS
507 ,P_CATEGORY_TO)
508 AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
509 AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
510 AND MSI.COSTING_ENABLED_FLAG = 'Y'
511 AND MP.ORGANIZATION_ID = LP_ORGANIZATION_ID
512 AND CIC.ORGANIZATION_ID = LP_ORGANIZATION_ID
513 AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
514 AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
515 OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
516 AND not exists (
517 SELECT
518 'X'
519 FROM
520 CST_ITEM_COSTS CIC2
521 WHERE CIC2.ORGANIZATION_ID = LP_ORGANIZATION_ID
522 AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
523 AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
524 OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
525 AND not exists (
526 SELECT
527 'X'
528 FROM
529 CST_ITEM_COSTS CIC3
530 WHERE CIC3.ORGANIZATION_ID = LP_ORGANIZATION_ID
531 AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
532 AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
533 AND CIC.BASED_ON_ROLLUP_FLAG = 1
534 AND BP.organization_id (+) = MSI.ORGANIZATION_ID
535 AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
536 'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+)
537 UNION
538 SELECT
539 P_ROLLUP_ID,
540 MIC.INVENTORY_ITEM_ID,
541 MIC.ORGANIZATION_ID,
542 sysdate,
543 L_USER_ID,
544 sysdate,
545 L_USER_ID,
546 L_LOGIN_ID,
547 L_REQUEST_ID,
548 L_PROG_APPL_ID,
549 L_PROG_ID,
550 sysdate
551 FROM
552 MTL_ITEM_CATEGORIES MIC,
553 MTL_SYSTEM_ITEMS MSI,
554 BOM_PARAMETERS BP,
555 MTL_CATEGORIES_KFV MCV,
556 CST_ITEM_COSTS CIC,
557 MTL_PARAMETERS MP
558 WHERE LP_ORGANIZATION_ID is null
559 AND MIC.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
560 AND MIC.CATEGORY_ID = MCV.CATEGORY_ID
561 AND MCV.CONCATENATED_SEGMENTS >= DECODE(P_CATEGORY_FROM
562 ,NULL
563 ,MCV.CONCATENATED_SEGMENTS
564 ,P_CATEGORY_FROM)
565 AND MCV.CONCATENATED_SEGMENTS <= DECODE(P_CATEGORY_TO
566 ,NULL
567 ,MCV.CONCATENATED_SEGMENTS
568 ,P_CATEGORY_TO)
569 AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
570 AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
571 AND MSI.COSTING_ENABLED_FLAG = 'Y'
572 AND MP.ORGANIZATION_ID = MIC.ORGANIZATION_ID
573 AND CIC.ORGANIZATION_ID = MIC.ORGANIZATION_ID
574 AND CIC.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
575 AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
576 OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
577 AND not exists (
578 SELECT
579 'X'
580 FROM
581 CST_ITEM_COSTS CIC2
582 WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
583 AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
584 AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
585 OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
586 AND not exists (
587 SELECT
588 'X'
589 FROM
590 CST_ITEM_COSTS CIC3
591 WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
592 AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
593 AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
594 AND CIC.BASED_ON_ROLLUP_FLAG = 1
595 AND BP.organization_id (+) = MSI.ORGANIZATION_ID
596 AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
597 'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
598 ELSE
599 L_STMT_NUM := 190;
600 INSERT INTO CST_SC_LISTS
601 (ROLLUP_ID
602 ,INVENTORY_ITEM_ID
603 ,ORGANIZATION_ID
604 ,LAST_UPDATE_DATE
605 ,LAST_UPDATED_BY
606 ,CREATION_DATE
607 ,CREATED_BY
608 ,LAST_UPDATE_LOGIN
609 ,REQUEST_ID
610 ,PROGRAM_APPLICATION_ID
611 ,PROGRAM_ID
612 ,PROGRAM_UPDATE_DATE)
613 SELECT
614 P_ROLLUP_ID,
615 MSI.INVENTORY_ITEM_ID,
616 MSI.ORGANIZATION_ID,
617 sysdate,
618 L_USER_ID,
619 sysdate,
620 L_USER_ID,
621 L_LOGIN_ID,
622 L_REQUEST_ID,
623 L_PROG_APPL_ID,
624 L_PROG_ID,
625 sysdate
626 FROM
627 MTL_SYSTEM_ITEMS_KFV MSI,
628 BOM_PARAMETERS BP,
629 CST_ITEM_COSTS CIC,
630 MTL_PARAMETERS MP
631 WHERE LP_ORGANIZATION_ID is not null
632 AND MSI.ORGANIZATION_ID = LP_ORGANIZATION_ID
633 AND MSI.CONCATENATED_SEGMENTS >= DECODE(P_ITEM_FROM
634 ,NULL
635 ,MSI.CONCATENATED_SEGMENTS
636 ,P_ITEM_FROM)
637 AND MSI.CONCATENATED_SEGMENTS <= DECODE(P_ITEM_TO
638 ,NULL
639 ,MSI.CONCATENATED_SEGMENTS
640 ,P_ITEM_TO)
641 AND MSI.COSTING_ENABLED_FLAG = 'Y'
642 AND MP.ORGANIZATION_ID = LP_ORGANIZATION_ID
643 AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
644 AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
645 AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
646 OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
647 AND not exists (
648 SELECT
649 'X'
650 FROM
651 CST_ITEM_COSTS CIC2
652 WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
653 AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
654 AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
655 OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
656 AND not exists (
657 SELECT
658 'X'
659 FROM
660 CST_ITEM_COSTS CIC3
661 WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
662 AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
663 AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
664 AND CIC.BASED_ON_ROLLUP_FLAG = 1
665 AND NVL(CIC.ITEM_COST
666 ,0) = DECODE(LP_RANGE_TYPE
667 ,4
668 ,0
669 ,NVL(CIC.ITEM_COST
670 ,0))
671 AND BP.organization_id (+) = MSI.ORGANIZATION_ID
672 AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
673 'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+)
674 UNION
675 SELECT
676 P_ROLLUP_ID,
677 MSI.INVENTORY_ITEM_ID,
678 MSI.ORGANIZATION_ID,
679 sysdate,
680 L_USER_ID,
681 sysdate,
682 L_USER_ID,
683 L_LOGIN_ID,
684 L_REQUEST_ID,
685 L_PROG_APPL_ID,
686 L_PROG_ID,
687 sysdate
688 FROM
689 MTL_SYSTEM_ITEMS_KFV MSI,
690 BOM_PARAMETERS BP,
691 CST_ITEM_COSTS CIC,
692 MTL_PARAMETERS MP
693 WHERE LP_ORGANIZATION_ID is null
694 AND MSI.CONCATENATED_SEGMENTS >= DECODE(P_ITEM_FROM
695 ,NULL
696 ,MSI.CONCATENATED_SEGMENTS
697 ,P_ITEM_FROM)
698 AND MSI.CONCATENATED_SEGMENTS <= DECODE(P_ITEM_TO
699 ,NULL
700 ,MSI.CONCATENATED_SEGMENTS
701 ,P_ITEM_TO)
702 AND MSI.COSTING_ENABLED_FLAG = 'Y'
703 AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
704 AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
705 AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
706 AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
707 OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
708 AND not exists (
709 SELECT
710 'X'
711 FROM
712 CST_ITEM_COSTS CIC2
713 WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
714 AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
715 AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
716 OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
717 AND not exists (
718 SELECT
719 'X'
720 FROM
721 CST_ITEM_COSTS CIC3
722 WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
723 AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
724 AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
725 AND CIC.BASED_ON_ROLLUP_FLAG = 1
726 AND NVL(CIC.ITEM_COST
727 ,0) = DECODE(LP_RANGE_TYPE
728 ,4
729 ,0
730 ,NVL(CIC.ITEM_COST
731 ,0))
732 AND BP.organization_id (+) = MSI.ORGANIZATION_ID
733 AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
734 'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
735 END IF;
736 L_STMT_NUM := 200;
737 IF NVL(LP_INC_UNIMP_ECN_FLAG
738 ,2) = 1 THEN
739 L_EXCLUDE_UNIMP_ECO := 2;
740 ELSE
741 L_EXCLUDE_UNIMP_ECO := 1;
742 END IF;
743 L_STMT_NUM := 210;
744 IF NVL(LP_ENG_BILL_FLAG
745 ,2) = 1 THEN
746 L_EXCLUDE_ENG := 2;
747 ELSE
748 L_EXCLUDE_ENG := 1;
749 END IF;
750 L_STMT_NUM := 210;
751 IF (P_LOT_SIZE_OPTION IS NOT NULL) THEN
752 SELECT
753 MEANING
754 INTO P_LOT_SIZE_OPTION_NAME
755 FROM
756 MFG_LOOKUPS
757 WHERE LOOKUP_TYPE = 'CST_SC_LOT_OPTION'
758 AND LOOKUP_CODE = P_LOT_SIZE_OPTION;
759 END IF;
760 L_STMT_NUM := 220;
761 SELECT
762 sysdate
763 INTO L_TIMESTAMP
764 FROM
765 DUAL;
766 /*SRW.MESSAGE(0
767 ,'Before CSTPSCEX.supply_chain_rollup ' || TO_CHAR(L_TIMESTAMP
768 ,'YYYY/MM/DD HH24:MI:SS'))*/NULL;
769 L_STMT_NUM := 230;
770 CSTPSCEX.SUPPLY_CHAIN_ROLLUP(P_ROLLUP_ID
771 ,LP_EXPLOSION_LEVEL
772 ,LP_REPORT_LEVEL
773 ,LP_ASSIGNMENT_SET_ID
774 ,P_CONVERSION_TYPE
775 ,LP_COST_TYPE_ID
776 ,LP_BUY_COST_TYPE_ID
777 ,TO_DATE(LP_REVISION_DATE
778 ,'YYYY/MM/DD HH24:MI:SS')
779 ,L_EXCLUDE_UNIMP_ECO
780 ,L_EXCLUDE_ENG
781 ,LP_ALT_BOM_DESG
782 ,P_ALT_RTG_DESG
783 ,P_LOCK_FLAG
784 ,L_USER_ID
785 ,L_LOGIN_ID
786 ,L_REQUEST_ID
787 ,L_PROG_ID
788 ,L_PROG_APPL_ID
789 ,O_ERROR_CODE
790 ,O_ERROR_MSG
791 ,P_LOT_SIZE_OPTION
792 ,P_LOT_SIZE_SETTING
793 ,LP_REPORT_OPTION_TYPE
794 ,LP_REPORT_TYPE_TYPE
795 ,P_BUY_COST_DETAIL);
796 L_STMT_NUM := 240;
797 SELECT
798 sysdate
799 INTO L_TIMESTAMP
800 FROM
801 DUAL;
802 /*SRW.MESSAGE(0
803 ,'After CSTPSCEX.supply_chain_rollup ' || TO_CHAR(L_TIMESTAMP
804 ,'YYYY/MM/DD HH24:MI:SS'))*/NULL;
805 L_STMT_NUM := 250;
806 SELECT
807 count(*)
808 INTO L_ROWS_UNEXPLODED
809 FROM
810 CST_SC_BOM_EXPLOSION CSBE
811 WHERE CSBE.ROLLUP_ID = P_ROLLUP_ID
812 AND CSBE.DELETED_FLAG <> 'Y';
813 L_STMT_NUM := 260;
814 IF L_ROWS_UNEXPLODED = 0 THEN
815 /*SRW.MESSAGE(0
816 ,'No loop found')*/NULL;
817 ELSE
818 /*SRW.MESSAGE(0
819 ,'Loop found: ' || L_ROWS_UNEXPLODED || ' rows unexploded')*/NULL;
820 FOR cur IN LOOP_ROWS(p_rollup_id) LOOP
821 /*SRW.MESSAGE(0
822 ,'Assmbly Item ' || CUR.ASSEMBLY_ITEM_ID || ' Org ' || CUR.ASSEMBLY_ORGANIZATION_ID || ' Component Item ' || CUR.COMPONENT_ITEM_ID || ' Org ' || CUR.COMPONENT_ORGANIZATION_ID)*/NULL;
823 END LOOP;
824 END IF;
825 END IF;
826 L_STMT_NUM := 270;
827 SELECT
828 count(SOB.CURRENCY_CODE)
829 INTO P_NUM_CURRENCIES
830 FROM
831 CST_SC_LOW_LEVEL_CODES CSLLC,
832 HR_ORGANIZATION_INFORMATION HOI,
833 GL_LEDGERS SOB
834 WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
835 AND HOI.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID
836 AND HOI.ORG_INFORMATION_CONTEXT = 'Acounting Information'
837 AND SOB.LEDGER_ID = HOI.ORG_INFORMATION1;
838 L_STMT_NUM := 272;
839 SELECT
840 ALTERNATE_BOM_DESIGNATOR
841 INTO L_SNAPSHOT_DESIGNATOR
842 FROM
843 CST_COST_TYPES
844 WHERE COST_TYPE_ID = LP_COST_TYPE_ID;
845 IF L_SNAPSHOT_DESIGNATOR IS NOT NULL THEN
846 FOR orgs IN NO_ALT_ORGS(l_snapshot_designator) LOOP
847 SELECT
848 ORGANIZATION_CODE
849 INTO L_NO_ALT_ORG
850 FROM
851 MTL_PARAMETERS
852 WHERE ORGANIZATION_ID = ORGS.ORGANIZATION_ID;
853 /*SRW.MESSAGE(0
854 ,'Alternate ' || L_SNAPSHOT_DESIGNATOR || ' is not defined in organziation ' || L_NO_ALT_ORG)*/NULL;
855 END LOOP;
856 END IF;
857 L_STMT_NUM := 275;
858 FOR orgs IN NO_BOM_ORGS LOOP
859 SELECT
860 ORGANIZATION_CODE
861 INTO L_NO_BOM_ORG
862 FROM
863 MTL_PARAMETERS
864 WHERE ORGANIZATION_ID = ORGS.ORGANIZATION_ID;
865 /*SRW.MESSAGE(0
866 ,'Org: ' || L_NO_BOM_ORG || '. ')*/NULL;
867 END LOOP;
868 IF O_ERROR_CODE = 8888 THEN
869 /*SRW.MESSAGE(0
870 ,'Alternate Designaor can not be NULL for the specified Cost Type ')*/NULL;
871 END IF;
872 L_STMT_NUM := 280;
873 IF O_ERROR_CODE <> 0 THEN
874 L_STR_POS := 0;
875 WHILE L_STR_POS < LENGTHB(O_ERROR_MSG) LOOP
876
877 /*SRW.MESSAGE(0
878 ,SUBSTRB(O_ERROR_MSG
879 ,L_STR_POS + 1
880 ,L_STR_POS + 180))*/NULL;
881 L_STR_POS := L_STR_POS + 180;
882 END LOOP;
883 ROLLBACK;
884 RETURN FALSE;
885 ELSE
886 RETURN TRUE;
887 END IF;
888 LP_CATEGORY_FROM:=P_CATEGORY_FROM;
889 LP_CATEGORY_TO:=P_CATEGORY_TO;
890 EXCEPTION
891 WHEN OTHERS THEN
892 /*SRW.MESSAGE('2001'
893 ,L_STMT_NUM || ' ' || SUBSTRB(SQLERRM
894 ,1
895 ,180))*/NULL;
896 ROLLBACK;
897 RETURN (FALSE);
898 END BEFOREREPORT;
899
900 FUNCTION CF_S_DIFFERENCEFORMULA(S_FROZEN_VALUE IN NUMBER
901 ,S_REPORT_VALUE IN NUMBER) RETURN NUMBER IS
902 BEGIN
903 RETURN (S_FROZEN_VALUE - S_REPORT_VALUE);
904 END CF_S_DIFFERENCEFORMULA;
905
906 FUNCTION CF_S_PERCENTFORMULA(S_FROZEN_VALUE IN NUMBER
907 ,S_REPORT_VALUE IN NUMBER
908 ,CF_S_DIFFERENCE IN NUMBER) RETURN NUMBER IS
909 BEGIN
910 IF (S_FROZEN_VALUE = 0 OR S_REPORT_VALUE = 0) THEN
911 RETURN (0);
912 ELSE
913 IF CF_S_DIFFERENCE > 0.0 THEN
914 RETURN (ROUND(100 * CF_S_DIFFERENCE / S_REPORT_VALUE
915 ,1));
916 ELSE
917 RETURN (ROUND(100 * CF_S_DIFFERENCE / S_FROZEN_VALUE
918 ,1));
919 END IF;
920 END IF;
921 END CF_S_PERCENTFORMULA;
922
923 FUNCTION CF_S_S_SRC_TOTALFORMULA(S_S_PERCENT IN NUMBER
924 ,S_S_EFFECTIVE_ITEM_COST IN NUMBER) RETURN NUMBER IS
925 BEGIN
926 IF NVL(S_S_PERCENT
927 ,0) = 0 THEN
928 RETURN 0;
929 END IF;
930 RETURN NVL(S_S_EFFECTIVE_ITEM_COST
931 ,0) / (S_S_PERCENT / 100);
932 END CF_S_S_SRC_TOTALFORMULA;
933
934 FUNCTION CF_S_R_USER_DEFINED_COSTFORMUL(S_R_ITEM_COST IN NUMBER
935 ,CS_SUM_EFFECTIVE_COST IN NUMBER) RETURN NUMBER IS
936 BEGIN
937 RETURN NVL(S_R_ITEM_COST
938 ,0) - NVL(CS_SUM_EFFECTIVE_COST
939 ,0);
940 END CF_S_R_USER_DEFINED_COSTFORMUL;
941
942 FUNCTION CF_S_S_PERCENT_COSTFORMULA(S_R_ITEM_COST IN NUMBER
943 ,S_S_EFFECTIVE_ITEM_COST IN NUMBER) RETURN NUMBER IS
944 BEGIN
945 IF S_R_ITEM_COST = 0 THEN
946 RETURN 0;
947 END IF;
948 RETURN S_S_EFFECTIVE_ITEM_COST / S_R_ITEM_COST * 100;
949 END CF_S_S_PERCENT_COSTFORMULA;
950
951 FUNCTION CF_S_R_USER_DEFINED_PERCENTFOR(S_R_ITEM_COST IN NUMBER
952 ,CF_S_R_USER_DEFINED_COST IN NUMBER) RETURN NUMBER IS
953 BEGIN
954 IF S_R_ITEM_COST = 0 THEN
955 RETURN 0;
956 END IF;
957 RETURN CF_S_R_USER_DEFINED_COST / S_R_ITEM_COST * 100;
958 END CF_S_R_USER_DEFINED_PERCENTFOR;
959
960 FUNCTION CF_SUM_EXT_COSTFORMULA0009(CS_SUM_COMP_EXT_COST IN NUMBER
961 ,CS_SUM_RES_EXT_COST IN NUMBER) RETURN NUMBER IS
962 BEGIN
963 RETURN NVL(CS_SUM_COMP_EXT_COST
964 ,0) + NVL(CS_SUM_RES_EXT_COST
965 ,0);
966 END CF_SUM_EXT_COSTFORMULA0009;
967
968 FUNCTION CF_S_SUM_PERCENTFORMULA0011(CS_S_SUM_DIFFERENCE IN NUMBER) RETURN NUMBER IS
969 BEGIN
970 IF CS_S_SUM_DIFFERENCE = 0 THEN
971 RETURN 0;
972 END IF;
973 RETURN 100;
974 END CF_S_SUM_PERCENTFORMULA0011;
975
976 FUNCTION AFTERREPORT RETURN BOOLEAN IS
977 L_DEFAULT_MATL_OVHD NUMBER(15);
978 L_ORGANIZATION_CODE VARCHAR2(3);
979 CONC_STATUS BOOLEAN;
980 RETURN_STATUS NUMBER := 1;
981 CURSOR NO_DEF_OVHD_ORGS IS
982 SELECT
983 DISTINCT
984 ORGANIZATION_ID
985 FROM
986 CST_SC_LOW_LEVEL_CODES
987 WHERE ROLLUP_ID = P_ROLLUP_ID;
988 BEGIN
989 FOR organizations IN NO_DEF_OVHD_ORGS LOOP
990 SELECT
991 NVL(DEFAULT_MATL_OVHD_COST_ID
992 ,0),
993 ORGANIZATION_CODE
994 INTO L_DEFAULT_MATL_OVHD,L_ORGANIZATION_CODE
995 FROM
996 MTL_PARAMETERS
997 WHERE ORGANIZATION_ID = ORGANIZATIONS.ORGANIZATION_ID;
998 IF (L_DEFAULT_MATL_OVHD = 0) THEN
999 FND_MESSAGE.SET_NAME('BOM'
1000 ,'CST_SC_DEF_MATL_OVHD_SUBELEM');
1001 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE'
1002 ,L_ORGANIZATION_CODE);
1003 /*SRW.MESSAGE(0
1004 ,FND_MESSAGE.GET)*/NULL;
1005 END IF;
1006 END LOOP;
1007 IF (LP_REPORT_OPTION_TYPE <> 3) THEN
1008 COMMIT;
1009 ELSE
1010 ROLLBACK;
1011 END IF;
1012 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
1013 RETURN (TRUE);
1014 END AFTERREPORT;
1015
1016 FUNCTION AFTERPFORM RETURN BOOLEAN IS
1017 BEGIN
1018 RETURN (TRUE);
1019 END AFTERPFORM;
1020
1021 END BOM_CSTRSCCR_XMLP_PKG;
1022
1023