1 PACKAGE BODY GMD_QA_RCV_PUB AS
2 /* $Header: GMDPRECB.pls 120.1 2006/04/10 09:20:41 plowe noship $ */
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 ) RETURN VARCHAR2 IS
10
11 CURSOR cur_get_disposition IS
12 select disposition
13 from
14 po_headers_all poh,
15 po_lines_all pol,
16 po_line_locations_all poll,
17 rcv_transactions rcv ,
18 gmd_sampling_events se,
19 rcv_shipment_lines rsl,
20 rcv_shipment_headers rsh
21 where segment1 = p_po_num
22 and poh.po_header_id = pol.po_header_id
23 and rsl.line_num = p_po_line_num
24 and pol.line_num = p_po_line_num
25 and poh.po_header_id = rsl.po_header_id
26 and poll.shipment_num = p_shipment_num
27 and poll.po_line_id = pol.po_line_id
28 and poll.line_location_id = rcv.po_line_location_id
29 and rcv.shipment_header_id = rsh.shipment_header_id
30 and se.receipt_id = rsh.shipment_header_id
31 and se.receipt_line_id = rsl.shipment_line_id
32 and rsl.shipment_header_id = rsh.shipment_header_id
33 and rsh.RECEIPT_NUM = p_receipt_num
34 order by rcv.creation_date desc
35 ;
36
37 l_disposition VARCHAR2(3);
38 l_progress VARCHAR2(3);
39
40 BEGIN
41 gmd_debug.log_initialize('PLOWE');
42 gmd_debug.put_line('entering get_disposition ');
43 gmd_api_pub.log_message('GMD 1','entering get_disposition ');
44 IF (p_po_num IS NOT NULL) THEN
45 OPEN cur_get_disposition;
46 l_progress := '010';
47 FETCH cur_get_disposition INTO l_disposition;
48 l_progress := '020';
49 IF (cur_get_disposition%FOUND) THEN
50 IF cur_get_disposition%ISOPEN THEN
51 CLOSE cur_get_disposition;
52 END IF;
53 l_progress := '030';
54 RETURN l_disposition;
55 ELSE
56 l_progress := '040';
57 IF cur_get_disposition%ISOPEN THEN
58 CLOSE cur_get_disposition;
59 END IF;
60 RETURN NULL;
61 END IF;
62 ELSE
63 l_progress := '050';
64 RETURN NULL;
65 END IF;
66 exception
67 when others then
68 IF cur_get_disposition%ISOPEN THEN
69 CLOSE cur_get_disposition;
70 END IF;
71
72 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_DISPOSITION','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
73 return(l_progress);
74 --return (sqlerrm);
75
76
77 END get_disposition;
78
79 FUNCTION get_quantity
80 ( p_po_num IN VARCHAR2,
81 p_po_line_num IN VARCHAR2,
82 p_shipment_num IN VARCHAR2,
83 p_receipt_num IN VARCHAR2
84 ) RETURN NUMBER IS
85
86 CURSOR cur_get_quantity IS
87 select rcv.quantity -- PAL verify
88 from
89 po_headers_all poh,
90 po_lines_all pol,
91 po_line_locations_all poll,
92 rcv_transactions rcv ,
93 gmd_sampling_events se,
94 rcv_shipment_lines rsl,
95 rcv_shipment_headers rsh
96 where segment1 = p_po_num
97 and poh.po_header_id = pol.po_header_id
98 and rsl.line_num = p_po_line_num
99 and pol.line_num = p_po_line_num
100 and poh.po_header_id = rsl.po_header_id
101 and poll.shipment_num = p_shipment_num
102 and poll.po_line_id = pol.po_line_id
103 and poll.line_location_id = rcv.po_line_location_id
104 and rcv.shipment_header_id = rsh.shipment_header_id
105 and se.receipt_id = rsh.shipment_header_id
106 and se.receipt_line_id = rsl.shipment_line_id
107 and rsl.shipment_header_id = rsh.shipment_header_id
108 and rsh.RECEIPT_NUM = p_receipt_num
109 order by rcv.creation_date desc
110 ;
111
112 l_quantity NUMBER;
113 l_progress VARCHAR2(3);
114
115 BEGIN
116 IF (p_po_num IS NOT NULL) THEN
117 OPEN cur_get_quantity;
118 l_progress := '010';
119 FETCH cur_get_quantity INTO l_quantity;
120 l_progress := '020';
121 IF (cur_get_quantity%FOUND) THEN
122 IF cur_get_quantity%ISOPEN THEN
123 CLOSE cur_get_quantity;
124 END IF;
125 l_progress := '030';
126 RETURN l_quantity;
127 ELSE
128 IF cur_get_quantity%ISOPEN THEN
129 CLOSE cur_get_quantity;
130 END IF;
131 l_progress := '040';
132 RETURN 0;
133 END IF;
134 ELSE
135 l_progress := '050';
136 RETURN 0;
137 END IF;
138 exception
139 when others then
140 IF cur_get_quantity%ISOPEN THEN
141 CLOSE cur_get_quantity;
142 END IF;
143
144 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_QUANTITY','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
145 return(l_progress);
146 --return (sqlerrm);
147
148 END get_quantity;
149
150 FUNCTION get_inspection_result
151 ( p_po_num IN VARCHAR2,
152 p_po_line_num IN VARCHAR2,
153 p_shipment_num IN VARCHAR2,
154 p_receipt_num IN VARCHAR2
155 ) RETURN VARCHAR2 IS
156
157 CURSOR cur_get_inspection_result IS
158 select disposition
159 from
160 po_headers_all poh,
161 po_lines_all pol,
162 po_line_locations_all poll,
163 rcv_transactions rcv ,
164 gmd_sampling_events se,
165 rcv_shipment_lines rsl,
166 rcv_shipment_headers rsh
167 where segment1 = p_po_num
168 and poh.po_header_id = pol.po_header_id
169 and rsl.line_num = p_po_line_num
170 and pol.line_num = p_po_line_num
171 and poh.po_header_id = rsl.po_header_id
172 and poll.shipment_num = p_shipment_num
173 and poll.po_line_id = pol.po_line_id
174 and poll.line_location_id = rcv.po_line_location_id
175 and rcv.shipment_header_id = rsh.shipment_header_id
176 and se.receipt_id = rsh.shipment_header_id
177 and se.receipt_line_id = rsl.shipment_line_id
178 and rsl.shipment_header_id = rsh.shipment_header_id
179 and rsh.RECEIPT_NUM = p_receipt_num
180 order by rcv.creation_date desc
181 ;
182
183 /* need to translate accept or reject into the language that is used
184 by this trick below */
185
186 l_lkup varchar2(30);
187
188 CURSOR cur_lkup IS
189 --SELECT displayed_field
190 SELECT substr(rtrim(displayed_field),1,10)
191 FROM po_lookup_codes
192 WHERE lookup_type = 'ERT RESULTS ACTION'
193 and lookup_code = l_lkup; /* 'ACCEPT' or 'REJECT' */
194
195 l_inspection_result VARCHAR2(80);
196 l_temp_res VARCHAR2(3);
197 l_progress VARCHAR2(3);
198
199 BEGIN
200 IF (p_po_num IS NOT NULL) THEN
201
202 OPEN cur_get_inspection_result;
203 l_progress := '010';
204 FETCH cur_get_inspection_result INTO l_temp_res;
205 l_progress := '020';
206 IF (cur_get_inspection_result%FOUND) THEN
207 IF cur_get_inspection_result%ISOPEN THEN
208 CLOSE cur_get_inspection_result;
209 END IF;
210
211 IF l_temp_res = '4A'
212 or l_temp_res = '5AV' -- 5018575
213 then
214 l_lkup := 'ACCEPT';
215 else
216 l_lkup := 'REJECT';
217 end if;
218 l_progress := '030';
219 -- determine accept or reject based on disposition
220 OPEN cur_lkup;
221 l_progress := '040';
222 FETCH cur_lkup INTO l_inspection_result;
223 l_progress := '050';
224 IF (cur_lkup%FOUND) THEN
225 IF cur_lkup%ISOPEN THEN
226 CLOSE cur_lkup;
227 END IF;
228
229 l_progress := '060';
230 RETURN l_inspection_result;
231 ELSE
232 IF cur_lkup%ISOPEN THEN
233 CLOSE cur_lkup;
234 END IF;
235
236 l_progress := '070';
237 RETURN null;
238 END IF;
239 ELSE
240 IF cur_get_inspection_result%ISOPEN THEN
241 CLOSE cur_get_inspection_result;
242 END IF;
243 l_progress := '080';
244 RETURN null;
245 END IF; -- IF (cur_get_inspection_result%FOUND) THEN
246 ELSE
247 l_progress := '090';
248 RETURN null;
249 END IF; -- IF (p_po_num IS NOT NULL) THEN
250 exception
251 when others then
252 IF cur_get_inspection_result%ISOPEN THEN
253 CLOSE cur_get_inspection_result;
254 END IF;
255 IF cur_lkup%ISOPEN THEN
256 CLOSE cur_lkup;
257 END IF;
258
259 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.GET_INSPECTION_RESULT','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
260 return(l_progress);
261 --return (sqlerrm);
262
263 END get_inspection_result;
264
265
266 PROCEDURE store_collection_details(
267 p_po_num IN VARCHAR2,
268 p_po_line_num IN VARCHAR2,
269 p_shipment_num IN VARCHAR2,
270 p_receipt_num IN VARCHAR2,
271 p_plan_name IN VARCHAR2, -- actually plan number - need to derive id
272 p_collection_id IN NUMBER,
273 p_occurrence IN NUMBER) is
274
275 PRAGMA AUTONOMOUS_TRANSACTION;
276
277
278 CURSOR cur_get_id IS
279 select sampling_event_id
280 from
281 po_headers_all poh,
282 po_lines_all pol,
283 po_line_locations_all poll,
284 rcv_transactions rcv ,
285 gmd_sampling_events se,
286 rcv_shipment_lines rsl,
287 rcv_shipment_headers rsh
288 where segment1 = p_po_num
289 and poh.po_header_id = pol.po_header_id
290 and rsl.line_num = p_po_line_num
291 and pol.line_num = p_po_line_num
292 and poh.po_header_id = rsl.po_header_id
293 and poll.shipment_num = p_shipment_num
294 and poll.po_line_id = pol.po_line_id
295 and poll.line_location_id = rcv.po_line_location_id
296 and rcv.shipment_header_id = rsh.shipment_header_id
297 and se.receipt_id = rsh.shipment_header_id
298 and se.receipt_line_id = rsl.shipment_line_id
299 and rsl.shipment_header_id = rsh.shipment_header_id
300 and rsh.RECEIPT_NUM = p_receipt_num
301 order by rcv.creation_date desc;
302
303 cursor cur_get_plan_id is
304 select plan_id from qa_plans where name = p_plan_name;
305
306
307
308 l_progress VARCHAR2(3);
309 l_id NUMBER;
310 l_plan_id NUMBER;
311
312
313 BEGIN
314 gmd_debug.log_initialize('PLOWE');
315 gmd_debug.put_line('entering store_collection_details ');
316 --gmd_api_pub.log_message('GMD 1','entering store_collection_details ');
317 --gmd_api_pub.log_message('GMD 1','PO_NUM = ',p_po_num,'ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
318
319 IF (p_po_num IS NOT NULL) THEN
320 OPEN cur_get_id;
321 l_progress := '010';
322 FETCH cur_get_id INTO l_id;
323 l_progress := '020';
324 IF (cur_get_id%NOTFOUND) THEN
325 IF cur_get_id%ISOPEN THEN
326 l_id := 0;
327 CLOSE cur_get_id;
328 END IF;
329 l_progress := '030';
330 ELSE
331 l_progress := '040';
332 IF cur_get_id%ISOPEN THEN
333 CLOSE cur_get_id;
334 END IF;
335 END IF; -- IF (cur_get_id%FOUND) THEN
336 ELSE
337 l_progress := '050';
338 l_id := 0;
339 END IF; -- IF (p_po_num IS NOT NULL) THEN
340
341 IF (p_plan_name IS NOT NULL) THEN
342 OPEN cur_get_plan_id;
343 l_progress := '060';
344 FETCH cur_get_plan_id INTO l_plan_id;
345 l_progress := '070';
346 IF (cur_get_plan_id%NOTFOUND) THEN
347 IF cur_get_plan_id%ISOPEN THEN
348 l_plan_id := 0;
349 CLOSE cur_get_plan_id;
350 END IF;
351 l_progress := '080';
352
353 ELSE
354 l_progress := '090';
355 IF cur_get_plan_id%ISOPEN THEN
356 CLOSE cur_get_plan_id;
357 END IF;
358
359 END IF; -- IF (cur_get_plan_id%NOTFOUND) THEN
360 ELSE
361 l_progress := '100';
362 l_plan_id := 0;
363 END IF; -- IF (p_plan_name IS NOT NULL) THEN
364
365 If l_id <> 0 then
366 update gmd_sampling_events
367 set
368 plan_id = l_plan_id,
369 occurrence = p_occurrence,
370 collection_id = p_collection_id,
371 LAST_UPDATE_DATE = sysdate,
372 LAST_UPDATED_BY = fnd_global.user_id,
373 LAST_UPDATE_LOGIN = fnd_global.login_id
374 where sampling_event_id = l_id;
375 l_progress := '110';
376 COMMIT;
377 l_progress := '120';
378 end if;
379 gmd_api_pub.log_message('GMD 11','exiting store_collection_details ');
380 exception
381 when others then
382 IF cur_get_plan_id%ISOPEN THEN
383 CLOSE cur_get_plan_id;
384 END IF;
385 IF cur_get_id%ISOPEN THEN
386 CLOSE cur_get_id;
387 END IF;
388
389 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QA_RCV_PUB.STORE_COLLECTION_DETAILS','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
390 rollback;
391
392 END store_collection_details ;
393
394
395
396 END GMD_QA_RCV_PUB;
[Home] [Help]