1 PACKAGE BODY GMD_QCR05USR_XMLP_PKG AS
2 /* $Header: QCR05USRB.pls 120.0.12020000.3 2012/10/29 19:05:45 plowe ship $ */
3 FUNCTION EXPIREDCFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 /*SRW.REFERENCE(EXPIRED_ITEMS)*/NULL;
6 IF EXPIRED_ITEMS = 'Yes' THEN
7 EXPIREDCP := ' and ((mln.EXPIRATION_DATE <= ''' || TO_CHAR(SYSDATE) || ''') ';
8 ELSE
9 EXPIREDCP := ' ';
10 END IF;
11 /*SRW.MESSAGE(1000
12 ,' :ExpiredCP ' || EXPIREDCP)*/NULL;
13 RETURN (EXPIREDCP);
14 END EXPIREDCFFORMULA;
15
16 FUNCTION EXPIREDCPFORMULA(EXPIREDCF IN VARCHAR2) RETURN VARCHAR2 IS
17 BEGIN
18 /*SRW.REFERENCE(EXPIREDCF)*/NULL;
19 RETURN NULL;
20 END EXPIREDCPFORMULA;
21
22 FUNCTION RETEST_REQCFFORMULA RETURN VARCHAR2 IS
23 BEGIN
24 IF (EXPIRED_ITEMS = 'Yes') THEN
25 IF (RETEST_ITEMS = 'Yes') THEN
26 RETEST_REQCP := ' or (mln.RETEST_DATE <= ''' || TO_CHAR(SYSDATE) || ''')) ';
27 ELSE
28 RETEST_REQCP := ')';
29 END IF;
30 ELSE
31 IF (RETEST_ITEMS = 'Yes') THEN
32 RETEST_REQCP := ' and (mln.RETEST_DATE <=''' || TO_CHAR(SYSDATE) || ''') ';
33 ELSE
34 RETEST_REQCP := ' ';
35 END IF;
36 END IF;
37 /*SRW.MESSAGE(1000
38 ,':Retest_ReqCP ' || RETEST_REQCP)*/NULL;
39 RETURN (RETEST_REQCP);
40 END RETEST_REQCFFORMULA;
41
42 FUNCTION FUTURE_EXPIRECFFORMULA RETURN VARCHAR2 IS
43 BEGIN
44 /*SRW.REFERENCE(FUTURE_EXPIRE)*/NULL;
45 /*SRW.REFERENCE(EXPIRY_WITHIN)*/NULL;
46 IF FUTURE_EXPIRE = 'Yes' THEN
47 IF EXPIRY_WITHIN IS NULL THEN
48 FUTURE_EXPIRYCP := 'and ((mln.expiration_date >= ''' || TO_CHAR(SYSDATE) || ''')';
49 ELSE
50 FUTURE_EXPIRYCP := ' and (((mln.expiration_date <= ''' || TO_CHAR(SYSDATE + EXPIRY_WITHIN) || ''')
51 and (mln.expiration_date >= ''' || TO_CHAR(SYSDATE) || '''))';
52 END IF;
53 ELSE
54 FUTURE_EXPIRYCP := ' ';
55 END IF;
56 /*SRW.MESSAGE(1000
57 ,' :Future_ExpiryCP ' || FUTURE_EXPIRYCP)*/NULL;
58 RETURN (FUTURE_EXPIRYCP);
59 END FUTURE_EXPIRECFFORMULA;
60
61 FUNCTION FUTURE_EXPIRYCPFORMULA(FUTURE_EXPIRYCF IN VARCHAR2) RETURN VARCHAR2 IS
62 BEGIN
63 /*SRW.REFERENCE(FUTURE_EXPIRYCF)*/NULL;
64 RETURN NULL;
65 END FUTURE_EXPIRYCPFORMULA;
66
67 FUNCTION FUTURERETESTCPFORMULA(FUTURERETESTCF IN VARCHAR2) RETURN VARCHAR2 IS
68 BEGIN
69 /*SRW.REFERENCE(FUTURERETESTCF)*/NULL;
70 RETURN NULL;
71 END FUTURERETESTCPFORMULA;
72
73 FUNCTION FUTURERETESTCFFORMULA RETURN VARCHAR2 IS
74 BEGIN
75 /*SRW.REFERENCE(FUTURE_EXPIRE)*/NULL;
76 /*SRW.REFERENCE(FUTURE_RETEST)*/NULL;
77 /*SRW.REFERENCE(RETEST_WITHIN)*/NULL;
78 IF (FUTURE_EXPIRE = 'Yes') THEN
79 IF (FUTURE_RETEST = 'Yes') THEN
80 FUTURERETESTCP := ' or (mln.retest_date <=''' || TO_CHAR(SYSDATE + NVL(RETEST_WITHIN
81 ,0)) || ''')) ';
82 ELSE
83 FUTURERETESTCP := ')';
84 END IF;
85 ELSE
86 IF (FUTURE_RETEST = 'Yes') THEN
87 FUTURERETESTCP := ' and (mln.retest_date<=''' || TO_CHAR(SYSDATE) || ''') ';
88 ELSE
89 FUTURERETESTCP := ' ';
90 END IF;
91 END IF;
92 RETURN (FUTURERETESTCP);
93 /*SRW.MESSAGE(1000
94 ,':FutureRetestCP ' || FUTURERETESTCP)*/NULL;
95 END FUTURERETESTCFFORMULA;
96
100 RETURN NULL;
97 FUNCTION EXCLUSIVE_TESTSCPFORMULA(EXCLUSIVE_TESTSCF IN VARCHAR2) RETURN VARCHAR2 IS
98 BEGIN
99 /*SRW.REFERENCE(EXCLUSIVE_TESTSCF)*/NULL;
101 END EXCLUSIVE_TESTSCPFORMULA;
102
103 FUNCTION EXCLUSIVE_TESTSCFFORMULA RETURN VARCHAR2 IS
104 BEGIN
105 /*SRW.REFERENCE(EXCLUSIVE_TEST)*/NULL;
106 IF EXCLUSIVE_TEST = 'Yes' THEN
107 EXCLUSIVE_TESTSCP := ' and t.test_id
108 not in
109 (select test_id
110 from gmd_results r
111 where
112 r.result_date > ''' || TO_CHAR(SYSDATE - NVL(EXCLUSIVE_WITHIN
113 ,0)) || ''')';
114 ELSE
115 EXCLUSIVE_TESTSCP := ' ';
116 END IF;
117 RETURN (EXCLUSIVE_TESTSCP);
118 END EXCLUSIVE_TESTSCFFORMULA;
119
120 FUNCTION EXCLUSIVE_TESTVALIDTRIGGER RETURN BOOLEAN IS
121 BEGIN
122 RETURN (TRUE);
123 END EXCLUSIVE_TESTVALIDTRIGGER;
124
125 FUNCTION EXPIRED_ITEMSVALIDTRIGGER RETURN BOOLEAN IS
126 BEGIN
127 RETURN (TRUE);
128 END EXPIRED_ITEMSVALIDTRIGGER;
129
130 FUNCTION EXCLUSIVE_WITHINVALIDTRIGGER RETURN BOOLEAN IS
131 BEGIN
132 RETURN (TRUE);
133 END EXCLUSIVE_WITHINVALIDTRIGGER;
134
135 FUNCTION EXPIRY_WITHINVALIDTRIGGER RETURN BOOLEAN IS
136 BEGIN
137 RETURN (TRUE);
138 END EXPIRY_WITHINVALIDTRIGGER;
139
140 FUNCTION FUTURE_EXPIREVALIDTRIGGER RETURN BOOLEAN IS
141 BEGIN
142 RETURN (TRUE);
143 END FUTURE_EXPIREVALIDTRIGGER;
144
145 FUNCTION FUTURE_RETESTVALIDTRIGGER RETURN BOOLEAN IS
146 BEGIN
147 RETURN (TRUE);
148 END FUTURE_RETESTVALIDTRIGGER;
149
150 FUNCTION RETEST_ITEMSVALIDTRIGGER RETURN BOOLEAN IS
151 BEGIN
152 RETURN (TRUE);
153 END RETEST_ITEMSVALIDTRIGGER;
154
155 FUNCTION RETEST_WITHINVALIDTRIGGER RETURN BOOLEAN IS
156 BEGIN
157 RETURN (TRUE);
158 END RETEST_WITHINVALIDTRIGGER;
159
160 FUNCTION FROM_WHSEVALIDTRIGGER RETURN BOOLEAN IS
161 BEGIN
162 RETURN (TRUE);
163 END FROM_WHSEVALIDTRIGGER;
164
165 FUNCTION TO_WHSEVALIDTRIGGER RETURN BOOLEAN IS
166 BEGIN
167 RETURN (TRUE);
168 END TO_WHSEVALIDTRIGGER;
169
170 FUNCTION AFTERPFORM RETURN BOOLEAN IS
171 BEGIN
172 IF FROM_WHSE IS NULL THEN
173 IF TO_WHSE IS NOT NULL THEN
174 /*SRW.MESSAGE(100
175 ,GGM_MESSAGE.GET('IC_FRM_REQD_FOR_THRU'))*/NULL;
176 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
177 END IF;
178 END IF;
179 IF FROM_LOTSTATUS IS NULL THEN
180 IF TO_LOTSTATUS IS NOT NULL THEN
181 /*SRW.MESSAGE(100
182 ,GGM_MESSAGE.GET('IC_FRM_REQD_FOR_THRU'))*/NULL;
183 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
184 END IF;
185 END IF;
186 RETURN (TRUE);
187 END AFTERPFORM;
188
189 FUNCTION SUBINVENTORYCFFORMULA RETURN VARCHAR2 IS
190 BEGIN
191 IF FROM_SUBINVENTORY IS NULL THEN
192 IF TO_SUBINVENTORY IS NULL THEN
193 RETURN (' ');
194 END IF;
195 ELSE
196 IF TO_SUBINVENTORY IS NULL THEN
197 RETURN (' and moqd.subinventory_code >= ''' || FROM_SUBINVENTORY || '''');
198 ELSE
199 RETURN (' and moqd.subinventory_code >= ''' || FROM_SUBINVENTORY || ''' and moqd.subinventory_code <= ''' || TO_SUBINVENTORY || '''');
200 END IF;
201 END IF;
202 RETURN ' ';
203 END SUBINVENTORYCFFORMULA;
204
205 FUNCTION LOT_STATUSCFFORMULA RETURN VARCHAR2 IS
206 L_FROM_STATUS_ID number; -- 14779924
207 L_TO_STATUS_ID number; -- 14779924
208 BEGIN
209 IF FROM_LOTSTATUS IS NULL THEN
210 IF TO_LOTSTATUS IS NULL THEN
211 RETURN (' ');
212 END IF;
213 ELSE
214 -- 14779924 get from id from R12 table MTL_MATERIAL_STATUSES
215 SELECT
216 STATUS_ID
217 INTO L_FROM_STATUS_ID
218 FROM
219 MTL_MATERIAL_STATUSES
220 WHERE STATUS_CODE = FROM_LOTSTATUS;
221
222 IF TO_LOTSTATUS IS NULL THEN
223 RETURN (' AND stat.status_code >= ''' || FROM_LOTSTATUS || ''''); -- -- 14779924
224 --RETURN (' AND mln.status_id >= ''' || L_FROM_STATUS_ID || ''''); -- -- 14779924
225 --RETURN (' AND mln.status_id >= ''' || FROM_LOTSTATUS || '''');
226 ELSE
227 -- 14779924 get to id from R12 table MTL_MATERIAL_STATUSES
228 SELECT
229 STATUS_ID
230 INTO L_TO_STATUS_ID
231 FROM
232 MTL_MATERIAL_STATUSES
233 WHERE STATUS_CODE = TO_LOTSTATUS;
234 RETURN (' AND stat.status_code >=''' || FROM_LOTSTATUS || ''' and stat.status_code <=''' || TO_LOTSTATUS || ''''); -- -- 14779924
235 --RETURN (' AND mln.status_id >=''' || L_FROM_STATUS_ID || ''' and mln.status_id <=''' || L_TO_STATUS_ID || ''''); -- -- 14779924
236 --RETURN (' AND mln.status_id >=''' || FROM_LOTSTATUS || ''' and mln.status_id <=''' || TO_LOTSTATUS || '''');
237 END IF;
238 END IF;
239 RETURN ' ';
240 END LOT_STATUSCFFORMULA;
241
242 -- old below
243
244 /* FUNCTION LOT_STATUSCFFORMULA1 RETURN VARCHAR2 IS
245 BEGIN
246 IF FROM_LOTSTATUS IS NULL THEN
247 IF TO_LOTSTATUS IS NULL THEN
248 RETURN (' ');
249 END IF;
250 ELSE
251 IF TO_LOTSTATUS IS NULL THEN
252 RETURN (' AND mln.status_id >= ''' || FROM_LOTSTATUS || '''');
253 ELSE
257 RETURN ' ';
254 RETURN (' AND mln.status_id >=''' || FROM_LOTSTATUS || ''' and mln.status_id <=''' || TO_LOTSTATUS || '''');
255 END IF;
256 END IF;
258 END LOT_STATUSCFFORMUL1; */
259
260 FUNCTION FROM_SUBINVENTORYCFFORMULA RETURN VARCHAR2 IS
261 BEGIN
262 IF FROM_SUBINVENTORY IS NULL THEN
263 FROM_SUBINVENTORYCP := SY_ALL;
264 ELSE
265 FROM_SUBINVENTORYCP := FROM_SUBINVENTORY;
266 END IF;
267 /*SRW.MESSAGE(1000
268 ,':FROM_SUBINVENTORYCP ' || FROM_SUBINVENTORYCP)*/NULL;
269 RETURN (FROM_SUBINVENTORYCP);
270 END FROM_SUBINVENTORYCFFORMULA;
271
272 FUNCTION FROM_LOTSTATUSCFFORMULA RETURN VARCHAR2 IS
273 BEGIN
274 IF FROM_LOTSTATUS IS NULL THEN
275 FROM_LOTSTATUSCP := SY_ALL;
276 ELSE
277 FROM_LOTSTATUSCP := FROM_LOTSTATUS;
278 END IF;
279 RETURN (FROM_LOTSTATUSCP);
280 END FROM_LOTSTATUSCFFORMULA;
281
282 FUNCTION TO_LOTSTATUSCFFORMULA RETURN VARCHAR2 IS
283 BEGIN
284 IF TO_LOTSTATUS IS NULL THEN
285 TO_LOTSTATUSCP := SY_ALL;
286 ELSE
287 TO_LOTSTATUSCP := TO_LOTSTATUS;
288 END IF;
289 RETURN (TO_LOTSTATUSCP);
290 END TO_LOTSTATUSCFFORMULA;
291
292 FUNCTION TO_SUBINVENTORYCFFORMULA RETURN VARCHAR2 IS
293 BEGIN
294 IF TO_SUBINVENTORY IS NULL THEN
295 TO_SUBINVENTORYCP := SY_ALL;
296 ELSE
297 TO_SUBINVENTORYCP := TO_SUBINVENTORY;
298 END IF;
299 /*SRW.MESSAGE(1000
300 ,':TO_SUBINVENTORYCP ' || TO_SUBINVENTORYCP)*/NULL;
301 RETURN (TO_SUBINVENTORYCP);
302 END TO_SUBINVENTORYCFFORMULA;
303
304 PROCEDURE QCRO5USR_HEADER IS
305 BEGIN
306 NULL;
307 END QCRO5USR_HEADER;
308
309 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
310 BEGIN
311 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
312 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
313 RETURN (TRUE);
314 END BEFOREREPORT;
315
316 FUNCTION CF_MINFORMULA(MIN_SPEC IN VARCHAR2
317 ,TEST_TYPE IN VARCHAR2
318 ,PRECISION IN NUMBER) RETURN CHAR IS
319 L_PRECISION VARCHAR2(25);
320 L_NUMBER NUMBER;
321 L_MIN_SPEC VARCHAR2(25);
322 BEGIN
323 IF MIN_SPEC IS NOT NULL THEN
324 IF TEST_TYPE in ('N','L','E') THEN
325 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
326 ,PRECISION) - 1);
327 L_NUMBER := MIN_SPEC;
328 L_MIN_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
329 ,L_PRECISION));
330 RETURN (L_MIN_SPEC);
331 ELSE
332 L_NUMBER := MIN_SPEC;
333 RETURN L_NUMBER;
334 END IF;
335 ELSE
336 RETURN NULL;
337 END IF;
338 END CF_MINFORMULA;
339
340 FUNCTION CF_MAXFORMULA(MAX_SPEC IN VARCHAR2
341 ,TEST_TYPE IN VARCHAR2
342 ,PRECISION IN NUMBER) RETURN CHAR IS
343 L_PRECISION VARCHAR2(25);
344 L_NUMBER NUMBER;
345 L_MAX_SPEC VARCHAR2(25);
346 BEGIN
347 IF MAX_SPEC IS NOT NULL THEN
348 IF TEST_TYPE in ('N','L','E') THEN
349 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
350 ,PRECISION) - 1);
351 L_NUMBER := MAX_SPEC;
352 L_MAX_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
353 ,L_PRECISION));
354 RETURN (L_MAX_SPEC);
355 ELSE
356 L_NUMBER := MAX_SPEC;
357 RETURN L_NUMBER;
358 END IF;
359 ELSE
360 RETURN NULL;
361 END IF;
362 END CF_MAXFORMULA;
363
364 FUNCTION CF_TARGETFORMULA(TARGET_SPEC IN NUMBER
365 ,TEST_TYPE IN VARCHAR2
366 ,PRECISION IN NUMBER) RETURN CHAR IS
367 L_PRECISION VARCHAR2(25);
368 L_NUMBER NUMBER;
369 L_TARGET_SPEC VARCHAR2(25);
370 BEGIN
371 IF TARGET_SPEC IS NOT NULL THEN
372 IF TEST_TYPE in ('N','L','E') THEN
373 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
374 ,PRECISION) - 1);
375 L_NUMBER := TARGET_SPEC;
376 L_TARGET_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
377 ,L_PRECISION));
378 RETURN (L_TARGET_SPEC);
379 ELSE
380 L_NUMBER := TARGET_SPEC;
381 RETURN L_NUMBER;
382 END IF;
383 ELSE
384 RETURN NULL;
385 END IF;
386 END CF_TARGETFORMULA;
387
388 FUNCTION ORGCFFORMULA RETURN CHAR IS
389 ORG_CODE VARCHAR2(3);
390 BEGIN
391 SELECT
392 DISTINCT
393 ORGANIZATION_CODE
394 INTO ORG_CODE
395 FROM
396 MTL_PARAMETERS
397 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
398 ORGCP := ORG_CODE;
399 /*SRW.MESSAGE(1000
400 ,':ORGCP ' || ORGCP)*/NULL;
401 RETURN (ORGCP);
402 END ORGCFFORMULA;
403
404 FUNCTION AFTERREPORT RETURN BOOLEAN IS
405 BEGIN
406 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
407 RETURN (TRUE);
408 END AFTERREPORT;
409
410 FUNCTION EXPIREDCP_P RETURN VARCHAR2 IS
411 BEGIN
412 RETURN EXPIREDCP;
413 END EXPIREDCP_P;
414
415 FUNCTION RETEST_REQCP_P RETURN VARCHAR2 IS
416 BEGIN
417 RETURN RETEST_REQCP;
418 END RETEST_REQCP_P;
419
420 FUNCTION FUTURE_EXPIRYCP_P RETURN VARCHAR2 IS
421 BEGIN
422 RETURN FUTURE_EXPIRYCP;
423 END FUTURE_EXPIRYCP_P;
424
425 FUNCTION FUTURERETESTCP_P RETURN VARCHAR2 IS
426 BEGIN
427 RETURN FUTURERETESTCP;
428 END FUTURERETESTCP_P;
429
430 FUNCTION EXCLUSIVE_TESTSCP_P RETURN VARCHAR2 IS
431 BEGIN
432 RETURN EXCLUSIVE_TESTSCP;
433 END EXCLUSIVE_TESTSCP_P;
434
435 FUNCTION FROM_SUBINVENTORYCP_P RETURN VARCHAR2 IS
436 BEGIN
437 RETURN FROM_SUBINVENTORYCP;
438 END FROM_SUBINVENTORYCP_P;
439
440 FUNCTION FROM_LOTSTATUSCP_P RETURN VARCHAR2 IS
441 BEGIN
442 RETURN FROM_LOTSTATUSCP;
443 END FROM_LOTSTATUSCP_P;
444
445 FUNCTION TO_LOTSTATUSCP_P RETURN VARCHAR2 IS
446 BEGIN
447 RETURN TO_LOTSTATUSCP;
448 END TO_LOTSTATUSCP_P;
449
450 FUNCTION TO_SUBINVENTORYCP_P RETURN VARCHAR2 IS
451 BEGIN
452 RETURN TO_SUBINVENTORYCP;
453 END TO_SUBINVENTORYCP_P;
454
455 FUNCTION ORGCP_P RETURN VARCHAR2 IS
456 BEGIN
457 RETURN ORGCP;
458 END ORGCP_P;
459
460 END GMD_QCR05USR_XMLP_PKG;
461
[Home] [Help]