[Home] [Help]
PACKAGE BODY: APPS.BOM_BOMRBOMC_XMLP_PKG
Source
1 PACKAGE BODY BOM_BOMRBOMC_XMLP_PKG AS
2 /* $Header: BOMRBOMCB.pls 120.0.12020000.2 2012/07/05 09:30:24 ntungare ship $ */
3
4 FUNCTION GET_REV(D3_COMPO_ORG_ID IN NUMBER
5 ,D3_COMPONENT_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
6 ITM_REV VARCHAR2(3);
7 ORG_ID NUMBER := D3_COMPO_ORG_ID;
8 ITEM_ID NUMBER := D3_COMPONENT_ITEM_ID;
9 CURSOR C1 IS
10 SELECT
11 REVISION
12 FROM
13 MTL_ITEM_REVISIONS MIR
14 WHERE INVENTORY_ITEM_ID = ITEM_ID
15 AND ORGANIZATION_ID = ORG_ID
16 AND MIR.EFFECTIVITY_DATE <= TO_DATE(LP_REVISION_DATE
17 ,'YYYY/MM/DD HH24:MI:SS')
18 AND ( ( P_IMPL_FLAG = 2 )
19 OR ( P_IMPL_FLAG = 1
20 AND IMPLEMENTATION_DATE IS NOT NULL ) )
21 ORDER BY
22 EFFECTIVITY_DATE,
23 REVISION;
24 BEGIN
25 OPEN C1;
26 FETCH C1
27 INTO ITM_REV;
28 IF C1%NOTFOUND THEN
29 CLOSE C1;
30 RETURN NULL;
31 END IF;
32 CLOSE C1;
33 RETURN ITM_REV;
34 END GET_REV;
35
36 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
37 BEGIN
38 DECLARE
39 L_ORGANIZATION_NAME VARCHAR2(240);
40 L_EXPLODE_OPTION VARCHAR2(80);
41 L_IMPL VARCHAR2(80);
42 L_RANGE_OPTION VARCHAR2(80);
43 L_SPECIFIC_ITEM VARCHAR2(81);
44 L_CATEGORY_SET VARCHAR2(40);
45 L_PRINT_OPTION1 VARCHAR2(80);
46 L_PLAN_FACTOR VARCHAR2(80);
47 L_ALT_OPTION VARCHAR2(80);
48 L_ORDER_BY VARCHAR2(80);
49 L_SEQ_ID NUMBER;
50 L_STR VARCHAR2(2000);
51 L_BOM_OR_ENG NUMBER;
52 L_ERR_MSG VARCHAR2(80);
53 L_ERR_CODE NUMBER;
54 EXPLODER_ERROR EXCEPTION;
55 LOOP_ERROR EXCEPTION;
56 TABLE_NAME VARCHAR2(20);
57 ITEM_ID_NULL EXCEPTION;
58
59 ROWS_PROCESSED INTEGER;
60 CURSOR_NAME INTEGER;
61 l_CODE varchar2(10); -- :='MSTK';
62 l_APPL_SHORT_NAME varchar2(20) := 'INV';
63 l_OPERATOR varchar2(20);
64 l_OPERAND1 varchar2(20);
65 l_OPERAND2 varchar2(20);
66 l_TABLEALIAS varchar2(20);
67 l_OUTPUT varchar2(20);
68 l_NumOf_Bind_Vars_Assy Number :=0;
69 l_NumOf_Bind_Vars_Cat Number :=0;
70 l_bind_variables_tab_assy fnd_flex_xml_publisher_apis.bind_variables;
71 l_bind_variables_tab_cat fnd_flex_xml_publisher_apis.bind_variables;
72 l_idFlexNum number ;
73 P_ASSY_WHERE Varchar2(2000) := '';
74 P_CAT_WHERE Varchar2(2000) := '';
75
76 cursor c1(flex_code in VARCHAR2, flex_structure_code in VARCHAR2) is
77 SELECT fifst.id_flex_num
78 FROM fnd_id_flex_structures fifst
79 WHERE fifst.application_id = 401
80 AND fifst.id_flex_code = flex_code
81 AND fifst.id_flex_structure_code = flex_structure_code
82 AND fifst.enabled_flag = 'Y'
83 AND fifst.freeze_flex_definition_flag = 'Y'
84 ORDER BY fifst.id_flex_num;
85
86 BEGIN
87
88 CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
89 OPEN C1('MSTK', 'SYSTEM_ITEMS');
90 FETCH C1 INTO l_idFlexNuM;
91 CLOSE C1;
92
93 IF (P_ITEM_FROM IS NOT NULL AND P_ITEM_TO IS NOT NULL) THEN
94 l_CODE := 'MSTK';
95 l_OPERAND1 := P_ITEM_FROM;
96 l_OPERAND2 := P_ITEM_TO;
97 l_OPERATOR := 'BETWEEN';
98 l_OUTPUT := 'P_ASS_BETWEEN';
99 l_TABLEALIAS := 'MSI';
100
101 ELSIF (P_ITEM_FROM IS NOT NULL AND P_ITEM_TO IS NULL) THEN
102 l_CODE := 'MSTK';
103 l_OPERAND1 := P_ITEM_FROM;
104 l_OPERAND2 := '';
105 l_OPERATOR := '>=';
106 l_OUTPUT := 'P_ASS_BETWEEN';
107 l_TABLEALIAS := 'MSI';
108
109 ELSIF (P_ITEM_FROM IS NULL AND P_ITEM_TO IS NOT NULL) THEN
110 l_CODE := 'MSTK';
111 l_OPERAND1 := '';
112 l_OPERAND2 := P_ITEM_TO;
113 l_OPERATOR := '<=';
114 l_OUTPUT := 'P_ASS_BETWEEN';
115 l_TABLEALIAS := 'MSI';
116
117 ELSE
118 l_CODE := 'MSTK';
119 l_OPERAND1 := NULL;
120 l_OPERAND2 := NULL;
121 l_OPERATOR := '=';
122 l_OUTPUT := 'P_ASS_BETWEEN';
123 l_TABLEALIAS := 'MSI';
124 END IF;
125
126 FND_FLEX_XML_PUBLISHER_APIS.KFF_WHERE
127 (P_LEXICAL_NAME => l_OUTPUT
128 ,P_APPLICATION_SHORT_NAME => l_APPL_SHORT_NAME
129 ,P_ID_FLEX_CODE => l_CODE
130 ,P_ID_FLEX_NUM => l_idFlexNuM
131 ,P_CODE_COMBINATION_TABLE_ALIAS => l_TABLEALIAS
132 ,P_OPERATOR => l_OPERATOR
133 ,P_OPERAND1 => l_OPERAND1
134 ,P_OPERAND2 => l_OPERAND2
135 ,X_WHERE_EXPRESSION => P_ASSY_WHERE
136 ,X_NUMOF_BIND_VARIABLES => l_NumOf_Bind_Vars_Assy
137 ,X_BIND_VARIABLES => l_bind_variables_tab_assy
138 );
139
140 OPEN C1('MCAT', 'ITEM_CATEGORIES');
141 FETCH C1 INTO l_idFlexNuM;
142 CLOSE C1;
143
144 IF (P_CATEGORY_FROM IS NOT NULL AND P_CATEGORY_TO IS NOT NULL) THEN
145 l_CODE := 'MCAT';
146 l_OPERAND1 := P_CATEGORY_FROM;
147 l_OPERAND2 := P_CATEGORY_TO;
148 l_OPERATOR := 'BETWEEN';
149 l_OUTPUT := 'P_CAT_BETWEEN';
150 l_TABLEALIAS := 'MC';
151
152 ELSIF (P_CATEGORY_FROM IS NOT NULL AND P_CATEGORY_TO IS NULL) THEN
153 l_CODE := 'MCAT';
154 l_OPERAND1 := P_CATEGORY_FROM;
155 l_OPERAND2 := P_CATEGORY_TO;
156 l_OPERATOR := '>=';
157 l_OUTPUT := 'P_CAT_BETWEEN';
158 l_TABLEALIAS := 'MC';
159
160 ELSIF (P_CATEGORY_FROM IS NULL AND P_CATEGORY_TO IS NOT NULL) THEN
161 l_CODE := 'MCAT';
162 l_OPERAND1 := P_CATEGORY_FROM;
163 l_OPERAND2 := P_CATEGORY_TO;
164 l_OPERATOR := '<=';
165 l_OUTPUT := 'P_CAT_BETWEEN';
166 l_TABLEALIAS := 'MC';
167
168 ELSE
169 l_CODE := 'MCAT';
170 l_OPERAND1 := P_CATEGORY_FROM;
171 l_OPERAND2 := P_CATEGORY_TO;
172 l_OPERATOR := '=';
173 l_OUTPUT := 'P_CAT_BETWEEN';
174 l_TABLEALIAS := 'MC';
175 END IF;
176
177
178 FND_FLEX_XML_PUBLISHER_APIS.KFF_WHERE
179 (P_LEXICAL_NAME => l_OUTPUT
180 ,P_APPLICATION_SHORT_NAME => l_APPL_SHORT_NAME
181 ,P_ID_FLEX_CODE => l_CODE
182 ,P_ID_FLEX_NUM => l_idFlexNuM
183 ,P_CODE_COMBINATION_TABLE_ALIAS => l_TABLEALIAS
184 ,P_OPERATOR => l_OPERATOR
185 ,P_OPERAND1 => l_OPERAND1
186 ,P_OPERAND2 => l_OPERAND2
187 ,X_WHERE_EXPRESSION => P_CAT_WHERE
188 ,X_NUMOF_BIND_VARIABLES => l_NumOf_Bind_Vars_Cat
189 ,X_BIND_VARIABLES => l_bind_variables_tab_cat
190 );
191
192
193
194 LP_REVISION_DATE:=P_REVISION_DATE;
195
196 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
197 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
198 TABLE_NAME := 'P_DEBUG';
199 IF (P_RANGE_OPTION_TYPE = 1) AND (P_ITEM_ID IS NULL) THEN
200 FND_MESSAGE.SET_NAME('null'
201 ,'MFG_REQUIRED_VALUE');
202 FND_MESSAGE.SET_TOKEN('ENTITY'
203 ,'specific item');
204 P_MSG_BUF := FND_MESSAGE.GET;
205 /*SRW.MESSAGE('999'
206 ,P_MSG_BUF)*/NULL;
207 RAISE ITEM_ID_NULL;
208 END IF;
209 IF P_PRINT_OPTION1_FLAG = 2 THEN
210 /*SRW.SET_MAXROW('Q_ELEMENT'
211 ,0)*/NULL;
212 END IF;
213 TABLE_NAME := 'ORG_DEF';
214 SELECT
215 O.ORGANIZATION_NAME
216 INTO L_ORGANIZATION_NAME
217 FROM
218 ORG_ORGANIZATION_DEFINITIONS O
222 SELECT
219 WHERE O.ORGANIZATION_ID = P_ORGANIZATION_ID;
220 TABLE_NAME := 'MTL_ITEM_FLEXFIELDS';
221 IF (P_RANGE_OPTION_TYPE = 1) THEN
223 ITEM_NUMBER
224 INTO L_SPECIFIC_ITEM
225 FROM
226 MTL_ITEM_FLEXFIELDS
227 WHERE ITEM_ID = P_ITEM_ID
228 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
229 P_SPECIFIC_ITEM := L_SPECIFIC_ITEM;
230 END IF;
231 TABLE_NAME := 'MTL_CATEGORY_SETS';
232 IF P_CATEGORY_SET_ID > 0 THEN
233 SELECT
234 CATEGORY_SET_NAME
235 INTO L_CATEGORY_SET
236 FROM
237 MTL_CATEGORY_SETS
238 WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID;
239 P_CATEGORY_SET := L_CATEGORY_SET;
240 END IF;
241 TABLE_NAME := 'MFG_LOOKUPS1';
242 SELECT
243 SUBSTR(L1.MEANING
244 ,1
245 ,40),
246 SUBSTR(L2.MEANING
247 ,1
248 ,4),
249 SUBSTR(L3.MEANING
250 ,1
251 ,40),
252 SUBSTR(L4.MEANING
253 ,1
254 ,4),
255 SUBSTR(L5.MEANING
256 ,1
257 ,4)
258 INTO L_EXPLODE_OPTION,L_IMPL,L_RANGE_OPTION,L_PRINT_OPTION1,L_PLAN_FACTOR
259 FROM
260 MFG_LOOKUPS L1,
261 MFG_LOOKUPS L2,
262 MFG_LOOKUPS L3,
263 MFG_LOOKUPS L4,
264 MFG_LOOKUPS L5
265 WHERE L1.LOOKUP_TYPE = 'BOM_INQUIRY_DISPLAY_TYPE'
266 AND L1.LOOKUP_CODE = P_EXPLODE_OPTION_TYPE
267 AND L2.LOOKUP_TYPE = 'SYS_YES_NO'
268 AND L2.LOOKUP_CODE = P_IMPL_FLAG
269 AND L3.LOOKUP_TYPE = 'BOM_SELECTION_TYPE'
270 AND L3.LOOKUP_CODE = P_RANGE_OPTION_TYPE
271 AND L4.LOOKUP_TYPE = 'SYS_YES_NO'
272 AND L4.LOOKUP_CODE = P_PRINT_OPTION1_FLAG
273 AND L5.LOOKUP_TYPE = 'SYS_YES_NO'
274 AND L5.LOOKUP_CODE = P_PLAN_FACTOR_FLAG;
275 TABLE_NAME := 'MFG_LOOKUPS2';
276 SELECT
277 SUBSTR(L1.MEANING
278 ,1
279 ,40),
280 SUBSTR(L2.MEANING
281 ,1
282 ,40)
283 INTO L_ALT_OPTION,L_ORDER_BY
284 FROM
285 MFG_LOOKUPS L1,
286 MFG_LOOKUPS L2
287 WHERE L1.LOOKUP_TYPE = 'MCG_AUTOLOAD_OPTION'
288 AND L1.LOOKUP_CODE = P_ALT_OPTION_TYPE
289 AND L2.LOOKUP_TYPE = 'BOM_BILL_SORT_ORDER_TYPE'
290 AND L2.LOOKUP_CODE = P_ORDER_BY_TYPE;
291 P_ORGANIZATION_NAME := L_ORGANIZATION_NAME;
292 P_EXPLODE_OPTION := L_EXPLODE_OPTION;
293 P_IMPL := L_IMPL;
294 P_RANGE_OPTION := L_RANGE_OPTION;
295 P_PRINT_OPTION1 := L_PRINT_OPTION1;
296 P_ALT_OPTION := L_ALT_OPTION;
297 P_ORDER_BY := L_ORDER_BY;
298 P_PLAN_FACTOR := L_PLAN_FACTOR;
299 IF P_BOM_OR_ENG = 'BOM' THEN
300 L_BOM_OR_ENG := 1;
301 ELSE
302 L_BOM_OR_ENG := 2;
303 END IF;
304 TABLE_NAME := 'DUAL_SEQUENCE';
305 SELECT
306 BOM_LISTS_S.NEXTVAL
307 INTO L_SEQ_ID
308 FROM
309 DUAL;
310 P_SEQUENCE_ID := L_SEQ_ID;
311 TABLE_NAME := 'ITEM FLEX RANGE';
312 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
313 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
314 IF P_RANGE_OPTION_TYPE = 2 THEN
315 IF (P_ITEM_FROM IS NOT NULL) THEN
316 IF (P_ITEM_TO IS NOT NULL) THEN
317 NULL;
318 ELSE
319 NULL;
320 END IF;
321 ELSE
322 IF (P_ITEM_TO IS NOT NULL) THEN
323 NULL;
324 END IF;
325 END IF;
326 TABLE_NAME := 'CATEGORY FLEX RANGE';
327 IF (P_CATEGORY_FROM IS NOT NULL) THEN
328 IF (P_CATEGORY_TO IS NOT NULL) THEN
329 NULL;
330 ELSE
331 NULL;
332 END IF;
333 ELSE
334 IF (P_CATEGORY_TO IS NOT NULL) THEN
335 NULL;
336 END IF;
337 END IF;
338 END IF;
339 TABLE_NAME := 'BUILD SQL';
340 L_STR := 'INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID,
341 ALTERNATE_DESIGNATOR) ';
342 IF P_RANGE_OPTION_TYPE = 1 THEN
343 L_STR := L_STR || ' SELECT DISTINCT ' || TO_CHAR(L_SEQ_ID) || ',
344 ' || TO_CHAR(P_ITEM_ID) || ',
345 bbom.alternate_bom_designator
346 FROM bom_bill_of_materials bbom
347 WHERE bbom.organization_id = ' || TO_CHAR(P_ORGANIZATION_ID) || '
348 AND bbom.assembly_item_id = ' || TO_CHAR(P_ITEM_ID);
349 ELSE
350 L_STR := L_STR || ' SELECT DISTINCT ' || TO_CHAR(L_SEQ_ID) || ',
351 msi.inventory_item_id,
352 bbom.alternate_bom_designator
353 FROM mtl_item_categories mic,
354 mtl_system_items msi,
355 mtl_categories mc,
356 bom_bill_of_materials bbom
357 WHERE ' || P_ASSY_WHERE || '
358 AND msi.inventory_item_id = mic.inventory_item_id
359 AND msi.organization_id =
360 ' || TO_CHAR(P_ORGANIZATION_ID) || '
361 AND mic.organization_id =
362 ' || TO_CHAR(P_ORGANIZATION_ID) || '
363 AND mic.category_set_id =
364 ' || TO_CHAR(P_CATEGORY_SET_ID) || '
365 AND mic.category_id = mc.category_id
366 AND mc.structure_id =
367 ' || TO_CHAR(P_CATEGORY_STRUCTURE_ID) || '
368 AND ' || P_CAT_WHERE || '
369 AND msi.inventory_item_id = bbom.assembly_item_id
370 AND msi.organization_id = bbom.organization_id
371 AND msi.bom_enabled_flag = ''Y''';
372 END IF;
373 L_STR := L_STR || ' AND ( (' || TO_CHAR(P_ALT_OPTION_TYPE) || ' = 1)
374 OR
375 (' || TO_CHAR(P_ALT_OPTION_TYPE) || ' = 2
376 AND bbom.alternate_bom_designator IS NULL)
377 OR
378 (' || TO_CHAR(P_ALT_OPTION_TYPE) || ' = 3
379 AND NVL(bbom.alternate_bom_designator,''XXX'')=
380 NVL(''' || P_ALTERNATE_DESG || ''', ''XXX''))
381 )
382 AND ( (''' || P_BOM_OR_ENG || ''' = ''BOM''
383 AND bbom.assembly_type = 1)
384 OR
385 (''' || P_BOM_OR_ENG || ''' = ''ENG'')
386 )';
387 TABLE_NAME := 'EXECUTE SQL';
388
389 /*
390 EXECUTE IMMEDIATE
391 L_STR; */
392
393 DBMS_SQL.PARSE(CURSOR_NAME
394 ,L_STR
395 ,1);
396
397 FOR i IN 1..l_NumOf_Bind_Vars_Assy LOOP
398 IF (l_bind_variables_tab_assy(i).data_type='VARCHAR2') THEN
399 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).varchar2_value);
400 ELSIF (l_bind_variables_tab_assy(i).data_type='NUMBER') THEN
401 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).canonical_value);
402 ELSIF (l_bind_variables_tab_assy(i).data_type='DATE') THEN
403 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).date_value);
404 END IF;
405 END LOOP ;
406
407 FOR i IN 1..l_NumOf_Bind_Vars_Cat LOOP
408 IF (l_bind_variables_tab_cat(i).data_type='VARCHAR2') THEN
409 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).varchar2_value);
410 ELSIF (l_bind_variables_tab_cat(i).data_type='NUMBER') THEN
411 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).canonical_value);
412 ELSIF (l_bind_variables_tab_cat(i).data_type='DATE') THEN
413 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).date_value);
414 END IF;
415 END LOOP ;
416
417 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CURSOR_NAME);
418 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
419
420
421
422 TABLE_NAME := 'CALL EXPLODER';
423 IF LP_REVISION_DATE IS NULL THEN
424 LP_REVISION_DATE := TO_CHAR(SYSDATE
425 ,'YYYY/MM/DD HH24:MI:SS');
426 END IF;
427 EXPLOSION_REPORT(ORG_ID => P_ORGANIZATION_ID
428 ,ORDER_BY => P_ORDER_BY_TYPE
429 ,LIST_ID => L_SEQ_ID
430 ,GRP_ID => P_GROUP_ID
431 ,SESSION_ID => -1
432 ,LEVELS_TO_EXPLODE => P_EXPLOSION_LEVEL
433 ,BOM_OR_ENG => L_BOM_OR_ENG
437 ,CST_TYPE_ID => -1
434 ,IMPL_FLAG => P_IMPL_FLAG
435 ,EXPLODE_OPTION => P_EXPLODE_OPTION_TYPE
436 ,MODULE => 2
438 ,STD_COMP_FLAG => -1
439 ,EXPL_QTY => P_EXPLOSION_QUANTITY
440 ,REPORT_OPTION => -1
441 ,REQ_ID => P_CONC_REQUEST_ID
442 ,LOCK_FLAG => -1
443 ,ROLLUP_OPTION => -1
444 ,ALT_RTG_DESG => ''
445 ,ALT_DESG => P_ALTERNATE_DESG
446 ,REV_DATE => LP_REVISION_DATE
447 ,ERR_MSG => L_ERR_MSG
448 ,ERROR_CODE => L_ERR_CODE
449 ,VERIFY_FLAG => 0
450 ,CST_RLP_ID => 0
451 ,PLAN_FACTOR_FLAG => P_PLAN_FACTOR_FLAG
452 ,INCL_LT_FLAG => 2);
453 TABLE_NAME := 'EXPLODE COMPLETE';
454 IF L_ERR_CODE = 9999 THEN
455 RAISE LOOP_ERROR;
456 END IF;
457 IF L_ERR_CODE < 0 THEN
458 RAISE EXPLODER_ERROR;
459 END IF;
460 RETURN (TRUE);
461 EXCEPTION
462 /* WHEN SRW.DO_SQL_FAILURE OTHERS THEN
463 SRW.MESSAGE('2000'
464 ,TABLE_NAME || SQLERRM)NULL;
465 RETURN (FALSE);*/
466 WHEN EXPLODER_ERROR THEN
467 /*SRW.MESSAGE('2001'
468 ,L_ERR_MSG)*/NULL;
469 RETURN (FALSE);
470 WHEN LOOP_ERROR THEN
471 P_ERR_MSG := L_ERR_MSG;
472 FND_MESSAGE.SET_NAME('null'
473 ,':P_ERR_MSG');
474 P_MSG_BUF := FND_MESSAGE.GET;
475 /*SRW.MESSAGE('9999'
476 ,P_MSG_BUF)*/NULL;
477 RETURN (FALSE);
478 WHEN ITEM_ID_NULL THEN
479 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
480 WHEN NO_DATA_FOUND THEN
481 /*SRW.MESSAGE('2003'
482 ,TABLE_NAME || SQLERRM)*/NULL;
483
484 RETURN (TRUE);
485 WHEN OTHERS THEN
486 /*SRW.MESSAGE('2000'
487 ,TABLE_NAME || SQLERRM)*/NULL;
488
489 RETURN (FALSE);
490 END;
491 RETURN (TRUE);
492 END BEFOREREPORT;
493
494 FUNCTION GET_ELE_DESC(M_BOM_ITEM_TYPE IN NUMBER
495 ,D2_ELEMENT_NAME IN VARCHAR2
496 ,M_ITEM_CATALOG_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
497 L_DESC VARCHAR2(240);
498 ORG_ID NUMBER := P_ORGANIZATION_ID;
499 L_ITEM_TYPE NUMBER := M_BOM_ITEM_TYPE;
500 L_ELEMENT_NAME VARCHAR(30) := D2_ELEMENT_NAME;
501 L_CATALOG_GROUP_ID NUMBER := M_ITEM_CATALOG_GROUP_ID;
502 BEGIN
503 IF L_ITEM_TYPE = 1 THEN
504 SELECT
505 DESCRIPTION
506 INTO L_DESC
507 FROM
508 MTL_DESCRIPTIVE_ELEMENTS
509 WHERE ITEM_CATALOG_GROUP_ID = L_CATALOG_GROUP_ID
510 AND ELEMENT_NAME = L_ELEMENT_NAME;
511 ELSIF L_ITEM_TYPE = 2 THEN
512 SELECT
513 MIN(DESCRIPTION)
514 INTO L_DESC
515 FROM
516 MTL_DESCRIPTIVE_ELEMENTS
517 WHERE ELEMENT_NAME = L_ELEMENT_NAME;
518 END IF;
519 RETURN (L_DESC);
520 END GET_ELE_DESC;
521
522 FUNCTION AFTERREPORT RETURN BOOLEAN IS
523 BEGIN
524 BEGIN
525 ROLLBACK;
526 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
527 EXCEPTION
528 WHEN OTHERS THEN
529 RETURN (TRUE);
530 END;
531 RETURN (TRUE);
532 END AFTERREPORT;
533
534 PROCEDURE EXPLODER_USEREXIT(VERIFY_FLAG IN NUMBER
535 ,ORG_ID IN NUMBER
536 ,ORDER_BY IN NUMBER
537 ,GRP_ID IN NUMBER
538 ,SESSION_ID IN NUMBER
539 ,LEVELS_TO_EXPLODE IN NUMBER
540 ,BOM_OR_ENG IN NUMBER
541 ,IMPL_FLAG IN NUMBER
542 ,PLAN_FACTOR_FLAG IN NUMBER
543 ,EXPLODE_OPTION IN NUMBER
544 ,MODULE IN NUMBER
545 ,CST_TYPE_ID IN NUMBER
546 ,STD_COMP_FLAG IN NUMBER
547 ,EXPL_QTY IN NUMBER
548 ,ITEM_ID IN NUMBER
549 ,ALT_DESG IN VARCHAR2
550 ,COMP_CODE IN VARCHAR2
551 ,REV_DATE IN VARCHAR2
552 ,ERR_MSG OUT NOCOPY VARCHAR2
553 ,ERROR_CODE OUT NOCOPY NUMBER) IS
554 BEGIN
555 /* STPROC.INIT('begin BOMPEXPL.EXPLODER_USEREXIT(:VERIFY_FLAG,
556 :ORG_ID, :ORDER_BY, :GRP_ID, :SESSION_ID, :LEVELS_TO_EXPLODE, :BOM_OR_ENG,
557 :IMPL_FLAG, :PLAN_FACTOR_FLAG, :EXPLODE_OPTION, :MODULE, :CST_TYPE_ID, :STD_COMP_FLAG, :EXPL_QTY, :ITEM_ID, :ALT_DESG, :COMP_CODE, :REV_DATE, :ERR_MSG, :ERROR_CODE); end;');
558 STPROC.BIND_I(VERIFY_FLAG);
559 STPROC.BIND_I(ORG_ID);
560 STPROC.BIND_I(ORDER_BY);
561 STPROC.BIND_I(GRP_ID);
562 STPROC.BIND_I(SESSION_ID);
563 STPROC.BIND_I(LEVELS_TO_EXPLODE);
564 STPROC.BIND_I(BOM_OR_ENG);
565 STPROC.BIND_I(IMPL_FLAG);
566 STPROC.BIND_I(PLAN_FACTOR_FLAG);
567 STPROC.BIND_I(EXPLODE_OPTION);
568 STPROC.BIND_I(MODULE);
569 STPROC.BIND_I(CST_TYPE_ID);
570 STPROC.BIND_I(STD_COMP_FLAG);
571 STPROC.BIND_I(EXPL_QTY);
572 STPROC.BIND_I(ITEM_ID);
573 STPROC.BIND_I(ALT_DESG);
574 STPROC.BIND_I(COMP_CODE);
575 STPROC.BIND_I(REV_DATE);
579 STPROC.RETRIEVE(19
576 STPROC.BIND_O(ERR_MSG);
577 STPROC.BIND_O(ERROR_CODE);
578 STPROC.EXECUTE;
580 ,ERR_MSG);
581 STPROC.RETRIEVE(20
582 ,ERROR_CODE);*/null;
583 END EXPLODER_USEREXIT;
584
585 PROCEDURE EXPLOSION_REPORT(VERIFY_FLAG IN NUMBER
586 ,ORG_ID IN NUMBER
587 ,ORDER_BY IN NUMBER
588 ,LIST_ID IN NUMBER
589 ,GRP_ID IN NUMBER
590 ,SESSION_ID IN NUMBER
591 ,LEVELS_TO_EXPLODE IN NUMBER
592 ,BOM_OR_ENG IN NUMBER
593 ,IMPL_FLAG IN NUMBER
594 ,PLAN_FACTOR_FLAG IN NUMBER
595 ,INCL_LT_FLAG IN NUMBER
596 ,EXPLODE_OPTION IN NUMBER
597 ,MODULE IN NUMBER
598 ,CST_TYPE_ID IN NUMBER
599 ,STD_COMP_FLAG IN NUMBER
600 ,EXPL_QTY IN NUMBER
601 ,REPORT_OPTION IN NUMBER
602 ,REQ_ID IN NUMBER
603 ,CST_RLP_ID IN NUMBER
604 ,LOCK_FLAG IN NUMBER
605 ,ROLLUP_OPTION IN NUMBER
606 ,ALT_RTG_DESG IN VARCHAR2
607 ,ALT_DESG IN VARCHAR2
608 ,REV_DATE IN VARCHAR2
609 ,ERR_MSG OUT NOCOPY VARCHAR2
610 ,ERROR_CODE OUT NOCOPY NUMBER) IS
611 BEGIN
612 /* STPROC.INIT('begin BOMPEXPL.EXPLOSION_REPORT(:VERIFY_FLAG
613 , :ORG_ID, :ORDER_BY, :LIST_ID, :GRP_ID, :SESSION_ID, :LEVELS_TO_EXPLODE,
614 :BOM_OR_ENG, :IMPL_FLAG, :PLAN_FACTOR_FLAG, :INCL_LT_FLAG, :EXPLODE_OPTION, :MODULE, :CST_TYPE_ID,
615 :STD_COMP_FLAG, :EXPL_QTY, :REPORT_OPTION, :REQ_ID, :CST_RLP_ID, :LOCK_FLAG, :ROLLUP_OPTION, :ALT_RTG_DESG,
616 :ALT_DESG, :REV_DATE, :ERR_MSG, :ERROR_CODE); end;');
617 STPROC.BIND_I(VERIFY_FLAG);
618 STPROC.BIND_I(ORG_ID);
619 STPROC.BIND_I(ORDER_BY);
620 STPROC.BIND_I(LIST_ID);
621 STPROC.BIND_I(GRP_ID);
622 STPROC.BIND_I(SESSION_ID);
623 STPROC.BIND_I(LEVELS_TO_EXPLODE);
624 STPROC.BIND_I(BOM_OR_ENG);
625 STPROC.BIND_I(IMPL_FLAG);
626 STPROC.BIND_I(PLAN_FACTOR_FLAG);
627 STPROC.BIND_I(INCL_LT_FLAG);
628 STPROC.BIND_I(EXPLODE_OPTION);
629 STPROC.BIND_I(MODULE);
630 STPROC.BIND_I(CST_TYPE_ID);
631 STPROC.BIND_I(STD_COMP_FLAG);
632 STPROC.BIND_I(EXPL_QTY);
633 STPROC.BIND_I(REPORT_OPTION);
634 STPROC.BIND_I(REQ_ID);
635 STPROC.BIND_I(CST_RLP_ID);
636 STPROC.BIND_I(LOCK_FLAG);
637 STPROC.BIND_I(ROLLUP_OPTION);
638 STPROC.BIND_I(ALT_RTG_DESG);
639 STPROC.BIND_I(ALT_DESG);
640 STPROC.BIND_I(REV_DATE);
641 STPROC.BIND_O(ERR_MSG);
642 STPROC.BIND_O(ERROR_CODE);
643 STPROC.EXECUTE;
644 STPROC.RETRIEVE(25
645 ,ERR_MSG);
646 STPROC.RETRIEVE(26
647 ,ERROR_CODE);*/
648 BOMPEXPL.EXPLOSION_REPORT(VERIFY_FLAG,ORG_ID,ORDER_BY,LIST_ID,GRP_ID,SESSION_ID,
649 LEVELS_TO_EXPLODE,BOM_OR_ENG,IMPL_FLAG,PLAN_FACTOR_FLAG,INCL_LT_FLAG,
650 EXPLODE_OPTION,MODULE,CST_TYPE_ID,STD_COMP_FLAG,EXPL_QTY,REPORT_OPTION,REQ_ID,
651 CST_RLP_ID,LOCK_FLAG,ROLLUP_OPTION,ALT_RTG_DESG,ALT_DESG,REV_DATE,ERR_MSG,ERROR_CODE);
652 END EXPLOSION_REPORT;
653
654 PROCEDURE EXPLODERS(VERIFY_FLAG IN NUMBER
655 ,ONLINE_FLAG IN NUMBER
656 ,ORG_ID IN NUMBER
657 ,ORDER_BY IN NUMBER
658 ,GRP_ID IN NUMBER
659 ,SESSION_ID IN NUMBER
660 ,L_LEVELS_TO_EXPLODE IN NUMBER
661 ,BOM_OR_ENG IN NUMBER
662 ,IMPL_FLAG IN NUMBER
663 ,PLAN_FACTOR_FLAG IN NUMBER
664 ,INCL_LT_FLAG IN NUMBER
665 ,L_EXPLODE_OPTION IN NUMBER
666 ,MODULE IN NUMBER
667 ,CST_TYPE_ID IN NUMBER
668 ,STD_COMP_FLAG IN NUMBER
669 ,REV_DATE IN VARCHAR2
670 ,ERR_MSG OUT NOCOPY VARCHAR2
671 ,ERROR_CODE OUT NOCOPY NUMBER) IS
672 BEGIN
673 /* STPROC.INIT('begin BOMPEXPL.EXPLODERS(:VERIFY_FLAG, :ONLINE_FLAG, :ORG_ID,
674 :ORDER_BY, :GRP_ID, :SESSION_ID, :L_LEVELS_TO_EXPLODE, :BOM_OR_ENG,
675 :IMPL_FLAG, :PLAN_FACTOR_FLAG, :INCL_LT_FLAG, :L_EXPLODE_OPTION, :MODULE, :CST_TYPE_ID, :STD_COMP_FLAG, :REV_DATE, :ERR_MSG, :ERROR_CODE); end;');
676 STPROC.BIND_I(VERIFY_FLAG);
677 STPROC.BIND_I(ONLINE_FLAG);
678 STPROC.BIND_I(ORG_ID);
679 STPROC.BIND_I(ORDER_BY);
680 STPROC.BIND_I(GRP_ID);
681 STPROC.BIND_I(SESSION_ID);
682 STPROC.BIND_I(L_LEVELS_TO_EXPLODE);
683 STPROC.BIND_I(BOM_OR_ENG);
684 STPROC.BIND_I(IMPL_FLAG);
685 STPROC.BIND_I(PLAN_FACTOR_FLAG);
686 STPROC.BIND_I(INCL_LT_FLAG);
687 STPROC.BIND_I(L_EXPLODE_OPTION);
688 STPROC.BIND_I(MODULE);
689 STPROC.BIND_I(CST_TYPE_ID);
690 STPROC.BIND_I(STD_COMP_FLAG);
691 STPROC.BIND_I(REV_DATE);
692 STPROC.BIND_O(ERR_MSG);
693 STPROC.BIND_O(ERROR_CODE);
694 STPROC.EXECUTE;
695 STPROC.RETRIEVE(17
696 ,ERR_MSG);
697 STPROC.RETRIEVE(18
698 ,ERROR_CODE);*/null;
699 END EXPLODERS;
700
701 PROCEDURE LOOPSTR2MSG(GRP_ID IN NUMBER
702 ,VERIFY_MSG OUT NOCOPY VARCHAR2) IS
703 BEGIN
704 /* STPROC.INIT('begin BOMPEXPL.LOOPSTR2MSG(:GRP_ID, :VERIFY_MSG); end;');
705 STPROC.BIND_I(GRP_ID);
706 STPROC.BIND_O(VERIFY_MSG);
707 STPROC.EXECUTE;
708 STPROC.RETRIEVE(2
709 ,VERIFY_MSG);*/null;
710 END LOOPSTR2MSG;
711 FUNCTION g_filter RETURN boolean is
712 BEGIN
713 IF P_PRINT_OPTION1_FLAG = 2 THEN
714 RETURN(FALSE);
715 ELSE
716 RETURN(TRUE);
717 end if;
718 END g_filter;
719 END BOM_BOMRBOMC_XMLP_PKG;
720