[Home] [Help]
PACKAGE BODY: APPS.PO_RFQS_SV
Source
1 PACKAGE BODY PO_RFQS_SV as
2 /* $Header: POXSORFB.pls 120.0.12000000.3 2007/10/11 13:50:58 ppadilam ship $ */
3
4 /*============================= PO_RFQS_SV ===============================*/
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_rfqs_sv.val_header_delete(X_po_header_id, X_allow_delete);
21
22 -- dbms_output.put_line('After call');
23
24
25 END test_val_header_delete;
26
27 /*===========================================================================
28
29 PROCEDURE NAME: val_header_delete()
30
31 ===========================================================================*/
32
33 PROCEDURE val_header_delete(X_po_header_id IN NUMBER,
34 X_allow_delete IN OUT NOCOPY BOOLEAN) IS
35
36 x_progress VARCHAR2(3) := '';
37 x_delete_test VARCHAR2(1) := 'Y';
38
39 BEGIN
40 x_progress := '010';
41
42 /*
43 ** Check to see if the RFQ header has been printed
44 ** if it has NOT, allow deletion.
45 ** if it has, display message and prevent deletion.
46 */
47 SELECT MAX('N')
48 INTO X_delete_test
49 FROM po_rfq_vendors
50 WHERE printed_date is not null
51 AND po_header_id = X_po_header_id;
52
53 x_progress := '020';
54
55 IF (nvl(X_delete_test,'Y') = 'Y') THEN
56 x_progress := '030';
57
58 /*
59 ** Check to see if RFQ is referenced on a Quotation
60 ** if it is NOT, allow deletion.
61 ** if it is, display message and prevent deletion.
62 */
63 SELECT MAX('N')
64 INTO X_delete_test
65 FROM po_headers poh
66 WHERE from_header_id = X_po_header_id;
67
68 x_progress := '040';
69
70 IF (nvl(X_delete_test,'Y') ='Y') THEN
71 X_allow_delete := TRUE;
72 ELSE
73 X_allow_delete := FALSE;
74 po_message_s.app_error('PO_RFQ_QT_DELETE_NA');
75 END IF;
76
77 ELSE
78 X_allow_delete := FALSE;
79 po_message_s.app_error('PO_RFQ_DELETE_PRINT_RFQ_NA');
80 END IF;
81
82 --dbms_output.put_line('Allow delete = '||X_delete_test);
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 -- dbms_output.put_line('In VAL exception');
87 po_message_s.sql_error('val_header_delete', x_progress, sqlcode);
88
89 END val_header_delete;
90
91 /*===========================================================================
92
93 PROCEDURE NAME: test_val_line_delete()
94
95 ===========================================================================*/
96
97 PROCEDURE test_val_line_delete(X_po_line_id IN NUMBER,
98 X_po_header_id IN NUMBER) IS
99
100 X_allow_delete VARCHAR2(1) := '';
101
102 BEGIN
103
104 -- dbms_output.put_line('Before_call');
105
106 po_rfqs_sv.val_line_delete(X_po_line_id, X_po_header_id, X_allow_delete);
107
108 -- dbms_output.put_line('After call');
109 -- dbms_output.put_line('Allow Delete = '||X_allow_delete);
110
111 END test_val_line_delete;
112
113 /*===========================================================================
114
115 PROCEDURE NAME: val_line_delete()
116
117 ===========================================================================*/
118
119 PROCEDURE val_line_delete(X_po_line_id IN NUMBER,
120 X_po_header_id IN NUMBER,
121 X_allow_delete IN OUT NOCOPY VARCHAR2) IS
122
123 x_progress VARCHAR2(3) := '';
124 x_fetched_line_id NUMBER := '';
125
126 CURSOR C is
127 SELECT pol.po_line_id
128 FROM po_lines pol
129 WHERE pol.from_header_id = X_po_header_id
130 AND pol.from_line_id = X_po_line_id;
131
132 BEGIN
133
134 -- dbms_output.put_line('Before open cursor');
135
136 x_progress := '010';
137 OPEN C;
138 x_progress := '020';
139
140 FETCH C into x_fetched_line_id;
141
142 /*
143 ** Check to see if the RFQ line is used on a quotation
144 ** if it is NOT, allow deletion.
145 ** if it is, display message and prevent deletion.
146 */
147 IF C%NOTFOUND THEN
148 X_allow_delete := 'Y';
149 -- dbms_output.put_line('Allow delete = '||X_allow_delete);
150
151 ELSE
152 X_allow_delete := 'N';
153 po_message_s.app_error('PO_RFQ_QT_DELETE_NA');
154 -- dbms_output.put_line('Allow delete = '||X_allow_delete);
155
156 END IF;
157
158 CLOSE C;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 -- dbms_output.put_line('In VAL exception');
163 po_message_s.sql_error('val_line_delete', x_progress, sqlcode);
164
165 END val_line_delete;
166
167 /*===========================================================================
168
169 FUNCTION NAME: get_vendor_count
170
171 ===========================================================================*/
172
173 FUNCTION get_vendor_count
174 (X_vendor_list_header_id NUMBER) return number is
175
176 x_vendor_count NUMBER;
177 X_Progress varchar2(3) := '';
178
179 BEGIN
180
181 X_progress := '010';
182
183 /* Bug 875124 :
184 Using po_vendor_list_entries_v to get the count
185 as po_vendor_list_entries_v contains vendor_list with active vendors
186 */
187 SELECT count(*)
188 INTO X_vendor_count
189 FROM po_vendor_list_entries_v
190 WHERE vendor_list_header_id = X_vendor_list_header_id;
191
192 X_progress := '020';
193
194 return(x_vendor_count);
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 return(0);
199 RAISE;
200
201 END get_vendor_count;
202
203 /*===========================================================================
204
205 FUNCTION NAME: val_vendor_site()
206
207 ===========================================================================*/
208
209 FUNCTION val_vendor_site
210 (X_po_header_id IN NUMBER,
211 X_vendor_id IN NUMBER,
212 X_vendor_site_id IN NUMBER,
213 X_row_id IN VARCHAR2) RETURN BOOLEAN is
214
215 x_duplicate_vendor_site varchar2(1) := 'Y';
216 X_Progress varchar2(3) := '000';
217
218 BEGIN
219
220 X_progress := '010';
221
222 SELECT MAX('N')
223 INTO X_duplicate_vendor_site
224 FROM po_rfq_vendors
225 WHERE po_header_id = X_po_header_id
226 AND vendor_id = X_vendor_id
227 AND vendor_site_id = X_vendor_site_id
228 AND (X_row_id IS NULL
229 OR rowid <> X_row_id);
230
231 X_progress := '020';
232
233 if (nvl(X_duplicate_vendor_site,'Y') = 'Y') then
234 return(TRUE);
235
236 else
237 po_message_s.app_error('PO_RFQ_VENDOR_ALREADY_EXISTS');
238 return(FALSE);
239
240 end if;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 return(FALSE);
245 po_message_s.sql_error('val_vendor_site', x_progress, sqlcode);
246 RAISE;
247
248 END val_vendor_site;
249
250
251 /*===========================================================================
252
253 FUNCTION NAME: val_vendor_update
254
255 ===========================================================================*/
256
257 FUNCTION val_vendor_update
258 (X_po_header_id IN NUMBER,
259 X_vendor_id IN NUMBER,
260 X_vendor_site_id IN NUMBER) RETURN BOOLEAN is
261
262 X_allow_update varchar2(1) := 'Y';
263 X_Progress varchar2(3) := '000';
264
265 BEGIN
266
267 X_progress := '010';
268
269 SELECT MAX('N')
270 INTO X_allow_update
271 FROM po_headers
272 WHERE from_header_id = X_po_header_id
273 AND vendor_id = X_vendor_id
274 AND vendor_site_id = X_vendor_site_id
275 AND from_type_lookup_code = 'RFQ'
276 AND type_lookup_code = 'QUOTATION';
277
278
279 X_progress := '020';
280
281 if (nvl(X_allow_update,'Y') = 'Y') then
282 return(TRUE);
283
284 else
285 po_message_s.app_error('PO_QUOTE_ENTERED_UPDATE_NA');
286 return(FALSE);
287
288 end if;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 return(FALSE);
293 po_message_s.sql_error('val_vendor_update', x_progress, sqlcode);
294 RAISE;
295
296 END val_vendor_update;
297
298 /*===========================================================================
299
300 PROCEDURE NAME: copy_vendor_list_to_rfq()
301
302 ===========================================================================*/
303
304 PROCEDURE copy_vendor_list_to_rfq(X_rowid IN OUT NOCOPY VARCHAR2,
305 X_po_header_id IN OUT NOCOPY NUMBER,
306 X_max_sequence_num IN NUMBER,
307 X_last_update_date IN DATE,
308 X_last_updated_by IN NUMBER,
309 X_last_update_login IN NUMBER,
310 X_creation_date IN DATE,
311 X_created_by IN NUMBER,
312 X_list_header_id IN NUMBER,
313 x_vendors_hold IN OUT NOCOPY VARCHAR2 ) IS
314
315 CURSOR C IS SELECT rowid FROM PO_RFQ_VENDORS
316 WHERE po_header_id = X_po_header_id
317 AND sequence_num = X_max_sequence_num + 1;
318
319 CURSOR C2 IS SELECT po_headers_s.nextval FROM sys.dual;
320
321 /* Bug # 6161855 */
322 l_list_header_id NUMBER := X_list_header_id;
323 CURSOR C3 IS SELECT vendor_id FROM po_vendor_list_entries WHERE vendor_list_header_id= l_list_header_id ;
324 /*end of Bug # 6161855*/
325
326 x_progress VARCHAR2(3) := '';
327 x_vendor_list_name po_vendor_list_headers.vendor_list_name%type := null;
328
329 /* Bug # 6161855 */
330 l_vendor_name po_vendors.vendor_name%TYPE := NULL;
331 myResult number;
332 flagValue varchar2(1);
333 /*end of Bug # 6161855*/
334
335 BEGIN
336
337 /* Bug # 6161855 */
338 OPEN C3;
339 LOOP
340 FETCH C3 into myResult;
341 EXIT WHEN C3%NOTFOUND;
342 SELECT Nvl(hold_flag,'F'),vendor_name INTO flagValue, l_vendor_name FROM po_vendors WHERE vendor_id=myResult;
343
344 IF flagValue='Y' then
345 x_vendors_hold:=x_vendors_hold ||', '|| l_vendor_name;
346 END if;
347
348 END LOOP;
349 CLOSE C3;
350 /*end of Bug # 6161855*/
351
352 X_progress := '010';
353
354 if (X_po_header_id is NULL) then
355 OPEN C2;
356 FETCH C2 INTO X_po_header_id;
357 CLOSE C2;
358 end if;
359
360 X_progress := '020';
361
362 /* Bug 875124 :
363 Using po_vendor_list_entries_v to insert into po_rfq_vendors
364 as po_vendor_list_entries_v contains vendor_list with active vendors
365 */
366 insert into po_rfq_vendors
367 (po_header_id,
368 sequence_num,
369 last_update_date,
370 last_updated_by,
371 last_update_login,
372 creation_date,
373 created_by,
374 vendor_id,
375 vendor_site_id,
376 vendor_contact_id,
377 print_flag,
378 print_count)
379 select
380 X_po_header_id,
381 rownum + X_max_sequence_num,
382 X_last_update_date,
383 X_last_updated_by,
384 X_last_update_login,
385 X_creation_date,
386 X_created_by,
387 vle.vendor_id,
388 vle.vendor_site_id,
389 vle.vendor_contact_id,
390 'Y',
391 '0'
392 from po_vendor_list_entries_v vle
393 where vle.vendor_list_header_id = X_list_header_id
394 and not exists (select 'vendor already there'
395 from po_rfq_vendors rv
396 where vle.vendor_site_id = rv.vendor_site_id
397 and rv.po_header_id = X_po_header_id);
398
399 X_progress := '030';
400
401 OPEN C;
402 FETCH C INTO X_rowid;
403 if (C%NOTFOUND) then
404 CLOSE C;
405
406 select vendor_list_name
407 into x_vendor_list_name
408 from po_vendor_list_headers
409 where vendor_list_header_id = X_list_header_id;
410
411 Raise NO_DATA_FOUND;
412 end if;
413 CLOSE C;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 -- dbms_output.put_line('In VAL exception');
418
419 -- Bug 430179 set message name and token here, and later retrieve it on the client side
420
421 po_message_s.app_error('PO_COPY_SUPPLIERS_TO_RFQ','LIST',x_vendor_list_name);
422
423 END copy_vendor_list_to_rfq;
424
425 END PO_RFQS_SV;