1 PACKAGE BODY PO_RFQ_VENDOR_SV as
2 /* $Header: POXSORVB.pls 115.1 2002/11/26 19:49:47 sbull ship $ */
3 /*========================== PO_RFQ_VENDOR_SV ============================*/
4
5
6 /*===========================================================================
7
8 PROCEDURE NAME: test_get_sequence_num()
9
10 ===========================================================================*/
11
12 PROCEDURE test_get_sequence_num(X_po_header_id IN NUMBER) IS
13
14 X_sequence_num NUMBER := '';
15
16 BEGIN
17 dbms_output.put_line('before call');
18
19 po_rfq_vendor_sv.get_sequence_num(X_po_header_id,
20 X_sequence_num);
21
22 dbms_output.put_line('The next sequence number is = '||X_sequence_num);
23
24 END test_get_sequence_num;
25
26
27 /*===========================================================================
28
29 FUNCTION NAME: get_sequence_num()
30
31 ===========================================================================*/
32
33 PROCEDURE get_sequence_num
34 (X_po_header_id IN NUMBER,
35 X_sequence_num IN OUT NOCOPY NUMBER) IS
36
37 x_progress VARCHAR2(3) := '';
38
39 CURSOR C is
40 SELECT max(sequence_num) + 1
41 FROM po_rfq_vendors
42 WHERE po_header_id = X_po_header_id;
43
44 BEGIN
45
46 dbms_output.put_line('Before_open_cursor');
47
48 IF (X_po_header_id is not null) THEN
49 x_progress := '010';
50 OPEN C;
51 x_progress := '020';
52
53 /*
54 ** Get the next sequence number.
55 */
56 FETCH C into X_sequence_num;
57 CLOSE C;
58
59 /*
60 ** If there is no sequence number retrieved, then this is the first
61 ** RFQ Vendor line to be created. Default the sequence number to 1
62 ** in this case.
63 */
64 IF (X_sequence_num is NULL) THEN
65 X_sequence_num := 1;
66 END IF;
67
68 dbms_output.put_line('Next sequence number is: '||X_sequence_num);
69
70 ELSE
71 x_progress := '030';
72 po_message_s.sql_error('no po_header_id', x_progress, sqlcode);
73
74 END IF;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 po_message_s.sql_error('get_sequence_num', x_progress, sqlcode);
79
80 END get_sequence_num;
81
82 /*===========================================================================
83
84 PROCEDURE NAME: test_val_seq_num_unique
85
86 ===========================================================================*/
87 PROCEDURE test_val_seq_num_unique (X_po_header_id IN NUMBER,
88 X_sequence_num IN NUMBER) IS
89
90 X_seq_num_is_unique VARCHAR2(1) := '';
91
92 BEGIN
93
94 dbms_output.put_line('before call');
95
96 po_rfq_vendor_sv.val_seq_num_unique(X_po_header_id, X_sequence_num,
97 X_seq_num_is_unique);
98
99 dbms_output.put_line('after call');
100 dbms_output.put_line('Sequence unique = '||X_seq_num_is_unique);
101
102 END test_val_seq_num_unique;
103
104
105 /*===========================================================================
106
107 PROCEDURE NAME: val_seq_num_unique()
108
109 ===========================================================================*/
110
111 PROCEDURE val_seq_num_unique
112 (X_po_header_id IN NUMBER,
113 X_sequence_num IN NUMBER,
114 X_seq_num_is_unique IN OUT NOCOPY VARCHAR2) IS
115
116 x_progress VARCHAR2(3) := '';
117
118 BEGIN
119
120 x_progress := '010';
121
122 /*
123 ** Verify uniqueness of the sequence number for the RFQ Vendor list.
124 */
125 SELECT MAX('N')
126 INTO X_seq_num_is_unique
127 FROM po_rfq_vendors
128 WHERE po_header_id = X_po_header_id
129 AND sequence_num = X_sequence_num;
130
131 x_progress := '020';
132
133 IF (X_seq_num_is_unique is NULL) THEN
134 X_seq_num_is_unique := 'Y';
135 END IF;
136
137 dbms_output.put_line('Sequence is unique = '||X_seq_num_is_unique);
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 po_message_s.sql_error('val_seq_num_unique', x_progress, sqlcode);
142
143 END val_seq_num_unique;
144
145 END PO_RFQ_VENDOR_SV;
146
[Home] [Help]