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