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.
PACKAGE BODY: APPS.GMD_QA_RCV_PUB

Source


1 PACKAGE BODY GMD_QA_RCV_PUB AS
2 /* $Header: GMDPRECB.pls 120.5.12020000.3 2012/11/13 21:44:39 plowe ship $ */
3 
4 FUNCTION get_disposition
5 ( p_po_num IN VARCHAR2,
6   p_po_line_num IN VARCHAR2,
7   p_shipment_num IN VARCHAR2,
8   p_receipt_num IN VARCHAR2,
9   p_shipment_line_id IN NUMBER default null -- 7447810
10 ) RETURN VARCHAR2 IS
11 
12 
13 -- Bug 13980338 - ENHANCED QUALITY RECEIVING GIVES INCORRECT RESULTS. comment out old cursor aand see new cursor
14 /*CURSOR cur_get_disposition IS -- 7447810 optimized query for shipment_line_id
15 select disposition
16 from
17 --po_headers_all poh,
18 --po_lines_all pol,
19 --po_line_locations_all poll,
20 rcv_transactions rcv ,
21 gmd_sampling_events se,
22 rcv_shipment_lines rsl,
23 rcv_shipment_headers rsh
24 where
25 -- segment1 = p_po_num
26 --and poh.po_header_id = pol.po_header_id
27 --and rsl.line_num = p_po_line_num
28 --and pol.line_num = p_po_line_num
29 --and poh.po_header_id = rsl.po_header_id
30 --and poll.shipment_num = p_shipment_num
31 --and poll.po_line_id = pol.po_line_id
32 --and poll.line_location_id = rcv.po_line_location_id
33 rcv.shipment_header_id = rsh.shipment_header_id
34 and se.receipt_id = rsh.shipment_header_id
35 and se.receipt_line_id = rsl.shipment_line_id
36 and rsl.shipment_header_id = rsh.shipment_header_id
37 and rcv.shipment_line_ID = p_shipment_line_id
38 --and rsh.RECEIPT_NUM = p_receipt_num
39 order by rcv.creation_date desc; */
40 
41 CURSOR cur_get_disposition IS -- 7447810 optimized query for shipment_line_id
42 select disposition
43 from
44 po_headers_all poh,
45 po_lines_all pol,
46 po_line_locations_all poll,
47 rcv_transactions rcv ,
48 gmd_sampling_events se,
49 rcv_shipment_lines rsl,
50 rcv_shipment_headers rsh
51 where
52 segment1 = p_po_num
53 and poh.po_header_id = pol.po_header_id
54 --and rsl.line_num = p_shipment_num --p_po_line_num - CHANGED FOR 14249028  take this out
55 and pol.line_num = p_po_line_num
56 and poh.po_header_id = rsl.po_header_id
57 and poll.shipment_num = p_shipment_num
58 and poll.po_line_id = pol.po_line_id
59 and poll.line_location_id = rcv.po_line_location_id
60 and rcv.shipment_header_id = rsh.shipment_header_id
61 and se.receipt_id = rsh.shipment_header_id
62 and se.receipt_line_id = rsl.shipment_line_id
63 and rsl.shipment_header_id = rsh.shipment_header_id
64 and rcv.shipment_line_ID = rsl.shipment_line_ID-- new line for both bugs 14249028 and 13980338
65 and rcv.shipment_line_ID = p_shipment_line_id
66 --and rsh.RECEIPT_NUM = p_receipt_num
67 order by rcv.creation_date desc;
68 
69 
70 
71 l_disposition VARCHAR2(3);
72 l_progress  	   	VARCHAR2(3);
73 
74   BEGIN
75   	gmd_debug.log_initialize('OPM Receiving Inspection');
76   	gmd_debug.put_line('entering get_disposition ');
77 		gmd_debug.put_line('p_po_num : '|| p_po_num );
78 	  gmd_debug.put_line('p_po_line_num : '|| p_po_line_num );
79 	  gmd_debug.put_line('p_shipment_num : '|| p_shipment_num );
80 		gmd_debug.put_line('p_receipt_num  : '|| p_receipt_num  );
81 		gmd_debug.put_line('p_shipment_line_id : '|| p_shipment_line_id );
82 
83 
84     IF (p_po_num IS NOT NULL) THEN
85       OPEN cur_get_disposition;
86        l_progress := '010';
87       FETCH cur_get_disposition INTO l_disposition;
88        l_progress := '020';
89       IF (cur_get_disposition%FOUND) THEN
90         IF cur_get_disposition%ISOPEN THEN
91          CLOSE cur_get_disposition;
92         END IF;
93          l_progress := '030';
94         gmd_debug.put_line('progress  = ', l_progress);
95         RETURN l_disposition;
96       ELSE
97          l_progress := '040';
98         IF cur_get_disposition%ISOPEN THEN
99          CLOSE cur_get_disposition;
100        	END IF;
101        	 gmd_debug.put_line('progress  = ', l_progress);
102         RETURN NULL;
103       END IF;
104     ELSE
105       l_progress := '050';
106       gmd_debug.put_line('progress  = ', l_progress);
107     	RETURN NULL;
108     END IF;
109 exception
110 	when others then
111 			 IF cur_get_disposition%ISOPEN THEN
112          CLOSE cur_get_disposition;
113        END IF;
114 
115 	     gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_DISPOSITION','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
116 	     return(l_progress);
117 	     --return (sqlerrm);
118 
119 
120   END get_disposition;
121 
122 FUNCTION get_quantity
123 ( p_po_num IN VARCHAR2,
124   p_po_line_num IN VARCHAR2,
125   p_shipment_num IN VARCHAR2,
126   p_receipt_num IN VARCHAR2,
127   p_shipment_line_id IN NUMBER default null -- 7447810
128 ) RETURN NUMBER IS
129 
130 
131 -- Bug 13980338 - ENHANCED QUALITY RECEIVING GIVES INCORRECT RESULTS. comment out old cursor aand see new cursor
132 /*CURSOR cur_get_quantity IS  -- 7447810 optimized query for shipment_line_id
133 select rcv.quantity
134 from
135 --po_headers_all poh,
136 --po_lines_all pol,
137 --po_line_locations_all poll,
138 rcv_transactions rcv ,
139 gmd_sampling_events se,
140 rcv_shipment_lines rsl,
141 rcv_shipment_headers rsh
142 where
143 -- segment1 = p_po_num
144 --and poh.po_header_id = pol.po_header_id
145 --and rsl.line_num = p_po_line_num
146 --and pol.line_num = p_po_line_num
147 --and poh.po_header_id = rsl.po_header_id
148 --and poll.shipment_num = p_shipment_num
149 --and poll.po_line_id = pol.po_line_id
150 --and poll.line_location_id = rcv.po_line_location_id
151 rcv.shipment_header_id = rsh.shipment_header_id
152 and se.receipt_id = rsh.shipment_header_id
153 and se.receipt_line_id = rsl.shipment_line_id
154 and rsl.shipment_header_id = rsh.shipment_header_id
155 and rcv.shipment_line_ID = p_shipment_line_id
156 --and rsh.RECEIPT_NUM = p_receipt_num
157 order by rcv.creation_date desc; */
158 CURSOR cur_get_quantity IS  -- 7447810 optimized query for shipment_line_id
159 select rcv.quantity
160 from
161 po_headers_all poh,
162 po_lines_all pol,
163 po_line_locations_all poll,
164 rcv_transactions rcv ,
165 gmd_sampling_events se,
166 rcv_shipment_lines rsl,
167 rcv_shipment_headers rsh
168 where
169  segment1 = p_po_num
170 and poh.po_header_id = pol.po_header_id
171 --and rsl.line_num = p_shipment_num --p_po_line_num - CHANGED FOR 14249028
172 and pol.line_num = p_po_line_num
173 and poh.po_header_id = rsl.po_header_id
174 and poll.shipment_num = p_shipment_num
175 and poll.po_line_id = pol.po_line_id
176 and poll.line_location_id = rcv.po_line_location_id
177 and rcv.shipment_header_id = rsh.shipment_header_id
178 and se.receipt_id = rsh.shipment_header_id
179 and se.receipt_line_id = rsl.shipment_line_id
180 and rsl.shipment_header_id = rsh.shipment_header_id
181 and rcv.shipment_line_ID = rsl.shipment_line_ID-- new line for both bugs 14249028 and 13980338
182 and rcv.shipment_line_ID = p_shipment_line_id
183 --and rsh.RECEIPT_NUM = p_receipt_num
184 order by rcv.creation_date desc;
185 
186 
187 
188 l_quantity NUMBER;
189 l_progress  	   	VARCHAR2(3);
190 
191   BEGIN
192 
193     gmd_debug.put_line('entering get_quantity');
194     gmd_debug.put_line('p_po_num : '|| p_po_num );
195 	  gmd_debug.put_line('p_po_line_num : '|| p_po_line_num );
196 	  gmd_debug.put_line('p_shipment_num : '|| p_shipment_num );
197 		gmd_debug.put_line('p_receipt_num  : '|| p_receipt_num  );
198 		gmd_debug.put_line('p_shipment_line_id : '|| p_shipment_line_id );
199 
200 
201     IF (p_po_num IS NOT NULL) THEN
202       OPEN cur_get_quantity;
203        l_progress := '010';
204       FETCH cur_get_quantity INTO l_quantity;
205        l_progress := '020';
206       IF (cur_get_quantity%FOUND) THEN
207         IF cur_get_quantity%ISOPEN THEN
208          CLOSE cur_get_quantity;
209         END IF;
210          l_progress := '030';
211         gmd_debug.put_line('progress  = ', l_progress);
212         RETURN l_quantity;
213       ELSE
214         IF cur_get_quantity%ISOPEN THEN
215          CLOSE cur_get_quantity;
216         END IF;
217          l_progress := '040';
218         gmd_debug.put_line('progress  = ', l_progress);
219         RETURN 0;
220       END IF;
221     ELSE
222        l_progress := '050';
223       gmd_debug.put_line('progress  = ', l_progress);
224     	RETURN 0;
225     END IF;
226 exception
227 	when others then
228 			 IF cur_get_quantity%ISOPEN THEN
229          CLOSE cur_get_quantity;
230        END IF;
231 
232 	     gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_QUANTITY','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
233 	     return(l_progress);
234 	     --return (sqlerrm);
235 
236 END get_quantity;
237 
238 FUNCTION get_inspection_result -- 7447810
239 ( p_po_num IN VARCHAR2,
240   p_po_line_num IN VARCHAR2,
241   p_shipment_num IN VARCHAR2,
242   p_receipt_num IN VARCHAR2,
243   p_shipment_line_id IN NUMBER default null -- 7447810
244 ) RETURN VARCHAR2 IS
245 
246 -- Bug 13980338 - ENHANCED QUALITY RECEIVING GIVES INCORRECT RESULTS. comment out old cursor aand see new cursor
247 /*CURSOR cur_get_inspection_result IS  -- 7447810 optimized query for shipment_line_id
248 select disposition
249 from
250 --po_headers_all poh,
251 --po_lines_all pol,
252 --po_line_locations_all poll,
253 rcv_transactions rcv ,
254 gmd_sampling_events se,
255 rcv_shipment_lines rsl,
256 rcv_shipment_headers rsh
257 where
258 -- segment1 = p_po_num
259 --and poh.po_header_id = pol.po_header_id
260 --and rsl.line_num = p_po_line_num
261 --and pol.line_num = p_po_line_num
262 --and poh.po_header_id = rsl.po_header_id
263 --and poll.shipment_num = p_shipment_num
264 --and poll.po_line_id = pol.po_line_id
265 --and poll.line_location_id = rcv.po_line_location_id
266 rcv.shipment_header_id = rsh.shipment_header_id
267 and se.receipt_id = rsh.shipment_header_id
268 and se.receipt_line_id = rsl.shipment_line_id
269 and rsl.shipment_header_id = rsh.shipment_header_id
270 and rcv.shipment_line_ID = p_shipment_line_id
271 --and rsh.RECEIPT_NUM = p_receipt_num
272 order by rcv.creation_date desc; */
273 
274 CURSOR cur_get_inspection_result IS  -- 7447810 optimized query for shipment_line_id
275 select disposition
276 from
277 po_headers_all poh,
278 po_lines_all pol,
279 po_line_locations_all poll,
280 rcv_transactions rcv ,
281 gmd_sampling_events se,
282 rcv_shipment_lines rsl,
283 rcv_shipment_headers rsh
284 where
285  segment1 = p_po_num
286 and poh.po_header_id = pol.po_header_id
287 --and rsl.line_num = p_shipment_num --p_po_line_num - CHANGED FOR 14249028
288 and pol.line_num = p_po_line_num
289 and poh.po_header_id = rsl.po_header_id
290 and poll.shipment_num = p_shipment_num
291 and poll.po_line_id = pol.po_line_id
292 and poll.line_location_id = rcv.po_line_location_id
293 and rcv.shipment_header_id = rsh.shipment_header_id
294 and se.receipt_id = rsh.shipment_header_id
295 and se.receipt_line_id = rsl.shipment_line_id
296 and rsl.shipment_header_id = rsh.shipment_header_id
297 and rcv.shipment_line_ID = rsl.shipment_line_ID-- new line for both bugs 14249028 and 13980338
298 and rcv.shipment_line_ID = p_shipment_line_id
299 --and rsh.RECEIPT_NUM = p_receipt_num
300 order by rcv.creation_date desc;
301 
302 
303 /* need to translate accept or reject into the language that is used
304 by this trick below */
305 
306 l_lkup varchar2(30);
307 
308 CURSOR cur_lkup IS
309 --SELECT displayed_field
310 SELECT substr(rtrim(displayed_field),1,10)
311 FROM po_lookup_codes
312 WHERE lookup_type = 'ERT RESULTS ACTION'
313 and lookup_code = l_lkup;       /* 'ACCEPT' or 'REJECT' */
314 
315 l_inspection_result VARCHAR2(80);
316 l_temp_res VARCHAR2(3);
317 l_progress  	   	VARCHAR2(3);
318 
319   BEGIN
320 
321     gmd_debug.put_line('entering get_inspection_result');
322     gmd_debug.put_line('p_po_num : '|| p_po_num );
323 	  gmd_debug.put_line('p_po_line_num : '|| p_po_line_num );
324 	  gmd_debug.put_line('p_shipment_num : '|| p_shipment_num );
325 		gmd_debug.put_line('p_receipt_num  : '|| p_receipt_num  );
326 		gmd_debug.put_line('p_shipment_line_id : '|| p_shipment_line_id );
327 
328 
329     IF (p_po_num IS NOT NULL) THEN
330 
331       OPEN cur_get_inspection_result;
332       l_progress := '010';
333       FETCH cur_get_inspection_result INTO l_temp_res;
334       l_progress := '020';
335       IF (cur_get_inspection_result%FOUND) THEN
336         IF cur_get_inspection_result%ISOPEN THEN
337          CLOSE cur_get_inspection_result;
338         END IF;
339 
340         IF l_temp_res = '4A'
341         or l_temp_res = '5AV'   -- 5018575
342         then
343            l_lkup := 'ACCEPT';
344         else
345            l_lkup := 'REJECT';
346         end if;
347         l_progress := '030';
348         -- determine accept or reject based on disposition
349         OPEN cur_lkup;
350         l_progress := '040';
351         FETCH cur_lkup INTO l_inspection_result;
352         l_progress := '050';
353         IF (cur_lkup%FOUND) THEN
354           IF cur_lkup%ISOPEN THEN
355          		CLOSE cur_lkup;
356        		END IF;
357 
358           l_progress := '060';
359           gmd_debug.put_line('progress  = ', l_progress);
360           RETURN l_inspection_result;
361         ELSE
362           IF cur_lkup%ISOPEN THEN
363          		CLOSE cur_lkup;
364        		END IF;
365 
366           l_progress := '070';
367           gmd_debug.put_line('progress  = ', l_progress);
368           RETURN null;
369         END IF;
370      ELSE
371         IF cur_get_inspection_result%ISOPEN THEN
372          CLOSE cur_get_inspection_result;
373         END IF;
374         l_progress := '080';
375         gmd_debug.put_line('progress  = ', l_progress);
376     	  RETURN null;
377      END IF; -- IF (cur_get_inspection_result%FOUND) THEN
378    ELSE
379    		l_progress := '090';
380    	  gmd_debug.put_line('progress  = ', l_progress);
381    	  RETURN null;
382    END IF; -- IF (p_po_num IS NOT NULL) THEN
383 exception
384 	when others then
385 			 IF cur_get_inspection_result%ISOPEN THEN
386          CLOSE cur_get_inspection_result;
387        END IF;
388 			 IF cur_lkup%ISOPEN THEN
389          CLOSE cur_lkup;
390        END IF;
391 
392 	     gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_INSPECTION_RESULT','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
393 	     return(l_progress);
394 	     --return (sqlerrm);
395 
396 END get_inspection_result;
397 
398 
399 PROCEDURE store_collection_details(
400 						p_po_num IN VARCHAR2,
401   					p_po_line_num IN VARCHAR2,
402   					p_shipment_num IN VARCHAR2,
403   					p_receipt_num IN VARCHAR2,
404   					p_plan_name IN VARCHAR2, -- actually plan number - need to derive id
405   					p_collection_id IN NUMBER,
406   					p_occurrence IN NUMBER) is
407 
408 PRAGMA AUTONOMOUS_TRANSACTION;
409 
410 
411 
412 
413 cursor cur_get_plan_id is
414 select plan_id from qa_plans where name = p_plan_name;
415 
416 CURSOR cur_get_id IS
417 select sampling_event_id
418 from
419 po_headers_all poh,
420 po_lines_all pol,
421 po_line_locations_all poll,
422 rcv_transactions rcv ,
423 gmd_sampling_events se,
424 rcv_shipment_lines rsl,
425 rcv_shipment_headers rsh
426 where segment1 = p_po_num
427 and   poh.po_header_id = pol.po_header_id
428 --and rsl.line_num = p_shipment_num --p_po_line_num - CHANGED FOR 14249028
429 and   pol.line_num =  p_po_line_num
430 and   poh.po_header_id = rsl.po_header_id
431 and   poll.shipment_num  =  p_shipment_num
432 and   poll.po_line_id =  pol.po_line_id
433 and   poll.line_location_id = rcv.po_line_location_id
434 and   rcv.shipment_header_id = rsh.shipment_header_id
435 and   se.receipt_id  =  rsh.shipment_header_id
436 and   se.receipt_line_id = rsl.shipment_line_id
437 and   rsl.shipment_header_id  = rsh.shipment_header_id
438 and   rcv.shipment_line_ID = rsl.shipment_line_ID-- new line  for both bugs 14249028 and 13980338
439 --and   rsh.RECEIPT_NUM = p_receipt_num
440 order by rcv.creation_date desc;
441 
442 l_progress  	   	VARCHAR2(3);
443 l_id           NUMBER;
444 l_plan_id      NUMBER;
445 
446 
447 BEGIN
448 		gmd_debug.log_initialize('PLOWE');
449 		gmd_debug.put_line('entering store_collection_details ');
450 		--gmd_api_pub.log_message('GMD 1','entering store_collection_details ');
451 		--gmd_api_pub.log_message('GMD 1','PO_NUM = ',p_po_num,'ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
452 
453     IF (p_po_num IS NOT NULL) THEN
454       OPEN cur_get_id;
455       l_progress := '010';
456       FETCH cur_get_id INTO l_id;
457       l_progress := '020';
458       IF (cur_get_id%NOTFOUND) THEN
459         IF cur_get_id%ISOPEN THEN
460         	l_id := 0;
461          	CLOSE cur_get_id;
462         END IF;
463       l_progress := '030';
464       ELSE
465         l_progress := '040';
466         IF cur_get_id%ISOPEN THEN
467         	  CLOSE cur_get_id;
468        	END IF;
469       END IF;   -- IF (cur_get_id%FOUND) THEN
470     ELSE
471       l_progress := '050';
472     	l_id := 0;
473     END IF;  -- IF (p_po_num IS NOT NULL) THEN
474 
475 		IF (p_plan_name IS NOT NULL) THEN
476       OPEN cur_get_plan_id;
477       l_progress := '060';
478       FETCH cur_get_plan_id INTO l_plan_id;
479       l_progress := '070';
480       IF (cur_get_plan_id%NOTFOUND) THEN
481         IF cur_get_plan_id%ISOPEN THEN
482       	  	l_plan_id := 0;
483         	 	CLOSE cur_get_plan_id;
484         END IF;
485         l_progress := '080';
486 
487       ELSE
488         l_progress := '090';
489         IF cur_get_plan_id%ISOPEN THEN
490          	CLOSE cur_get_plan_id;
491        	END IF;
492 
493       END IF;  -- IF (cur_get_plan_id%NOTFOUND) THEN
494     ELSE
495       l_progress := '100';
496     	l_plan_id := 0;
497     END IF; -- IF (p_plan_name IS NOT NULL) THEN
498 
499 		If l_id <> 0 then
500 			update gmd_sampling_events
501             set
502             plan_id = l_plan_id,
503             occurrence = p_occurrence,
504             collection_id = p_collection_id,
505             LAST_UPDATE_DATE = sysdate,
506             LAST_UPDATED_BY = fnd_global.user_id,
507             LAST_UPDATE_LOGIN = fnd_global.login_id
508             where sampling_event_id = l_id;
509       l_progress := '110';
510       COMMIT;
511       l_progress := '120';
512 		end if;
513 		gmd_api_pub.log_message('GMD 11','exiting store_collection_details ');
514 exception
515 	when others then
516 			 IF cur_get_plan_id%ISOPEN THEN
517          CLOSE cur_get_plan_id;
518        END IF;
519        IF cur_get_id%ISOPEN THEN
520          CLOSE cur_get_id;
521        END IF;
522        gmd_debug.put_line('progress  = ', l_progress);
523 	     gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.STORE_COLLECTION_DETAILS','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
524        rollback;
525 
526 END store_collection_details ;
527 
528 
529 
530 END GMD_QA_RCV_PUB;