[Home] [Help]
PACKAGE BODY: APPS.INV_INVISMMX_XMLP_PKG
Source
1 PACKAGE BODY INV_INVISMMX_XMLP_PKG AS
2 /* $Header: INVISMMXB.pls 120.2.12020000.2 2012/07/09 09:07:22 ptkumar ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 LINE_NUM NUMBER := 0;
5 V_DUMMY VARCHAR2(20);
6 L_RETURN_STATUS VARCHAR2(1);
7 L_MSG_COUNT NUMBER;
8 L_MSG_DATA VARCHAR2(2000);
12 LINE_NUM := 10;
9 L_MSG VARCHAR2(2000);
10 L_SUBINV_TBL INV_MMX_WRAPPER_PVT.SUBINVTABLETYPE;
11 BEGIN
13 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
14 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
15 LINE_NUM := 20;
16 DECLARE
17 ERROR_MESSAGE VARCHAR2(80) := NULL;
18 BEGIN
19 LINE_NUM := 30;
20 IF P_SUBINV IS NOT NULL THEN
21 L_SUBINV_TBL(1) := P_SUBINV;
22 END IF;
23 IF P_LEVEL = 2 AND P_SUBINV IS NULL THEN
24 BEGIN
25 SELECT
26 MEANING
27 INTO ERROR_MESSAGE
28 FROM
29 MFG_LOOKUPS
30 WHERE LOOKUP_TYPE = 'INV_MMX_RPT_MSGS'
31 AND LOOKUP_CODE = 3;
32 EXCEPTION
33 WHEN OTHERS THEN
34 NULL;
35 END;
36 /*SRW.MESSAGE(1
37 ,ERROR_MESSAGE)*/NULL;
38 END IF;
39 LINE_NUM := 40;
40 IF P_LEVEL = 2 THEN
41 P_INCLUDE_NONNET := 1;
42 END IF;
43 LINE_NUM := 50;
44 END;
45 LINE_NUM := 60;
46 LINE_NUM := 70;
47 LINE_NUM := 80;
48 SELECT
49 EMPLOYEE_ID
50 INTO P_EMPLOYEE_ID
51 FROM
52 FND_USER
53 WHERE USER_ID = P_USER_ID;
54 LINE_NUM := 90;
55 SELECT
56 sysdate
57 INTO P_SYSDATE
58 FROM
59 SYS.DUAL;
60 LINE_NUM := 110;
61 P_DATE_TIME := TO_CHAR(P_SYSDATE
62 ,'DD-MON-YYYY HH24:MI');
63 P_D_CUTOFF_1 := NVL(P_D_CUTOFF
64 ,P_SYSDATE);
65 P_S_CUTOFF_1 := NVL(P_S_CUTOFF
66 ,P_SYSDATE);
67 IF (P_D_CUTOFF_REL IS NOT NULL) THEN
68 P_D_CUTOFF_REL_1 := NVL(P_D_CUTOFF_REL
69 ,0);
70 P_D_CUTOFF_1 := NVL(P_D_CUTOFF_1
71 ,P_SYSDATE) + P_D_CUTOFF_REL_1;
72 END IF;
73 LINE_NUM := 120;
74 IF (P_S_CUTOFF_REL IS NOT NULL) THEN
75 P_S_CUTOFF_REL_1 := NVL(P_S_CUTOFF_REL
76 ,0);
77 P_S_CUTOFF_1 := NVL(P_S_CUTOFF_1
78 ,P_SYSDATE) + P_S_CUTOFF_REL_1;
79 END IF;
80 LINE_NUM := 130;
81 BEGIN
82 SELECT
83 SUBSTR(NAME
84 ,1
85 ,30)
86 INTO P_ORG_NAME
87 FROM
88 HR_ALL_ORGANIZATION_UNITS_TL
89 WHERE ORGANIZATION_ID = P_ORG_ID
90 AND LANGUAGE = USERENV('LANG');
91 END;
92 LINE_NUM := 140;
93 LINE_NUM := 150;
94 IF P_CAT_SET_ID IS NOT NULL THEN
95 SELECT
96 STRUCTURE_ID
97 INTO P_MCAT_STRUCT
98 FROM
99 MTL_CATEGORY_SETS
100 WHERE CATEGORY_SET_ID = P_CAT_SET_ID;
101 ELSE
102 SELECT
103 CSET.CATEGORY_SET_ID,
104 CSET.STRUCTURE_ID
105 INTO P_CAT_SET_ID,P_MCAT_STRUCT
106 FROM
107 MTL_CATEGORY_SETS CSET,
108 MTL_DEFAULT_CATEGORY_SETS DEF
109 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
110 AND DEF.FUNCTIONAL_AREA_ID = 1;
111 END IF;
112 LINE_NUM := 160;
113 SELECT
114 ID_FLEX_NUM
115 INTO P_MSTK_STRUCT
116 FROM
117 FND_ID_FLEX_STRUCTURES
118 WHERE ID_FLEX_CODE = 'MSTK';
119 SELECT
120 MEANING
121 INTO P_SORTER
122 FROM
123 MFG_LOOKUPS
124 WHERE LOOKUP_TYPE = 'MTL_MINMAX_RPT_SORT_BY'
125 AND LOOKUP_CODE = DECODE(P_SORT
126 ,1
127 ,2
128 ,P_SORT);
129 LINE_NUM := 170;
130 DECLARE
131 V_P_RANGE_SQL VARCHAR2(1000);
132 BEGIN
133 IF P_ITEM_LO IS NOT NULL AND P_ITEM_HI IS NOT NULL THEN
134 P_RANGE_SQL := 'c.concatenated_segments between ' || '''' || P_ITEM_LO || '''' || ' and ' || '''' || P_ITEM_HI || '''';--13881958
135 ELSIF P_ITEM_LO IS NOT NULL THEN
136 P_RANGE_SQL := 'c.concatenated_segments >= ' || '''' || P_ITEM_LO || '''';--13881958
137 ELSIF P_ITEM_HI IS NOT NULL THEN
138 P_RANGE_SQL := 'c.concatenated_segments <= ' || '''' || P_ITEM_HI || '''';--13881958
139 END IF;
140 LINE_NUM := 180;
141 IF P_RANGE_SQL IS NOT NULL THEN
142 V_P_RANGE_SQL := P_RANGE_SQL;
143 P_RANGE_SQL := NULL;
144 END IF;
145 IF P_CATG_LO IS NOT NULL AND P_CATG_HI IS NOT NULL THEN
146 NULL;
147 ELSIF P_CATG_LO IS NOT NULL THEN
148 NULL;
149 ELSIF P_CATG_HI IS NOT NULL THEN
150 NULL;
151 END IF;
152 LINE_NUM := 190;
153 IF P_RANGE_SQL IS NOT NULL THEN
154 V_P_RANGE_SQL := V_P_RANGE_SQL || ' and ' || P_RANGE_SQL;
155 P_RANGE_SQL := NULL;
156 END IF;
157 IF P_PLANNER_LO IS NOT NULL AND P_PLANNER_HI IS NOT NULL THEN
158 P_RANGE_SQL := 'c.planner_code between ' || '''' || P_PLANNER_LO || '''' || ' and ' || '''' || P_PLANNER_HI || '''';
159 ELSIF P_PLANNER_LO IS NOT NULL THEN
160 P_RANGE_SQL := 'c.planner_code >= ' || '''' || P_PLANNER_LO || '''';
161 ELSIF P_PLANNER_HI IS NOT NULL THEN
162 P_RANGE_SQL := 'c.planner_code <= ' || '''' || P_PLANNER_HI || '''';
163 END IF;
164 IF P_RANGE_SQL IS NOT NULL THEN
165 V_P_RANGE_SQL := V_P_RANGE_SQL || ' and ' || P_RANGE_SQL;
166 P_RANGE_SQL := NULL;
167 END IF;
168 LINE_NUM := 200;
172 P_RANGE_SQL := 'c.lot_control_code <> 2';
169 IF P_LOT_CTL = 1 THEN
170 P_RANGE_SQL := 'c.lot_control_code = 2';
171 ELSIF P_LOT_CTL = 2 THEN
173 END IF;
174 IF P_RANGE_SQL IS NOT NULL THEN
175 V_P_RANGE_SQL := V_P_RANGE_SQL || ' and ' || P_RANGE_SQL;
176 P_RANGE_SQL := NULL;
177 END IF;
178 P_RANGE_SQL := V_P_RANGE_SQL;
179 END;
180 LINE_NUM := 215;
181 LINE_NUM := 220;
182 SELECT
183 MEANING
184 INTO P_SB_TEXT
185 FROM
186 MFG_LOOKUPS
187 WHERE LOOKUP_TYPE = 'MTL_MINMAX_RPT_SORT_BY'
188 AND LOOKUP_CODE = P_SORT;
189 SELECT
190 MEANING
191 INTO P_SELECTION_TEXT
192 FROM
193 MFG_LOOKUPS
194 WHERE LOOKUP_TYPE = 'MTL_MINMAX_RPT_SEL'
195 AND LOOKUP_CODE = P_SELECTION;
196 SELECT
197 MEANING
198 INTO P_LVL_TEXT
199 FROM
200 MFG_LOOKUPS
201 WHERE LOOKUP_TYPE = 'MTL_MINMAX_LEVEL'
202 AND LOOKUP_CODE = P_LEVEL;
203 SELECT
204 MEANING
205 INTO P_DISPLAY_MODE_TEXT
206 FROM
207 MFG_LOOKUPS
208 WHERE LOOKUP_TYPE = 'INV_SRS_MMX_REPORT_FORMAT'
209 AND LOOKUP_CODE = P_DISPLAY_MODE;
210 LINE_NUM := 230;
211 IF P_HANDLE_REP_ITEM IS NULL THEN
212 P_HANDLE_REP_ITEM := 3;
213 END IF;
214 SELECT
215 MEANING
216 INTO P_REPITM_TEXT
217 FROM
218 MFG_LOOKUPS
219 WHERE LOOKUP_TYPE = 'MTL_MINMAX_HANDLE_REP_ITEM'
220 AND LOOKUP_CODE = P_HANDLE_REP_ITEM;
221 IF P_DD_LOC_ID IS NOT NULL THEN
222 SELECT
223 LOCATION_CODE
224 INTO P_DDL_TEXT
225 FROM
226 HR_LOCATIONS LOC
227 WHERE LOC.LOCATION_ID = P_DD_LOC_ID;
228 END IF;
229 LINE_NUM := 240;
230 SELECT
231 CATEGORY_SET_NAME
232 INTO P_CSET_TEXT
233 FROM
234 MTL_CATEGORY_SETS
235 WHERE CATEGORY_SET_ID = P_CAT_SET_ID;
236 SELECT
237 MEANING
238 INTO P_YES_TEXT
239 FROM
240 MFG_LOOKUPS
241 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
242 AND LOOKUP_CODE = 1;
243 SELECT
244 MEANING
245 INTO P_NO_TEXT
246 FROM
247 MFG_LOOKUPS
248 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
249 AND LOOKUP_CODE = 2;
250 LINE_NUM := 250;
251 SELECT
252 MEANING
253 INTO P_LOTCTL_TEXT
254 FROM
255 MFG_LOOKUPS
256 WHERE LOOKUP_TYPE = 'MIN_MAX_REPORT_LOT_CONTROL'
257 AND LOOKUP_CODE = NVL(P_LOT_CTL
258 ,3);
259 IF P_INCLUDE_NONNET = 1 THEN
260 P_NONET_TEXT := P_YES_TEXT;
261 ELSIF P_INCLUDE_NONNET = 2 THEN
262 P_NONET_TEXT := P_NO_TEXT;
263 ELSE
264 P_INCLUDE_NONNET := 1;
265 P_NONET_TEXT := P_YES_TEXT;
266 END IF;
267 LINE_NUM := 260;
268 IF P_RESTOCK = 1 THEN
269 P_RSTK_TEXT := P_YES_TEXT;
270 ELSE
271 P_RSTK_TEXT := P_NO_TEXT;
272 END IF;
273 IF P_NET_UNRSV = 1 THEN
274 P_NET_UR_TEXT := P_YES_TEXT;
275 ELSE
276 P_NET_UR_TEXT := P_NO_TEXT;
277 END IF;
278 IF P_NET_RSV = 1 THEN
279 P_NET_R_TEXT := P_YES_TEXT;
280 ELSE
281 P_NET_R_TEXT := P_NO_TEXT;
282 END IF;
283 IF P_NET_WIP = 1 THEN
284 P_NET_W_TEXT := P_YES_TEXT;
285 ELSE
286 P_NET_W_TEXT := P_NO_TEXT;
287 IF P_NET_WIP IS NULL THEN
288 P_NET_WIP := 2;
289 END IF;
290 END IF;
291 LINE_NUM := 270;
292 IF P_INCLUDE_WIP = 1 THEN
293 P_INC_W_TEXT := P_YES_TEXT;
294 ELSE
295 P_INC_W_TEXT := P_NO_TEXT;
296 IF P_INCLUDE_WIP IS NULL THEN
297 P_INCLUDE_WIP := 2;
298 END IF;
299 END IF;
300 IF P_INCLUDE_PO = 1 THEN
301 P_INC_PO_TEXT := P_YES_TEXT;
302 ELSE
303 P_INC_PO_TEXT := P_NO_TEXT;
304 END IF;
305 IF P_INCLUDE_MO = 1 THEN
306 P_INC_MO_TEXT := P_YES_TEXT;
307 ELSE
308 P_INC_MO_TEXT := P_NO_TEXT;
309 END IF;
310 IF P_INCLUDE_IF = 1 THEN
311 P_INC_IF_TEXT := P_YES_TEXT;
312 ELSE
313 P_INC_IF_TEXT := P_NO_TEXT;
314 END IF;
315 IF P_SHOW_DESC = 1 THEN
316 P_SHOW_DESC_TEXT := P_YES_TEXT;
317 ELSE
318 P_SHOW_DESC_TEXT := P_NO_TEXT;
319 END IF;
320 P_MO_LINE_GROUPING := NVL(FND_PROFILE.VALUE('INV_REPL_MO_GROUPING')
321 ,1);
322 LINE_NUM := 280;
323 /*SRW.MESSAGE(69
324 ,'Calling INV_MMX_WRAPPER_PVT.exec_min_max from Before Report Trigger')*/NULL;
325 INV_MMX_WRAPPER_PVT.EXEC_MIN_MAX(X_RETURN_STATUS => L_RETURN_STATUS
326 ,X_MSG_COUNT => L_MSG_COUNT
327 ,X_MSG_DATA => L_MSG_DATA
328 ,P_ITEM_SELECT => P_ITEM_SELECT
329 ,P_HANDLE_REP_ITEM => P_HANDLE_REP_ITEM
330 ,P_PUR_REVISION => P_PUR_REVISION
331 ,P_CAT_SELECT => P_CAT_SELECT
332 ,P_CAT_SET_ID => P_CAT_SET_ID
333 ,P_MCAT_STRUCT => P_MCAT_STRUCT
334 ,P_LEVEL => P_LEVEL
335 ,P_RESTOCK => P_RESTOCK
336 ,P_INCLUDE_NONNET => P_INCLUDE_NONNET
337 ,P_INCLUDE_PO => P_INCLUDE_PO
338 ,P_INCLUDE_MO => P_INCLUDE_MO
339 ,P_INCLUDE_WIP => P_INCLUDE_WIP
340 ,P_INCLUDE_IF => P_INCLUDE_IF
341 ,P_NET_RSV => P_NET_RSV
342 ,P_NET_UNRSV => P_NET_UNRSV
343 ,P_NET_WIP => P_NET_WIP
344 ,P_ORGANIZATION_ID => P_ORG_ID
345 ,P_USER_ID => P_USER_ID
346 ,P_EMPLOYEE_ID => P_EMPLOYEE_ID
347 ,P_SUBINV_TBL => L_SUBINV_TBL
348 ,P_DD_LOC_ID => P_DD_LOC_ID
349 ,P_BUYER_HI => P_BUYER_HI
350 ,P_BUYER_LO => P_BUYER_LO
351 ,P_RANGE_BUYER => P_RANGE_BUYER
352 ,P_RANGE_SQL => P_RANGE_SQL
353 ,P_SORT => P_SORT
354 ,P_SELECTION => P_SELECTION
355 ,P_SYSDATE => P_SYSDATE
356 ,P_S_CUTOFF => P_S_CUTOFF_1
357 ,P_D_CUTOFF => P_D_CUTOFF_1
358 ,P_GEN_REPORT => 'Y'
359 ,P_MO_LINE_GROUPING => P_MO_LINE_GROUPING);
360 IF L_RETURN_STATUS = 'W' THEN
361 P_WARN := 'W';
362 L_RETURN_STATUS := 'S';
363 /*SRW.MESSAGE(100
364 ,'Warning')*/NULL;
365 END IF;
366 IF (L_RETURN_STATUS <> 'S') THEN
367 IF L_MSG_COUNT > 0 THEN
368 FOR i IN 1 .. L_MSG_COUNT LOOP
369 L_MSG := FND_MSG_PUB.GET(I
370 ,'F');
371 /*SRW.MESSAGE(70
372 ,'INV_MMX_WRAPPER_PVT.exec_min_max returned error:' || L_MSG)*/NULL;
373 FND_MSG_PUB.DELETE_MSG(I);
374 END LOOP;
375 ELSE
376 /*SRW.MESSAGE(70
377 ,'INV_MMX_WRAPPER_PVT.exec_min_max returned an error: ' || L_MSG_DATA)*/NULL;
378 END IF;
379 RETURN (FALSE);
380 END IF;
381 RETURN (TRUE);
382 EXCEPTION
383 -- WHEN /*SRW.UNKNOWN_USER_EXIT*/OTHERS THEN
384 -- /*SRW.MESSAGE(80
385 -- ,'Unknown user exit (after line ' || LINE_NUM || ')')*/NULL;
386 -- RETURN (FALSE);
387 -- WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
388 -- /*SRW.MESSAGE(85
389 -- ,'User exit failed (after line ' || LINE_NUM || ')')*/NULL;
390 -- RETURN (FALSE);
391 WHEN OTHERS THEN
392 /*SRW.MESSAGE(90
393 ,'Error - Before Report')*/NULL;
394 /*SRW.MESSAGE(101
395 ,'Error after line ' || LINE_NUM || ':' || SQLCODE || ':' || SQLERRM)*/NULL;
396 RETURN (FALSE);
397 END BEFOREREPORT;
398 FUNCTION AFTERREPORT RETURN BOOLEAN IS
399 BEGIN
400 DECLARE
401 JUNK NUMBER;
402 V_DUMMY VARCHAR2(20);
403 L_RET BOOLEAN;
404 BEGIN
405 DELETE FROM INV_MIN_MAX_TEMP;
406 COMMIT;
407 IF P_WARN = 'W' THEN
408 L_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING'
409 ,'Please see log file for Details');
410 END IF;
411 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
412 END;
413 RETURN (TRUE);
414 END AFTERREPORT;
415 END INV_INVISMMX_XMLP_PKG;
416