1 PACKAGE BODY GMD_QCR05USR_XMLP_PKG AS
2 /* $Header: QCR05USRB.pls 120.0 2007/12/24 13:09:26 krreddy noship $ */
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
97 FUNCTION EXCLUSIVE_TESTSCPFORMULA(EXCLUSIVE_TESTSCF IN VARCHAR2) RETURN VARCHAR2 IS
98 BEGIN
99 /*SRW.REFERENCE(EXCLUSIVE_TESTSCF)*/NULL;
100 RETURN 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 BEGIN
207 IF FROM_LOTSTATUS IS NULL THEN
208 IF TO_LOTSTATUS IS NULL THEN
209 RETURN (' ');
210 END IF;
211 ELSE
212 IF TO_LOTSTATUS IS NULL THEN
213 RETURN (' AND mln.status_id >= ''' || FROM_LOTSTATUS || '''');
214 ELSE
215 RETURN (' AND mln.status_id >=''' || FROM_LOTSTATUS || ''' and mln.status_id <=''' || TO_LOTSTATUS || '''');
216 END IF;
217 END IF;
218 RETURN ' ';
219 END LOT_STATUSCFFORMULA;
220
221 FUNCTION FROM_SUBINVENTORYCFFORMULA RETURN VARCHAR2 IS
222 BEGIN
223 IF FROM_SUBINVENTORY IS NULL THEN
224 FROM_SUBINVENTORYCP := SY_ALL;
225 ELSE
226 FROM_SUBINVENTORYCP := FROM_SUBINVENTORY;
227 END IF;
228 /*SRW.MESSAGE(1000
229 ,':FROM_SUBINVENTORYCP ' || FROM_SUBINVENTORYCP)*/NULL;
230 RETURN (FROM_SUBINVENTORYCP);
231 END FROM_SUBINVENTORYCFFORMULA;
232
233 FUNCTION FROM_LOTSTATUSCFFORMULA RETURN VARCHAR2 IS
234 BEGIN
235 IF FROM_LOTSTATUS IS NULL THEN
236 FROM_LOTSTATUSCP := SY_ALL;
237 ELSE
238 FROM_LOTSTATUSCP := FROM_LOTSTATUS;
239 END IF;
240 RETURN (FROM_LOTSTATUSCP);
241 END FROM_LOTSTATUSCFFORMULA;
242
243 FUNCTION TO_LOTSTATUSCFFORMULA RETURN VARCHAR2 IS
244 BEGIN
245 IF TO_LOTSTATUS IS NULL THEN
246 TO_LOTSTATUSCP := SY_ALL;
247 ELSE
248 TO_LOTSTATUSCP := TO_LOTSTATUS;
249 END IF;
250 RETURN (TO_LOTSTATUSCP);
251 END TO_LOTSTATUSCFFORMULA;
252
253 FUNCTION TO_SUBINVENTORYCFFORMULA RETURN VARCHAR2 IS
254 BEGIN
255 IF TO_SUBINVENTORY IS NULL THEN
256 TO_SUBINVENTORYCP := SY_ALL;
257 ELSE
258 TO_SUBINVENTORYCP := TO_SUBINVENTORY;
259 END IF;
260 /*SRW.MESSAGE(1000
261 ,':TO_SUBINVENTORYCP ' || TO_SUBINVENTORYCP)*/NULL;
262 RETURN (TO_SUBINVENTORYCP);
263 END TO_SUBINVENTORYCFFORMULA;
264
265 PROCEDURE QCRO5USR_HEADER IS
266 BEGIN
267 NULL;
268 END QCRO5USR_HEADER;
269
270 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
271 BEGIN
272 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
273 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
274 RETURN (TRUE);
275 END BEFOREREPORT;
276
277 FUNCTION CF_MINFORMULA(MIN_SPEC IN VARCHAR2
278 ,TEST_TYPE IN VARCHAR2
279 ,PRECISION IN NUMBER) RETURN CHAR IS
280 L_PRECISION VARCHAR2(25);
281 L_NUMBER NUMBER;
282 L_MIN_SPEC VARCHAR2(25);
283 BEGIN
284 IF MIN_SPEC IS NOT NULL THEN
285 IF TEST_TYPE in ('N','L','E') THEN
286 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
287 ,PRECISION) - 1);
288 L_NUMBER := MIN_SPEC;
289 L_MIN_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
290 ,L_PRECISION));
291 RETURN (L_MIN_SPEC);
292 ELSE
293 L_NUMBER := MIN_SPEC;
294 RETURN L_NUMBER;
295 END IF;
296 ELSE
297 RETURN NULL;
298 END IF;
299 END CF_MINFORMULA;
300
301 FUNCTION CF_MAXFORMULA(MAX_SPEC IN VARCHAR2
302 ,TEST_TYPE IN VARCHAR2
303 ,PRECISION IN NUMBER) RETURN CHAR IS
304 L_PRECISION VARCHAR2(25);
305 L_NUMBER NUMBER;
306 L_MAX_SPEC VARCHAR2(25);
307 BEGIN
308 IF MAX_SPEC IS NOT NULL THEN
309 IF TEST_TYPE in ('N','L','E') THEN
310 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
311 ,PRECISION) - 1);
312 L_NUMBER := MAX_SPEC;
313 L_MAX_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
314 ,L_PRECISION));
315 RETURN (L_MAX_SPEC);
316 ELSE
317 L_NUMBER := MAX_SPEC;
318 RETURN L_NUMBER;
319 END IF;
320 ELSE
321 RETURN NULL;
322 END IF;
323 END CF_MAXFORMULA;
324
325 FUNCTION CF_TARGETFORMULA(TARGET_SPEC IN NUMBER
326 ,TEST_TYPE IN VARCHAR2
327 ,PRECISION IN NUMBER) RETURN CHAR IS
328 L_PRECISION VARCHAR2(25);
329 L_NUMBER NUMBER;
330 L_TARGET_SPEC VARCHAR2(25);
331 BEGIN
332 IF TARGET_SPEC IS NOT NULL THEN
333 IF TEST_TYPE in ('N','L','E') THEN
334 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
335 ,PRECISION) - 1);
336 L_NUMBER := TARGET_SPEC;
337 L_TARGET_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
338 ,L_PRECISION));
339 RETURN (L_TARGET_SPEC);
340 ELSE
341 L_NUMBER := TARGET_SPEC;
342 RETURN L_NUMBER;
343 END IF;
344 ELSE
345 RETURN NULL;
346 END IF;
347 END CF_TARGETFORMULA;
348
349 FUNCTION ORGCFFORMULA RETURN CHAR IS
350 ORG_CODE VARCHAR2(3);
351 BEGIN
352 SELECT
353 DISTINCT
354 ORGANIZATION_CODE
355 INTO ORG_CODE
356 FROM
357 MTL_PARAMETERS
358 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
359 ORGCP := ORG_CODE;
360 /*SRW.MESSAGE(1000
361 ,':ORGCP ' || ORGCP)*/NULL;
362 RETURN (ORGCP);
363 END ORGCFFORMULA;
364
365 FUNCTION AFTERREPORT RETURN BOOLEAN IS
366 BEGIN
367 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
368 RETURN (TRUE);
369 END AFTERREPORT;
370
371 FUNCTION EXPIREDCP_P RETURN VARCHAR2 IS
372 BEGIN
373 RETURN EXPIREDCP;
374 END EXPIREDCP_P;
375
376 FUNCTION RETEST_REQCP_P RETURN VARCHAR2 IS
377 BEGIN
378 RETURN RETEST_REQCP;
379 END RETEST_REQCP_P;
380
381 FUNCTION FUTURE_EXPIRYCP_P RETURN VARCHAR2 IS
382 BEGIN
383 RETURN FUTURE_EXPIRYCP;
384 END FUTURE_EXPIRYCP_P;
385
386 FUNCTION FUTURERETESTCP_P RETURN VARCHAR2 IS
387 BEGIN
388 RETURN FUTURERETESTCP;
389 END FUTURERETESTCP_P;
390
391 FUNCTION EXCLUSIVE_TESTSCP_P RETURN VARCHAR2 IS
392 BEGIN
393 RETURN EXCLUSIVE_TESTSCP;
394 END EXCLUSIVE_TESTSCP_P;
395
396 FUNCTION FROM_SUBINVENTORYCP_P RETURN VARCHAR2 IS
397 BEGIN
398 RETURN FROM_SUBINVENTORYCP;
399 END FROM_SUBINVENTORYCP_P;
400
401 FUNCTION FROM_LOTSTATUSCP_P RETURN VARCHAR2 IS
402 BEGIN
403 RETURN FROM_LOTSTATUSCP;
404 END FROM_LOTSTATUSCP_P;
405
406 FUNCTION TO_LOTSTATUSCP_P RETURN VARCHAR2 IS
407 BEGIN
408 RETURN TO_LOTSTATUSCP;
409 END TO_LOTSTATUSCP_P;
410
411 FUNCTION TO_SUBINVENTORYCP_P RETURN VARCHAR2 IS
412 BEGIN
413 RETURN TO_SUBINVENTORYCP;
414 END TO_SUBINVENTORYCP_P;
415
416 FUNCTION ORGCP_P RETURN VARCHAR2 IS
417 BEGIN
418 RETURN ORGCP;
419 END ORGCP_P;
420
421 END GMD_QCR05USR_XMLP_PKG;
422
[Home] [Help]