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.

APPS.GMDQC_RESULTS dependencies on QC_SPEC_MST

Line 4: p_item_id qc_spec_mst.item_id%type,

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,

Line 6: P_orgn_code qc_spec_mst.orgn_code%type,

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,

Line 7: P_CUST_ID qc_spec_mst.CUST_ID%type default NULL,

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,

Line 8: P_VENDOR_ID qc_spec_mst.VENDOR_ID%type default NULL,

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,

Line 9: P_LOT_ID qc_spec_mst.LOT_ID%type default NULL,

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,

Line 10: P_WHSE_CODE qc_spec_mst.WHSE_CODE%type default NULL,

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,

Line 11: P_LOCATION qc_spec_mst.LOCATION%type default NULL,

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,

Line 12: P_BATCH_ID qc_spec_mst.BATCH_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)

Line 13: P_FORMULA_ID qc_spec_mst.FORMULA_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:

Line 14: P_ROUTING_ID qc_spec_mst.ROUTING_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

Line 15: P_OPRN_ID qc_spec_mst.OPRN_ID%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);

Line 16: p_routingstep_id qc_spec_mst.routingstep_id%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;

Line 41: FROM qc_spec_mst

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

Line 50: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id||

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;

Line 59: FROM qc_spec_mst

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

Line 68: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id||

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;

Line 90: FROM qc_spec_mst

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

Line 101: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 123: FROM qc_spec_mst

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

Line 136: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 156: FROM qc_spec_mst

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

Line 166: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 185: FROM qc_spec_mst

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

Line 196: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 213: FROM qc_spec_mst

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

Line 222: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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

Line 238: FROM qc_spec_mst

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

Line 247: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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

Line 266: FROM qc_spec_mst

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

Line 278: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 298: FROM qc_spec_mst

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

Line 310: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||

Line 328: FROM qc_spec_mst

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

Line 337: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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

Line 353: FROM qc_spec_mst

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

Line 363: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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'||
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;

Line 378: FROM qc_spec_mst

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

Line 386: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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;

Line 403: FROM qc_spec_mst

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

Line 417: FROM qc_spec_mst

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

Line 429: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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||

Line 441: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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

Line 457: FROM qc_spec_mst

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

Line 470: FROM qc_spec_mst

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

Line 482: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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||
486: ' AND NVL(routingstep_id,0) = '|| v_routingstep_id||

Line 494: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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

Line 508: FROM qc_spec_mst

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

Line 518: FROM qc_spec_mst

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

Line 529: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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;

Line 540: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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;

Line 551: FROM qc_spec_mst

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

Line 560: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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;

Line 581: FROM qc_spec_mst

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

Line 596: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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)'||

Line 614: FROM qc_spec_mst

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

Line 630: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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)'

Line 646: FROM qc_spec_mst

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

Line 662: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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)'

Line 678: FROM qc_spec_mst

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

Line 693: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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)'

Line 708: FROM qc_spec_mst

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

Line 722: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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;

Line 736: FROM qc_spec_mst

732:
733: if v_lot_id > 0 then
734:
735: SELECT count(*) into v_count
736: FROM qc_spec_mst
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

Line 750: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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;

Line 765: FROM qc_spec_mst

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

Line 780: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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)'

Line 796: FROM qc_spec_mst

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

Line 811: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''||

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;

Line 825: FROM qc_spec_mst

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

Line 838: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||''''

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;

Line 852: FROM qc_spec_mst

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

Line 866: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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;

Line 877: FROM qc_spec_mst

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

Line 890: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND '

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;

Line 899: FROM qc_spec_mst

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

Line 912: select 'SELECT qc_spec_id, assay_code FROM qc_spec_mst WHERE item_id = ' ||p_item_id|| ' AND ' ||

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;