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