1 PACKAGE BODY gmdqc_results AS
2 /* $Header: GMDCOMB.pls 120.0 2005/05/25 19:54:45 appldev noship $ */
3 function QC_FIND_SPEC (
4 p_item_id qc_spec_mst.item_id%type,
5 p_sample_date qc_smpl_mst.sample_date%type,
6 P_orgn_code qc_spec_mst.orgn_code%type,
7 P_CUST_ID qc_spec_mst.CUST_ID%type default NULL,
8 P_VENDOR_ID qc_spec_mst.VENDOR_ID%type default NULL,
9 P_LOT_ID qc_spec_mst.LOT_ID%type default NULL,
10 P_WHSE_CODE qc_spec_mst.WHSE_CODE%type default NULL,
11 P_LOCATION qc_spec_mst.LOCATION%type default NULL,
12 P_BATCH_ID qc_spec_mst.BATCH_ID%type default NULL,
13 P_FORMULA_ID qc_spec_mst.FORMULA_ID%type default NULL,
14 P_ROUTING_ID qc_spec_mst.ROUTING_ID%type default NULL,
15 P_OPRN_ID qc_spec_mst.OPRN_ID%type default NULL,
16 p_routingstep_id qc_spec_mst.routingstep_id%type default NULL)
17
18 return varchar2 is
19 v_temp_string varchar2(2000);
20 V_count number;
21 V_count1 number;
22 V_cust_id number;
23 V_vendor_id number;
24 V_LOT_ID number;
25 V_BATCH_ID number;
26 V_FORMULA_ID number;
27 V_ROUTING_ID number;
28 V_OPRN_ID number;
29 V_routingstep_id number;
30 /* skarimis 07/18/2000 added V_add_string variable which hold the Null conditions for Item Specifications*/
31 v_add_string varchar2(2000);
32
33 begin
34 if p_cust_id is not NULL OR
35 p_vendor_id is not NULL then
36 /* Customer/Vender Specification */
37 select nvl(p_cust_id,0) into v_cust_id from dual;
38 select nvl(p_vendor_id,0) into v_vendor_id from dual;
39 /* Local Customer/Vender */
40 SELECT count(*) into v_count
41 FROM qc_spec_mst
42 WHERE item_id = p_item_id
43 AND p_sample_date BETWEEN from_date AND to_date
44 AND delete_mark = 0
45 AND orgn_code = p_orgn_code
46 AND NVL(cust_id,0) = v_cust_id
47 AND NVL(vendor_id,0) = v_vendor_id ;
48
49 if v_count > 0 then
50 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id||
51 ' AND ' ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0
52 AND orgn_code ='||''''||p_orgn_code||''''||' AND NVL(cust_id,0) = '||v_cust_id||
53 ' AND NVL(vendor_id,0) = '||v_vendor_id into v_temp_string from dual;
54 return v_temp_string;
55 end if;
56
57 /* Global Customer/Vender */
58 SELECT count(*) into v_count
59 FROM qc_spec_mst
60 WHERE item_id = p_item_id
61 AND p_sample_date BETWEEN from_date AND to_date
62 AND delete_mark = 0
63 AND orgn_code is NULL
64 AND NVL(cust_id,0) = v_cust_id
65 AND NVL(vendor_id,0) = v_vendor_id;
66
67 if v_count > 0 then
68 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id||
69 ' AND ' || ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0
70 AND orgn_code is NULL AND NVL(cust_id,0) = '||v_cust_id||
71 ' AND NVL(vendor_id,0) = '||v_vendor_id into v_temp_string from dual;
72 return v_temp_string;
73 end if;
74 elsif
75 P_BATCH_ID is not NULL OR
76 P_FORMULA_ID is not NULL OR
77 P_ROUTING_ID is not NULL OR
78 P_OPRN_ID is not NULL OR
79 P_routingstep_id is not NULL then
80 /* Production Specification */
81
82 select nvl(p_batch_id,0) into v_batch_id from dual;
83 select nvl(p_formula_id,0) into v_formula_id from dual;
84 select nvl(p_routing_id,0) into v_routing_id from dual;
85 select nvl(p_oprn_id,0) into v_oprn_id from dual;
86 select nvl(p_routingstep_id,0) into v_routingstep_id from dual;
87
88 /* ORG + Batch + Formula + Routing + Operation */
89 SELECT count(*) into v_count
90 FROM qc_spec_mst
91 WHERE item_id = p_item_id
92 AND p_sample_date BETWEEN from_date AND to_date
93 AND delete_mark = 0
94 AND orgn_code = p_orgn_code
95 AND NVL(batch_id,0) = v_batch_id
96 AND NVL(formula_id,0) = v_formula_id
97 AND NVL(routing_id,0) = v_routing_id
98 AND NVL(routingstep_id,0) = v_routingstep_id
99 AND NVL(oprn_id,0) = v_oprn_id;
100 if v_count > 0 then
101 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
102 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND
103 delete_mark = 0 AND orgn_code ='||''''||p_orgn_code||''''||
104 ' AND NVL(batch_id,0) = '||v_batch_id||
105 ' AND NVL(formula_id,0) = '||v_formula_id||
106 ' AND NVL(routing_id,0) = '||v_routing_id||
107 ' AND NVL(routingstep_id,0) = '||v_routingstep_id||
108 ' AND NVL(oprn_id,0) = '||v_oprn_id
109 into v_temp_string from dual;
110 return v_temp_string;
111 end if;
112
113 /* Batch + Formula + Routing + Operation */
114 v_count := 0;
115
116 if v_batch_id > 0 or
117 v_formula_id > 0 or
118 v_routing_id > 0 or
119 v_routingstep_id > 0 or
120 v_oprn_id > 0 then
121
122 SELECT count(*) into v_count
123 FROM qc_spec_mst
124 WHERE item_id = p_item_id
125 AND p_sample_date BETWEEN from_date AND to_date
126 AND delete_mark = 0
127 AND orgn_code is NULL
128 AND NVL(batch_id,0) = v_batch_id
129 AND NVL(formula_id,0) = v_formula_id
130 AND NVL(routing_id,0) = v_routing_id
131 AND NVL(routingstep_id,0) = v_routingstep_id
132 AND NVL(oprn_id,0) = v_oprn_id;
133 end if;
134
135 if v_count > 0 then
136 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
137 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
138 orgn_code is NULL'||
139 ' AND NVL(batch_id,0) = '||v_batch_id||
140 ' AND NVL(formula_id,0) = '||v_formula_id||
141 ' AND NVL(routing_id,0) = '||v_routing_id||
142 ' AND NVL(routingstep_id,0) = '||v_routingstep_id||
143 ' AND NVL(oprn_id,0) = '||v_oprn_id
144 into v_temp_string from dual;
145 return v_temp_string;
146 end if;
147
148 /* Orgn + Batch + Formula + Routing */
149 v_count := 0;
150
151 if v_batch_id > 0 or
152 v_formula_id > 0 or
153 v_routing_id > 0 then
154
155 SELECT count(*) into v_count
156 FROM qc_spec_mst
157 WHERE item_id = p_item_id
158 AND p_sample_date BETWEEN from_date AND to_date
159 AND delete_mark = 0
160 AND orgn_code = p_orgn_code
161 AND NVL(batch_id,0) = v_batch_id
162 AND NVL(formula_id,0) = v_formula_id
163 AND NVL(routing_id,0) = v_routing_id;
164 end if;
165 if v_count > 0 then
166 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
167 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
168 orgn_code ='||''''||p_orgn_code||''''||
169 ' AND NVL(batch_id,0) = '||v_batch_id||
170 ' AND NVL(formula_id,0) = '||v_formula_id||
171 ' AND NVL(routing_id,0) = '||v_routing_id
172 into v_temp_string from dual;
173 return v_temp_string;
174 end if;
175
176
177 /* Batch + Formula + Routing */
178 v_count := 0;
179
180 if v_batch_id > 0 or
181 v_formula_id > 0 or
182 v_routing_id > 0 then
183
184 SELECT count(*) into v_count
185 FROM qc_spec_mst
186 WHERE item_id = p_item_id
187 AND p_sample_date BETWEEN from_date AND to_date
188 AND delete_mark = 0
189 AND orgn_code is NULL
190 AND NVL(batch_id,0) = v_batch_id
191 AND NVL(formula_id,0) = v_formula_id
192 AND NVL(routing_id,0) = v_routing_id;
193 end if;
194
195 if v_count > 0 then
196 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
197 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0
198 AND orgn_code is NULL'||
199 ' AND NVL(batch_id,0) = '||v_batch_id||
200 ' AND NVL(formula_id,0) = '||v_formula_id||
201 ' AND NVL(routing_id,0) = '||v_routing_id
202 into v_temp_string from dual;
203 return v_temp_string;
204 end if;
205
206 /* Orgn + Batch + Formula*/
207 v_count := 0;
208
209 if v_batch_id > 0 or
210 v_formula_id > 0 then
211
212 SELECT count(*) into v_count
213 FROM qc_spec_mst
214 WHERE item_id = p_item_id
215 AND p_sample_date BETWEEN from_date AND to_date
216 AND delete_mark = 0
217 AND orgn_code = p_orgn_code
218 AND NVL(batch_id,0) = v_batch_id
219 AND NVL(formula_id,0) = v_formula_id;
220 end if;
221 if v_count > 0 then
222 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
223 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
224 orgn_code ='||''''||p_orgn_code||''''||
225 ' AND NVL(batch_id,0) = '||v_batch_id||
226 ' AND NVL(formula_id,0) = '||v_formula_id
227 into v_temp_string from dual;
228 return v_temp_string;
229 end if;
230
231 /* Batch + Formula */
232 v_count := 0;
233
234 if v_batch_id > 0 or
235 v_formula_id > 0 then
236
237 SELECT count(*) into v_count
238 FROM qc_spec_mst
239 WHERE item_id = p_item_id
240 AND p_sample_date BETWEEN from_date AND to_date
241 AND delete_mark = 0
242 AND orgn_code is NULL
243 AND NVL(batch_id,0) = v_batch_id
244 AND NVL(formula_id,0) = v_formula_id;
245 end if;
246 if v_count > 0 then
247 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
248 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
249 orgn_code is NULL'||
250 ' AND NVL(batch_id,0) = '||v_batch_id||
251 ' AND NVL(formula_id,0) = '||v_formula_id
252 into v_temp_string from dual;
253 return v_temp_string;
254 end if;
255
256
257 /* Orgn + Formula + Routing + Operation*/
258 v_count := 0;
259
260 if v_formula_id > 0 or
261 v_routing_id > 0 or
262 v_routingstep_id > 0 or
263 v_oprn_id > 0 then
264
265 SELECT count(*) into v_count
266 FROM qc_spec_mst
267 WHERE item_id = p_item_id
268 AND p_sample_date BETWEEN from_date AND to_date
269 AND delete_mark = 0
270 AND orgn_code = p_orgn_code
271 AND NVL(formula_id,0) = v_formula_id
272 AND NVL(routing_id,0) = v_routing_id
273 AND NVL(routingstep_id,0) = v_routingstep_id
274 AND NVL(oprn_id,0) = v_oprn_id;
275 end if;
276
277 if v_count > 0 then
278 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
279 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
280 ''''||p_orgn_code||''''||
281 ' AND NVL(formula_id,0) = '||v_formula_id||
282 ' AND NVL(routing_id,0) = '||v_routing_id||
283 ' AND NVL(routingstep_id,0) = '||v_routingstep_id||
284 ' AND NVL(oprn_id,0) = '||v_oprn_id
285 into v_temp_string from dual;
286 return v_temp_string;
287 end if;
288
289 /* Formula + Routing + Operation*/
290 v_count := 0;
291
292 if v_formula_id > 0 or
293 v_routing_id > 0 or
294 v_routingstep_id > 0 or
295 v_oprn_id > 0 then
296
297 SELECT count(*) into v_count
298 FROM qc_spec_mst
299 WHERE item_id = p_item_id
300 AND p_sample_date BETWEEN from_date AND to_date
301 AND delete_mark = 0
302 AND orgn_code is NULL
303 AND NVL(formula_id,0) = v_formula_id
304 AND NVL(routing_id,0) = v_routing_id
305 AND NVL(routingstep_id,0) = v_routingstep_id
306 AND NVL(oprn_id,0) = v_oprn_id;
307 end if;
308
309 if v_count > 0 then
310 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
311 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
312 orgn_code is NULL'||
313 ' AND NVL(formula_id,0) = '||v_formula_id||
314 ' AND NVL(routing_id,0) = '||v_routing_id||
315 ' AND NVL(routingstep_id,0) = '||v_routingstep_id||
316 ' AND NVL(oprn_id,0) = '||v_oprn_id
317 into v_temp_string from dual;
318 return v_temp_string;
319 end if;
320
321 /* Orgn + Formula + Routing */
322 v_count := 0;
323
324 if v_formula_id > 0 or
325 v_routing_id > 0 then
326
327 SELECT count(*) into v_count
328 FROM qc_spec_mst
329 WHERE item_id = p_item_id
330 AND p_sample_date BETWEEN from_date AND to_date
331 AND delete_mark = 0
332 AND orgn_code = p_orgn_code
333 AND NVL(formula_id,0) = v_formula_id
334 AND NVL(routing_id,0) = v_routing_id;
335 end if;
336 if v_count > 0 then
337 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
338 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
339 ''''||p_orgn_code||''''||
340 ' AND NVL(formula_id,0) = '||v_formula_id||
341 ' AND NVL(routing_id,0) = '||v_routing_id
342 into v_temp_string from dual;
343 return v_temp_string;
344 end if;
345
346 /* Formula + Routing */
347 v_count := 0;
348
349 if v_formula_id > 0 or
350 v_routing_id > 0 then
351
352 SELECT count(*) into v_count
353 FROM qc_spec_mst
354 WHERE item_id = p_item_id
355 AND p_sample_date BETWEEN from_date AND to_date
356 AND delete_mark = 0
357 AND orgn_code is NULL
358 AND NVL(formula_id,0) = v_formula_id
359 AND NVL(routing_id,0) = v_routing_id;
360 end if;
361
362 if v_count > 0 then
363 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
364 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code is NULL'||
368 return v_temp_string;
365 ' AND NVL(formula_id,0) = '||v_formula_id||
366 ' AND NVL(routing_id,0) = '||v_routing_id
367 into v_temp_string from dual;
369 end if;
370
371 /* SKARIMIS Added this condition due to bug raised by Joan on vis database bug no 1347027 date 07/18/2000 */
372 /* Orgn + Formula */
373 v_count := 0;
374
375 if p_orgn_code is NOT NULL AND v_formula_id > 0 then
376
377 SELECT count(*) into v_count
378 FROM qc_spec_mst
379 WHERE item_id = p_item_id
380 AND p_sample_date BETWEEN from_date AND to_date
381 AND delete_mark = 0
382 AND orgn_code = p_orgn_code
383 AND NVL(formula_id,0) = v_formula_id;
384 end if;
385 if v_count > 0 then
386 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
387 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
388 ''''||p_orgn_code||''''||
389 ' AND NVL(formula_id,0) = '||v_formula_id
390 into v_temp_string from dual;
391 return v_temp_string;
392 end if;
393 /* End of New Code addition */
394
395 /* Orgn + Formula + Operation OR Orgn + Routing + Routing Step */
396 v_count := 0;
397 v_count1 := 0;
398
399 if v_formula_id > 0 or
400 v_oprn_id > 0 then
401
402 SELECT min(preference) into v_count
403 FROM qc_spec_mst
404 WHERE item_id = p_item_id
405 AND p_sample_date BETWEEN from_date AND to_date
406 AND delete_mark = 0
407 AND orgn_code = p_orgn_code
408 AND NVL(formula_id,0) = v_formula_id
409 AND NVL(oprn_id,0) = v_oprn_id;
410 end if;
411
412 if v_routing_id > 0 or
413 v_routingstep_id > 0 or
414 v_oprn_id > 0 then
415
416 SELECT min(preference) into v_count1
417 FROM qc_spec_mst
418 WHERE item_id = p_item_id
419 AND p_sample_date BETWEEN from_date AND to_date
420 AND delete_mark = 0
421 AND orgn_code = p_orgn_code
422 AND NVL(routing_id,0) = v_routing_id
423 AND NVL(routingstep_id,0) = v_routingstep_id
424 AND NVL(oprn_id,0) = v_oprn_id;
425 end if;
426
427 if v_count1 > 0 and v_count1 < v_count then
428
429 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
430 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
431 ''''||p_orgn_code||''''||
432 ' AND NVL(routing_id,0) = '|| v_routing_id||
433 ' AND NVL(routingstep_id,0) = '|| v_routingstep_id||
434 ' AND NVL(oprn_id,0) = '|| v_oprn_id
435 into v_temp_string from dual;
436 return v_temp_string;
437 end if;
438
439 if v_count > 0 and v_count < v_count1 then
440
441 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
442 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
443 ''''||p_orgn_code||''''||
444 ' AND NVL(formula_id,0) = '||v_formula_id||
445 ' AND NVL(oprn_id,0) = '||v_oprn_id
446 into v_temp_string from dual;
447 return v_temp_string;
448 end if;
449
450 /* Formula + Operation OR Routing + Routing step */
451 v_count := 0;
452 v_count1 := 0;
453 if v_formula_id > 0 or
454 v_oprn_id > 0 then
455
456 SELECT min(preference) into v_count
457 FROM qc_spec_mst
458 WHERE item_id = p_item_id
459 AND p_sample_date BETWEEN from_date AND to_date
460 AND delete_mark = 0
461 AND orgn_code is NULL
462 AND NVL(formula_id,0) = v_formula_id
463 AND NVL(oprn_id,0) = v_oprn_id;
464 end if;
465
466 if v_routing_id > 0 or
467 v_routingstep_id > 0 or
468 v_oprn_id > 0 then
469 SELECT min(preference) into v_count1
470 FROM qc_spec_mst
471 WHERE item_id = p_item_id
472 AND p_sample_date BETWEEN from_date AND to_date
473 AND delete_mark = 0
474 AND orgn_code is NULL
475 AND NVL(routing_id,0) = v_routing_id
476 AND NVL(routingstep_id,0) = v_routingstep_id
477 AND NVL(oprn_id,0) = v_oprn_id;
478 end if;
479
480 if v_count1 > 0 and v_count1 < v_count then
481
482 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
483 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
484 orgn_code is NULL'||
485 ' AND NVL(routing_id,0) = '|| v_routing_id||
489 return v_temp_string;
486 ' AND NVL(routingstep_id,0) = '|| v_routingstep_id||
487 ' AND NVL(oprn_id,0) = '|| v_oprn_id
488 into v_temp_string from dual;
490 end if;
491
492 if v_count > 0 and v_count < v_count1 then
493
494 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
495 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
496 orgn_code is NULL '||
497 ' AND NVL(formula_id,0) = '||v_formula_id||
498 ' AND NVL(oprn_id,0) = '||v_oprn_id
499 into v_temp_string from dual;
500 return v_temp_string;
501 end if;
502
503 /* Formula OR Routing */
504 v_count := 0;
505 v_count1 := 0;
506 if v_formula_id > 0 then
507 SELECT min(preference) into v_count
508 FROM qc_spec_mst
509 WHERE item_id = p_item_id
510 AND p_sample_date BETWEEN from_date AND to_date
511 AND delete_mark = 0
512 AND orgn_code is NULL
513 AND formula_id = v_formula_id;
514 end if;
515
516 if v_routing_id > 0 then
517 SELECT min(preference) into v_count1
518 FROM qc_spec_mst
519 WHERE item_id = p_item_id
520 AND p_sample_date BETWEEN from_date AND to_date
521 AND delete_mark = 0
522 AND orgn_code is NULL
523 AND routing_id = v_routing_id;
524 end if;
525
526 if (v_count > 0 and v_count1 > 0 and v_count < v_count1) or
527 (v_count > 0 and v_count1 = 0 ) then
528
529 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
530 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
531 orgn_code is NULL'||
532 ' AND formula_id = '||v_formula_id
533 into v_temp_string from dual;
534 return v_temp_string;
535 end if;
536
537 if (v_count > 0 and v_count1 > 0 and v_count1 < v_count) or
538 (v_count1 > 0 and v_count = 0 ) then
539
540 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
541 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code is NULL'||
542 ' AND routing_id = '||v_routing_id
543 into v_temp_string from dual;
544 return v_temp_string;
545 end if;
546
547 /* Operation */
548 V_count := 0;
549 if v_oprn_id > 0 then
550 SELECT count(*) into v_count
551 FROM qc_spec_mst
552 WHERE item_id = p_item_id
553 AND p_sample_date BETWEEN from_date AND to_date
554 AND delete_mark = 0
555 AND orgn_code is NULL
556 AND oprn_id = v_oprn_id;
557 end if;
558
559 if v_count > 0 then
560 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
561 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code is NULL'||
562 ' AND oprn_id = '||v_oprn_id
563 into v_temp_string from dual;
564 return v_temp_string;
565 end if;
566 else
567 /* Item Specification */
568
569 v_add_string:=' AND (FORMULA_ID IS NULL AND ROUTING_ID IS NULL AND OPRN_ID IS NULL AND CUST_ID IS NULL AND VENDOR_ID IS NULL)';
570
571 select nvl(p_lot_id,0) into v_lot_id from dual;
572
573 /* Orgn + Lot/Sublot + Warehouse + Location */
574 v_count := 0;
575
576 if v_lot_id > 0 or
577 p_whse_code is NOT NULL or
578 p_location is NOT NULL then
579
580 SELECT count(*) into v_count
581 FROM qc_spec_mst
582 WHERE item_id = p_item_id
583 AND p_sample_date BETWEEN from_date AND to_date
584 AND delete_mark = 0
585 AND orgn_code = p_orgn_code
586 AND NVL(lot_id,0) = v_lot_id
587 AND NVL(whse_code,0) = NVL(p_whse_code,0)
588 AND NVL(location,0) = NVL(p_location,0)
589 AND FORMULA_ID IS NULL
590 AND ROUTING_ID IS NULL
591 AND OPRN_ID IS NULL
592 AND CUST_ID IS NULL
593 AND VENDOR_ID IS NULL;
594 end if;
595 if v_count > 0 then
596 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
597 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||''''||
598 p_orgn_code||''''||
599 ' AND NVL(lot_id,0) = '||v_lot_id||
600 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'||
601 ' AND NVL(location,0) = NVL('||''''||p_location||''''||',0)'
602 into v_temp_string from dual;
603 return v_temp_string||v_add_string;
604 end if;
605
609 if v_lot_id > 0 or
606 /* Lot/Sublot + Warehouse + Location */
607 v_count := 0;
608
610 p_whse_code is NOT NULL or
611 p_location is NOT NULL then
612
613 SELECT count(*) into v_count
614 FROM qc_spec_mst
615 WHERE item_id = p_item_id
616 AND p_sample_date BETWEEN from_date AND to_date
617 AND delete_mark = 0
618 AND orgn_code is NULL
619 AND NVL(lot_id,0) = v_lot_id
620 AND NVL(whse_code,0) = NVL(p_whse_code,0)
621 AND NVL(location,0) = NVL(location,0)
622 AND FORMULA_ID IS NULL
623 AND ROUTING_ID IS NULL
624 AND OPRN_ID IS NULL
625 AND CUST_ID IS NULL
626 AND VENDOR_ID IS NULL;
627
628 end if;
629 if v_count > 0 then
630 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
631 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code is NULL'||
632 ' AND NVL(lot_id,0) = '||v_lot_id||
633 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'||
634 ' AND NVL(location,0) = NVL('||''''||p_location||''''||',0)'
635 into v_temp_string from dual;
636 return v_temp_string||v_add_string;
637 end if;
638
639 /* Orgn + Lot/Sublot + Warehouse */
640 v_count := 0;
641
642 if v_lot_id > 0 or
643 p_whse_code is NOT NULL then
644
645 SELECT count(*) into v_count
646 FROM qc_spec_mst
647 WHERE item_id = p_item_id
648 AND p_sample_date BETWEEN from_date AND to_date
649 AND delete_mark = 0
650 AND orgn_code = p_orgn_code
651 AND NVL(lot_id,0) = v_lot_id
652 AND NVL(whse_code,0) = NVL(p_whse_code,0)
653 AND FORMULA_ID IS NULL
654 AND ROUTING_ID IS NULL
655 AND OPRN_ID IS NULL
656 AND CUST_ID IS NULL
657 AND VENDOR_ID IS NULL;
658
659 end if;
660
661 if v_count > 0 then
662 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
663 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
664 orgn_code ='||''''||p_orgn_code||''''||
665 ' AND NVL(lot_id,0) = '||v_lot_id||
666 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'
667 into v_temp_string from dual;
668 return v_temp_string||v_add_string;
669 end if;
670
671 /* Lot/Sublot + Warehouse */
672 v_count := 0;
673
674 if v_lot_id > 0 or
675 p_whse_code is NOT NULL then
676
677 SELECT count(*) into v_count
678 FROM qc_spec_mst
679 WHERE item_id = p_item_id
680 AND p_sample_date BETWEEN from_date AND to_date
681 AND delete_mark = 0
682 AND orgn_code is NULL
683 AND NVL(lot_id,0) = v_lot_id
684 AND NVL(whse_code,0) = NVL(p_whse_code,0)
685 AND FORMULA_ID IS NULL
686 AND ROUTING_ID IS NULL
687 AND OPRN_ID IS NULL
688 AND CUST_ID IS NULL
689 AND VENDOR_ID IS NULL;
690
691 end if;
692 if v_count > 0 then
693 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
694 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
695 orgn_code is NULL'||
696 ' AND NVL(lot_id,0) = '||v_lot_id||
697 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'
698 into v_temp_string from dual;
699 return v_temp_string||v_add_string;
700 end if;
701
702 /* Orgn + Lot/Sublot */
703 v_count := 0;
704
705 if v_lot_id > 0 then
706
707 SELECT count(*) into v_count
708 FROM qc_spec_mst
709 WHERE item_id = p_item_id
710 AND p_sample_date BETWEEN from_date AND to_date
711 AND delete_mark = 0
712 AND orgn_code = p_orgn_code
713 AND lot_id = v_lot_id
714 AND FORMULA_ID IS NULL
715 AND ROUTING_ID IS NULL
716 AND OPRN_ID IS NULL
717 AND CUST_ID IS NULL
718 AND VENDOR_ID IS NULL;
719
720 end if;
721 if v_count > 0 then
722 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
723 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
724 orgn_code ='||''''||p_orgn_code||''''||
725 ' AND lot_id = '||v_lot_id
726 into v_temp_string from dual;
727 return v_temp_string||v_add_string;
728 end if;
729
730 /* Lot/Sublot */
731 v_count := 0;
732
736 FROM qc_spec_mst
733 if v_lot_id > 0 then
734
735 SELECT count(*) into v_count
737 WHERE item_id = p_item_id
738 AND p_sample_date BETWEEN from_date AND to_date
739 AND delete_mark = 0
740 AND orgn_code is NULL
741 AND lot_id = v_lot_id
742 AND FORMULA_ID IS NULL
743 AND ROUTING_ID IS NULL
744 AND OPRN_ID IS NULL
745 AND CUST_ID IS NULL
746 AND VENDOR_ID IS NULL;
747
748 end if;
749 if v_count > 0 then
750 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
751 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
752 orgn_code is NULL'||
753 ' AND lot_id = '||v_lot_id
754 into v_temp_string from dual;
755 return v_temp_string||v_add_string;
756 end if;
757
758 /* Orgn Warehouse + Location */
759 v_count := 0;
760
761 if p_whse_code is NOT NULL or
762 p_location is NOT NULL then
763
764 SELECT count(*) into v_count
765 FROM qc_spec_mst
766 WHERE item_id = p_item_id
767 AND p_sample_date BETWEEN from_date AND to_date
768 AND delete_mark = 0
769 AND orgn_code = p_orgn_code
770 AND NVL(whse_code,0) = NVL(p_whse_code,0)
771 AND NVL(location,0) = NVL(location,0)
772 AND FORMULA_ID IS NULL
773 AND ROUTING_ID IS NULL
774 AND OPRN_ID IS NULL
775 AND CUST_ID IS NULL
776 AND VENDOR_ID IS NULL;
777
778 end if;
779 if v_count > 0 then
780 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
781 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
782 orgn_code ='||''''||p_orgn_code||''''||
783 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'||
784 ' AND NVL(location,0) = NVL('||''''||p_location||''''||',0)'
785 into v_temp_string from dual;
786 return v_temp_string||v_add_string;
787 end if;
788
789 /* Warehouse + Location */
790 v_count := 0;
791
792 if p_whse_code is NOT NULL or
793 p_location is NOT NULL then
794
795 SELECT count(*) into v_count
796 FROM qc_spec_mst
797 WHERE item_id = p_item_id
798 AND p_sample_date BETWEEN from_date AND to_date
799 AND delete_mark = 0
800 AND orgn_code is NULL
801 AND NVL(whse_code,0) = NVL(p_whse_code,0)
802 AND NVL(location,0) = NVL(location,0)
803 AND FORMULA_ID IS NULL
804 AND ROUTING_ID IS NULL
805 AND OPRN_ID IS NULL
806 AND CUST_ID IS NULL
807 AND VENDOR_ID IS NULL;
808
809 end if;
810 if v_count > 0 then
811 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||
812 p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code is NULL'||
813 ' AND NVL(whse_code,0) = NVL('||''''||p_whse_code||''''||',0)'||
814 ' AND NVL(location,0) = NVL('||''''||p_location||''''||',0)'
815 into v_temp_string from dual;
816 return v_temp_string||v_add_string;
817 end if;
818
819 /* Orgn Warehouse */
820 v_count := 0;
821
822 if p_whse_code is not NULL then
823
824 SELECT count(*) into v_count
825 FROM qc_spec_mst
826 WHERE item_id = p_item_id
827 AND p_sample_date BETWEEN from_date AND to_date
828 AND delete_mark = 0
829 AND orgn_code = p_orgn_code
830 AND whse_code = p_whse_code
831 AND FORMULA_ID IS NULL
832 AND ROUTING_ID IS NULL
833 AND OPRN_ID IS NULL
834 AND CUST_ID IS NULL
835 AND VENDOR_ID IS NULL;
836 end if;
837 if v_count > 0 then
838 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''
839 ||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND orgn_code ='||
840 ''''||p_orgn_code||''''||
841 ' AND whse_code = '||''''||p_whse_code||''''
842 into v_temp_string from dual;
843 return v_temp_string||v_add_string;
844 end if;
845
846 /* Warehouse */
847 v_count := 0;
848
849 if p_whse_code is not NULL then
850
851 SELECT count(*) into v_count
852 FROM qc_spec_mst
853 WHERE item_id = p_item_id
854 AND p_sample_date BETWEEN from_date AND to_date
855 AND delete_mark = 0
856 AND orgn_code is NULL
857 AND whse_code = p_whse_code
858 AND FORMULA_ID IS NULL
859 AND ROUTING_ID IS NULL
860 AND OPRN_ID IS NULL
861 AND CUST_ID IS NULL
862 AND VENDOR_ID IS NULL;
863
864 end if;
865 if v_count > 0 then
866 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
867 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
868 orgn_code is NULL'||
869 ' AND whse_code = '||''''||p_whse_code||''''
870 into v_temp_string from dual;
871 return v_temp_string||v_add_string;
872 end if;
873 end if;
874 /* ORGN + ITEM */
875 v_count := 0;
876 SELECT count(*) into v_count
877 FROM qc_spec_mst
878 WHERE item_id = p_item_id
879 AND p_sample_date BETWEEN from_date AND to_date
880 AND delete_mark = 0
881 AND orgn_code = p_orgn_code
882 AND FORMULA_ID IS NULL
883 AND ROUTING_ID IS NULL
884 AND OPRN_ID IS NULL
885 AND CUST_ID IS NULL
886 AND VENDOR_ID IS NULL;
887
888
889 if v_count > 0 then
890 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '
891 ||''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
892 orgn_code ='||''''||p_orgn_code||''''
893 into v_temp_string from dual;
894 return v_temp_string||v_add_string;
895 end if;
896 /* ITEM */
897 v_count := 0;
898 SELECT count(*) into v_count
899 FROM qc_spec_mst
900 WHERE item_id = p_item_id
901 AND p_sample_date BETWEEN from_date AND to_date
902 AND delete_mark = 0
903 AND orgn_code is NULL
904 AND FORMULA_ID IS NULL
905 AND ROUTING_ID IS NULL
906 AND OPRN_ID IS NULL
907 AND CUST_ID IS NULL
908 AND VENDOR_ID IS NULL;
909
910
911 if v_count > 0 then
912 select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||
913 ''''||p_sample_date||''''|| ' BETWEEN from_date AND to_date AND delete_mark = 0 AND
914 orgn_code is NULL'
915 into v_temp_string from dual;
916 return v_temp_string||v_add_string;
917 end if;
918 return '';
919 end qc_find_spec;
920
921 end gmdqc_results;
[Home] [Help]