1 PACKAGE BODY PO_CORE_SV1 AS
2 /* $Header: POXPICOB.pls 120.2 2006/04/20 11:32:12 bao noship $ */
3
4 /*================================================================
5
6 FUNCTION NAME: val_effective_date()
7
8 ==================================================================*/
9 FUNCTION val_effective_date(x_effective_date IN DATE,
10 x_po_header_id IN NUMBER)
11 RETURN BOOLEAN IS
12
13 x_progress VARCHAR2(3):= null;
14 l_temp VARCHAR2(10);
15
16 CURSOR l_val_date_csr IS
17 SELECT 'valid'
18 FROM po_headers
19 WHERE po_header_id = x_po_header_id
20 AND (TRUNC(x_effective_date) BETWEEN
21 TRUNC(nvl(start_date,x_effective_date)) AND
22 TRUNC(nvl(end_date, x_effective_date)));
23
24 BEGIN
25 x_progress := '010';
26 /* This is to check if the X_effective_date is between start_date
27 and end_date for a specific po_header_id */
28
29 -- Bug 2449186. Truncate the dates when comparing them. Also
30 -- refactored to use a cursor instead of a select with count(*).
31 OPEN l_val_date_csr;
32 FETCH l_val_date_csr INTO l_temp;
33
34 x_progress := '020';
35
36 IF l_val_date_csr%FOUND THEN
37 CLOSE l_val_date_csr;
38 RETURN TRUE; /* validation succeeds */
39 ELSE
40 CLOSE l_val_date_csr;
41 RETURN FALSE; /* validation fails */
42 END IF;
43
44 EXCEPTION
45 WHEN others THEN
46 IF l_val_date_csr%ISOPEN THEN
47 CLOSE l_val_date_csr;
48 END IF;
49 po_message_s.sql_error('val_effective_date', x_progress, sqlcode);
50 raise;
51 END val_effective_date;
52
53 /*================================================================
54
55 FUNCTION NAME: val_numeric_value()
56
57 ==================================================================*/
58 FUNCTION val_numeric_value(x_value IN VARCHAR2) RETURN BOOLEAN
59 IS
60
61 x_progress varchar2(3) := null;
62 X_temp NUMBER;
63
64 BEGIN
65 x_progress := '010';
66
67 X_temp := TO_NUMBER(X_value);
68
69 RETURN TRUE;
70
71 EXCEPTION
72 WHEN value_error THEN /* when input value is not a numeric */
73 RETURN FALSE;
74 WHEN others THEN
75 po_message_s.sql_error('val_numeric_value', x_progress,sqlcode);
76 raise;
77 END val_numeric_value;
78
79 /*================================================================
80
81 FUNCTION NAME: val_start_and_end_date()
82
83 ==================================================================*/
84 FUNCTION val_start_and_end_date(X_start_date IN DATE,
85 X_end_date IN DATE)
86 RETURN BOOLEAN
87 IS
88
89 x_progress varchar2(3) := null;
90
91 BEGIN
92 x_progress := '010';
93
94 IF (X_start_date > X_end_date) THEN
95 RETURN FALSE; /* validation fails */
96 ELSE
97 RETURN TRUE; /* validation succeeds */
98 END IF;
99
100 EXCEPTION
101 WHEN others THEN
102 po_message_s.sql_error('val_start_and_end_date', x_progress,sqlcode);
103 raise;
104 END val_start_and_end_date;
105
106 /*================================================================
107
108 FUNCTION NAME: val_flag_value()
109
110 ==================================================================*/
111 FUNCTION val_flag_value(x_flag_value IN VARCHAR2)
112 RETURN BOOLEAN
113 IS
114
115 x_progress varchar2(3) := null;
116
117 BEGIN
118 x_progress := '010';
119
120 IF X_flag_value In ('Y','N') THEN
121 RETURN TRUE; /* validation fails */
122 ELSE
123 RETURN FALSE; /* validation succeeds */
124 END IF;
125
126 EXCEPTION
127 WHEN others THEN
128 po_message_s.sql_error('val_flag_value', x_progress,sqlcode);
129 raise;
130 END val_flag_value;
131
132 /*================================================================
133
134 FUNCTION NAME: default_po_unique_identifier()
135
136 ==================================================================*/
137
138 FUNCTION default_po_unique_identifier(X_table_name IN VARCHAR2)
139 return varchar2 IS
140
141 X_progress varchar2(3) := NULL;
142 X_po_unique_identifier_v NUMBER := NULL;
143
144 l_org_id NUMBER := PO_MOAC_UTILS_PVT.get_current_org_id; -- bug5174177
145
146 BEGIN
147
148 X_progress := '010';
149
150 -- bug5174177
151 -- PO number generation logic has been moved to the following procedure,
152 -- which takes in ou id.
153 X_po_unique_identifier_v := default_po_unique_identifier
154 ( p_table_name => x_table_name,
155 p_org_id => l_org_id
156 );
157
158 RETURN X_po_unique_identifier_v;
159
160 END default_po_unique_identifier;
161
162
163 -- bug5174177 START
164 -- Moved the po num generation logic to this overloaded function.
165 -- Need to introduce p_org_id because autocreate creates the PO
166 -- from a different OU.
167
168 -----------------------------------------------------------------------
169 --Start of Comments
170 --Name: default_po_unique_identifier
171 --Pre-reqs: None
172 --Modifies:
173 --Locks:
174 -- None
175 --Function:
176 -- Return the next po/req number generated by the system
177 --Parameters:
178 --IN:
179 --p_table_name
180 -- Type of document to generate number for. Possible values are:
181 -- PO_HEADERS -- For PO and PA
182 -- PO_HEADERS_QUOTE -- For Quote
183 -- PO_HEADERS_RFQ -- For RFQ
184 -- PO_REQUISITION_HEADERS -- For Requisition
185 --p_org_id
186 -- Operating unit where the document belongs to
187 --IN OUT:
188 --OUT:
189 --Returns: Next po/req number generated by the system
190 --Notes:
191 --Testing:
192 --End of Comments
193 ------------------------------------------------------------------------
194 FUNCTION default_po_unique_identifier
195 ( p_table_name IN VARCHAR2,
196 p_org_id IN NUMBER
197 ) RETURN VARCHAR2 IS
198 PRAGMA AUTONOMOUS_TRANSACTION; -- bug5047141
199
200 l_progress varchar2(3) := NULL;
201 l_po_unique_identifier_v NUMBER := NULL;
202
203 BEGIN
204
205 l_progress := '010';
206
207 -- increment the number in po_unique_identifier_cont_all by 1
208 -- based on table_name and org_id from parameter list.
209 UPDATE po_unique_identifier_cont_all
210 SET current_max_unique_identifier =
211 current_max_unique_identifier +1
212 WHERE table_name = p_table_name
213 AND org_id = p_org_id
214 RETURNING current_max_unique_identifier
215 INTO l_po_unique_identifier_v;
216
217 COMMIT; -- bug5047141
218
219
220 RETURN TO_CHAR(l_po_unique_identifier_v);
221
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 RETURN NULL;
225 WHEN OTHERS THEN
226 PO_MESSAGE_S.sql_error('default_po_unique_identifier',
227 l_progress, sqlcode);
228 RAISE;
229 END default_po_unique_identifier;
230
231 -- bug5174177 END
232
233
234
235
236 /*================================================================
237
238 FUNCTION NAME: val_max_and_min_qty()
239
240 ==================================================================*/
241 FUNCTION val_max_and_min_qty(x_qty1 IN NUMBER,
242 x_qty2 IN NUMBER) RETURN BOOLEAN
243 IS
244
245 x_progress varchar2(3) := null;
246
247 BEGIN
248 x_progress := '010';
249
250 /* check to see if x_qty1 > x_qty2 */
251
252 IF (x_qty1 > x_qty2) THEN
253 RETURN FALSE; /* validation fails */
254 ELSE
255 RETURN TRUE; /* validation succeeds */
256 END IF;
257
258 EXCEPTION
259 WHEN others THEN
260 po_message_s.sql_error
261 ('val_gt_and_lt_qty', x_progress, sqlcode);
262 raise;
263 END val_max_and_min_qty;
264
265 /*================================================================
266
267 FUNCTION NAME: val_discount()
268
269 ==================================================================*/
270 FUNCTION val_discount(x_discount IN NUMBER) RETURN BOOLEAN
271 IS
272
273 x_progress varchar2(3) := null;
274
275 BEGIN
276 x_progress := '010';
277
278 IF (x_discount < 0) OR (x_discount > 100) THEN
279 RETURN FALSE; /* validation fails */
280 ELSE
281 RETURN TRUE; /* validation succeeds */
282 END IF;
283
284 EXCEPTION
285 WHEN others THEN
286 po_message_s.sql_error('val_discount_betwn_zero_and_hundred',
287 x_progress,sqlcode);
288 raise;
289 END val_discount;
290
291
292 END PO_CORE_SV1;
[Home] [Help]