eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BOM_BOMRWUIT_XMLP_PKG

Source


1 PACKAGE BODY BOM_BOMRWUIT_XMLP_PKG AS
2 /* $Header: BOMRWUITB.pls 120.1 2008/01/07 07:12:06 nchinnam noship $ */
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     BEGIN
24       P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
25       LP_ORG_ID := P_ORG_ID;
26       /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
27       IF P_REPORT_OPTION = 1 AND P_ITEM_ID IS NULL THEN
28         FND_MESSAGE.SET_NAME('null'
29                             ,'MFG_REQUIRED_VALUE');
30         FND_MESSAGE.SET_TOKEN('ENTITY'
31                              ,'specific item');
32         P_MSG_BUF := FND_MESSAGE.GET;
33         /*SRW.MESSAGE('999'
34                    ,P_MSG_BUF)*/NULL;
35         RAISE ITEM_ID_NULL;
36       END IF;
37       P_QTY_PRECISION := FND_PROFILE.VALUE('REPORT_QUANTITY_PRECISION');
38       TABLE_NAME := 'IMPLOSION_TEMP_S:';
39       SELECT
40         BOM_IMPLOSION_TEMP_S.NEXTVAL
41       INTO L_SEQUENCE_ID
42       FROM
43         DUAL;
44       P_SEQUENCE_ID := L_SEQUENCE_ID;
45       IF P_ENG_BILL_FLAG = 'BOM' THEN
46         L_BOM_OR_ENG := 1;
47       ELSE
48         L_BOM_OR_ENG := 2;
49       END IF;
50       TABLE_NAME := 'ITEM_FLEXFIELDS:';
51       IF P_REPORT_OPTION = 1 THEN
52         SELECT
53           ITEM_NUMBER
54         INTO L_ITEM_NUMBER
55         FROM
56           MTL_ITEM_FLEXFIELDS
57         WHERE ITEM_ID = P_ITEM_ID
58           AND ORGANIZATION_ID = LP_ORG_ID;
59         P_SPECIFIC_ITEM := L_ITEM_NUMBER;
60       END IF;
61       TABLE_NAME := 'USER_EXIT_ITEM:';
62       IF P_REPORT_OPTION = 2 THEN
63         IF (P_ITEM_FROM IS NOT NULL) THEN
64           IF (P_ITEM_TO IS NOT NULL) THEN
65             NULL;
66           ELSE
67             NULL;
68           END IF;
69         ELSE
70           IF (P_ITEM_TO IS NOT NULL) THEN
71             NULL;
72           END IF;
73         END IF;
74         TABLE_NAME := 'USER_EXIT_CAT:';
75         IF (P_CAT_FROM IS NOT NULL) THEN
76           IF (P_CAT_TO IS NOT NULL) THEN
77             NULL;
78           ELSE
79             NULL;
80           END IF;
81         ELSE
82           IF (P_CAT_TO IS NOT NULL) THEN
83             NULL;
84           END IF;
85         END IF;
86       END IF;
87       IF P_DATE IS NULL THEN
88         P_DATE := TO_CHAR(SYSDATE
89                          ,'YYYY/MM/DD HH24:MI:SS');
90       END IF;
91       TABLE_NAME := 'IMPLOSION_TEMP:';
92       IF P_ALL_ORGS = 1 THEN
93         FOR C1 IN (SELECT
94                      ORGANIZATION_ID
95                    FROM
96                      MTL_PARAMETERS MP
97                    WHERE MASTER_ORGANIZATION_ID = (
98                      SELECT
99                        MASTER_ORGANIZATION_ID
100                      FROM
101                        MTL_PARAMETERS
102                      WHERE ORGANIZATION_ID = LP_ORG_ID )
103                      AND MP.ORGANIZATION_ID IN (
104                      SELECT
105                        ORGANIZATION_ID
106                      FROM
107                        ORG_ACCESS_VIEW
108                      WHERE RESPONSIBILITY_ID = FND_PROFILE.VALUE('RESP_ID')
109                        AND RESP_APPLICATION_ID = FND_PROFILE.VALUE('RESP_APPL_ID') )) LOOP
110           N := N + 1;
111           T_ORG_CODE_LIST(N) := C1.ORGANIZATION_ID;
112         END LOOP;
113       ELSIF P_ALL_ORGS = 2 THEN
114         IF P_ORG_HIERARCHY IS NOT NULL THEN
115           INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(P_ORG_HIERARCHY
116                                                  ,LP_ORG_ID
117                                                  ,T_ORG_CODE_LIST);
118         ELSIF P_ORG_HIERARCHY IS NULL THEN
119           T_ORG_CODE_LIST(1) := LP_ORG_ID;
120         END IF;
121       END IF;
122       FOR I IN T_ORG_CODE_LIST.FIRST .. T_ORG_CODE_LIST.LAST LOOP
123         LP_ORG_ID := T_ORG_CODE_LIST(I);
124         L_STR := 'INSERT INTO BOM_IMPLOSION_TEMP (
125                                    SEQUENCE_ID,LOWEST_ITEM_ID,CURRENT_ITEM_ID,PARENT_ITEM_ID,
126                                    CURRENT_LEVEL,SORT_CODE,CURRENT_ASSEMBLY_TYPE,
127                                    LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
128                                    REQUEST_ID,ORGANIZATION_ID) ';
129         IF P_REPORT_OPTION = 1 THEN
130           INSERT INTO BOM_IMPLOSION_TEMP
131             (SEQUENCE_ID
132             ,LOWEST_ITEM_ID
133             ,CURRENT_ITEM_ID
134             ,PARENT_ITEM_ID
135             ,CURRENT_LEVEL
136             ,SORT_CODE
137             ,CURRENT_ASSEMBLY_TYPE
138             ,LAST_UPDATE_DATE
139             ,LAST_UPDATED_BY
140             ,CREATION_DATE
141             ,CREATED_BY
142             ,REQUEST_ID
143             ,ORGANIZATION_ID)
144           VALUES   (L_SEQUENCE_ID
145             ,P_ITEM_ID
146             ,P_ITEM_ID
147             ,P_ITEM_ID
148             ,0
149             ,L_SORT_CODE
150             ,NULL
151             ,SYSDATE
152             ,1
153             ,SYSDATE
154             ,1
155             ,P_CONC_REQUEST_ID
156             ,LP_ORG_ID);
157         ELSE
158           L_STR := L_STR || 'SELECT /*+ ORDERED */ DISTINCT ' || TO_CHAR(L_SEQUENCE_ID) || ', MSI.INVENTORY_ITEM_ID,MSI.INVENTORY_ITEM_ID,MSI.INVENTORY_ITEM_ID
159                                                  ,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,
160                                                      MTL_ITEM_CATEGORIES MIC,
161                                                      MTL_CATEGORIES MC
162                                                  WHERE ' || P_ASS_BETWEEN || '
163                                                  AND    MSI.BOM_ENABLED_FLAG = ''Y''
164                                                  AND    MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
165                                                  AND    MSI.ORGANIZATION_ID = ' || TO_CHAR(LP_ORG_ID) || '
166                                                  AND    MIC.ORGANIZATION_ID = ' || TO_CHAR(LP_ORG_ID) || '
167                                                  AND    MIC.CATEGORY_SET_ID = ' || TO_CHAR(P_SET_ID) || '
168                                                  AND    MIC.CATEGORY_ID = MC.CATEGORY_ID
169                                                  AND    MC.STRUCTURE_ID =
170                                                  ' || TO_CHAR(P_CATEGORY_STRUCTURE_ID) || '
171                                                  AND    ' || P_CAT_BETWEEN;
172           EXECUTE IMMEDIATE
173             L_STR;
174         END IF;
175         IMPLOSION(L_SEQUENCE_ID
176                  ,L_BOM_OR_ENG
177                  ,LP_ORG_ID
178                  ,P_IMPLEMENTED
179                  ,P_DATE_OPTION
180                  ,P_LEVEL
181                  ,P_DATE
182                  ,L_ERR_MSG
183                  ,L_ERR_CODE);
184         IF L_ERR_CODE <> 0 THEN
185           RAISE IMPLOSION_ERR;
186         END IF;
187       END LOOP;
188       LP_QTY_PRECISION:=get_precision(P_QTY_PRECISION);
189       P_DATE_1:=P_DATE;
190       RETURN (TRUE);
191     EXCEPTION
192       WHEN temp_exp THEN
193         --RAISE_APPLICATION_ERROR(-20101,TABLE_NAME ||temp_count);
194 	null;
195       WHEN ITEM_ID_NULL THEN
196         /*SRW.MESSAGE('6','aborting...')*/NULL;
197         /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,TABLE_NAME || SQLERRM);
198       WHEN IMPLOSION_ERR THEN
199         /*SRW.MESSAGE('4000'
200                    ,L_ERR_MSG)*/NULL;RAISE_APPLICATION_ERROR(-20102,TABLE_NAME || SQLERRM);
201         RETURN (FALSE);
202       WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
203         /*SRW.MESSAGE('1000'
204                    ,TABLE_NAME || SQLERRM)*/NULL;RAISE_APPLICATION_ERROR(-20103,TABLE_NAME || SQLERRM);
205         RETURN (FALSE);
206 
207     END;
208     RETURN (TRUE);
209   END BEFOREREPORT;
210 
211   FUNCTION AFTERREPORT RETURN BOOLEAN IS
212   BEGIN
213     BEGIN
214       /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
215       ROLLBACK;
216     END;
217     RETURN (TRUE);
218   END AFTERREPORT;
219 
220   FUNCTION GET_STATUS(REVISED_ITEM_SEQUENCE_ID IN NUMBER
221                      ,IMPLEMENTED_FLAG IN NUMBER
222                      ,NOTICE IN VARCHAR2) RETURN VARCHAR2 IS
223     STATUS_NAME VARCHAR2(80);
224   BEGIN
225     IF REVISED_ITEM_SEQUENCE_ID IS NULL THEN
226       IF (IMPLEMENTED_FLAG = 1 AND NOTICE IS NOT NULL) THEN
227         SELECT
228           ML.MEANING
229         INTO STATUS_NAME
230         FROM
231           MFG_LOOKUPS ML
232         WHERE ML.LOOKUP_CODE = 6
233           AND ML.LOOKUP_TYPE = 'ECG_ECN_STATUS';
234       END IF;
235     ELSE
236       SELECT
237         ML.MEANING
238       INTO STATUS_NAME
239       FROM
240         ENG_REVISED_ITEMS ERI,
241         MFG_LOOKUPS ML
242       WHERE ERI.REVISED_ITEM_SEQUENCE_ID = REVISED_ITEM_SEQUENCE_ID
243         AND ML.LOOKUP_CODE = ERI.STATUS_TYPE
244         AND ML.LOOKUP_TYPE = 'ECG_ECN_STATUS';
245     END IF;
246     RETURN (STATUS_NAME);
247   EXCEPTION
248     WHEN OTHERS THEN
249       RETURN ('');
250   END GET_STATUS;
251 
252   FUNCTION GET_ORGCODE(ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
253     ORG_CODE VARCHAR2(3);
254   BEGIN
255     IF ORGANIZATION_ID IS NOT NULL THEN
256       SELECT
257         ORG.ORGANIZATION_CODE
258       INTO ORG_CODE
259       FROM
260         ORG_ORGANIZATION_DEFINITIONS ORG
261       WHERE ORG.ORGANIZATION_ID = GET_ORGCODE.ORGANIZATION_ID;
262     ELSE
263       RETURN ('');
264     END IF;
265     RETURN (ORG_CODE);
266   END GET_ORGCODE;
267 
268   FUNCTION CF_ALL_ORGSFORMULA RETURN CHAR IS
269   BEGIN
270     IF P_ALL_ORGS = 1 THEN
271       RETURN ('Yes');
272     ELSE
273       RETURN ('No');
274     END IF;
275   END CF_ALL_ORGSFORMULA;
276 
277   PROCEDURE IMPLODER_USEREXIT(SEQUENCE_ID IN NUMBER
278                              ,ENG_MFG_FLAG IN NUMBER
279                              ,ORG_ID IN NUMBER
280                              ,IMPL_FLAG IN NUMBER
281                              ,DISPLAY_OPTION IN NUMBER
282                              ,LEVELS_TO_IMPLODE IN NUMBER
283                              ,ITEM_ID IN NUMBER
284                              ,IMPL_DATE IN VARCHAR2
285                              ,ERR_MSG OUT NOCOPY VARCHAR2
286                              ,ERR_CODE OUT NOCOPY NUMBER) IS
287   BEGIN
288     /*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;');
289     STPROC.BIND_I(SEQUENCE_ID);
290     STPROC.BIND_I(ENG_MFG_FLAG);
291     STPROC.BIND_I(ORG_ID);
292     STPROC.BIND_I(IMPL_FLAG);
293     STPROC.BIND_I(DISPLAY_OPTION);
294     STPROC.BIND_I(LEVELS_TO_IMPLODE);
295     STPROC.BIND_I(ITEM_ID);
296     STPROC.BIND_I(IMPL_DATE);
297     STPROC.BIND_O(ERR_MSG);
298     STPROC.BIND_O(ERR_CODE);
299     STPROC.EXECUTE;
300     STPROC.RETRIEVE(9
301                    ,ERR_MSG);
302     STPROC.RETRIEVE(10
303                    ,ERR_CODE);*/
304     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);
305   END IMPLODER_USEREXIT;
306 
307   PROCEDURE IMPLOSION(SEQUENCE_ID IN NUMBER
308                      ,ENG_MFG_FLAG IN NUMBER
309                      ,ORG_ID IN NUMBER
310                      ,IMPL_FLAG IN NUMBER
311                      ,DISPLAY_OPTION IN NUMBER
312                      ,LEVELS_TO_IMPLODE IN NUMBER
313                      ,IMPL_DATE IN VARCHAR2
314                      ,ERR_MSG OUT NOCOPY VARCHAR2
315                      ,ERR_CODE OUT NOCOPY NUMBER) IS
316   BEGIN
317     /*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;');
318     STPROC.BIND_I(SEQUENCE_ID);
319     STPROC.BIND_I(ENG_MFG_FLAG);
320     STPROC.BIND_I(ORG_ID);
321     STPROC.BIND_I(IMPL_FLAG);
322     STPROC.BIND_I(DISPLAY_OPTION);
323     STPROC.BIND_I(LEVELS_TO_IMPLODE);
324     STPROC.BIND_I(IMPL_DATE);
325     STPROC.BIND_O(ERR_MSG);
326     STPROC.BIND_O(ERR_CODE);
327     STPROC.EXECUTE;
328     STPROC.RETRIEVE(8
329                    ,ERR_MSG);
330     STPROC.RETRIEVE(9
331                    ,ERR_CODE);*/
332 
333 
334   BOMPIMPL.IMPLOSION(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, DISPLAY_OPTION, LEVELS_TO_IMPLODE, IMPL_DATE, ERR_MSG, ERR_CODE);
335 END IMPLOSION;
336 
337   PROCEDURE SL_IMPLODER(SEQUENCE_ID IN NUMBER
338                        ,ENG_MFG_FLAG IN NUMBER
339                        ,ORG_ID IN NUMBER
340                        ,IMPL_FLAG IN NUMBER
341                        ,DISPLAY_OPTION IN NUMBER
342                        ,IMPL_DATE IN VARCHAR2
343                        ,ERR_MSG OUT NOCOPY VARCHAR2
344                        ,ERROR_CODE OUT NOCOPY NUMBER) IS
345   BEGIN
346     /*STPROC.INIT('begin BOMPIMPL.SL_IMPLODER(:SEQUENCE_ID, :ENG_MFG_FLAG, :ORG_ID, :IMPL_FLAG, :DISPLAY_OPTION, :IMPL_DATE, :ERR_MSG, :ERROR_CODE); end;');
347     STPROC.BIND_I(SEQUENCE_ID);
348     STPROC.BIND_I(ENG_MFG_FLAG);
349     STPROC.BIND_I(ORG_ID);
350     STPROC.BIND_I(IMPL_FLAG);
351     STPROC.BIND_I(DISPLAY_OPTION);
352     STPROC.BIND_I(IMPL_DATE);
353     STPROC.BIND_O(ERR_MSG);
354     STPROC.BIND_O(ERROR_CODE);
355     STPROC.EXECUTE;
356     STPROC.RETRIEVE(7
357                    ,ERR_MSG);
358     STPROC.RETRIEVE(8
359                    ,ERROR_CODE);*/
360 
361     BOMPIMPL.SL_IMPLODER(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, DISPLAY_OPTION, IMPL_DATE, ERR_MSG, ERROR_CODE);
362   END SL_IMPLODER;
363 
364   PROCEDURE ML_IMPLODER(SEQUENCE_ID IN NUMBER
365                        ,ENG_MFG_FLAG IN NUMBER
366                        ,ORG_ID IN NUMBER
367                        ,IMPL_FLAG IN NUMBER
368                        ,A_LEVELS_TO_IMPLODE IN NUMBER
369                        ,IMPL_DATE IN VARCHAR2
370                        ,ERR_MSG OUT NOCOPY VARCHAR2
371                        ,ERROR_CODE OUT NOCOPY NUMBER) IS
372   BEGIN
373     /*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;');
374     STPROC.BIND_I(SEQUENCE_ID);
375     STPROC.BIND_I(ENG_MFG_FLAG);
376     STPROC.BIND_I(ORG_ID);
377     STPROC.BIND_I(IMPL_FLAG);
378     STPROC.BIND_I(A_LEVELS_TO_IMPLODE);
379     STPROC.BIND_I(IMPL_DATE);
380     STPROC.BIND_O(ERR_MSG);
381     STPROC.BIND_O(ERROR_CODE);
382     STPROC.EXECUTE;
383     STPROC.RETRIEVE(7
384                    ,ERR_MSG);
385     STPROC.RETRIEVE(8
386                    ,ERROR_CODE);*/
387     BOMPIMPL.ML_IMPLODER(SEQUENCE_ID, ENG_MFG_FLAG, ORG_ID, IMPL_FLAG, A_LEVELS_TO_IMPLODE, IMPL_DATE, ERR_MSG, ERROR_CODE);
388   END ML_IMPLODER;
389 
390 function get_precision(qty_precision in number) return VARCHAR2 is
391 begin
392 
393 if qty_precision = 0 then return('999G999G999G990');
394 
395 elsif qty_precision = 1 then return('999G999G999G990D0');
396 
397 elsif qty_precision = 3 then return('999G999G999G990D000');
398 
399 elsif qty_precision = 4 then return('999G999G999G990D0000');
400 
401 elsif qty_precision = 5 then return('999G999G999G990D00000');
402 
403 elsif qty_precision = 6 then  return('999G999G999G990D000000');
404 
405 else return('999G999G999G990D00');
406 
407 end if;
408 
409 end;
410 
411 END BOM_BOMRWUIT_XMLP_PKG;
412