[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_HEADERS2
Source
1 PACKAGE BODY PO_VAL_HEADERS2 AS
2 -- $Header: PO_VAL_HEADERS2.plb 120.19.12010000.2 2008/08/04 08:41:02 rramasam ship $
3 c_entity_type_header CONSTANT VARCHAR2(30) := po_validations.c_entity_type_header;
4 -- The module base for this package.
5 d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_HEADERS2');
6
7 -- The module base for the subprogram.
8 d_po_header_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PO_HEADER_ID');
9 d_document_num CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DOCUMENT_NUM');
10 d_type_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'TYPE_LOOKUP_CODE');
11 d_currency_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CURRENCY_CODE');
12 d_rate_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'RATE_INFO');
13 d_agent_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AGENT_ID');
14 d_vendor_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'VENDOR_INFO');
15 d_ship_to_location_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIP_TO_LOCATION_ID');
16 d_bill_to_location_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'BILL_TO_LOCATION_ID');
17 d_last_updated_by CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'LAST_UPDATED_BY');
18 d_ship_via_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIP_VIA_LOOKUP_CODE');
19 d_fob_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'FOB_LOOKUP_CODE');
20 d_freight_terms_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'FREIGHT_TERMS_LOOKUP_CODE');
21 d_shipping_control CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIPPING_CONTROL');
22 d_approval_status CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'APPROVAL_STATUS');
23 d_acceptance_required_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ACCEPTANCE_REQUIRED_FLAG');
24 d_acceptance_due_date CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ACCEPTANCE_DUE_DATE');
25 d_cancel_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CANCEL_FLAG');
26 d_closed_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CLOSED_CODE');
27 d_print_count CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRINT_COUNT');
28 d_amount_to_encumber CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_TO_ENCUMBER');
29 d_style_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'STYLE_ID');
30 d_amount_limit CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_LIMIT');
31
32 -------------------------------------------------------------------------
33 -- po_header_id cannot be null and must not exist in Transaction header table.
34 -- Called for the create case.
35 -------------------------------------------------------------------------
36 PROCEDURE po_header_id(
37 p_id_tbl IN po_tbl_number,
38 p_po_header_id_tbl IN po_tbl_number,
39 x_result_set_id IN OUT NOCOPY NUMBER,
40 x_result_type OUT NOCOPY VARCHAR2)
41 IS
42 d_mod CONSTANT VARCHAR2(100) := d_po_header_id;
43 BEGIN
44 IF x_result_set_id IS NULL THEN
45 x_result_set_id := po_validations.next_result_set_id();
46 END IF;
47
48 IF po_log.d_proc THEN
49 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
50 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
51 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
52 END IF;
53
54 x_result_type := po_validations.c_result_type_success;
55 FORALL i IN 1 .. p_id_tbl.COUNT
56 INSERT INTO po_validation_results_gt
57 (result_set_id,
58 result_type,
59 entity_type,
60 entity_id,
61 message_name,
62 column_name,
63 column_val,
64 token1_name,
65 token1_value,
66 token2_name,
67 token2_value,
68 validation_id)
69 SELECT x_result_set_id,
70 po_validations.c_result_type_failure,
71 c_entity_type_header,
72 p_id_tbl(i),
73 DECODE(p_po_header_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_PO_HDR_ID_UNIQUE'),
74 'PO_HEADER_ID',
75 p_po_header_id_tbl(i),
76 'COLUMN_NAME',
77 'PO_HEADER_ID',
78 'VALUE',
79 p_po_header_id_tbl(i),
80 DECODE(p_po_header_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_header_id_not_null,
81 PO_VAL_CONSTANTS.c_po_header_id_unique)
82 FROM DUAL
83 WHERE p_id_tbl(i) IS NULL OR EXISTS(SELECT 1
84 FROM po_headers_all poh
85 WHERE p_po_header_id_tbl(i) = poh.po_header_id);
86
87 IF (SQL%ROWCOUNT > 0) THEN
88 x_result_type := po_validations.c_result_type_failure;
89 END IF;
90
91 IF po_log.d_proc THEN
92 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
93 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
94 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
95 END IF;
96 EXCEPTION
97 WHEN OTHERS THEN
98 IF po_log.d_exc THEN
99 po_log.exc(d_mod, 0, NULL);
100 END IF;
101
102 RAISE;
103 END po_header_id;
104
105 -----------------------------------------------------------------------------------------
106 -- document_num must not be null, must be unique, greater than or equal to zero and be of the correct type.
107 -----------------------------------------------------------------------------------------
108 PROCEDURE document_num(
109 p_id_tbl IN po_tbl_number,
110 p_po_header_id_tbl IN po_tbl_number,
111 p_document_num_tbl IN po_tbl_varchar30,
112 p_type_lookup_code_tbl IN po_tbl_varchar30,
113 p_manual_po_num_type IN VARCHAR2,
114 p_manual_quote_num_type IN VARCHAR2,
115 x_results IN OUT NOCOPY po_validation_results_type,
116 x_result_set_id IN OUT NOCOPY NUMBER,
117 x_result_type OUT NOCOPY VARCHAR2)
118 IS
119 d_mod CONSTANT VARCHAR2(100) := d_document_num;
120 l_num_test NUMBER;
121 BEGIN
122 IF x_result_set_id IS NULL THEN
123 x_result_set_id := po_validations.next_result_set_id();
124 END IF;
125
126 IF (x_results IS NULL) THEN
127 x_results := po_validation_results_type.new_instance();
128 END IF;
129
130 IF po_log.d_proc THEN
131 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
132 po_log.proc_begin(d_mod, 'p_document_num_tbl', p_document_num_tbl);
133 po_log.proc_begin(d_mod, 'p_type_lookup_code_tbl', p_type_lookup_code_tbl);
134 po_log.proc_begin(d_mod, 'p_manual_po_num_type', p_manual_po_num_type);
135 po_log.proc_begin(d_mod, 'p_manual_quote_num_type', p_manual_quote_num_type);
136 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
137 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
138 END IF;
139
140 x_result_type := po_validations.c_result_type_success;
141 -- Bulk validate document_num uniqueness
142 FORALL i IN 1 .. p_id_tbl.COUNT
143 INSERT INTO po_validation_results_gt
144 (result_set_id,
145 result_type,
146 entity_type,
147 entity_id,
148 message_name,
149 column_name,
150 column_val,
151 token1_name,
152 token1_value,
153 validation_id)
154 SELECT x_result_set_id,
155 po_validations.c_result_type_failure,
156 c_entity_type_header,
157 p_id_tbl(i),
158 'PO_PDOI_DOC_NUM_UNIQUE',
159 'DOCUMENT_NUM',
160 p_document_num_tbl(i),
161 'VALUE',
162 p_document_num_tbl(i),
163 PO_VAL_CONSTANTS.c_document_num_unique
164 FROM DUAL
165 WHERE p_document_num_tbl(i) IS NOT NULL AND
166 EXISTS(SELECT 1
167 FROM po_headers
168 WHERE segment1 = p_document_num_tbl(i)
169 AND ((p_type_lookup_code_tbl(i) IN ('BLANKET', 'STANDARD')
170 AND
171 type_lookup_code IN ('BLANKET', 'CONTRACT',
172 'PLANNED', 'STANDARD'))
173 OR
174 (p_type_lookup_code_tbl(i) = 'QUOTATION' AND
175 type_lookup_code = p_type_lookup_code_tbl(i))));
176
177 IF (SQL%ROWCOUNT > 0) THEN
178 x_result_type := po_validations.c_result_type_failure;
179 END IF;
180
181 FOR i IN 1 .. p_id_tbl.COUNT LOOP
182 IF p_document_num_tbl(i) IS NULL THEN
183 x_results.add_result(p_entity_type => c_entity_type_header,
184 p_entity_id => p_id_tbl(i),
185 p_column_name => 'DOCUMENT_NUM',
186 p_column_val => p_document_num_tbl(i),
187 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
188 p_token1_name => 'COLUMN_NAME',
189 p_token1_value => 'DOCUMENT_NUM',
190 p_token2_name => 'VALUE',
191 p_token2_value => p_document_num_tbl(i),
192 p_validation_id => PO_VAL_CONSTANTS.c_document_num_not_null);
193
194 x_result_type := po_validations.c_result_type_failure; -- bug5101044
195 ELSIF ((p_type_lookup_code_tbl(i) IN ('BLANKET', 'STANDARD')
196 AND p_manual_po_num_type = 'NUMERIC')
197 OR
198 (p_type_lookup_code_tbl(i) = 'QUOTATION'
199 AND p_manual_quote_num_type = 'NUMERIC')) THEN
200
201 BEGIN
202 l_num_test := TO_NUMBER(p_document_num_tbl(i));
203
204 -- validate that document_num is greater than or equal to zero.
205 -- Note that -po_header_id is a special segment1 that PDOI
206 -- temporarily sets for documents without segment1 specified.
207 IF (p_document_num_tbl(i) < 0 AND
208 p_document_num_tbl(i) <> -p_po_header_id_tbl(i)) THEN
209 x_results.add_result(p_entity_type => c_entity_type_header,
210 p_entity_id => p_id_tbl(i),
211 p_column_name => 'DOCUMENT_NUM',
212 p_column_val => p_document_num_tbl(i),
213 p_message_name => 'PO_PDOI_LT_ZERO',
214 p_token1_name => 'COLUMN_NAME',
215 p_token1_value => 'DOCUMENT_NUM',
216 p_token2_name => 'VALUE',
217 p_token2_value => p_document_num_tbl(i),
218 p_validation_id => PO_VAL_CONSTANTS.c_document_num_ge_zero);
219 x_result_type := po_validations.c_result_type_failure;
220 END IF;
221
222 EXCEPTION
223 WHEN VALUE_ERROR THEN
224 -- exception occured because value wasn't numeric
225 x_results.add_result(p_entity_type => c_entity_type_header,
226 p_entity_id => p_id_tbl(i),
227 p_column_name => 'DOCUMENT_NUM',
228 p_column_val => p_document_num_tbl(i),
229 p_message_name => 'PO_PDOI_VALUE_NUMERIC',
230 p_token1_name => 'COLUMN_NAME',
231 p_token1_value => 'DOCUMENT_NUM',
232 p_token2_name => 'VALUE',
233 p_token2_value => p_document_num_tbl(i),
234 p_validation_id => PO_VAL_CONSTANTS.c_document_num_valid);
235 x_result_type := po_validations.c_result_type_failure;
236 END;
237 END IF;
238 END LOOP;
239
240 IF po_log.d_proc THEN
241 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
242 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
243 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
244 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
245 END IF;
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF po_log.d_exc THEN
249 po_log.exc(d_mod, 0, NULL);
250 END IF;
251
252 RAISE;
253
254 END document_num;
255
256 -------------------------------------------------------------------------
257 -- type_lookup_code cannot be null and must be equal to
258 -- BLANKET, STANDARD or QUOTATION.
259 -------------------------------------------------------------------------
260 PROCEDURE type_lookup_code(
261 p_id_tbl IN po_tbl_number,
262 p_type_lookup_code_tbl IN po_tbl_varchar30,
263 x_results IN OUT NOCOPY po_validation_results_type,
264 x_result_type OUT NOCOPY VARCHAR2)
265 IS
266 d_mod CONSTANT VARCHAR2(100) := d_type_lookup_code;
267 BEGIN
268 IF (x_results IS NULL) THEN
269 x_results := po_validation_results_type.new_instance();
270 END IF;
271
272 IF po_log.d_proc THEN
273 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
274 po_log.proc_begin(d_mod, 'p_type_lookup_code_tbl', p_type_lookup_code_tbl);
275 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
276 END IF;
277
278 x_result_type := po_validations.c_result_type_success;
279
280 FOR i IN 1 .. p_id_tbl.COUNT LOOP
281 IF p_type_lookup_code_tbl(i) IS NULL THEN
282 x_results.add_result(p_entity_type => c_entity_type_header,
283 p_entity_id => p_id_tbl(i),
284 p_column_name => 'TYPE_LOOKUP_CODE',
285 p_column_val => p_type_lookup_code_tbl(i),
286 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
287 p_token1_name => 'COLUMN_NAME',
288 p_token1_value => 'TYPE_LOOKUP_CODE',
289 p_token2_name => 'VALUE',
290 p_token2_value => p_type_lookup_code_tbl(i),
291 p_validation_id => PO_VAL_CONSTANTS.c_type_lookup_code_not_null);
292 x_result_type := po_validations.c_result_type_failure;
293 ELSIF p_type_lookup_code_tbl(i) NOT IN('BLANKET', 'STANDARD', 'QUOTATION') THEN
294 x_results.add_result(p_entity_type => c_entity_type_header,
295 p_entity_id => p_id_tbl(i),
296 p_column_name => 'TYPE_LOOKUP_CODE',
297 p_column_val => p_type_lookup_code_tbl(i),
298 p_message_name => 'PO_PDOI_INVALID_TYPE_LKUP_CD',
299 p_token1_name => 'VALUE',
300 p_token1_value => p_type_lookup_code_tbl(i),
301 p_validation_id => PO_VAL_CONSTANTS.c_type_lookup_code_valid);
302 x_result_type := po_validations.c_result_type_failure;
303 END IF;
304 END LOOP;
305
306 IF po_log.d_proc THEN
307 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
308 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
309 END IF;
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF po_log.d_exc THEN
313 po_log.exc(d_mod, 0, NULL);
314 END IF;
315
316 RAISE;
317 END type_lookup_code;
318
319 -------------------------------------------------------------------------
320 -- validate currency_code not null and against FND_CURRENCIES.
321 -------------------------------------------------------------------------
322 PROCEDURE currency_code(
323 p_id_tbl IN po_tbl_number,
324 p_currency_code_tbl IN po_tbl_varchar30,
325 x_result_set_id IN OUT NOCOPY NUMBER,
326 x_result_type OUT NOCOPY VARCHAR2)
327 IS
328 d_mod CONSTANT VARCHAR2(100) := d_currency_code;
329 BEGIN
330 IF x_result_set_id IS NULL THEN
331 x_result_set_id := po_validations.next_result_set_id();
332 END IF;
333
334 IF po_log.d_proc THEN
335 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
336 po_log.proc_begin(d_mod, 'p_currency_code_tbl', p_currency_code_tbl);
337 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
338 END IF;
339
340 x_result_type := po_validations.c_result_type_success;
341 -- Bulk validate currency_code not null and against FND_CURRENCIES
342 FORALL i IN 1 .. p_id_tbl.COUNT
343 INSERT INTO po_validation_results_gt
344 (result_set_id,
345 result_type,
346 entity_type,
347 entity_id,
348 message_name,
349 column_name,
350 column_val,
351 token1_name,
352 token1_value,
353 token2_name,
354 token2_value,
355 validation_id)
356 SELECT x_result_set_id,
357 po_validations.c_result_type_failure,
358 c_entity_type_header,
359 p_id_tbl(i),
360 DECODE(p_currency_code_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_CURRENCY'),
361 'CURRENCY_CODE',
362 p_currency_code_tbl(i),
363 'COLUMN_NAME',
364 'CURRENCY_CODE',
365 'VALUE',
366 p_currency_code_tbl(i),
367 DECODE(p_currency_code_tbl(i), NULL, PO_VAL_CONSTANTS.c_currency_code_not_null,
368 PO_VAL_CONSTANTS.c_currency_code_valid)
369 FROM DUAL
370 WHERE p_currency_code_tbl(i) IS NULL
371 OR NOT EXISTS(
372 SELECT 1
373 FROM fnd_currencies cur
374 WHERE p_currency_code_tbl(i) = cur.currency_code
375 AND cur.enabled_flag = 'Y'
376 AND SYSDATE BETWEEN NVL(cur.start_date_active, SYSDATE - 1)
377 AND NVL(cur.end_date_active, SYSDATE + 1));
378
379 IF (SQL%ROWCOUNT > 0) THEN
380 x_result_type := po_validations.c_result_type_failure;
381 END IF;
382
383 IF po_log.d_proc THEN
384 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
385 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
386 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
387 END IF;
388 EXCEPTION
389 WHEN OTHERS THEN
390 IF po_log.d_exc THEN
391 po_log.exc(d_mod, 0, NULL);
392 END IF;
393
394 RAISE;
395 END currency_code;
396
397 -------------------------------------------------------------------------
398 -- If currency_code equals functional currency code, rate_type, rate_date and rate must be null.
399 -- If currency_code does not equal functional currency code, validate rate_type not null,
400 -- validate rate_type against gl_daily_conversion_type_v, validate rate is not null and positive,
401 -- validate rate against g1_currency_api.get_rate().
402 -------------------------------------------------------------------------
403 PROCEDURE rate_info(
404 p_id_tbl IN po_tbl_number,
405 p_currency_code_tbl IN po_tbl_varchar30,
406 p_rate_type_tbl IN po_tbl_varchar30,
407 p_rate_tbl IN po_tbl_number,
408 p_rate_date_tbl IN po_tbl_date,
409 p_func_currency_code IN VARCHAR2,
410 p_set_of_books_id IN NUMBER,
411 x_result_set_id IN OUT NOCOPY NUMBER,
412 x_results IN OUT NOCOPY po_validation_results_type,
413 x_result_type OUT NOCOPY VARCHAR2)
414 IS
415 d_mod CONSTANT VARCHAR2(100) := d_rate_info;
416 x_rate NUMBER;
417 BEGIN
418 IF x_result_set_id IS NULL THEN
419 x_result_set_id := po_validations.next_result_set_id();
420 END IF;
421
422 IF (x_results IS NULL) THEN
423 x_results := po_validation_results_type.new_instance();
424 END IF;
425
426 IF po_log.d_proc THEN
427 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
428 po_log.proc_begin(d_mod, 'p_currency_code_tbl', p_currency_code_tbl);
429 po_log.proc_begin(d_mod, 'p_rate_tbl', p_rate_tbl);
430 po_log.proc_begin(d_mod, 'p_rate_type_tbl', p_rate_type_tbl);
431 po_log.proc_begin(d_mod, 'p_rate_date_tbl', p_rate_date_tbl);
432 po_log.proc_begin(d_mod, 'p_func_currency_code', p_func_currency_code);
433 po_log.proc_begin(d_mod, 'p_set_of_books_id', p_set_of_books_id);
434 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
435 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
436 END IF;
437
438 x_result_type := po_validations.c_result_type_success;
439
440 FOR i IN 1 .. p_id_tbl.COUNT LOOP
441
442 -- check if currency is equal to functional currency
443 IF p_func_currency_code = NVL(p_currency_code_tbl(i), ' ') THEN
444 -- validate rate is null if currency is functional
445 IF p_rate_tbl(i) IS NOT NULL THEN
446 x_results.add_result(p_entity_type => c_entity_type_header,
447 p_entity_id => p_id_tbl(i),
448 p_column_name => 'RATE',
449 p_column_val => p_rate_tbl(i),
450 p_message_name => 'PO_PDOI_RATE_INFO_NULL',
451 p_token1_name => 'COLUMN_NAME',
452 p_token1_value => 'RATE',
453 p_token2_name => 'VALUE',
454 p_token2_value => p_rate_tbl(i),
455 p_validation_id => PO_VAL_CONSTANTS.c_rate_null);
456 x_result_type := po_validations.c_result_type_failure;
457 END IF;
458
459 -- validate rate_type is null if currency is functional
460 IF p_rate_type_tbl(i) IS NOT NULL THEN
461 x_results.add_result(p_entity_type => c_entity_type_header,
462 p_entity_id => p_id_tbl(i),
463 p_column_name => 'RATE_TYPE',
464 p_column_val => p_rate_type_tbl(i),
465 p_message_name => 'PO_PDOI_RATE_INFO_NULL',
466 p_token1_name => 'COLUMN_NAME',
467 p_token1_value => 'RATE_TYPE',
468 p_token2_name => 'VALUE',
469 p_token2_value => p_rate_type_tbl(i),
470 p_validation_id => PO_VAL_CONSTANTS.c_rate_type_null);
471 x_result_type := po_validations.c_result_type_failure;
472 END IF;
473
474 -- validate rate_date is null if currency is functional
475 IF p_rate_date_tbl(i) IS NOT NULL THEN
476 x_results.add_result(p_entity_type => c_entity_type_header,
477 p_entity_id => p_id_tbl(i),
478 p_column_name => 'RATE_DATE',
479 p_column_val => p_rate_date_tbl(i),
480 p_message_name => 'PO_PDOI_RATE_INFO_NULL',
481 p_token1_name => 'COLUMN_NAME',
482 p_token1_value => 'RATE_DATE',
483 p_token2_name => 'VALUE',
484 p_token2_value => p_rate_date_tbl(i),
485 p_validation_id => PO_VAL_CONSTANTS.c_rate_date_null);
486 x_result_type := po_validations.c_result_type_failure;
487 END IF;
488 ELSE
489 -- currency is not functional
490
491 -- validate rate is not null
492 IF p_rate_tbl(i) IS NULL THEN
493 x_results.add_result(p_entity_type => c_entity_type_header,
494 p_entity_id => p_id_tbl(i),
495 p_column_name => 'RATE',
496 p_column_val => p_rate_tbl(i),
497 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
498 p_token1_name => 'COLUMN_NAME',
499 p_token1_value => 'RATE',
500 p_token2_name => 'VALUE',
501 p_token2_value => p_rate_tbl(i),
502 p_validation_id => PO_VAL_CONSTANTS.c_rate_not_null);
503 x_result_type := po_validations.c_result_type_failure;
504 END IF;
505
506 -- validate rate is positive
507 IF NVL(p_rate_tbl(i), 1) < 0 THEN
508 x_results.add_result(p_entity_type => c_entity_type_header,
509 p_entity_id => p_id_tbl(i),
510 p_column_name => 'RATE',
511 p_column_val => p_rate_tbl(i),
512 p_message_name => 'PO_PDOI_LT_ZERO',
513 p_token1_name => 'COLUMN_NAME',
514 p_token1_value => 'RATE',
515 p_token2_name => 'VALUE',
516 p_token2_value => p_rate_tbl(i),
517 p_validation_id => PO_VAL_CONSTANTS.c_rate_ge_zero);
518 x_result_type := po_validations.c_result_type_failure;
519 END IF;
520
521 -- validate rate_type is not null if currency is functional
522 IF p_rate_type_tbl(i) IS NULL THEN
523 x_results.add_result(p_entity_type => c_entity_type_header,
524 p_entity_id => p_id_tbl(i),
525 p_column_name => 'RATE_TYPE',
526 p_column_val => p_rate_type_tbl(i),
527 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
528 p_token1_name => 'COLUMN_NAME',
529 p_token1_value => 'RATE_TYPE',
530 p_token2_name => 'VALUE',
531 p_token2_value => p_rate_type_tbl(i),
532 p_validation_id => PO_VAL_CONSTANTS.c_rate_type_not_null);
533 x_result_type := po_validations.c_result_type_failure;
534 END IF;
535
536 -- validate rate against g1_currency_api.get_rate()
537 IF p_rate_type_tbl(i) IS NOT NULL AND
538 p_rate_tbl(i) IS NOT NULL AND
539 p_rate_type_tbl(i) <> 'User'
540 THEN
541 -- Bug 5547502: replaced get_rate() with get_rate_sql() which has
542 -- proper exception handling.
543 x_rate := gl_currency_api.get_rate_sql(p_set_of_books_id,
544 p_currency_code_tbl(i),
545 p_rate_date_tbl(i),
546 p_rate_type_tbl(i));
547 x_rate := ROUND(x_rate, 15);
548
549 IF (NVL(x_rate, 0) <> NVL(p_rate_tbl(i), 0)) THEN
550 x_results.add_result(p_entity_type => c_entity_type_header,
551 p_entity_id => p_id_tbl(i),
552 p_column_name => 'RATE',
553 p_column_val => p_rate_tbl(i),
554 p_message_name => 'PO_PDOI_INVALID_RATE',
555 p_token1_name => 'VALUE',
556 p_token1_value => p_rate_tbl(i),
557 p_validation_id => PO_VAL_CONSTANTS.c_rate_valid);
558 x_result_type := po_validations.c_result_type_failure;
559 END IF;
560 END IF;
561 END IF;
562 END LOOP;
563
564 -- validate rate_type against GL_DAILY_CONVERSION_TYPES
565 FORALL i IN 1 .. p_id_tbl.COUNT
566 INSERT INTO po_validation_results_gt
567 (result_set_id,
568 result_type,
569 entity_type,
570 entity_id,
571 message_name,
572 column_name,
573 column_val,
574 token1_name,
575 token1_value,
576 validation_id)
577 SELECT x_result_set_id,
578 po_validations.c_result_type_failure,
579 c_entity_type_header,
580 p_id_tbl(i),
581 'PO_PDOI_INVALID_RATE_TYPE',
582 'RATE_TYPE',
583 p_rate_type_tbl(i),
584 'VALUE',
585 p_rate_type_tbl(i),
586 PO_VAL_CONSTANTS.c_rate_type_valid
587 FROM DUAL
588 WHERE p_func_currency_code <> NVL(p_currency_code_tbl(i), ' ')
589 AND p_rate_type_tbl(i) IS NOT NULL
590 AND NOT EXISTS(SELECT 1
591 FROM gl_daily_conversion_types_v dct
592 WHERE p_rate_type_tbl(i) = dct.conversion_type);
593
594 IF (SQL%ROWCOUNT > 0) THEN
595 x_result_type := po_validations.c_result_type_failure;
596 END IF;
597
598 IF po_log.d_proc THEN
599 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
600 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
601 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
602 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
603 END IF;
604 EXCEPTION
605 WHEN OTHERS THEN
606 IF po_log.d_exc THEN
607 po_log.exc(d_mod, 0, NULL);
608 END IF;
609
610 RAISE;
611 END rate_info;
612
613 -------------------------------------------------------------------------
614 -- Agent Id must not be null and validate against PO_AGENTS.
615 -------------------------------------------------------------------------
616 PROCEDURE agent_id(
617 p_id_tbl IN po_tbl_number,
618 p_agent_id_tbl IN po_tbl_number,
619 x_result_set_id IN OUT NOCOPY NUMBER,
620 x_result_type OUT NOCOPY VARCHAR2)
621 IS
622 d_mod CONSTANT VARCHAR2(100) := d_agent_id;
623 BEGIN
624 IF x_result_set_id IS NULL THEN
625 x_result_set_id := po_validations.next_result_set_id();
626 END IF;
627
628 IF po_log.d_proc THEN
629 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
630 po_log.proc_begin(d_mod, 'p_agent_id_tbl', p_agent_id_tbl);
631 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
632 END IF;
633
634 x_result_type := po_validations.c_result_type_success;
635 -- validate agent_id against PO_AGENTS
636 FORALL i IN 1 .. p_id_tbl.COUNT
637 INSERT INTO po_validation_results_gt
638 (result_set_id,
639 result_type,
640 entity_type,
641 entity_id,
642 message_name,
643 column_name,
644 column_val,
645 token1_name,
646 token1_value,
647 token2_name,
648 token2_value,
649 validation_id)
650 SELECT x_result_set_id,
651 po_validations.c_result_type_failure,
652 c_entity_type_header,
653 p_id_tbl(i),
654 DECODE(p_agent_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_BUYER'),
655 'AGENT_ID',
656 p_agent_id_tbl(i),
657 'COLUMN_NAME',
658 'AGENT_ID',
659 'VALUE',
660 p_agent_id_tbl(i),
661 DECODE(p_agent_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_agent_id_not_null,
662 PO_VAL_CONSTANTS.c_agent_id_valid)
663 FROM DUAL
664 WHERE p_agent_id_tbl(i) IS NULL
665 OR NOT EXISTS(
666 SELECT 1
667 FROM po_agents poa
668 WHERE p_agent_id_tbl(i) = poa.agent_id
669 AND SYSDATE BETWEEN NVL(poa.start_date_active, SYSDATE - 1)
670 AND NVL(poa.end_date_active, SYSDATE + 1));
671
672 IF (SQL%ROWCOUNT > 0) THEN
673 x_result_type := po_validations.c_result_type_failure;
674 END IF;
675
676 IF po_log.d_proc THEN
677 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
678 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
679 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
680 END IF;
681 EXCEPTION
682 WHEN OTHERS THEN
683 IF po_log.d_exc THEN
684 po_log.exc(d_mod, 0, NULL);
685 END IF;
686
687 RAISE;
688 END agent_id;
689
690 -------------------------------------------------------------------------
691 -- validate vendorId is Not Null
692 -- validate vendorSiteId is Not Null
693 -- validate vendor_id using po_suppliers_val_v
694 -- validate vendor_site_id using po_supplier_sites_val_v
695 -- validate vendor_contact_id using po_vendor_contacts
696 -- validate vendor site CCR if approval status is APPROVED.
697 -------------------------------------------------------------------------
698 PROCEDURE vendor_info(
699 p_id_tbl IN po_tbl_number,
700 p_vendor_id_tbl IN po_tbl_number,
701 p_vendor_site_id_tbl IN po_tbl_number,
702 p_vendor_contact_id_tbl IN po_tbl_number,
703 p_federal_instance IN VARCHAR,
704 x_result_set_id IN OUT NOCOPY NUMBER,
705 x_results IN OUT NOCOPY po_validation_results_type,
706 x_result_type OUT NOCOPY VARCHAR2)
707 IS
708 d_mod CONSTANT VARCHAR2(100) := d_vendor_info;
709 l_return_status VARCHAR2(1);
710 l_msg_count NUMBER;
711 l_msg_data VARCHAR2(2000);
712 l_ccr_status VARCHAR2(1);
713 l_error_code NUMBER;
714
715 -- Vendor site registration exists and is Active
716 -- Other possible values include: 'D'(Deleted), 'E'(Expired), 'N'(Unknown)
717 -- and 'U'(Unregistered)
718 g_site_reg_active CONSTANT VARCHAR2(1) := 'A';
719 -- Vendor site registration does not exist, which means the vendor
720 -- is exempt from CCR
721 g_site_not_ccr_site CONSTANT NUMBER := 2;
722 BEGIN
723 IF x_result_set_id IS NULL THEN
724 x_result_set_id := po_validations.next_result_set_id();
725 END IF;
726
727 IF (x_results IS NULL) THEN
728 x_results := po_validation_results_type.new_instance();
729 END IF;
730
731 IF po_log.d_proc THEN
732 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
733 po_log.proc_begin(d_mod, 'p_vendor_id_tbl', p_vendor_id_tbl);
734 po_log.proc_begin(d_mod, 'p_vendor_site_id_tbl', p_vendor_site_id_tbl);
735 po_log.proc_begin(d_mod, 'p_vendor_contact_id_tbl', p_vendor_contact_id_tbl);
736 po_log.proc_begin(d_mod, 'p_federal_instance', p_federal_instance);
737 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
738 END IF;
739
740 x_result_type := po_validations.c_result_type_success;
741 FORALL i IN 1 .. p_id_tbl.COUNT
742 INSERT INTO po_validation_results_gt
743 (result_set_id,
744 result_type,
745 entity_type,
746 entity_id,
747 message_name,
748 column_name,
749 column_val,
750 token1_name,
751 token1_value,
752 token2_name,
753 token2_value,
754 validation_id)
755 SELECT x_result_set_id,
756 po_validations.c_result_type_failure,
757 c_entity_type_header,
758 p_id_tbl(i),
759 DECODE(p_vendor_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_VENDOR'),
760 'VENDOR_ID',
761 p_vendor_id_tbl(i),
762 'COLUMN_NAME',
763 'VENDOR_ID',
764 'VALUE',
765 p_vendor_id_tbl(i),
766 DECODE(p_vendor_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_not_null,
767 PO_VAL_CONSTANTS.c_vendor_valid)
768 FROM DUAL
769 WHERE p_vendor_id_tbl(i) IS NULL
770 OR NOT EXISTS(SELECT 1
771 FROM po_suppliers_val_v psv
772 WHERE p_vendor_id_tbl(i) = psv.vendor_id);
773
774 IF (SQL%ROWCOUNT > 0) THEN
775 x_result_type := po_validations.c_result_type_failure;
776 END IF;
777
778 FORALL i IN 1 .. p_id_tbl.COUNT
779 INSERT INTO po_validation_results_gt
780 (result_set_id,
781 result_type,
782 entity_type,
783 entity_id,
784 message_name,
785 column_name,
786 column_val,
787 token1_name,
788 token1_value,
789 token2_name,
790 token2_value,
791 validation_id)
792 SELECT x_result_set_id,
793 po_validations.c_result_type_failure,
794 c_entity_type_header,
795 p_id_tbl(i),
796 DECODE(p_vendor_site_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_VENDOR_SITE'),
797 'VENDOR_SITE_ID',
798 p_vendor_site_id_tbl(i),
799 'COLUMN_NAME',
800 'VENDOR_SITE_ID',
801 'VALUE',
802 p_vendor_site_id_tbl(i),
803 DECODE(p_vendor_site_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_site_not_null,
804 PO_VAL_CONSTANTS.c_vendor_site_valid)
805 FROM DUAL
806 WHERE p_vendor_id_tbl(i) IS NOT NULL
807 AND ( p_vendor_site_id_tbl(i) IS NULL
808 OR NOT EXISTS(SELECT 1
809 FROM po_supplier_sites_val_v pssv
810 WHERE p_vendor_id_tbl(i) = pssv.vendor_id
811 AND p_vendor_site_id_tbl(i) = pssv.vendor_site_id));
812
813 IF (SQL%ROWCOUNT > 0) THEN
814 x_result_type := po_validations.c_result_type_failure;
815 END IF;
816
817 FORALL i IN 1 .. p_id_tbl.COUNT
818 INSERT INTO po_validation_results_gt
819 (result_set_id,
820 result_type,
821 entity_type,
822 entity_id,
823 message_name,
824 column_name,
825 column_val,
826 token1_name,
827 token1_value,
828 validation_id)
829 SELECT x_result_set_id,
830 po_validations.c_result_type_failure,
831 c_entity_type_header,
832 p_id_tbl(i),
833 'PO_PDOI_INVALID_VDR_CNTCT',
834 'VENDOR_CONTACT_ID',
835 p_vendor_contact_id_tbl(i),
836 'VALUE',
837 p_vendor_contact_id_tbl(i),
838 PO_VAL_CONSTANTS.c_vendor_contact_valid
839 FROM DUAL
840 WHERE p_vendor_id_tbl(i) IS NOT NULL
841 AND p_vendor_site_id_tbl(i) IS NOT NULL
842 AND p_vendor_contact_id_tbl(i) IS NOT NULL
843 AND NOT EXISTS(SELECT 1
844 FROM po_vendor_contacts pvc
845 WHERE p_vendor_site_id_tbl(i) = pvc.vendor_site_id
846 AND p_vendor_contact_id_tbl(i) = pvc.vendor_contact_id);
847
848 IF (SQL%ROWCOUNT > 0) THEN
849 x_result_type := po_validations.c_result_type_failure;
850 END IF;
851
852 IF (p_federal_instance = 'Y') THEN
853 FOR i IN 1 .. p_id_tbl.COUNT LOOP
854 BEGIN
855 fv_ccr_grp.fv_ccr_reg_status(p_api_version => 1.0,
856 p_init_msg_list => 'F',
857 p_vendor_site_id => p_vendor_site_id_tbl(i),
858 x_return_status => l_return_status,
859 x_msg_count => l_msg_count,
860 x_msg_data => l_msg_data,
861 x_ccr_status => l_ccr_status,
862 x_error_code => l_error_code);
863
864 -- (1) return status is error and vendor site is not exempt from CCR;
865 IF (l_return_status = fnd_api.g_ret_sts_error AND l_error_code <> g_site_not_ccr_site)
866 OR
867 -- (2) return status is success but registration status is not ACTIVE;
868 (l_return_status = fnd_api.g_ret_sts_success AND l_ccr_status <> g_site_reg_active)
869 OR
870 -- (3) return status is unexpected error
871 (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
872 x_results.add_result(p_entity_type => c_entity_type_header,
873 p_entity_id => p_id_tbl(i),
874 p_column_name => 'VENDOR_SITE_ID',
875 p_column_val => p_vendor_site_id_tbl(i),
876 p_message_name => 'PO_PDOI_VENDOR_SITE_CCR_INV',
877 p_token1_name => 'VENDOR_SITE_ID',
878 p_token1_value => p_vendor_site_id_tbl(i),
879 p_token2_name => 'VENDOR_ID',
880 p_token2_value => p_vendor_id_tbl(i),
881 p_validation_id => PO_VAL_CONSTANTS.c_vendor_site_ccr_valid);
882 x_result_type := po_validations.c_result_type_failure;
883 END IF;
884 END;
885 END LOOP;
886 END IF;
887
888 IF po_log.d_proc THEN
889 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
890 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
891 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
892 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
893 END IF;
894 EXCEPTION
895 WHEN OTHERS THEN
896 IF po_log.d_exc THEN
897 po_log.exc(d_mod, 0, NULL);
898 END IF;
899
900 RAISE;
901 END vendor_info;
902
903 -------------------------------------------------------------------------
904 -- ShipToLocationId must not be null and valid in HR_LOCATIONS.
905 -------------------------------------------------------------------------
906 PROCEDURE ship_to_location_id(
907 p_id_tbl IN po_tbl_number,
908 p_ship_to_location_id_tbl IN po_tbl_number,
909 -- Bug 7007502: Added new param p_type_lookup_code_tbl
910 p_type_lookup_code_tbl IN po_tbl_varchar30,
911 x_result_set_id IN OUT NOCOPY NUMBER,
912 x_result_type OUT NOCOPY VARCHAR2)
913 IS
914 d_mod CONSTANT VARCHAR2(100) := d_ship_to_location_id;
915 BEGIN
916 IF x_result_set_id IS NULL THEN
917 x_result_set_id := po_validations.next_result_set_id();
918 END IF;
919
920 IF po_log.d_proc THEN
921 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
922 po_log.proc_begin(d_mod, 'p_ship_to_location_id_tbl', p_ship_to_location_id_tbl);
923 po_log.proc_begin(d_mod, 'p_type_lookup_code_tbl', p_type_lookup_code_tbl);
924 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
925 END IF;
926
927 x_result_type := po_validations.c_result_type_success;
928 -- validate ship_to_location_id is not null (for PO and BPA) and valid in HR_LOCATIONS
929 FORALL i IN 1 .. p_id_tbl.COUNT
930 INSERT INTO po_validation_results_gt
931 (result_set_id,
932 result_type,
933 entity_type,
934 entity_id,
935 message_name,
936 column_name,
937 column_val,
938 token1_name,
939 token1_value,
940 token2_name,
941 token2_value,
942 validation_id)
943 SELECT x_result_set_id,
944 po_validations.c_result_type_failure,
945 c_entity_type_header,
946 p_id_tbl(i),
947 DECODE(p_ship_to_location_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_SHIP_LOC_ID'),
948 'SHIP_TO_LOCATION_ID',
949 p_ship_to_location_id_tbl(i),
950 'COLUMN_NAME',
951 'SHIP_TO_LOCATION_ID',
952 'VALUE',
953 p_ship_to_location_id_tbl(i),
954 DECODE(p_ship_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_ship_to_location_id_not_null,
955 PO_VAL_CONSTANTS.c_ship_to_location_id_valid)
956 FROM DUAL
957 WHERE (p_ship_to_location_id_tbl(i) IS NULL
958 -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
959 AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
960 OR (p_ship_to_location_id_tbl(i) IS NOT NULL
961 AND NOT EXISTS(
962 SELECT 1
963 FROM hr_locations hrl
964 WHERE hrl.ship_to_site_flag = 'Y'
965 AND p_ship_to_location_id_tbl(i) = hrl.location_id
966 AND SYSDATE < NVL(hrl.inactive_date, SYSDATE + 1)));
967
968 IF (SQL%ROWCOUNT > 0) THEN
969 x_result_type := po_validations.c_result_type_failure;
970 END IF;
971
972 IF po_log.d_proc THEN
973 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
974 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
975 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
976 END IF;
977 EXCEPTION
978 WHEN OTHERS THEN
979 IF po_log.d_exc THEN
980 po_log.exc(d_mod, 0, NULL);
981 END IF;
982
983 RAISE;
984 END ship_to_location_id;
985
986 -------------------------------------------------------------------------
987 -- BillToLocationId must not be null and validate against HR_LOCATIONS.
988 -------------------------------------------------------------------------
989 PROCEDURE bill_to_location_id(
990 p_id_tbl IN po_tbl_number,
991 p_bill_to_location_id_tbl IN po_tbl_number,
992 -- Bug 7007502: Added new param p_type_lookup_code_tbl
993 p_type_lookup_code_tbl IN po_tbl_varchar30,
994 x_result_set_id IN OUT NOCOPY NUMBER,
995 x_result_type OUT NOCOPY VARCHAR2)
996 IS
997 d_mod CONSTANT VARCHAR2(100) := d_bill_to_location_id;
998 BEGIN
999 IF x_result_set_id IS NULL THEN
1000 x_result_set_id := po_validations.next_result_set_id();
1001 END IF;
1002
1003 IF po_log.d_proc THEN
1004 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1005 po_log.proc_begin(d_mod, 'p_bill_to_location_id_tbl', p_bill_to_location_id_tbl);
1006 po_log.proc_begin(d_mod, 'p_type_lookup_code_tbl', p_type_lookup_code_tbl);
1007 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1008 END IF;
1009
1010 x_result_type := po_validations.c_result_type_success;
1011 -- validate bill_to_location_id is not null (for PO and BPA) and valid in HR_LOCATIONS
1012 FORALL i IN 1 .. p_id_tbl.COUNT
1013 INSERT INTO po_validation_results_gt
1014 (result_set_id,
1015 result_type,
1016 entity_type,
1017 entity_id,
1018 message_name,
1019 column_name,
1020 column_val,
1021 token1_name,
1022 token1_value,
1023 token2_name,
1024 token2_value,
1025 validation_id)
1026 SELECT x_result_set_id,
1027 po_validations.c_result_type_failure,
1028 c_entity_type_header,
1029 p_id_tbl(i),
1030 DECODE(p_bill_to_location_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_BILL_LOC_ID'),
1031 'BILL_TO_LOCATION_ID',
1032 p_bill_to_location_id_tbl(i),
1033 'COLUMN_NAME',
1034 'BILL_TO_LOCATION_ID',
1035 'VALUE',
1036 p_bill_to_location_id_tbl(i),
1037 DECODE(p_bill_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_bill_to_location_id_not_null,
1038 PO_VAL_CONSTANTS.c_bill_to_location_id_valid)
1039 FROM DUAL
1040 WHERE (p_bill_to_location_id_tbl(i) IS NULL
1041 -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
1042 AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
1043 OR (p_bill_to_location_id_tbl(i) IS NOT NULL
1044 AND NOT EXISTS(
1045 SELECT 1
1046 FROM hr_locations hrl
1047 WHERE hrl.bill_to_site_flag = 'Y'
1048 AND p_bill_to_location_id_tbl(i) = hrl.location_id
1049 AND SYSDATE < NVL(hrl.inactive_date, SYSDATE + 1)));
1050
1051 IF (SQL%ROWCOUNT > 0) THEN
1052 x_result_type := po_validations.c_result_type_failure;
1053 END IF;
1054
1055 IF po_log.d_proc THEN
1056 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1057 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1058 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1059 END IF;
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 IF po_log.d_exc THEN
1063 po_log.exc(d_mod, 0, NULL);
1064 END IF;
1065
1066 RAISE;
1067 END bill_to_location_id;
1068
1069 -------------------------------------------------------------------------
1070 -- validate ship_via_lookup_code against ORG_FREIGHT
1071 -------------------------------------------------------------------------
1072 PROCEDURE ship_via_lookup_code(
1073 p_id_tbl IN po_tbl_number,
1074 p_ship_via_lookup_code_tbl IN po_tbl_varchar30,
1075 p_inventory_org_id IN NUMBER,
1076 x_result_set_id IN OUT NOCOPY NUMBER,
1077 x_result_type OUT NOCOPY VARCHAR2)
1078 IS
1079 d_mod CONSTANT VARCHAR2(100) := d_ship_via_lookup_code;
1080 BEGIN
1081 IF x_result_set_id IS NULL THEN
1082 x_result_set_id := po_validations.next_result_set_id();
1083 END IF;
1084
1085 IF po_log.d_proc THEN
1086 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1087 po_log.proc_begin(d_mod, 'p_ship_via_lookup_code_tbl', p_ship_via_lookup_code_tbl);
1088 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1089 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1090 END IF;
1091
1092 x_result_type := po_validations.c_result_type_success;
1093 FORALL i IN 1 .. p_id_tbl.COUNT
1094 INSERT INTO po_validation_results_gt
1095 (result_set_id,
1096 result_type,
1097 entity_type,
1098 entity_id,
1099 message_name,
1100 column_name,
1101 column_val,
1102 token1_name,
1103 token1_value,
1104 validation_id)
1105 SELECT x_result_set_id,
1106 po_validations.c_result_type_failure,
1107 c_entity_type_header,
1108 p_id_tbl(i),
1109 'PO_PDOI_INVALID_FREIGHT_CARR',
1110 'SHIP_VIA_LOOKUP_CODE',
1111 p_ship_via_lookup_code_tbl(i),
1112 'VALUE',
1113 p_ship_via_lookup_code_tbl(i),
1114 PO_VAL_CONSTANTS.c_ship_via_lookup_code
1115 FROM DUAL
1116 WHERE p_ship_via_lookup_code_tbl(i) IS NOT NULL
1117 AND NOT EXISTS(
1118 SELECT 1
1119 FROM org_freight ofr
1120 WHERE p_ship_via_lookup_code_tbl(i) = ofr.freight_code
1121 AND NVL(ofr.disable_date, SYSDATE + 1) > SYSDATE
1122 AND ofr.organization_id = p_inventory_org_id);
1123
1124 IF (SQL%ROWCOUNT > 0) THEN
1125 x_result_type := po_validations.c_result_type_failure;
1126 END IF;
1127
1128 IF po_log.d_proc THEN
1129 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1130 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1131 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1132 END IF;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 IF po_log.d_exc THEN
1136 po_log.exc(d_mod, 0, NULL);
1137 END IF;
1138
1139 RAISE;
1140 END ship_via_lookup_code;
1141
1142 -------------------------------------------------------------------------
1143 -- validate fob_lookup_code against PO_LOOKUP_CODES
1144 -------------------------------------------------------------------------
1145 PROCEDURE fob_lookup_code(
1146 p_id_tbl IN po_tbl_number,
1147 p_fob_lookup_code_tbl IN po_tbl_varchar30,
1148 x_result_set_id IN OUT NOCOPY NUMBER,
1149 x_result_type OUT NOCOPY VARCHAR2)
1150 IS
1151 d_mod CONSTANT VARCHAR2(100) := d_fob_lookup_code;
1152 BEGIN
1153 IF (x_result_set_id IS NULL) THEN
1154 x_result_set_id := po_validations.next_result_set_id();
1155 END IF;
1156
1157 IF po_log.d_proc THEN
1158 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1159 po_log.proc_begin(d_mod, 'p_fob_lookup_code_tbl', p_fob_lookup_code_tbl);
1160 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1161 END IF;
1162
1163 x_result_type := po_validations.c_result_type_success;
1164 FORALL i IN 1 .. p_id_tbl.COUNT
1165 INSERT INTO po_validation_results_gt
1166 (result_set_id,
1167 result_type,
1168 entity_type,
1169 entity_id,
1170 message_name,
1171 column_name,
1172 column_val,
1173 token1_name,
1174 token1_value,
1175 validation_id)
1176 SELECT x_result_set_id,
1177 po_validations.c_result_type_failure,
1178 c_entity_type_header,
1179 p_id_tbl(i),
1180 'PO_PDOI_INVALID_FOB',
1181 'FOB_LOOKUP_CODE',
1182 p_fob_lookup_code_tbl(i),
1183 'VALUE',
1184 p_fob_lookup_code_tbl(i),
1185 PO_VAL_CONSTANTS.c_fob_lookup_code
1186 FROM DUAL
1187 WHERE p_fob_lookup_code_tbl(i) IS NOT NULL
1188 AND NOT EXISTS(
1189 SELECT 1
1190 FROM po_lookup_codes plc
1191 WHERE p_fob_lookup_code_tbl(i) = plc.lookup_code
1192 AND plc.lookup_type = 'FOB'
1193 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1));
1194
1195 IF (SQL%ROWCOUNT > 0) THEN
1196 x_result_type := po_validations.c_result_type_failure;
1197 END IF;
1198
1199 IF po_log.d_proc THEN
1200 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1201 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1202 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1203 END IF;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 IF po_log.d_exc THEN
1207 po_log.exc(d_mod, 0, NULL);
1208 END IF;
1209
1210 RAISE;
1211 END fob_lookup_code;
1212
1213 -------------------------------------------------------------------------
1214 -- validate freight_terms_lookup_code against PO_LOOKUP_CODES
1215 -------------------------------------------------------------------------
1216 PROCEDURE freight_terms_lookup_code(
1217 p_id_tbl IN po_tbl_number,
1218 p_freight_terms_lookup_tbl IN po_tbl_varchar30,
1219 x_result_set_id IN OUT NOCOPY NUMBER,
1220 x_result_type OUT NOCOPY VARCHAR2)
1221 IS
1222 d_mod CONSTANT VARCHAR2(100) := d_freight_terms_lookup_code;
1223 BEGIN
1224 IF (x_result_set_id IS NULL) THEN
1225 x_result_set_id := po_validations.next_result_set_id();
1226 END IF;
1227
1228 IF po_log.d_proc THEN
1229 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1230 po_log.proc_begin(d_mod, 'p_freight_terms_lookup_tbl', p_freight_terms_lookup_tbl);
1231 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1232 END IF;
1233
1234 x_result_type := po_validations.c_result_type_success;
1235 FORALL i IN 1 .. p_id_tbl.COUNT
1236 INSERT INTO po_validation_results_gt
1237 (result_set_id,
1238 result_type,
1239 entity_type,
1240 entity_id,
1241 message_name,
1242 column_name,
1243 column_val,
1244 token1_name,
1245 token1_value,
1246 validation_id)
1247 SELECT x_result_set_id,
1248 po_validations.c_result_type_failure,
1249 c_entity_type_header,
1250 p_id_tbl(i),
1251 'PO_PDOI_INVALID_FREIGHT_TERMS',
1252 'FREIGHT_TERMS_LOOKUP_CODE',
1253 p_freight_terms_lookup_tbl(i),
1254 'VALUE',
1255 p_freight_terms_lookup_tbl(i),
1256 PO_VAL_CONSTANTS.c_freight_terms_lookup_code
1257 FROM DUAL
1258 WHERE p_freight_terms_lookup_tbl(i) IS NOT NULL
1259 AND NOT EXISTS(
1260 SELECT 1
1261 FROM po_lookup_codes plc
1262 WHERE p_freight_terms_lookup_tbl(i) = plc.lookup_code
1263 AND plc.lookup_type = 'FREIGHT TERMS'
1264 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1));
1265
1266 IF (SQL%ROWCOUNT > 0) THEN
1267 x_result_type := po_validations.c_result_type_failure;
1268 END IF;
1269
1270 IF po_log.d_proc THEN
1271 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1272 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1273 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1274 END IF;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 IF po_log.d_exc THEN
1278 po_log.exc(d_mod, 0, NULL);
1279 END IF;
1280
1281 RAISE;
1282 END freight_terms_lookup_code;
1283
1284 -------------------------------------------------------------------------
1285 -- validate shipping_control against PO_LOOKUP_CODES
1286 -------------------------------------------------------------------------
1287 PROCEDURE shipping_control(
1288 p_id_tbl IN po_tbl_number,
1289 p_shipping_control_tbl IN po_tbl_varchar30,
1290 x_result_set_id IN OUT NOCOPY NUMBER,
1291 x_result_type OUT NOCOPY VARCHAR2)
1292 IS
1293 d_mod CONSTANT VARCHAR2(100) := d_shipping_control;
1294 BEGIN
1295 IF (x_result_set_id IS NULL) THEN
1296 x_result_set_id := po_validations.next_result_set_id();
1297 END IF;
1298
1299 IF po_log.d_proc THEN
1300 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1301 po_log.proc_begin(d_mod, 'p_shipping_control_tbl', p_shipping_control_tbl);
1302 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1303 END IF;
1304
1305 x_result_type := po_validations.c_result_type_success;
1306 FORALL i IN 1 .. p_id_tbl.COUNT
1307 INSERT INTO po_validation_results_gt
1308 (result_set_id,
1309 result_type,
1310 entity_type,
1311 entity_id,
1312 message_name,
1313 column_name,
1314 column_val,
1315 token1_name,
1316 token1_value,
1317 validation_id)
1318 SELECT x_result_set_id,
1319 po_validations.c_result_type_failure,
1320 c_entity_type_header,
1321 p_id_tbl(i),
1322 'PO_PDOI_INVALID_SHIPPING_CTRL',
1323 'SHIPPING_CONTROL',
1324 p_shipping_control_tbl(i),
1325 'VALUE',
1326 p_shipping_control_tbl(i),
1327 PO_VAL_CONSTANTS.c_shipping_control
1328 FROM DUAL
1329 WHERE p_shipping_control_tbl(i) IS NOT NULL
1330 AND NOT EXISTS(
1331 SELECT 1
1332 FROM po_lookup_codes plc
1333 WHERE p_shipping_control_tbl(i) = plc.lookup_code
1334 AND plc.lookup_type = 'SHIPPING CONTROL'
1335 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1));
1336
1337 IF (SQL%ROWCOUNT > 0) THEN
1338 x_result_type := po_validations.c_result_type_failure;
1339 END IF;
1340
1341 IF po_log.d_proc THEN
1342 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1343 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1344 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1345 END IF;
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 IF po_log.d_exc THEN
1349 po_log.exc(d_mod, 0, NULL);
1350 END IF;
1351
1352 RAISE;
1353 END shipping_control;
1354
1355 -------------------------------------------------------------------------
1356 -- validate acceptance_due_date is not null if acceptance_required_flag = Y.
1357 -- Only called for Blanket and SPO.
1358 -------------------------------------------------------------------------
1359 PROCEDURE acceptance_due_date(
1360 p_id_tbl IN po_tbl_number,
1361 p_acceptance_reqd_flag_tbl IN po_tbl_varchar1,
1362 p_acceptance_due_date_tbl IN po_tbl_date,
1363 x_results IN OUT NOCOPY po_validation_results_type,
1364 x_result_type OUT NOCOPY VARCHAR2)
1365 IS
1366 d_mod CONSTANT VARCHAR2(100) := d_acceptance_due_date;
1367 BEGIN
1368 IF (x_results IS NULL) THEN
1369 x_results := po_validation_results_type.new_instance();
1370 END IF;
1371
1372 IF po_log.d_proc THEN
1373 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1374 po_log.proc_begin(d_mod, 'p_acceptance_due_date_tbl', p_acceptance_due_date_tbl);
1375 po_log.proc_begin(d_mod, 'p_acceptance_reqd_flag_tbl', p_acceptance_reqd_flag_tbl);
1376 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1377 END IF;
1378
1379 x_result_type := po_validations.c_result_type_success;
1380
1381 -- Bug6601134(obsoleted 4467491)
1382 /*FOR i IN 1 .. p_id_tbl.COUNT LOOP
1383 IF p_acceptance_reqd_flag_tbl(i) = 'Y' AND p_acceptance_due_date_tbl(i) IS NULL THEN
1384 x_results.add_result(p_entity_type => c_entity_type_header,
1385 p_entity_id => p_id_tbl(i),
1386 p_column_name => 'ACCEPTANCE_DUE_DATE',
1387 p_column_val => p_acceptance_due_date_tbl(i),
1388 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1389 p_token1_name => 'COLUMN_NAME',
1390 p_token1_value => 'ACCEPTANCE_DUE_DATE',
1391 p_token2_name => 'VALUE',
1392 p_token2_value => p_acceptance_due_date_tbl(i),
1393 p_validation_id => PO_VAL_CONSTANTS.c_acceptance_due_date);
1394 x_result_type := po_validations.c_result_type_failure;
1395 END IF;
1396 END LOOP;*/
1397
1398 IF po_log.d_proc THEN
1399 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1400 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1401 END IF;
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 IF po_log.d_exc THEN
1405 po_log.exc(d_mod, 0, NULL);
1406 END IF;
1407
1408 RAISE;
1409 END acceptance_due_date;
1410
1411 -------------------------------------------------------------------------
1412 -- validate cancel_flag = N. Only called for Blanket and SPO.
1413 -------------------------------------------------------------------------
1414 PROCEDURE cancel_flag(
1415 p_id_tbl IN po_tbl_number,
1416 p_cancel_flag_tbl IN po_tbl_varchar1,
1417 x_results IN OUT NOCOPY po_validation_results_type,
1418 x_result_type OUT NOCOPY VARCHAR2)
1419 IS
1420 d_mod CONSTANT VARCHAR2(100) := d_cancel_flag;
1421 BEGIN
1422 IF (x_results IS NULL) THEN
1423 x_results := po_validation_results_type.new_instance();
1424 END IF;
1425
1426 IF po_log.d_proc THEN
1427 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1428 po_log.proc_begin(d_mod, 'p_cancel_flag_tbl', p_cancel_flag_tbl);
1429 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1430 END IF;
1431
1432 x_result_type := po_validations.c_result_type_success;
1433
1434 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1435 IF p_cancel_flag_tbl(i) <> 'N' THEN
1436 x_results.add_result(p_entity_type => c_entity_type_header,
1437 p_entity_id => p_id_tbl(i),
1438 p_column_name => 'CANCEL_FLAG',
1439 p_column_val => p_cancel_flag_tbl(i),
1440 p_message_name => 'PO_PDOI_INVALID_VALUE',
1441 p_token1_name => 'COLUMN_NAME',
1442 p_token1_value => 'CANCEL_FLAG',
1443 p_token2_name => 'VALUE',
1444 p_token2_value => p_cancel_flag_tbl(i),
1445 p_validation_id => PO_VAL_CONSTANTS.c_cancel_flag);
1446
1447 x_result_type := po_validations.c_result_type_failure;
1448 END IF;
1449 END LOOP;
1450
1451 IF po_log.d_proc THEN
1452 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1453 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1454 END IF;
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 IF po_log.d_exc THEN
1458 po_log.exc(d_mod, 0, NULL);
1459 END IF;
1460
1461 RAISE;
1462 END cancel_flag;
1463
1464 -------------------------------------------------------------------------
1465 -- validate closed_code = OPEN. Only called for Blanket and SPO.
1466 -------------------------------------------------------------------------
1467 PROCEDURE closed_code(
1468 p_id_tbl IN po_tbl_number,
1469 p_closed_code_tbl IN po_tbl_varchar30,
1470 p_acceptance_reqd_flag_tbl IN po_tbl_varchar1,
1471 x_results IN OUT NOCOPY po_validation_results_type,
1472 x_result_type OUT NOCOPY VARCHAR2)
1473 IS
1474 d_mod CONSTANT VARCHAR2(100) := d_closed_code;
1475 BEGIN
1476 IF (x_results IS NULL) THEN
1477 x_results := po_validation_results_type.new_instance();
1478 END IF;
1479
1480 IF po_log.d_proc THEN
1481 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1482 po_log.proc_begin(d_mod, 'p_closed_code_tbl', p_closed_code_tbl);
1483 po_log.proc_begin(d_mod, 'p_acceptance_reqd_flag_tbl', p_acceptance_reqd_flag_tbl);
1484 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1485 END IF;
1486
1487 x_result_type := po_validations.c_result_type_success;
1488
1489 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1490 IF p_acceptance_reqd_flag_tbl(i) ='Y' AND p_closed_code_tbl(i) <> 'OPEN' THEN
1491 x_results.add_result(p_entity_type => c_entity_type_header,
1492 p_entity_id => p_id_tbl(i),
1493 p_column_name => 'CLOSED_CODE',
1494 p_column_val => p_closed_code_tbl(i),
1495 p_message_name => 'PO_PDOI_INVALID_VALUE',
1496 p_token1_name => 'COLUMN_NAME',
1497 p_token1_value => 'CLOSED_CODE',
1498 p_token2_name => 'VALUE',
1499 p_token2_value => p_closed_code_tbl(i),
1500 p_validation_id => PO_VAL_CONSTANTS.c_closed_code);
1501
1502 x_result_type := po_validations.c_result_type_failure;
1503 END IF;
1504 END LOOP;
1505
1506 IF po_log.d_proc THEN
1507 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1508 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1509 END IF;
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 IF po_log.d_exc THEN
1513 po_log.exc(d_mod, 0, NULL);
1514 END IF;
1515
1516 RAISE;
1517 END closed_code;
1518
1519 -------------------------------------------------------------------------
1520 -- validate print_count = 0. Only called for Blanket and SPO.
1521 -------------------------------------------------------------------------
1522 PROCEDURE print_count(
1523 p_id_tbl IN po_tbl_number,
1524 p_print_count_tbl IN po_tbl_number,
1525 p_approval_status_tbl IN po_tbl_varchar30,
1526 x_results IN OUT NOCOPY po_validation_results_type,
1527 x_result_type OUT NOCOPY VARCHAR2)
1528 IS
1529 d_mod CONSTANT VARCHAR2(100) := d_print_count;
1530 BEGIN
1531 IF (x_results IS NULL) THEN
1532 x_results := po_validation_results_type.new_instance();
1533 END IF;
1534
1535 IF po_log.d_proc THEN
1536 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1537 po_log.proc_begin(d_mod, 'p_print_count_tbl', p_print_count_tbl);
1538 po_log.proc_begin(d_mod, 'p_approval_status_tbl', p_approval_status_tbl);
1539 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1540 END IF;
1541
1542 x_result_type := po_validations.c_result_type_success;
1543
1544 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1545 IF p_approval_status_tbl(i) <> 'APPROVED' AND p_print_count_tbl(i) <> 0 THEN
1546 x_results.add_result(p_entity_type => c_entity_type_header,
1547 p_entity_id => p_id_tbl(i),
1548 p_column_name => 'PRINT_COUNT',
1549 p_column_val => p_print_count_tbl(i),
1550 p_message_name => 'PO_PDOI_INVALID_VALUE',
1551 p_token1_name => 'COLUMN_NAME',
1552 p_token1_value => 'PRINT COUNT',
1553 p_token2_name => 'VALUE',
1554 p_token2_value => 0,
1555 p_validation_id => PO_VAL_CONSTANTS.c_print_count);
1556 x_result_type := po_validations.c_result_type_failure;
1557 END IF;
1558 END LOOP;
1559
1560 IF po_log.d_proc THEN
1561 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1562 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1563 END IF;
1564 EXCEPTION
1565 WHEN OTHERS THEN
1566 IF po_log.d_exc THEN
1567 po_log.exc(d_mod, 0, NULL);
1568 END IF;
1569
1570 RAISE;
1571 END print_count;
1572
1573 -------------------------------------------------------------------------
1574 -- validate approval_status = INCOMPLETE, APPROVED, INITIATE APPROVAL.
1575 -- Only called for Blanket and SPO.
1576 -------------------------------------------------------------------------
1577 PROCEDURE approval_status(
1578 p_id_tbl IN po_tbl_number,
1579 p_approval_status_tbl IN po_tbl_varchar30,
1580 x_results IN OUT NOCOPY po_validation_results_type,
1581 x_result_type OUT NOCOPY VARCHAR2)
1582 IS
1583 d_mod CONSTANT VARCHAR2(100) := d_approval_status;
1584 BEGIN
1585 IF (x_results IS NULL) THEN
1586 x_results := po_validation_results_type.new_instance();
1587 END IF;
1588
1589 IF po_log.d_proc THEN
1590 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1591 po_log.proc_begin(d_mod, 'p_approval_status_tbl', p_approval_status_tbl);
1592 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1593 END IF;
1594
1595 x_result_type := po_validations.c_result_type_success;
1596
1597 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1598 IF p_approval_status_tbl(i) NOT IN('APPROVED', 'INCOMPLETE', 'INITIATE APPROVAL') THEN
1599 x_results.add_result(p_entity_type => c_entity_type_header,
1600 p_entity_id => p_id_tbl(i),
1601 p_column_name => 'APPROVAL_STATUS',
1602 p_column_val => p_approval_status_tbl(i),
1603 p_message_name => 'PO_PDOI_INVALID_STATUS',
1604 p_validation_id => PO_VAL_CONSTANTS.c_approval_status);
1605 x_result_type := po_validations.c_result_type_failure;
1606 END IF;
1607 END LOOP;
1608
1609 IF po_log.d_proc THEN
1610 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1611 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1612 END IF;
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 IF po_log.d_exc THEN
1616 po_log.exc(d_mod, 0, NULL);
1617 END IF;
1618
1619 RAISE;
1620 END approval_status;
1621
1622 -------------------------------------------------------------------------
1623 -- validate amount_to_encumber > 0
1624 -------------------------------------------------------------------------
1625 PROCEDURE amount_to_encumber(
1626 p_id_tbl IN po_tbl_number,
1627 p_amount_to_encumber_tbl IN po_tbl_number,
1628 x_results IN OUT NOCOPY po_validation_results_type,
1629 x_result_type OUT NOCOPY VARCHAR2)
1630 IS
1631 d_mod CONSTANT VARCHAR2(100) := d_amount_to_encumber;
1632 BEGIN
1633 IF (x_results IS NULL) THEN
1634 x_results := po_validation_results_type.new_instance();
1635 END IF;
1636
1637 IF po_log.d_proc THEN
1638 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1639 po_log.proc_begin(d_mod, 'p_amount_to_encumber_tbl', p_amount_to_encumber_tbl);
1640 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1641 END IF;
1642
1643 x_result_type := po_validations.c_result_type_success;
1644
1645 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1646 IF p_amount_to_encumber_tbl(i) <= 0 THEN
1647 x_results.add_result(p_entity_type => c_entity_type_header,
1648 p_entity_id => p_id_tbl(i),
1649 p_column_name => 'AMOUNT_TO_ENCUMBER',
1650 p_column_val => p_amount_to_encumber_tbl(i),
1651 p_message_name => 'PO_PDOI_LT_ZERO',
1652 p_token1_name => 'COLUMN_NAME',
1653 p_token1_value => 'AMOUNT_TO_ENCUMBER',
1654 p_token2_name => 'VALUE',
1655 p_token2_value => p_amount_to_encumber_tbl(i),
1656 p_validation_id => PO_VAL_CONSTANTS.c_amount_to_encumber);
1657 x_result_type := po_validations.c_result_type_failure;
1658 END IF;
1659 END LOOP;
1660
1661 IF po_log.d_proc THEN
1662 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1663 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1664 END IF;
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 IF po_log.d_exc THEN
1668 po_log.exc(d_mod, 0, NULL);
1669 END IF;
1670
1671 RAISE;
1672 END amount_to_encumber;
1673
1674 -------------------------------------------------------------------------------------
1675 -- Validate style_id exists in system, is active and is not enabled for complex work.
1676 -------------------------------------------------------------------------------------
1677 PROCEDURE style_id(
1678 p_id_tbl IN po_tbl_number,
1679 p_style_id_tbl IN po_tbl_number,
1680 x_result_set_id IN OUT NOCOPY NUMBER,
1681 x_result_type OUT NOCOPY VARCHAR2)
1682 IS
1683 d_mod CONSTANT VARCHAR2(100) := d_style_id;
1684 BEGIN
1685 IF x_result_set_id IS NULL THEN
1686 x_result_set_id := po_validations.next_result_set_id();
1687 END IF;
1688
1689 IF po_log.d_proc THEN
1690 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1691 po_log.proc_begin(d_mod, 'p_style_id_tbl', p_style_id_tbl);
1692 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1693 END IF;
1694
1695 x_result_type := po_validations.c_result_type_success;
1696
1697 -- validate that the style_id exists in the system and the status is ACTIVE.
1698 FORALL i IN 1 .. p_id_tbl.COUNT
1699 INSERT INTO po_validation_results_gt
1700 (result_set_id,
1701 result_type,
1702 entity_type,
1703 entity_id,
1704 message_name,
1705 column_name,
1706 column_val,
1707 token1_name,
1708 token1_value,
1709 validation_id)
1710 SELECT x_result_set_id,
1711 po_validations.c_result_type_failure,
1712 c_entity_type_header,
1713 p_id_tbl(i),
1714 'PO_PDOI_INVALID_STYLE_ID',
1715 'STYLE_ID',
1716 p_style_id_tbl(i),
1717 'STYLE_ID',
1718 p_style_id_tbl(i),
1719 PO_VAL_CONSTANTS.c_style_id_valid
1720 FROM DUAL
1721 WHERE NOT EXISTS(SELECT 1
1722 FROM po_doc_style_headers pdsh
1723 WHERE pdsh.style_id = p_style_id_tbl(i) AND
1724 pdsh.status = 'ACTIVE');
1725
1726 IF (SQL%ROWCOUNT > 0) THEN
1727 x_result_type := po_validations.c_result_type_failure;
1728 END IF;
1729
1730 -- validate that complex work is not enabled
1731 FORALL i IN 1 .. p_id_tbl.COUNT
1732 INSERT INTO po_validation_results_gt
1733 (result_set_id,
1734 result_type,
1735 entity_type,
1736 entity_id,
1737 message_name,
1738 column_name,
1739 column_val,
1740 token1_name,
1741 token1_value,
1742 validation_id)
1743 SELECT x_result_set_id,
1744 po_validations.c_result_type_failure,
1745 c_entity_type_header,
1746 p_id_tbl(i),
1747 'PO_PDOI_COMPLEX_WORK_STYLE',
1748 'STYLE_ID',
1749 p_style_id_tbl(i),
1750 'STYLE_ID',
1751 p_style_id_tbl(i),
1752 PO_VAL_CONSTANTS.c_style_id_complex_work
1753 FROM DUAL
1754 WHERE EXISTS(SELECT 1
1755 FROM po_doc_style_headers pdsh
1756 WHERE pdsh.style_id = p_style_id_tbl(i) AND
1757 pdsh.progress_payment_flag = 'Y');
1758
1759 IF (SQL%ROWCOUNT > 0) THEN
1760 x_result_type := po_validations.c_result_type_failure;
1761 END IF;
1762
1763 IF po_log.d_proc THEN
1764 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1765 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1766 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1767 END IF;
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 IF po_log.d_exc THEN
1772 po_log.exc(d_mod, 0, NULL);
1773 END IF;
1774
1775 RAISE;
1776
1777 END style_id;
1778
1779 -- bug4911388 START
1780 -------------------------------------------------------------------------
1781 -- validate that acceptance_reuqired_flag has correct value
1782 -------------------------------------------------------------------------
1783 PROCEDURE acceptance_required_flag
1784 ( p_id_tbl IN PO_TBL_NUMBER,
1785 p_type_lookup_code_tbl IN PO_TBL_VARCHAR30,
1786 p_acceptance_required_flag_tbl IN PO_TBL_VARCHAR1,
1787 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
1788 x_result_type OUT NOCOPY VARCHAR2
1789 )
1790 IS
1791
1792 d_mod CONSTANT VARCHAR2(100) := d_acceptance_required_flag;
1793 BEGIN
1794 IF (x_results IS NULL) THEN
1795 x_results := po_validation_results_type.new_instance();
1796 END IF;
1797
1798 x_result_type := po_validations.c_result_type_success;
1799
1800 FOR i IN 1..p_id_tbl.COUNT LOOP
1801
1802 IF (p_type_lookup_code_tbl(i) = 'QUOTATION') THEN
1803
1804 IF ( NVL(p_acceptance_required_flag_tbl(i), 'N') <> 'N') THEN
1805
1806 x_results.add_result
1807 ( p_entity_type => c_entity_type_header,
1808 p_entity_id => p_id_tbl(i),
1809 p_column_name => 'ACCEPTANCE_REQUIRED_FLAG',
1810 p_column_val => p_acceptance_required_flag_tbl(i),
1811 p_message_name => 'PO_PDOI_COLUMN_NULL',
1812 p_token1_name => 'COLUMN_NAME',
1813 p_token1_value => 'ACCEPTANCE_REQUIRED_FLAG',
1814 p_token2_name => 'VALUE',
1815 p_token2_value => p_acceptance_required_flag_tbl(i),
1816 p_validation_id => PO_VAL_CONSTANTS.c_acceptance_required_flag
1817 );
1818
1819 x_result_type := po_validations.c_result_type_failure;
1820 END IF;
1821
1822 ELSE
1823
1824 IF (p_acceptance_required_flag_tbl(i) NOT IN ('N', 'Y', 'D', 'S')) THEN
1825
1826 x_results.add_result
1827 ( p_entity_type => c_entity_type_header,
1828 p_entity_id => p_id_tbl(i),
1829 p_column_name => 'ACCEPTANCE_REQUIRED_FLAG',
1830 p_column_val => p_acceptance_required_flag_tbl(i),
1831 p_message_name => 'PO_PDOI_INVALID_ACC_REQD_FLAG',
1832 p_validation_id => PO_VAL_CONSTANTS.c_acceptance_required_flag
1833 );
1834
1835 x_result_type := po_validations.c_result_type_failure;
1836 END IF;
1837 END IF;
1838
1839 END LOOP;
1840
1841
1842 IF po_log.d_proc THEN
1843 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1844 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1845 END IF;
1846
1847 EXCEPTION
1848 WHEN OTHERS THEN
1849 IF po_log.d_exc THEN
1850 po_log.exc(d_mod, 0, NULL);
1851 END IF;
1852
1853 RAISE;
1854 END acceptance_required_flag;
1855
1856 -- bug4911388 END
1857
1858
1859 -- bug5352625
1860
1861 -------------------------------------------------------------------------
1862 -- validate that amount limit is valid
1863 -------------------------------------------------------------------------
1864 PROCEDURE amount_limit
1865 ( p_id_tbl IN PO_TBL_NUMBER,
1866 p_amount_limit_tbl IN PO_TBL_NUMBER,
1867 p_amount_agreed_tbl IN PO_TBL_NUMBER,
1868 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
1869 x_result_type OUT NOCOPY VARCHAR2
1870 ) IS
1871 d_mod CONSTANT VARCHAR2(100) := d_amount_limit;
1872 BEGIN
1873
1874 IF (x_results IS NULL) THEN
1875 x_results := po_validation_results_type.new_instance();
1876 END IF;
1877
1878 x_result_type := po_validations.c_result_type_success;
1879
1880
1881
1882 PO_VALIDATION_HELPER.greater_or_equal_zero
1883 ( p_calling_module => d_mod,
1884 p_null_allowed_flag => PO_CORE_S.g_parameter_YES,
1885 p_value_tbl => p_amount_limit_tbl,
1886 p_entity_id_tbl => p_id_tbl,
1887 p_entity_type => c_entity_type_header,
1888 p_column_name => 'AMOUNT_LIMIT',
1889 p_message_name => 'PO_PDOI_LT_ZERO',
1890 p_token1_name => 'COLUMN_NAME',
1891 p_token1_value => 'AMOUNT_LIMIT',
1892 p_token2_name => 'VALUE',
1893 p_token2_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_amount_limit_tbl),
1894 p_validation_id => PO_VAL_CONSTANTS.c_amount_limit_gt_zero,
1895 x_results => x_results,
1896 x_result_type => x_result_type
1897 );
1898
1899
1900 FOR i IN 1..p_id_tbl.COUNT LOOP
1901
1902 -- Amount agreed has to be populated if amount limit is populated
1903 IF (p_amount_limit_tbl(i) IS NOT NULL AND
1904 p_amount_agreed_tbl(i) IS NULL) THEN
1905
1906 x_results.add_result
1907 ( p_entity_type => c_entity_type_HEADER,
1908 p_entity_id => p_id_tbl(i),
1909 p_column_name => 'AMOUNT_AGREED',
1910 p_column_val => p_amount_agreed_tbl(i),
1911 p_message_name => PO_MESSAGE_S.PO_AMT_LMT_NOT_NULL,
1912 p_validation_id => PO_VAL_CONSTANTS.c_amount_agreed_not_null
1913 );
1914
1915
1916 x_result_type := po_validations.c_result_type_failure;
1917 END IF;
1918
1919 IF ( p_amount_limit_tbl(i) < p_amount_agreed_tbl(i) ) THEN
1920
1921 x_results.add_result
1922 ( p_entity_type => c_entity_type_HEADER,
1923 p_entity_id => p_id_tbl(i),
1924 p_column_name => 'AMOUNT_LIMIT',
1925 p_column_val => p_amount_limit_tbl(i),
1926 p_message_name => PO_MESSAGE_S.PO_PO_AMT_LIMIT_CK_FAILED,
1927 p_validation_id => PO_VAL_CONSTANTS.c_amount_limit_gt_amt_agreed
1928 );
1929
1930 x_result_type := po_validations.c_result_type_failure;
1931 END IF;
1932
1933 END LOOP;
1934
1935
1936 IF po_log.d_proc THEN
1937 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1938 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1939 END IF;
1940
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 IF po_log.d_exc THEN
1944 po_log.exc(d_mod, 0, NULL);
1945 END IF;
1946
1947 RAISE;
1948 END amount_limit;
1949
1950
1951
1952 END PO_VAL_HEADERS2;