[Home] [Help]
PACKAGE BODY: APPS.BOM_BOMRWUIT_XMLP_PKG
Source
1 PACKAGE BODY BOM_BOMRWUIT_XMLP_PKG AS
2 /* $Header: BOMRWUITB.pls 120.5.12020000.2 2012/07/09 11:44:23 nlingamp ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 T_ORG_CODE_LIST INV_ORGHIERARCHY_PVT.ORGID_TBL_TYPE;
7 L_ORG_NAME VARCHAR2(60);
8 N NUMBER := 0;
9 L_ERR_MSG VARCHAR(80);
10 L_ERR_CODE NUMBER;
11 L_STR VARCHAR(2000);
12 L_SORT_CODE VARCHAR(10) := '0001';
13 L_SEQUENCE_ID NUMBER;
14 L_BOM_OR_ENG NUMBER;
15 L_ITEM_NUMBER VARCHAR(245);
16 ITEM_ID_NULL EXCEPTION;
17 TEMP_PROC_ERR EXCEPTION;
18 IMPLOSION_ERR EXCEPTION;
19 TABLE_NAME VARCHAR(20);
20 temp_count number(3):= 0;
21 temp_exp EXCEPTION;
22
23 ROWS_PROCESSED INTEGER;
24 CURSOR_NAME INTEGER;
25 l_CODE varchar2(10); -- :='MSTK';
26 l_APPL_SHORT_NAME varchar2(20) := 'INV';
27 l_OPERATOR varchar2(20);
28 l_OPERAND1 varchar2(20);
29 l_OPERAND2 varchar2(20);
30 l_TABLEALIAS varchar2(20);
31 l_OUTPUT varchar2(20);
32 l_NumOf_Bind_Vars_Assy Number :=0;
33 l_NumOf_Bind_Vars_Cat Number :=0;
34 l_bind_variables_tab_assy fnd_flex_xml_publisher_apis.bind_variables;
35 l_bind_variables_tab_cat fnd_flex_xml_publisher_apis.bind_variables;
36 l_idFlexNum number ;
37 P_ASSY_WHERE Varchar2(2000) := '';
38 P_CAT_WHERE Varchar2(2000) := '';
39
40 cursor c1(flex_code in VARCHAR2, flex_structure_code in VARCHAR2) is
41 SELECT fifst.id_flex_num
42 FROM fnd_id_flex_structures fifst
43 WHERE fifst.application_id = 401
44 AND fifst.id_flex_code = flex_code
45 AND fifst.id_flex_structure_code = flex_structure_code
46 AND fifst.enabled_flag = 'Y'
47 AND fifst.freeze_flex_definition_flag = 'Y'
48 ORDER BY fifst.id_flex_num;
49
50
51 BEGIN
52
53 CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
54 OPEN C1('MSTK', 'SYSTEM_ITEMS');
55 FETCH C1 INTO l_idFlexNuM;
56 CLOSE C1;
57
58 IF (P_ITEM_FROM IS NOT NULL AND P_ITEM_TO IS NOT NULL) THEN
59 l_CODE := 'MSTK';
60 l_OPERAND1 := P_ITEM_FROM;
61 l_OPERAND2 := P_ITEM_TO;
62 l_OPERATOR := 'BETWEEN';
63 l_OUTPUT := 'P_ASS_BETWEEN';
64 l_TABLEALIAS := 'MSI';
65
66 ELSIF (P_ITEM_FROM IS NOT NULL AND P_ITEM_TO IS NULL) THEN
67 l_CODE := 'MSTK';
68 l_OPERAND1 := P_ITEM_FROM;
69 l_OPERAND2 := '';
70 l_OPERATOR := '>=';
71 l_OUTPUT := 'P_ASS_BETWEEN';
72 l_TABLEALIAS := 'MSI';
73
74 ELSIF (P_ITEM_FROM IS NULL AND P_ITEM_TO IS NOT NULL) THEN
75 l_CODE := 'MSTK';
76 l_OPERAND1 := '';
77 l_OPERAND2 := P_ITEM_TO;
78 l_OPERATOR := '<=';
79 l_OUTPUT := 'P_ASS_BETWEEN';
80 l_TABLEALIAS := 'MSI';
81
82 ELSE
83 l_CODE := 'MSTK';
84 l_OPERAND1 := NULL;
85 l_OPERAND2 := NULL;
86 l_OPERATOR := '=';
87 l_OUTPUT := 'P_ASS_BETWEEN';
88 l_TABLEALIAS := 'MSI';
89 END IF;
90
91 FND_FLEX_XML_PUBLISHER_APIS.KFF_WHERE
92 (P_LEXICAL_NAME => l_OUTPUT
93 ,P_APPLICATION_SHORT_NAME => l_APPL_SHORT_NAME
94 ,P_ID_FLEX_CODE => l_CODE
95 ,P_ID_FLEX_NUM => l_idFlexNuM
96 ,P_CODE_COMBINATION_TABLE_ALIAS => l_TABLEALIAS
97 ,P_OPERATOR => l_OPERATOR
98 ,P_OPERAND1 => l_OPERAND1
99 ,P_OPERAND2 => l_OPERAND2
100 ,X_WHERE_EXPRESSION => P_ASSY_WHERE
101 ,X_NUMOF_BIND_VARIABLES => l_NumOf_Bind_Vars_Assy
102 ,X_BIND_VARIABLES => l_bind_variables_tab_assy
103 );
104
105 OPEN C1('MCAT', 'ITEM_CATEGORIES');
106 FETCH C1 INTO l_idFlexNuM;
107 CLOSE C1;
108
109 IF (P_CAT_FROM IS NOT NULL AND P_CAT_TO IS NOT NULL) THEN
110 l_CODE := 'MCAT';
111 l_OPERAND1 := P_CAT_FROM;
112 l_OPERAND2 := P_CAT_TO;
113 l_OPERATOR := 'BETWEEN';
114 l_OUTPUT := 'P_CAT_BETWEEN';
115 l_TABLEALIAS := 'MC';
116
117 ELSIF (P_CAT_FROM IS NOT NULL AND P_CAT_TO IS NULL) THEN
118 l_CODE := 'MCAT';
119 l_OPERAND1 := P_CAT_FROM;
120 l_OPERAND2 := P_CAT_TO;
121 l_OPERATOR := '>=';
122 l_OUTPUT := 'P_CAT_BETWEEN';
123 l_TABLEALIAS := 'MC';
124
125 ELSIF (P_CAT_FROM IS NULL AND P_CAT_TO IS NOT NULL) THEN
126 l_CODE := 'MCAT';
127 l_OPERAND1 := P_CAT_FROM;
128 l_OPERAND2 := P_CAT_TO;
129 l_OPERATOR := '<=';
130 l_OUTPUT := 'P_CAT_BETWEEN';
131 l_TABLEALIAS := 'MC';
132
133 ELSE
134 l_CODE := 'MCAT';
135 l_OPERAND1 := P_CAT_FROM;
136 l_OPERAND2 := P_CAT_TO;
137 l_OPERATOR := '=';
138 l_OUTPUT := 'P_CAT_BETWEEN';
139 l_TABLEALIAS := 'MC';
140 END IF;
141
142
143 FND_FLEX_XML_PUBLISHER_APIS.KFF_WHERE
144 (P_LEXICAL_NAME => l_OUTPUT
145 ,P_APPLICATION_SHORT_NAME => l_APPL_SHORT_NAME
146 ,P_ID_FLEX_CODE => l_CODE
147 ,P_ID_FLEX_NUM => l_idFlexNuM
148 ,P_CODE_COMBINATION_TABLE_ALIAS => l_TABLEALIAS
149 ,P_OPERATOR => l_OPERATOR
150 ,P_OPERAND1 => l_OPERAND1
151 ,P_OPERAND2 => l_OPERAND2
152 ,X_WHERE_EXPRESSION => P_CAT_WHERE
153 ,X_NUMOF_BIND_VARIABLES => l_NumOf_Bind_Vars_Cat
154 ,X_BIND_VARIABLES => l_bind_variables_tab_cat
155 );
156
157 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
158 LP_ORG_ID := P_ORG_ID;
159 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
160 IF P_REPORT_OPTION = 1 AND P_ITEM_ID IS NULL THEN
161 FND_MESSAGE.SET_NAME('null'
162 ,'MFG_REQUIRED_VALUE');
163 FND_MESSAGE.SET_TOKEN('ENTITY'
164 ,'specific item');
165 P_MSG_BUF := FND_MESSAGE.GET;
166 /*SRW.MESSAGE('999'
167 ,P_MSG_BUF)*/NULL;
168 RAISE ITEM_ID_NULL;
169 END IF;
170 P_QTY_PRECISION := FND_PROFILE.VALUE('REPORT_QUANTITY_PRECISION');
171 TABLE_NAME := 'IMPLOSION_TEMP_S:';
172 SELECT
173 BOM_IMPLOSION_TEMP_S.NEXTVAL
174 INTO L_SEQUENCE_ID
175 FROM
176 DUAL;
177 P_SEQUENCE_ID := L_SEQUENCE_ID;
178 IF P_ENG_BILL_FLAG = 'BOM' THEN
179 L_BOM_OR_ENG := 1;
180 ELSE
181 L_BOM_OR_ENG := 2;
182 END IF;
183 TABLE_NAME := 'ITEM_FLEXFIELDS:';
184 IF P_REPORT_OPTION = 1 THEN
185 SELECT
186 ITEM_NUMBER
187 INTO L_ITEM_NUMBER
188 FROM
189 MTL_ITEM_FLEXFIELDS
190 WHERE ITEM_ID = P_ITEM_ID
191 AND ORGANIZATION_ID = LP_ORG_ID;
192 P_SPECIFIC_ITEM := L_ITEM_NUMBER;
193 END IF;
194 TABLE_NAME := 'USER_EXIT_ITEM:';
195 IF P_REPORT_OPTION = 2 THEN
196 IF (P_ITEM_FROM IS NOT NULL) THEN
197 IF (P_ITEM_TO IS NOT NULL) THEN
198 NULL;
199 ELSE
200 NULL;
201 END IF;
202 ELSE
203 IF (P_ITEM_TO IS NOT NULL) THEN
204 NULL;
205 END IF;
206 END IF;
207 TABLE_NAME := 'USER_EXIT_CAT:';
208 IF (P_CAT_FROM IS NOT NULL) THEN
209 IF (P_CAT_TO IS NOT NULL) THEN
210 NULL;
211 ELSE
212 NULL;
213 END IF;
214 ELSE
215 IF (P_CAT_TO IS NOT NULL) THEN
216 NULL;
217 END IF;
218 END IF;
219 END IF;
220 IF P_DATE IS NULL THEN
221 P_DATE := TO_CHAR(SYSDATE
222 ,'YYYY/MM/DD HH24:MI:SS');
223 END IF;
224 TABLE_NAME := 'IMPLOSION_TEMP:';
225 IF P_ALL_ORGS = 1 THEN
226 FOR C1 IN (SELECT
227 ORGANIZATION_ID
228 FROM
229 MTL_PARAMETERS MP
230 WHERE MASTER_ORGANIZATION_ID = (
231 SELECT
232 MASTER_ORGANIZATION_ID
233 FROM
234 MTL_PARAMETERS
235 WHERE ORGANIZATION_ID = LP_ORG_ID )
236 AND MP.ORGANIZATION_ID IN (
237 SELECT
238 ORGANIZATION_ID
239 FROM
240 ORG_ACCESS_VIEW
241 WHERE RESPONSIBILITY_ID = FND_PROFILE.VALUE('RESP_ID')
242 AND RESP_APPLICATION_ID = FND_PROFILE.VALUE('RESP_APPL_ID') )) LOOP
243 N := N + 1;
244 T_ORG_CODE_LIST(N) := C1.ORGANIZATION_ID;
245 END LOOP;
246 ELSIF P_ALL_ORGS = 2 THEN
247 IF P_ORG_HIERARCHY IS NOT NULL THEN
248 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(P_ORG_HIERARCHY
249 ,LP_ORG_ID
250 ,T_ORG_CODE_LIST);
251 ELSIF P_ORG_HIERARCHY IS NULL THEN
252 T_ORG_CODE_LIST(1) := LP_ORG_ID;
253 END IF;
254 END IF;
255 FOR I IN T_ORG_CODE_LIST.FIRST .. T_ORG_CODE_LIST.LAST LOOP
256 LP_ORG_ID := T_ORG_CODE_LIST(I);
257 L_STR := 'INSERT INTO BOM_IMPLOSION_TEMP (
258 SEQUENCE_ID,LOWEST_ITEM_ID,CURRENT_ITEM_ID,PARENT_ITEM_ID,
259 CURRENT_LEVEL,SORT_CODE,CURRENT_ASSEMBLY_TYPE,
260 LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
261 REQUEST_ID,ORGANIZATION_ID) ';
262 IF P_REPORT_OPTION = 1 THEN
263 INSERT INTO BOM_IMPLOSION_TEMP
264 (SEQUENCE_ID
265 ,LOWEST_ITEM_ID
266 ,CURRENT_ITEM_ID
267 ,PARENT_ITEM_ID
268 ,CURRENT_LEVEL
269 ,SORT_CODE
270 ,CURRENT_ASSEMBLY_TYPE
271 ,LAST_UPDATE_DATE
272 ,LAST_UPDATED_BY
273 ,CREATION_DATE
274 ,CREATED_BY
275 ,REQUEST_ID
276 ,ORGANIZATION_ID)
277 VALUES (L_SEQUENCE_ID
278 ,P_ITEM_ID
279 ,P_ITEM_ID
280 ,P_ITEM_ID
281 ,0
282 ,L_SORT_CODE
283 ,NULL
284 ,SYSDATE
285 ,1
286 ,SYSDATE
287 ,1
288 ,P_CONC_REQUEST_ID
289 ,LP_ORG_ID);
290 ELSE
291 L_STR := L_STR || 'SELECT /*+ ORDERED */ DISTINCT ' || TO_CHAR(L_SEQUENCE_ID) || ', MSI.INVENTORY_ITEM_ID,MSI.INVENTORY_ITEM_ID,MSI.INVENTORY_ITEM_ID
292 ,0, ' || L_SORT_CODE || ', ' || 'NULL' || ', SYSDATE, 1, SYSDATE, 1,' || TO_CHAR(P_CONC_REQUEST_ID) || ', ' || TO_CHAR(LP_ORG_ID) || ' FROM MTL_SYSTEM_ITEMS MSI,
293 MTL_ITEM_CATEGORIES MIC,
294 MTL_CATEGORIES MC
295 WHERE ' || P_ASSY_WHERE || '
296 AND MSI.BOM_ENABLED_FLAG = ''Y''
297 AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
298 AND MSI.ORGANIZATION_ID = ' || TO_CHAR(LP_ORG_ID) || '
299 AND MIC.ORGANIZATION_ID = ' || TO_CHAR(LP_ORG_ID) || '
300 AND MIC.CATEGORY_SET_ID = ' || TO_CHAR(P_SET_ID) || '
301 AND MIC.CATEGORY_ID = MC.CATEGORY_ID
302 AND MC.STRUCTURE_ID =
303 ' || TO_CHAR(P_CATEGORY_STRUCTURE_ID) || '
304 AND ' || P_CAT_WHERE ;
305
306 DBMS_SQL.PARSE(CURSOR_NAME
307 ,L_STR
308 ,1);
309
310 FOR i IN 1..l_NumOf_Bind_Vars_Assy LOOP
311 IF (l_bind_variables_tab_assy(i).data_type='VARCHAR2') THEN
312 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).varchar2_value);
313 ELSIF (l_bind_variables_tab_assy(i).data_type='NUMBER') THEN
314 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).canonical_value);
315 ELSIF (l_bind_variables_tab_assy(i).data_type='DATE') THEN
316 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_assy(i).name,l_bind_variables_tab_assy(i).date_value);
317 END IF;
318 END LOOP ;
319
320 FOR i IN 1..l_NumOf_Bind_Vars_Cat LOOP
321 IF (l_bind_variables_tab_cat(i).data_type='VARCHAR2') THEN
322 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).varchar2_value);
323 ELSIF (l_bind_variables_tab_cat(i).data_type='NUMBER') THEN
324 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).canonical_value);
325 ELSIF (l_bind_variables_tab_cat(i).data_type='DATE') THEN
326 DBMS_SQL.BIND_VARIABLE(CURSOR_NAME,l_bind_variables_tab_cat(i).name,l_bind_variables_tab_cat(i).date_value);
327 END IF;
328 END LOOP ;
329
330 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CURSOR_NAME);
331 -- DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME); --bug 12998515
332
333 END IF;
334 IMPLOSION(L_SEQUENCE_ID
335 ,L_BOM_OR_ENG
336 ,LP_ORG_ID
337 ,P_IMPLEMENTED
338 ,P_DATE_OPTION
339 ,P_LEVEL
340 ,P_DATE
341 ,L_ERR_MSG
342 ,L_ERR_CODE);
343 IF L_ERR_CODE <> 0 THEN
344 RAISE IMPLOSION_ERR;
345 END IF;
346 END LOOP;
347 LP_QTY_PRECISION:=get_precision(P_QTY_PRECISION);
348 P_DATE_1:=P_DATE;
349 IF (DBMS_SQL.IS_OPEN(CURSOR_NAME)) THEN --bug 12998515
350 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
351 END IF;
352 RETURN (TRUE);
353 EXCEPTION
354 WHEN temp_exp THEN
355 --RAISE_APPLICATION_ERROR(-20101,TABLE_NAME ||temp_count);
356 IF (DBMS_SQL.IS_OPEN(CURSOR_NAME)) THEN --bug 12998515
357 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
358 END IF;
359 null;
360 WHEN ITEM_ID_NULL THEN
361 IF (DBMS_SQL.IS_OPEN(CURSOR_NAME)) THEN --bug 12998515
362 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
363 END IF;
364 /*SRW.MESSAGE('6','aborting...')*/NULL;
365 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,TABLE_NAME || SQLERRM);
366 WHEN IMPLOSION_ERR THEN
367 IF (DBMS_SQL.IS_OPEN(CURSOR_NAME)) THEN --bug 12998515
368 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
369 END IF;
370 /*SRW.MESSAGE('4000'
371 ,L_ERR_MSG)*/NULL;RAISE_APPLICATION_ERROR(-20102,TABLE_NAME || SQLERRM);
372 RETURN (FALSE);
373 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
374 IF (DBMS_SQL.IS_OPEN(CURSOR_NAME)) THEN --bug 12998515
375 DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
376 END IF;
377 /*SRW.MESSAGE('1000'
378 ,TABLE_NAME || SQLERRM)*/NULL;RAISE_APPLICATION_ERROR(-20103,TABLE_NAME || SQLERRM);
379 RETURN (FALSE);
380
381 END;
382 RETURN (TRUE);
383 END BEFOREREPORT;
384
385 FUNCTION AFTERREPORT RETURN BOOLEAN IS
386 BEGIN
387 BEGIN
388 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
389 ROLLBACK;
390 END;
391 RETURN (TRUE);
392 END AFTERREPORT;
393
394 FUNCTION GET_STATUS(REVISED_ITEM_SEQUENCE_ID IN NUMBER
395 ,IMPLEMENTED_FLAG IN NUMBER
396 ,NOTICE IN VARCHAR2) RETURN VARCHAR2 IS
397 STATUS_NAME VARCHAR2(80);
398 BEGIN
399 IF REVISED_ITEM_SEQUENCE_ID IS NULL THEN
400 IF (IMPLEMENTED_FLAG = 1 AND NOTICE IS NOT NULL) THEN
401 SELECT
402 ML.MEANING
403 INTO STATUS_NAME
404 FROM
405 MFG_LOOKUPS ML
406 WHERE ML.LOOKUP_CODE = 6
407 AND ML.LOOKUP_TYPE = 'ECG_ECN_STATUS';
408 END IF;
409 ELSE
410 SELECT
411 ML.MEANING
412 INTO STATUS_NAME
413 FROM
414 ENG_REVISED_ITEMS ERI,
415 MFG_LOOKUPS ML
416 WHERE ERI.REVISED_ITEM_SEQUENCE_ID = REVISED_ITEM_SEQUENCE_ID
417 AND ML.LOOKUP_CODE = ERI.STATUS_TYPE
418 AND ML.LOOKUP_TYPE = 'ECG_ECN_STATUS';
419 END IF;
420 RETURN (STATUS_NAME);
421 EXCEPTION
422 WHEN OTHERS THEN
423 RETURN ('');
424 END GET_STATUS;
425
426 FUNCTION GET_ORGCODE(ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
427 ORG_CODE VARCHAR2(3);
428 BEGIN
429 IF ORGANIZATION_ID IS NOT NULL THEN
430 SELECT
431 ORG.ORGANIZATION_CODE
432 INTO ORG_CODE
433 FROM
434 ORG_ORGANIZATION_DEFINITIONS ORG
435 WHERE ORG.ORGANIZATION_ID = GET_ORGCODE.ORGANIZATION_ID;
436 ELSE
437 RETURN ('');
438 END IF;
439 RETURN (ORG_CODE);
440 END GET_ORGCODE;
441
442 FUNCTION CF_ALL_ORGSFORMULA RETURN CHAR IS
443 BEGIN
444 IF P_ALL_ORGS = 1 THEN
445 RETURN ('Yes');
446 ELSE
447 RETURN ('No');
448 END IF;
449 END CF_ALL_ORGSFORMULA;
450
451 PROCEDURE IMPLODER_USEREXIT(SEQUENCE_ID IN NUMBER
452 ,ENG_MFG_FLAG IN NUMBER
453 ,ORG_ID IN NUMBER
454 ,IMPL_FLAG IN NUMBER
455 ,DISPLAY_OPTION IN NUMBER
456 ,LEVELS_TO_IMPLODE IN NUMBER
457 ,ITEM_ID IN NUMBER
458 ,IMPL_DATE IN VARCHAR2
459 ,ERR_MSG OUT NOCOPY VARCHAR2
460 ,ERR_CODE OUT NOCOPY NUMBER) IS
461 BEGIN
462 /*STPROC.INIT('begin BOMPIMPL.IMPLODER_USEREXIT(:SEQUENCE_ID, :ENG_MFG_FLAG, :ORG_ID, :IMPL_FLAG, :DISPLAY_OPTION, :LEVELS_TO_IMPLODE, :ITEM_ID, :IMPL_DATE, :ERR_MSG, :ERR_CODE); end;');
463 STPROC.BIND_I(SEQUENCE_ID);
464 STPROC.BIND_I(ENG_MFG_FLAG);
465 STPROC.BIND_I(ORG_ID);
466 STPROC.BIND_I(IMPL_FLAG);
467 STPROC.BIND_I(DISPLAY_OPTION);
468 STPROC.BIND_I(LEVELS_TO_IMPLODE);
469 STPROC.BIND_I(ITEM_ID);
470 STPROC.BIND_I(IMPL_DATE);
471 STPROC.BIND_O(ERR_MSG);
472 STPROC.BIND_O(ERR_CODE);
473 STPROC.EXECUTE;
474 STPROC.RETRIEVE(9
475 ,ERR_MSG);
476 STPROC.RETRIEVE(10
477 ,ERR_CODE);*/
478 BOMPIMPL.IMPLODER_USEREXIT(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, DISPLAY_OPTION, LEVELS_TO_IMPLODE, ITEM_ID, IMPL_DATE, ERR_MSG, ERR_CODE);
479 END IMPLODER_USEREXIT;
480
481 PROCEDURE IMPLOSION(SEQUENCE_ID IN NUMBER
482 ,ENG_MFG_FLAG IN NUMBER
483 ,ORG_ID IN NUMBER
484 ,IMPL_FLAG IN NUMBER
485 ,DISPLAY_OPTION IN NUMBER
486 ,LEVELS_TO_IMPLODE IN NUMBER
487 ,IMPL_DATE IN VARCHAR2
488 ,ERR_MSG OUT NOCOPY VARCHAR2
489 ,ERR_CODE OUT NOCOPY NUMBER) IS
490 BEGIN
491 /*STPROC.INIT('begin BOMPIMPL.IMPLOSION(:SEQUENCE_ID, :ENG_MFG_FLAG, :ORG_ID, :IMPL_FLAG, :DISPLAY_OPTION, :LEVELS_TO_IMPLODE, :IMPL_DATE, :ERR_MSG, :ERR_CODE); end;');
492 STPROC.BIND_I(SEQUENCE_ID);
493 STPROC.BIND_I(ENG_MFG_FLAG);
494 STPROC.BIND_I(ORG_ID);
495 STPROC.BIND_I(IMPL_FLAG);
496 STPROC.BIND_I(DISPLAY_OPTION);
497 STPROC.BIND_I(LEVELS_TO_IMPLODE);
498 STPROC.BIND_I(IMPL_DATE);
499 STPROC.BIND_O(ERR_MSG);
500 STPROC.BIND_O(ERR_CODE);
501 STPROC.EXECUTE;
502 STPROC.RETRIEVE(8
503 ,ERR_MSG);
504 STPROC.RETRIEVE(9
505 ,ERR_CODE);*/
506
507
508 BOMPIMPL.IMPLOSION(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, DISPLAY_OPTION, LEVELS_TO_IMPLODE, IMPL_DATE, ERR_MSG, ERR_CODE);
509 END IMPLOSION;
510
511 PROCEDURE SL_IMPLODER(SEQUENCE_ID IN NUMBER
512 ,ENG_MFG_FLAG IN NUMBER
513 ,ORG_ID IN NUMBER
514 ,IMPL_FLAG IN NUMBER
515 ,DISPLAY_OPTION IN NUMBER
516 ,IMPL_DATE IN VARCHAR2
517 ,ERR_MSG OUT NOCOPY VARCHAR2
518 ,ERROR_CODE OUT NOCOPY NUMBER) IS
519 BEGIN
520 /*STPROC.INIT('begin BOMPIMPL.SL_IMPLODER(:SEQUENCE_ID, :ENG_MFG_FLAG, :ORG_ID, :IMPL_FLAG, :DISPLAY_OPTION, :IMPL_DATE, :ERR_MSG, :ERROR_CODE); end;');
521 STPROC.BIND_I(SEQUENCE_ID);
522 STPROC.BIND_I(ENG_MFG_FLAG);
523 STPROC.BIND_I(ORG_ID);
524 STPROC.BIND_I(IMPL_FLAG);
525 STPROC.BIND_I(DISPLAY_OPTION);
526 STPROC.BIND_I(IMPL_DATE);
527 STPROC.BIND_O(ERR_MSG);
528 STPROC.BIND_O(ERROR_CODE);
529 STPROC.EXECUTE;
530 STPROC.RETRIEVE(7
531 ,ERR_MSG);
532 STPROC.RETRIEVE(8
533 ,ERROR_CODE);*/
534
535 BOMPIMPL.SL_IMPLODER(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, DISPLAY_OPTION, IMPL_DATE, ERR_MSG, ERROR_CODE);
536 END SL_IMPLODER;
537
538 PROCEDURE ML_IMPLODER(SEQUENCE_ID IN NUMBER
539 ,ENG_MFG_FLAG IN NUMBER
540 ,ORG_ID IN NUMBER
541 ,IMPL_FLAG IN NUMBER
542 ,A_LEVELS_TO_IMPLODE IN NUMBER
543 ,IMPL_DATE IN VARCHAR2
544 ,ERR_MSG OUT NOCOPY VARCHAR2
545 ,ERROR_CODE OUT NOCOPY NUMBER) IS
546 BEGIN
547 /*STPROC.INIT('begin BOMPIMPL.ML_IMPLODER(:SEQUENCE_ID, :ENG_MFG_FLAG, :ORG_ID, :IMPL_FLAG, :A_LEVELS_TO_IMPLODE, :IMPL_DATE, :ERR_MSG, :ERROR_CODE); end;');
548 STPROC.BIND_I(SEQUENCE_ID);
549 STPROC.BIND_I(ENG_MFG_FLAG);
550 STPROC.BIND_I(ORG_ID);
551 STPROC.BIND_I(IMPL_FLAG);
552 STPROC.BIND_I(A_LEVELS_TO_IMPLODE);
553 STPROC.BIND_I(IMPL_DATE);
554 STPROC.BIND_O(ERR_MSG);
555 STPROC.BIND_O(ERROR_CODE);
556 STPROC.EXECUTE;
557 STPROC.RETRIEVE(7
558 ,ERR_MSG);
559 STPROC.RETRIEVE(8
560 ,ERROR_CODE);*/
561 BOMPIMPL.ML_IMPLODER(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, A_LEVELS_TO_IMPLODE, IMPL_DATE, ERR_MSG, ERROR_CODE);
562 END ML_IMPLODER;
563
564 function get_precision(qty_precision in number) return VARCHAR2 is
565 begin
566
567 if qty_precision = 0 then return('999G999G999G990');
568
569 elsif qty_precision = 1 then return('999G999G999G990D0');
570
571 elsif qty_precision = 3 then return('999G999G999G990D000');
572
573 elsif qty_precision = 4 then return('999G999G999G990D0000');
574
575 elsif qty_precision = 5 then return('999G999G999G990D00000');
576
577 elsif qty_precision = 6 then return('999G999G999G990D000000');
578
579 else return('999G999G999G990D00');
580
581 end if;
582
583 end;
584
585 END BOM_BOMRWUIT_XMLP_PKG;
586