[Home] [Help]
PACKAGE BODY: APPS.BOM_BOMRBOMS_XMLP_PKG
Source
1 PACKAGE BODY BOM_BOMRBOMS_XMLP_PKG AS
2 /* $Header: BOMRBOMSB.pls 120.5 2008/01/28 07:30:12 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 ecode NUMBER(38);
5 emesg VARCHAR2(250);
6 BEGIN
7 DECLARE
8 L_ORGANIZATION_NAME VARCHAR2(240);
9 L_EXPLODE_OPTION VARCHAR2(80);
10 L_RANGE_OPTION VARCHAR2(80);
11 L_SPECIFIC_ITEM VARCHAR2(245);
12 L_CATEGORY_SET VARCHAR2(30);
13 L_YES VARCHAR2(80);
14 L_NO VARCHAR2(80);
15 L_ALT_OPTION VARCHAR2(80);
16 L_ORDER_BY VARCHAR2(80);
17 L_SEQ_ID NUMBER;
18 L_STR VARCHAR2(2000);
19 L_BOM_OR_ENG NUMBER;
20 L_ERR_MSG VARCHAR2(80);
21 L_ERR_CODE NUMBER;
22 EXPLODER_ERROR EXCEPTION;
23 LOOP_ERROR EXCEPTION;
24 ITEM_ID_NULL EXCEPTION;
25 TABLE_NAME VARCHAR2(20);
26 T_ORG_CODE_LIST INV_ORGHIERARCHY_PVT.ORGID_TBL_TYPE;
27 L_ORG_NAME VARCHAR2(60);
28 FLAG BOOLEAN;
29 N NUMBER := 0;
30 L_ORG_ID NUMBER;
31 CURSOR_NAME INTEGER;
32 ROWS_PROCESSED INTEGER;
33 P_ASS_BETWEEN VARCHAR2(1000);
34 P_CAT_BETWEEN VARCHAR2(1000);
35 BEGIN
36 TABLE_NAME := 'Begin_trigger';
37 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
38 LP_ORGANIZATION_ID := P_ORGANIZATION_ID;
39 LP_REVISION_DATE := P_REVISION_DATE;
40
41
42 LP_ALL_ORGS := P_ALL_ORGS;
43 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
44 TABLE_NAME := 'Trace';
45 TABLE_NAME := 'Check_specific';
46
47
48 IF (P_RANGE_OPTION_TYPE = 1) AND (P_ITEM_ID IS NULL) THEN
49 FND_MESSAGE.SET_NAME('null'
50 ,'MFG_REQUIRED_VALUE');
51 FND_MESSAGE.SET_TOKEN('ENTITY'
52 ,'specific item');
53 P_MSG_BUF := FND_MESSAGE.GET;
54 /*SRW.MESSAGE('999'
55 ,P_MSG_BUF)*/NULL;
56 RAISE ITEM_ID_NULL;
57 END IF;
58
59
60 TABLE_NAME := 'Supress_detail';
61
62 IF P_PRINT_OPTION1_FLAG = 2 THEN
63 /*SRW.SET_MAXROW('Q_ELEMENT'
64 ,0)*/NULL;
65 END IF;
66
67 TABLE_NAME := 'Ref_desg';
68
69
70 IF P_PRINT_OPTION4_FLAG = 2 THEN
71 /*SRW.SET_MAXROW('Q_REF_DESIG'
72 ,0)*/NULL;
73 END IF;
74
75 TABLE_NAME := 'Sub_comps';
76
77 IF P_PRINT_OPTION5_FLAG = 2 THEN
78 /*SRW.SET_MAXROW('Q_SUBS_COMPO'
79 ,0)*/NULL;
80 END IF;
81
82 TABLE_NAME := 'Org_define';
83
84
85 SELECT
86 O.ORGANIZATION_NAME
87 INTO L_ORGANIZATION_NAME
88 FROM
89 ORG_ORGANIZATION_DEFINITIONS O
90 WHERE O.ORGANIZATION_ID = LP_ORGANIZATION_ID;
91
92
93 TABLE_NAME := 'Category_sets';
94
95 IF P_CATEGORY_SET_ID > 0 THEN
96 SELECT
97 CATEGORY_SET_NAME
98 INTO L_CATEGORY_SET
99 FROM
100 MTL_CATEGORY_SETS
101 WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID;
102
103 P_CATEGORY_SET := L_CATEGORY_SET;
104 END IF;
105
106 TABLE_NAME := 'Item_flexfields';
107
108 IF P_ITEM_ID > 0 THEN
109 SELECT
110 ITEM_NUMBER
111 INTO L_SPECIFIC_ITEM
112 FROM
113 MTL_ITEM_FLEXFIELDS
114 WHERE ITEM_ID = P_ITEM_ID
115 AND ORGANIZATION_ID = LP_ORGANIZATION_ID;
116
117 P_SPECIFIC_ITEM := L_SPECIFIC_ITEM;
118 END IF;
119
120 TABLE_NAME := 'Lookups1';
121
122 SELECT
123 SUBSTR(L1.MEANING
124 ,1
125 ,40),
126 SUBSTR(L2.MEANING
127 ,1
128 ,40),
129 SUBSTR(L3.MEANING
130 ,1
131 ,40),
132 SUBSTR(L4.MEANING
133 ,1
134 ,40)
135 INTO L_EXPLODE_OPTION,L_ALT_OPTION,L_RANGE_OPTION,L_ORDER_BY
136 FROM
137 MFG_LOOKUPS L1,
138 MFG_LOOKUPS L2,
139 MFG_LOOKUPS L3,
140 MFG_LOOKUPS L4
141 WHERE L1.LOOKUP_TYPE = 'BOM_INQUIRY_DISPLAY_TYPE'
142 AND L1.LOOKUP_CODE = P_EXPLODE_OPTION_TYPE
143 AND L2.LOOKUP_TYPE = 'MCG_AUTOLOAD_OPTION'
144 AND L2.LOOKUP_CODE = P_ALT_OPTION_TYPE
145 AND L3.LOOKUP_TYPE = 'BOM_SELECTION_TYPE'
146 AND L3.LOOKUP_CODE = P_RANGE_OPTION_TYPE
147 AND L4.LOOKUP_TYPE = 'BOM_BILL_SORT_ORDER_TYPE'
148 AND L4.LOOKUP_CODE = P_ORDER_BY_TYPE;
149
150 TABLE_NAME := 'Lookups2';
151 SELECT
152 SUBSTR(L1.MEANING
153 ,1
154 ,4),
155 SUBSTR(L2.MEANING
156 ,1
157 ,4)
158 INTO L_YES,L_NO
159 FROM
160 MFG_LOOKUPS L1,
161 MFG_LOOKUPS L2
162 WHERE L1.LOOKUP_TYPE = 'SYS_YES_NO'
163 AND L1.LOOKUP_CODE = 1
164 AND L2.LOOKUP_TYPE = 'SYS_YES_NO'
165 AND L2.LOOKUP_CODE = 2;
166
167 P_YES := L_YES;
168 P_NO := L_NO;
169
170 TABLE_NAME := 'Print_option';
171
172 IF (P_PRINT_OPTION1_FLAG = 1) THEN
173 P_PRINT_OPTION1 := L_YES;
174 ELSE
175 P_PRINT_OPTION1 := L_NO;
176 END IF;
177
178 IF (P_PRINT_OPTION2_FLAG = 1) THEN
179 P_PRINT_OPTION2 := L_YES;
180 ELSE
181 P_PRINT_OPTION2 := L_NO;
182 END IF;
183
184 IF P_PRINT_OPTION3_FLAG = 1 THEN
185 P_PRINT_OPTION3 := L_YES;
186 ELSE
187 P_PRINT_OPTION3 := L_NO;
188 END IF;
189
190 IF P_PRINT_OPTION4_FLAG = 1 THEN
191 P_PRINT_OPTION4 := L_YES;
192 ELSE
193 P_PRINT_OPTION4 := L_NO;
194 END IF;
195
196 IF P_PRINT_OPTION5_FLAG = 1 THEN
197 P_PRINT_OPTION5 := L_YES;
198 ELSE
199 P_PRINT_OPTION5 := L_NO;
200 END IF;
201
202 IF P_PRINT_OPTION6_FLAG = 1 THEN
203 P_PRINT_OPTION6 := L_YES;
204 ELSE
205 P_PRINT_OPTION6 := L_NO;
206 END IF;
207
208 IF P_FULL_DESCRIPTION = 1 THEN
209 P_FULL_DESC_CHOICE := L_YES;
210 ELSE
211 P_FULL_DESC_CHOICE := L_NO;
212 END IF;
213
214 IF P_IMPL_FLAG = 1 THEN
215 P_IMPL := L_YES;
216 ELSE
217 P_IMPL := L_NO;
218 END IF;
219
220 IF P_PLAN_FACTOR_FLAG = 1 THEN
221 P_PLAN_FACTOR := L_YES;
222 ELSE
223 P_PLAN_FACTOR := L_NO;
224 END IF;
225
226 TABLE_NAME := 'Assign_values';
227
228 P_ORGANIZATION_NAME := L_ORGANIZATION_NAME;
229 P_EXPLODE_OPTION := L_EXPLODE_OPTION;
230 P_RANGE_OPTION := L_RANGE_OPTION;
231 P_ALT_OPTION := L_ALT_OPTION;
232 P_ORDER_BY := L_ORDER_BY;
233
234 IF P_BOM_OR_ENG = 'BOM' THEN
235 L_BOM_OR_ENG := 1;
236 ELSE
237 L_BOM_OR_ENG := 2;
238 END IF;
239
240 TABLE_NAME := 'Org Hierarchy';
241
242 IF P_ALL_ORGS = 1 THEN
243 FOR C1 IN (SELECT
244 ORGANIZATION_ID
245 FROM
246 MTL_PARAMETERS MP
247 WHERE MASTER_ORGANIZATION_ID = (
248 SELECT
249 MASTER_ORGANIZATION_ID
250 FROM
251 MTL_PARAMETERS
252 WHERE ORGANIZATION_ID = LP_ORGANIZATION_ID )
253 AND MP.ORGANIZATION_ID IN (
254 SELECT
255 ORGANIZATION_ID
256 FROM
257 ORG_ACCESS_VIEW
258 WHERE RESPONSIBILITY_ID = FND_PROFILE.VALUE('RESP_ID')
259 AND RESP_APPLICATION_ID = FND_PROFILE.VALUE('RESP_APPL_ID') )) LOOP
260 N := N + 1;
261 T_ORG_CODE_LIST(N) := C1.ORGANIZATION_ID;
262 END LOOP;
263 LP_ALL_ORGS := 'Yes';
264
265 ELSIF P_ALL_ORGS = 2 THEN
266 IF P_ORG_HIERARCHY IS NOT NULL THEN
267 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(P_ORG_HIERARCHY
268 ,LP_ORGANIZATION_ID
269 ,T_ORG_CODE_LIST);
270 ELSIF P_ORG_HIERARCHY IS NULL THEN
271 T_ORG_CODE_LIST(1) := LP_ORGANIZATION_ID;
272 END IF;
273 LP_ALL_ORGS := 'No';
274
275 ELSE
276 T_ORG_CODE_LIST(1) := LP_ORGANIZATION_ID;
277 END IF;
278
279 SELECT
280 BOM_LISTS_S.NEXTVAL
281 INTO P_SEQUENCE_ID1
282 FROM
283 DUAL;
284
285 FOR I IN T_ORG_CODE_LIST.FIRST .. T_ORG_CODE_LIST.LAST LOOP
286 INSERT INTO BOM_LISTS
287 (ORGANIZATION_ID
288 ,SEQUENCE_ID
289 ,ALTERNATE_DESIGNATOR)
290 VALUES (T_ORG_CODE_LIST(I)
291 ,P_SEQUENCE_ID1
292 ,I);
293 END LOOP;
294
295 FOR I IN T_ORG_CODE_LIST.FIRST .. T_ORG_CODE_LIST.LAST LOOP
296 LP_ORGANIZATION_ID := T_ORG_CODE_LIST(I);
297 TABLE_NAME := 'Select_sequence';
298 SELECT
299 BOM_LISTS_S.NEXTVAL
300 INTO L_SEQ_ID
301 FROM
302 DUAL;
303
304 P_SEQUENCE_ID := L_SEQ_ID;
305 TABLE_NAME := 'Locator_flex';
306 TABLE_NAME := 'Item_flex';
307
308 IF P_RANGE_OPTION_TYPE = 2 THEN
309 IF (P_ITEM_FROM IS NOT NULL) THEN
310 IF (P_ITEM_TO IS NOT NULL) THEN
311 NULL;
312 ELSE
313 NULL;
314 END IF;
315 ELSE
316 IF (P_ITEM_TO IS NOT NULL) THEN
317 NULL;
318 END IF;
319 END IF;
320 TABLE_NAME := 'Category_flex';
321 IF (P_CATEGORY_FROM IS NOT NULL) THEN
322 IF (P_CATEGORY_TO IS NOT NULL) THEN
323 NULL;
324 ELSE
325 NULL;
326 END IF;
327 ELSE
328 IF (P_CATEGORY_TO IS NOT NULL) THEN
329 NULL;
330 END IF;
331 END IF;
332 END IF;
333
334 TABLE_NAME := 'bom_lists';
335 CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
336 /***** Commented out to fix bug #6769853 ********
337 P_ASS_BETWEEN := BOMRBOMS_NEW.P_ASS_BETWEEN;
338 P_CAT_BETWEEN := BOMRBOMS_NEW.P_CAT_BETWEEN;
339 ***** Commented out to fix bug #6769853 ********/
340 P_ASS_BETWEEN := BOM_BOMRBOMS_XMLP_PKG.P_ASS_BETWEEN;
341 P_CAT_BETWEEN := BOM_BOMRBOMS_XMLP_PKG.P_CAT_BETWEEN;
342
343 L_STR := 'INSERT INTO BOM_LISTS (SEQUENCE_ID,ASSEMBLY_ITEM_ID,ALTERNATE_DESIGNATOR) ';
344 TABLE_NAME := 'l_string';
345
346 IF P_RANGE_OPTION_TYPE = 1 THEN
347 FLAG := TRUE;
348 L_STR := L_STR || 'SELECT DISTINCT ' || 'TO_CHAR(:b_l_seq_id)' || ',:b_P_ITEM_ID' || ',bbom.alternate_bom_designator
349 FROM bom_bill_of_materials bbom
350 WHERE bbom.assembly_item_id = ' || ':b_P_ITEM_ID' || ' and bbom.organization_id =' || ':b_P_ORGANIZATION_ID';
351
352
353 ELSE
354 FLAG := FALSE;
355 L_STR := L_STR || ' SELECT
356 DISTINCT ' || ':b_l_seq_id' || ',
357 msi.inventory_item_id,
358 bbom.alternate_bom_designator
359 FROM mtl_item_categories mic,
360 mtl_system_items msi,
361 mtl_categories mc,
362 bom_bill_of_materials bbom
363 WHERE ' || P_ASS_BETWEEN || '
364 AND msi.inventory_item_id = mic.inventory_item_id
365 AND msi.organization_id =
366 ' || ':b_P_ORGANIZATION_ID' || '
367 AND mic.organization_id =
368 ' || ':b_P_ORGANIZATION_ID' || '
369 AND mic.category_id = mc.category_id
370 AND mic.category_set_id =
371 ' || ':b_P_CATEGORY_SET_ID' || '
372 AND mc.structure_id =
373 ' || ':b_P_CATEGORY_STRUCTURE_ID' || '
374 AND ' || P_CAT_BETWEEN || '
375 AND msi.inventory_item_id = bbom.assembly_item_id
376 AND msi.organization_id = bbom.organization_id
377 AND msi.bom_enabled_flag = ''Y''';
378 END IF;
379
380 L_STR := L_STR || ' AND ( (' || 'TO_CHAR(:b_P_ALT_OPTION_TYPE)' || ' = 1)
381 OR
382 (' || 'TO_CHAR(:b_P_ALT_OPTION_TYPE)' || ' = 2
383 AND bbom.alternate_bom_designator IS NULL)
384 OR
385 (' || 'TO_CHAR(:b_P_ALT_OPTION_TYPE)' || ' = 3
386 AND NVL(bbom.alternate_bom_designator,''XXX'')=
387 NVL(' || ':b_P_ALTERNATE_DESG' || ', ''XXX''))
388 )';
389 L_STR := L_STR || 'AND ( (' || ':b_P_BOM_OR_ENG' || ' = ''BOM''
390 AND bbom.assembly_type = 1)
391 OR
392 (' || ':b_P_BOM_OR_ENG' || ' = ''ENG'')
393 )';
394
395
396 DBMS_SQL.PARSE(CURSOR_NAME
397 ,L_STR
398 ,1);
399 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
400 ,':b_l_seq_id'
401 ,L_SEQ_ID);
402 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
403 ,':b_P_ALT_OPTION_TYPE'
404 ,P_ALT_OPTION_TYPE);
405 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
406 ,':b_P_ALTERNATE_DESG'
407 ,P_ALTERNATE_DESG);
408 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
409 ,':b_P_BOM_OR_ENG'
410 ,P_BOM_OR_ENG);
411 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
412 ,':b_P_ORGANIZATION_ID'
413 ,LP_ORGANIZATION_ID);
417 ,P_ITEM_ID);
414 IF (FLAG = TRUE) THEN
415 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
416 ,':b_P_ITEM_ID'
418 ELSIF (FLAG = FALSE) THEN
419 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
420 ,':b_P_CATEGORY_SET_ID'
421 ,P_CATEGORY_SET_ID);
422 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME
423 ,':b_P_CATEGORY_STRUCTURE_ID'
424 ,P_CATEGORY_STRUCTURE_ID);
425 END IF;
426
427 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CURSOR_NAME);
428 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
429 TABLE_NAME := 'exploder';
430
431 IF LP_REVISION_DATE IS NULL THEN
432 LP_REVISION_DATE := TO_CHAR(SYSDATE
433 ,'YYYY/MM/DD HH24:MI:SS');
434 END IF;
435
436 EXPLOSION_REPORT(ORG_ID => LP_ORGANIZATION_ID
437 ,ORDER_BY => P_ORDER_BY_TYPE
438 ,LIST_ID => L_SEQ_ID
439 ,GRP_ID => P_GROUP_ID
440 ,SESSION_ID => -1
441 ,LEVELS_TO_EXPLODE => P_EXPLOSION_LEVEL
442 ,BOM_OR_ENG => L_BOM_OR_ENG
443 ,IMPL_FLAG => P_IMPL_FLAG
444 ,EXPLODE_OPTION => P_EXPLODE_OPTION_TYPE
445 ,MODULE => 2
446 ,CST_TYPE_ID => -1
447 ,STD_COMP_FLAG => -1
448 ,EXPL_QTY => P_EXPLOSION_QUANTITY
449 ,REPORT_OPTION => -1
450 ,REQ_ID => P_CONC_REQUEST_ID
451 ,LOCK_FLAG => -1
452 ,ROLLUP_OPTION => -1
453 ,ALT_RTG_DESG => ''
454 ,ALT_DESG => P_ALTERNATE_DESG
455 ,REV_DATE => LP_REVISION_DATE
456 ,ERR_MSG => L_ERR_MSG
457 ,ERROR_CODE => L_ERR_CODE
458 ,CST_RLP_ID => 0
459 ,VERIFY_FLAG => P_VERIFY_FLAG
460 ,PLAN_FACTOR_FLAG => P_PLAN_FACTOR_FLAG
461 ,INCL_LT_FLAG => P_PRINT_OPTION3_FLAG);
462 END LOOP;
463
464 IF (P_VERIFY_FLAG = 1 AND L_ERR_CODE = 9999) THEN
465 RETURN (TRUE);
466 END IF;
467
468 IF (P_VERIFY_FLAG = 2 AND L_ERR_CODE = 9999) THEN
469 RAISE LOOP_ERROR;
470 END IF;
471
472 IF L_ERR_CODE <> 0 THEN
473 IF L_ERR_CODE = 9998 THEN
474 RAISE LOOP_ERROR;
475 ELSE
476 RAISE EXPLODER_ERROR;
477 END IF;
478 END IF;
479
480 RETURN (TRUE);
481
482 EXCEPTION
483 WHEN EXPLODER_ERROR THEN
484 /*SRW.MESSAGE('1000'
485 ,L_ERR_MSG)*/NULL;
486
487 RETURN (FALSE);
488
489 WHEN LOOP_ERROR THEN
490 P_ERR_MSG := L_ERR_MSG;
491 FND_MESSAGE.SET_NAME('null'
492 ,':P_ERR_MSG');
493 P_MSG_BUF := FND_MESSAGE.GET;
494 /*SRW.MESSAGE(L_ERR_CODE
495 ,P_MSG_BUF)*/NULL;
496
497 RETURN (FALSE);
498
499 WHEN ITEM_ID_NULL THEN
500 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
501 WHEN OTHERS THEN
502 /*SRW.MESSAGE('2000'
503 ,TABLE_NAME || SQLERRM)*/NULL;
504 ecode := SQLCODE;
505 emesg := SQLERRM;
506
507
508 RETURN (FALSE);
509 END;
510
511 RETURN (TRUE);
512 END BEFOREREPORT;
513
514 FUNCTION AFTERREPORT RETURN BOOLEAN IS
515 BEGIN
516 BEGIN
517 ROLLBACK;
518 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
519 DELETE FROM BOM_LISTS
520 WHERE SEQUENCE_ID = P_SEQUENCE_ID1;
521 EXCEPTION
522 WHEN OTHERS THEN
523 RETURN (TRUE);
524 END;
525 RETURN (TRUE);
526 END AFTERREPORT;
527
528 FUNCTION GET_REV(COMPO_ORG_ID IN NUMBER
529 ,COMPONENT_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
530 REV VARCHAR2(3);
531 ORG_ID NUMBER := COMPO_ORG_ID;
532 ITEM_ID NUMBER := COMPONENT_ITEM_ID;
533 NO_REVISION_FOUND EXCEPTION;
534 PRAGMA EXCEPTION_INIT(NO_REVISION_FOUND,-20001);
535 BEGIN
536 IF (P_IMPL_FLAG = 1) THEN
537 BOM_REVISIONS.GET_REVISION(TYPE => 'PART'
538 ,ECO_STATUS => 'ALL'
539 ,EXAMINE_TYPE => 'IMPL_ONLY'
540 ,ORG_ID => ORG_ID
541 ,ITEM_ID => ITEM_ID
542 ,REV_DATE => TO_DATE(LP_REVISION_DATE
543 ,'YYYY/MM/DD HH24:MI:SS')
544 ,ITM_REV => REV);
545 ELSE
546 BOM_REVISIONS.GET_REVISION(TYPE => 'PART'
547 ,ECO_STATUS => 'ALL'
548 ,EXAMINE_TYPE => 'ALL'
549 ,ORG_ID => ORG_ID
550 ,ITEM_ID => ITEM_ID
551 ,REV_DATE => TO_DATE(LP_REVISION_DATE
552 ,'YYYY/MM/DD HH24:MI:SS')
553 ,ITM_REV => REV);
554 END IF;
555 RETURN (REV);
556 EXCEPTION
557 WHEN NO_REVISION_FOUND THEN
558 RETURN (' ');
559 END GET_REV;
560
561 FUNCTION GET_ELE_DESC(M_BOM_ITEM_TYPE IN NUMBER
562 ,D2_ELEMENT_NAME IN VARCHAR2
566 L_ITEM_TYPE NUMBER := M_BOM_ITEM_TYPE;
563 ,M_ITEM_CATALOG_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
564 L_DESC VARCHAR2(240);
565 ORG_ID NUMBER := LP_ORGANIZATION_ID;
567 L_ELEMENT_NAME VARCHAR(30) := D2_ELEMENT_NAME;
568 L_CATALOG_GROUP_ID NUMBER := M_ITEM_CATALOG_GROUP_ID;
569 BEGIN
570 IF L_ITEM_TYPE = 1 THEN
571 SELECT
572 DESCRIPTION
573 INTO L_DESC
574 FROM
575 MTL_DESCRIPTIVE_ELEMENTS
576 WHERE ITEM_CATALOG_GROUP_ID = L_CATALOG_GROUP_ID
577 AND ELEMENT_NAME = L_ELEMENT_NAME;
578 ELSIF L_ITEM_TYPE = 2 THEN
579 SELECT
580 MIN(DESCRIPTION)
581 INTO L_DESC
582 FROM
583 MTL_DESCRIPTIVE_ELEMENTS
584 WHERE ELEMENT_NAME = L_ELEMENT_NAME;
585 END IF;
586 RETURN (L_DESC);
587 END GET_ELE_DESC;
588
589 FUNCTION OPTIONAL_DISPFORMULA(OPTIONAL IN NUMBER) RETURN VARCHAR2 IS
590 BEGIN
591 IF OPTIONAL = 1 THEN
592 RETURN (P_YES);
593 ELSE
594 RETURN (P_NO);
595 END IF;
596 RETURN NULL;
597 END OPTIONAL_DISPFORMULA;
598
599 FUNCTION MUTUALLY_DISPFORMULA(MUTUALLY_EXCLUSIVE_OPTION IN NUMBER) RETURN VARCHAR2 IS
600 BEGIN
601 IF MUTUALLY_EXCLUSIVE_OPTION = 1 THEN
602 RETURN (P_YES);
603 ELSE
604 RETURN (P_NO);
605 END IF;
606 RETURN NULL;
607 END MUTUALLY_DISPFORMULA;
608
609 FUNCTION CHECK_ATP_DISPFORMULA(CHECK_ATP IN NUMBER) RETURN VARCHAR2 IS
610 BEGIN
611 IF CHECK_ATP = 1 THEN
612 RETURN (P_YES);
613 ELSE
614 RETURN (P_NO);
615 END IF;
616 RETURN NULL;
617 END CHECK_ATP_DISPFORMULA;
618
619 FUNCTION REQUIRED_TO_SHIP_DISPFORMULA(REQUIRED_TO_SHIP IN NUMBER) RETURN VARCHAR2 IS
620 BEGIN
621 IF REQUIRED_TO_SHIP = 1 THEN
622 RETURN (P_YES);
623 ELSE
624 RETURN (P_NO);
625 END IF;
626 RETURN NULL;
627 END REQUIRED_TO_SHIP_DISPFORMULA;
628
629 FUNCTION REQUIRED_FOR_REVENUE_DISPFORMU(REQUIRED_FOR_REVENUE IN NUMBER) RETURN VARCHAR2 IS
630 BEGIN
631 IF REQUIRED_FOR_REVENUE = 1 THEN
632 RETURN (P_YES);
633 ELSE
634 RETURN (P_NO);
635 END IF;
636 RETURN NULL;
637 END REQUIRED_FOR_REVENUE_DISPFORMU;
638
639 FUNCTION INCLUDE_ON_SHIP_DOCS_DISPFORMU(INCLUDE_ON_SHIP_DOCS IN NUMBER) RETURN VARCHAR2 IS
640 BEGIN
641 IF INCLUDE_ON_SHIP_DOCS = 1 THEN
642 RETURN (P_YES);
643 ELSE
644 RETURN (P_NO);
645 END IF;
646 RETURN NULL;
647 END INCLUDE_ON_SHIP_DOCS_DISPFORMU;
648
649 FUNCTION ENG_BILL_DISPFORMULA(ENG_BILL IN NUMBER) RETURN VARCHAR2 IS
650 BEGIN
651 IF ENG_BILL = 1 THEN
652 RETURN (P_NO);
653 ELSE
654 RETURN (P_YES);
655 END IF;
656 RETURN NULL;
657 END ENG_BILL_DISPFORMULA;
658
659 FUNCTION SUPPLY_TYPE_DISPFORMULA(WIP_SUPPLY_TYPE IN NUMBER) RETURN VARCHAR2 IS
660 BEGIN
661 IF WIP_SUPPLY_TYPE IS NOT NULL THEN
662 SELECT
663 MEANING
664 INTO DUMMY
665 FROM
666 MFG_LOOKUPS
667 WHERE LOOKUP_TYPE = 'WIP_SUPPLY'
668 AND LOOKUP_CODE = WIP_SUPPLY_TYPE;
669 RETURN (DUMMY);
670 ELSE
671 RETURN (NULL);
672 END IF;
673 RETURN NULL;
674 END SUPPLY_TYPE_DISPFORMULA;
675
676 FUNCTION CF_REVISION_DESCFORMULA(COMPONENT_ITEM_ID IN NUMBER
677 ,C_D3_REVISION IN VARCHAR2) RETURN CHAR IS
678 REV_DESC VARCHAR2(240);
679 ITEM_ID NUMBER := COMPONENT_ITEM_ID;
680 ORG_ID NUMBER := LP_ORGANIZATION_ID;
681 ACTIVE_REV VARCHAR2(3) := C_D3_REVISION;
682 BEGIN
683 SELECT
684 REV.DESCRIPTION
685 INTO REV_DESC
686 FROM
687 MTL_ITEM_REVISIONS REV
688 WHERE REV.INVENTORY_ITEM_ID = ITEM_ID
689 AND REV.ORGANIZATION_ID = ORG_ID
690 AND REV.REVISION = ACTIVE_REV;
691 RETURN (REV_DESC);
692 EXCEPTION
693 WHEN OTHERS THEN
694 RETURN (' ');
695 END CF_REVISION_DESCFORMULA;
696
697 FUNCTION CF_ORG_NAMEFORMULA(M_ORG_ID IN NUMBER) RETURN CHAR IS
698 ORG_NAME VARCHAR2(60);
699 BEGIN
700 SELECT
701 ORGANIZATION_NAME
702 INTO ORG_NAME
703 FROM
704 ORG_ORGANIZATION_DEFINITIONS ORG
705 WHERE ORG.ORGANIZATION_ID = M_ORG_ID;
706 RETURN (ORG_NAME);
707 END CF_ORG_NAMEFORMULA;
708
709 FUNCTION CF_ITEM_DESCFORMULA(M_ITEM_ID IN NUMBER
710 ,M_ORG_ID IN NUMBER) RETURN VARCHAR IS
711 ITEM_DESC VARCHAR2(240);
712 ITEM_ID NUMBER := M_ITEM_ID;
713 ORG_ID NUMBER := M_ORG_ID;
714 BEGIN
715 SELECT
716 DESCRIPTION
717 INTO ITEM_DESC
718 FROM
719 MTL_SYSTEM_ITEMS_TL
720 WHERE ORGANIZATION_ID = ORG_ID
721 AND INVENTORY_ITEM_ID = ITEM_ID
722 AND LANGUAGE = USERENV('LANG');
723 RETURN (ITEM_DESC);
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 RETURN (' ');
727 END CF_ITEM_DESCFORMULA;
728
729 FUNCTION CF_COMP_DESCFORMULA(COMPONENT_ITEM_ID IN NUMBER
730 ,COMPO_ORG_ID IN NUMBER) RETURN VARCHAR IS
731 ITEM_DESC VARCHAR2(240);
732 ITEM_ID NUMBER := COMPONENT_ITEM_ID;
733 ORG_ID NUMBER := COMPO_ORG_ID;
734 BEGIN
735 SELECT
736 DESCRIPTION
737 INTO ITEM_DESC
738 FROM
739 MTL_SYSTEM_ITEMS_TL
740 WHERE ORGANIZATION_ID = ORG_ID
741 AND INVENTORY_ITEM_ID = ITEM_ID
742 AND LANGUAGE = USERENV('LANG');
743 RETURN (ITEM_DESC);
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 RETURN (' ');
747 END CF_COMP_DESCFORMULA;
748
749 FUNCTION CF_SUBCOMP_DESCFORMULA(D32_ITEM_ID IN NUMBER
750 ,D32_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
751 ITEM_DESC VARCHAR2(240);
752 ITEM_ID NUMBER := D32_ITEM_ID;
753 ORG_ID NUMBER := D32_ORGANIZATION_ID;
754 BEGIN
755 SELECT
756 DESCRIPTION
757 INTO ITEM_DESC
758 FROM
759 MTL_SYSTEM_ITEMS_TL
760 WHERE ORGANIZATION_ID = ORG_ID
761 AND INVENTORY_ITEM_ID = ITEM_ID
762 AND LANGUAGE = USERENV('LANG');
763 RETURN (ITEM_DESC);
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 RETURN (' ');
767 END CF_SUBCOMP_DESCFORMULA;
768
769 PROCEDURE EXPLODER_USEREXIT(VERIFY_FLAG IN NUMBER
770 ,ORG_ID IN NUMBER
771 ,ORDER_BY IN NUMBER
772 ,GRP_ID IN NUMBER
773 ,SESSION_ID IN NUMBER
774 ,LEVELS_TO_EXPLODE IN NUMBER
775 ,BOM_OR_ENG IN NUMBER
776 ,IMPL_FLAG IN NUMBER
777 ,PLAN_FACTOR_FLAG IN NUMBER
778 ,EXPLODE_OPTION IN NUMBER
779 ,MODULE IN NUMBER
780 ,CST_TYPE_ID IN NUMBER
781 ,STD_COMP_FLAG IN NUMBER
782 ,EXPL_QTY IN NUMBER
783 ,ITEM_ID IN NUMBER
784 ,ALT_DESG IN VARCHAR2
785 ,COMP_CODE IN VARCHAR2
786 ,REV_DATE IN VARCHAR2
787 ,ERR_MSG OUT NOCOPY VARCHAR2
788 ,ERROR_CODE OUT NOCOPY NUMBER) IS
789 BEGIN
790 /*BOMPEXPL.EXPLODER_USEREXIT(VERIFY_FLAG, ORG_ID, ORDER_BY, GRP_ID, SESSION_ID,
791 LEVELS_TO_EXPLODE, BOM_OR_ENG, IMPL_FLAG, PLAN_FACTOR_FLAG, EXPLODE_OPTION, MODULE,
792 CST_TYPE_ID, STD_COMP_FLAG, EXPL_QTY, ITEM_ID, ALT_DESG, COMP_CODE, REV_DATE, ERR_MSG, ERROR_CODE);*/
793 null;
794 END EXPLODER_USEREXIT;
795
796 PROCEDURE EXPLOSION_REPORT(VERIFY_FLAG IN NUMBER
797 ,ORG_ID IN NUMBER
798 ,ORDER_BY IN NUMBER
799 ,LIST_ID IN NUMBER
800 ,GRP_ID IN NUMBER
801 ,SESSION_ID IN NUMBER
802 ,LEVELS_TO_EXPLODE IN NUMBER
803 ,BOM_OR_ENG IN NUMBER
804 ,IMPL_FLAG IN NUMBER
805 ,PLAN_FACTOR_FLAG IN NUMBER
806 ,INCL_LT_FLAG IN NUMBER
807 ,EXPLODE_OPTION IN NUMBER
808 ,MODULE IN NUMBER
809 ,CST_TYPE_ID IN NUMBER
810 ,STD_COMP_FLAG IN NUMBER
811 ,EXPL_QTY IN NUMBER
812 ,REPORT_OPTION IN NUMBER
813 ,REQ_ID IN NUMBER
814 ,CST_RLP_ID IN NUMBER
815 ,LOCK_FLAG IN NUMBER
816 ,ROLLUP_OPTION IN NUMBER
817 ,ALT_RTG_DESG IN VARCHAR2
818 ,ALT_DESG IN VARCHAR2
819 ,REV_DATE IN VARCHAR2
820 ,ERR_MSG OUT NOCOPY VARCHAR2
821 ,ERROR_CODE OUT NOCOPY NUMBER) IS
822 BEGIN
823 BOMPEXPL.EXPLOSION_REPORT(VERIFY_FLAG, ORG_ID, ORDER_BY, LIST_ID,
824 GRP_ID, SESSION_ID, LEVELS_TO_EXPLODE, BOM_OR_ENG, IMPL_FLAG,
825 PLAN_FACTOR_FLAG, INCL_LT_FLAG, EXPLODE_OPTION, MODULE, CST_TYPE_ID,
826 STD_COMP_FLAG, EXPL_QTY, REPORT_OPTION, REQ_ID, CST_RLP_ID, LOCK_FLAG,
827 ROLLUP_OPTION, ALT_RTG_DESG, ALT_DESG, REV_DATE, ERR_MSG, ERROR_CODE);
828 END EXPLOSION_REPORT;
829
830 END BOM_BOMRBOMS_XMLP_PKG;