1 PACKAGE BODY PO_QUOTES_SV as
2 /* $Header: POXSOQUB.pls 115.1 2002/11/26 19:50:15 sbull ship $ */
3 /*============================ PO_QUOTES_SV ==============================*/
4
5
6 /*===========================================================================
7
8 PROCEDURE NAME: test_val_header_delete()
9
10 ===========================================================================*/
11
12 PROCEDURE test_val_header_delete(X_po_header_id IN NUMBER) IS
13
14 X_allow_delete BOOLEAN;
15
16 BEGIN
17
18 dbms_output.put_line('Before_call');
19
20 po_quotes_sv.val_header_delete(X_po_header_id, X_allow_delete);
21
22 dbms_output.put_line('After call');
23
24 if (X_allow_delete) then
25 dbms_output.put_line('Allow Delete = TRUE');
26 else
27 dbms_output.put_line('Allow Delete = FALSE');
28 end if;
29
30 END test_val_header_delete;
31
32 /*===========================================================================
33
34 PROCEDURE NAME: val_header_delete()
35
36 ===========================================================================*/
37
38 PROCEDURE val_header_delete(X_po_header_id IN NUMBER,
39 X_allow_delete IN OUT NOCOPY BOOLEAN) IS
40
41 x_progress VARCHAR2(3) := '';
42 x_delete_test VARCHAR2(1) := 'Y';
43
44 BEGIN
45
46
47 x_progress := '010';
48
49 x_progress := '020';
50
51 /*
52 ** Verifies if this Quotation is referenced on a PO.
53 ** If it is NOT, verify the document is not used in autosource rules.
54 ** If it is, display message and prevent delete.
55 */
56 SELECT MAX('N')
57 INTO X_delete_test
58 FROM po_lines pol
59 WHERE pol.from_header_id = X_po_header_id;
60
61 x_progress := '020';
62
63 IF (nvl(X_delete_test,'Y') = 'Y') THEN
64 /*
65 ** Verify the Quotation is not used in autosource rules.
66 ** If it is NOT, verify it is not on a req line.
67 ** If it is, display message and prevent delete.
68 **
69 ** CMOK: If ASL installed, use po_asl_documents table.
70 */
71
72
73 SELECT MAX('N')
74 INTO X_delete_test
75 FROM po_asl_documents pad
76 WHERE pad.document_header_id = X_po_header_id;
77
78 IF (nvl(X_delete_test,'Y') = 'Y') THEN
79 /*
80 ** Verify the Quotation is not referenced on a Requisition line.
81 ** If it is NOT, allow the delete.
82 ** If it is, display message and prevent delete.
83 */
84 SELECT MAX('N')
85 INTO X_delete_test
86 FROM po_requisition_lines prl
87 WHERE prl.blanket_po_header_id = X_po_header_id;
88
89 IF (nvl(X_delete_test,'Y') = 'Y') THEN
90 X_allow_delete := TRUE;
91 dbms_output.put_line('Delete permitted');
92 ELSE
93 X_allow_delete := FALSE;
94 po_message_s.app_error('PO_DELETE_QT_ON_REQ');
95 /* DEBUG: this message needs to be added to the message dictionary */
96 END IF;
97
98 ELSE
99 X_allow_delete := FALSE;
100 po_message_s.app_error('PO_QT_DELETE_SOURCE');
101 END IF;
102
103 ELSE
104 X_allow_delete := FALSE;
105 po_message_s.app_error('PO_DELETE_QT_ON_PO_NA');
106 /* DEBUG: this message needs to be added to the message dictionary */
107 END IF;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 dbms_output.put_line('In VAL exception');
112 po_message_s.sql_error('val_header_delete', x_progress, sqlcode);
113
114 END val_header_delete;
115
116 /*===========================================================================
117
118 PROCEDURE NAME: test_val_line_delete()
119
120 ===========================================================================*/
121
122 PROCEDURE test_val_line_delete(X_po_line_id IN NUMBER,
123 X_po_line_num IN NUMBER,
124 X_po_header_id IN NUMBER) IS
125
126 X_allow_delete VARCHAR2(1) := '';
127
128 BEGIN
129
130 dbms_output.put_line('Before_call');
131
132 po_quotes_sv.val_line_delete(X_po_line_id, X_po_line_num, X_po_header_id,
133 X_allow_delete);
134
135 dbms_output.put_line('After call');
136 dbms_output.put_line('Allow Delete = '||X_allow_delete);
137
138 END test_val_line_delete;
139
140 /*===========================================================================
141
142 PROCEDURE NAME: val_line_delete()
143
144 ===========================================================================*/
145
146 PROCEDURE val_line_delete(X_po_line_id IN NUMBER,
147 X_po_line_num IN NUMBER,
148 X_po_header_id IN NUMBER,
149 X_allow_delete IN OUT NOCOPY VARCHAR2) IS
150
151 x_progress VARCHAR2(3) := '';
152 x_fetched_on_po NUMBER := '';
153 x_fetched_on_req NUMBER := '';
154 x_sourced NUMBER := '';
155
156 CURSOR C_ON_PO is
157 SELECT pol.po_line_id
158 FROM po_lines pol
159 WHERE pol.from_header_id = X_po_header_id
160 AND pol.from_line_id = X_po_line_id;
161
162 CURSOR C_ON_REQ is
163 SELECT prl.blanket_po_line_num
164 FROM po_requisition_lines prl
165 WHERE prl.BLANKET_PO_HEADER_ID = X_po_header_id
166 AND prl.BLANKET_PO_LINE_NUM = X_po_line_num;
167
168 BEGIN
169
170
171 x_progress := '005';
172
173 dbms_output.put_line('Before open cursors');
174
175 /*
176 ** open all cursors
177 */
178 x_progress := '010';
179 OPEN C_ON_PO;
180
181 x_progress := '020';
182 OPEN C_ON_REQ;
183
184 /*
185 ** and fetch values into all cursors.
186 */
187
188 x_progress := '030';
189 FETCH C_ON_PO into x_fetched_on_po;
190 FETCH C_ON_REQ into x_fetched_on_req;
191
192 /*
193 ** check to see if the Quotation line is referenced on a PO
194 ** if it is NOT, verify it is not used in autosource rules.
195 ** if it is, display message and prevent delete.
196 */
197 IF C_ON_PO%NOTFOUND THEN
198
199 /*
200 ** check to see if the Quotation line is used in ASL.
201 ** if it is NOT, verify it is not on a req line.
202 ** if it is, display message and prevent delete.
203 **
204 */
205
206
207 x_progress := '040';
208 SELECT count(*)
209 INTO x_sourced
210 FROM po_asl_documents pad
211 WHERE pad.DOCUMENT_HEADER_ID = X_po_header_id
212 AND pad.DOCUMENT_LINE_ID = X_po_line_id;
213
214
215 IF (x_sourced = 0) THEN
216
217 /*
218 ** verify the Quotation line is not used for reference on
219 ** a requisition line.
220 ** if it is NOT, allow the delete.
221 ** if it is, display message and prevent delete.
222 */
223
224 IF C_ON_REQ%NOTFOUND THEN
225 X_allow_delete := 'Y';
226 dbms_output.put_line('Allow delete = '||X_allow_delete);
227 ELSE
228 X_allow_delete := 'N';
229 po_message_s.app_error('PO_DELETE_REQS');
230 dbms_output.put_line('Allow delete = '||X_allow_delete);
231 END IF;
232
233 ELSE
234 X_allow_delete := 'N';
235 po_message_s.app_error('PO_QT_LINE_DELETE_SOURCE');
236 dbms_output.put_line('Allow delete = '||X_allow_delete);
237 END IF;
238
239 ELSE
240 X_allow_delete := 'N';
241 po_message_s.app_error('PO_QT_LINE_DELETE_NA');
242 dbms_output.put_line('Allow delete = '||X_allow_delete);
243 END IF;
244
245 /*
246 ** close cursors
247 */
248 CLOSE C_ON_PO;
249 CLOSE C_ON_REQ;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 dbms_output.put_line('In VAL exception');
254 po_message_s.sql_error('val_line_delete', x_progress, sqlcode);
255
256 END val_line_delete;
257
258 /*===========================================================================
259
260 PROCEDURE NAME: test_val_reply()
261
262 ===========================================================================*/
263
264 PROCEDURE test_val_reply
265 (X_from_header_id IN NUMBER,
266 X_vendor_id IN NUMBER,
267 X_vendor_site_id IN NUMBER) IS
268
269 BEGIN
270
271 dbms_output.put_line('Before_call');
272
273 IF po_quotes_sv.val_reply(X_from_header_id, X_vendor_id, X_vendor_site_id) THEN
274 dbms_output.put_line('Return TRUE');
275 ELSE
276 dbms_output.put_line('Return FALSE');
277 END IF;
278
279 END test_val_reply;
280
281 /*===========================================================================
282
283 FUNCTION NAME: val_reply()
284
285 ===========================================================================*/
286
287 FUNCTION val_reply
288 (X_from_header_id IN NUMBER,
289 X_vendor_id IN NUMBER,
290 X_vendor_site_id IN NUMBER) RETURN BOOLEAN IS
291
292 x_progress VARCHAR2(3) := '';
293 x_duplicate_reply VARCHAR2(1) := '';
294
295 BEGIN
296 x_progress := '010';
297
298 /*
299 ** Check if a quotation already exists for a specific RFQ/Supplier/Site
300 ** combination. If it does, return TRUE. Otherwise return FALSE.
301 */
302 SELECT MAX('Y')
303 INTO x_duplicate_reply
304 FROM po_headers
305 WHERE from_header_id = X_from_header_id
306 AND vendor_id = X_vendor_id
307 AND vendor_site_id = X_vendor_site_id
308 AND from_type_lookup_code = 'RFQ';
309
310 x_progress := '020';
311
312 IF (x_duplicate_reply is null) THEN
313 RETURN (FALSE);
314 ELSE
315 RETURN (TRUE);
316 END IF;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 dbms_output.put_line('In exception');
321 po_message_s.sql_error('val_reply', x_progress, sqlcode);
322
323 END val_reply;
324
325 /*===========================================================================
326
327 PROCEDURE NAME: test_get_quote_status()
328
329 ===========================================================================*/
330
331 PROCEDURE test_get_quote_status
332 (X_po_header_id IN NUMBER) IS
333
334 X_quote_referenced VARCHAR2(1) := '';
335
336 BEGIN
337
338 dbms_output.put_line('Before_call');
339
340 po_quotes_sv.get_quote_status(X_po_header_id, X_quote_referenced);
341
342 dbms_output.put_line('After call');
343 dbms_output.put_line('Quote Referenced? => '||X_quote_referenced);
344
345 END test_get_quote_status;
346
347 /*===========================================================================
348
349 PROCEDURE NAME: get_quote_status()
350
351 ===========================================================================*/
352
353 PROCEDURE get_quote_status(X_po_header_id IN NUMBER,
354 X_quote_referenced IN OUT NOCOPY VARCHAR2) IS
355
356 x_progress VARCHAR2(3) := NULL;
357
358 BEGIN
359 x_progress := '010';
360
361 /*
362 ** Check to see if quotation is referenced on a purchase order.
363 */
364 SELECT MAX('Y')
365 INTO X_quote_referenced
366 FROM po_lines pol
367 WHERE pol.from_header_id = X_po_header_id;
368
369 x_progress := '020';
373 x_progress := '030';
370
371 IF (X_quote_referenced is null) THEN
372
374
375 /*
376 ** Check to see if quotation is used for autosource rules.
377 */
378 SELECT MAX('Y')
379 INTO X_quote_referenced
380 FROM po_autosource_documents pad
381 WHERE pad.DOCUMENT_HEADER_ID = X_po_header_id;
382
383 x_progress := '040';
384
385 IF (X_quote_referenced is null) THEN
386 X_quote_referenced := 'N';
387 END IF;
388
389 END IF;
390
391 dbms_output.put_line('Is Quote Referenced? => '||X_quote_referenced);
392
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 dbms_output.put_line('In exception');
397 po_message_s.sql_error('get_quote_status', x_progress, sqlcode);
398
399 END get_quote_status;
400
401 /*===========================================================================
402
403 PROCEDURE NAME: test_get_from_rfq_defaults()
404
405 ===========================================================================*/
406
407 PROCEDURE test_get_from_rfq_defaults
408 (X_from_header_id IN NUMBER) IS
409
410 X_rfq_close_date DATE := '';
411 X_from_type_lookup_code VARCHAR2(30) := '';
412 X_approval_required_flag VARCHAR2(1) := '';
413
414
415 BEGIN
416 dbms_output.put_line('before call');
417
418 po_quotes_sv.get_from_rfq_defaults
419 (X_from_header_id,
420 X_rfq_close_date,
421 X_from_type_lookup_code,
422 X_approval_required_flag);
423
424 dbms_output.put_line('RFQ Close Date = '||X_rfq_close_date);
425 dbms_output.put_line('From Type Lookup Code = '||X_from_type_lookup_code);
426 dbms_output.put_line('Acceptance Required = '||X_approval_required_flag);
427
428 END test_get_from_rfq_defaults;
429
430 /*===========================================================================
431
432 PROCEDURE NAME: get_from_rfq_defaults()
433
434 ===========================================================================*/
435
436 PROCEDURE get_from_rfq_defaults
437 (X_from_header_id IN NUMBER,
438 X_rfq_close_date IN OUT NOCOPY DATE,
439 X_from_type_lookup_code IN OUT NOCOPY VARCHAR2,
440 X_approval_required_flag IN OUT NOCOPY VARCHAR2) IS
441
442 x_progress VARCHAR2(3) := '';
443
444 BEGIN
445 x_progress := '010';
446
447 IF (X_from_header_id is not null) THEN
448 /*
449 ** Get the defaults for the selected From RFQ number
450 */
451 SELECT poh.rfq_close_date,
452 poh.type_lookup_code,
453 poh.approval_required_flag
454 INTO X_rfq_close_date,
455 X_from_type_lookup_code,
456 X_approval_required_flag
457 FROM po_headers poh
458 WHERE poh.po_header_id = X_from_header_id;
459
460 ELSE
461 x_progress := '030';
462 po_message_s.sql_error('from_header_id is null', x_progress, sqlcode);
463
464 END IF;
465
466 dbms_output.put_line('RFQ Close Date = '||X_rfq_close_date);
467 dbms_output.put_line('From Type Lookup Code = '||X_from_type_lookup_code);
468 dbms_output.put_line('Acceptance Required = '||X_approval_required_flag);
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 dbms_output.put_line('In exception');
473 po_message_s.sql_error('get_from_rfq_defaults', x_progress, sqlcode);
474
475 END get_from_rfq_defaults;
476
477 /*===========================================================================
478
479 PROCEDURE NAME: get_approval_status()
480
481 ===========================================================================*/
482
483 PROCEDURE get_approval_status
484 (X_line_location_id IN NUMBER,
485 X_approval_status IN OUT NOCOPY VARCHAR2) IS
486
487 x_progress VARCHAR2(3) := '';
488
489 BEGIN
490 x_progress := '010';
491 X_approval_status := 'N';
492
493 /*
494 ** Check if the shipment has been approved. If it has, set
495 ** X_approval_status to 'Y', else it is 'N'.
496 */
497 SELECT MAX('Y')
498 INTO X_approval_status
499 FROM po_quotation_approvals
500 WHERE line_location_id = X_line_location_id
501 AND sysdate BETWEEN nvl(start_date_active, sysdate-1)
502 AND nvl(end_date_active, sysdate+1);
503
504 x_progress := '020';
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 dbms_output.put_line('In exception');
509 po_message_s.sql_error('get_approval_status', x_progress, sqlcode);
510
511 END get_approval_status;
512
513
514 END PO_QUOTES_SV;
[Home] [Help]