1 PACKAGE BODY PO_VALIDATION_HELPER AS
2 -- $Header: PO_VALIDATION_HELPER.plb 120.20.12010000.2 2008/11/17 06:09:40 mugoel ship $
3
4 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
5 c_RATE CONSTANT VARCHAR2(30) := 'RATE';
6 c_FIXED_PRICE CONSTANT VARCHAR2(30) := 'FIXED PRICE';
7 c_NEW CONSTANT VARCHAR2(30) := 'NEW';
8
9 -- PO_NOTIFICATION_CONTROLS.notification_control_code
10 c_EXPIRATION CONSTANT VARCHAR2(30) := 'EXPIRATION';
11
12 -- Constants for column names.
13 c_DISTRIBUTION_NUM CONSTANT VARCHAR2(30) := 'DISTRIBUTION_NUM';
14 c_SHIPMENT_NUM CONSTANT VARCHAR2(30) := 'SHIPMENT_NUM';
15 c_LINE_NUM CONSTANT VARCHAR2(30) := 'LINE_NUM';
16 c_PRICE_TYPE CONSTANT VARCHAR2(30) := 'PRICE_TYPE';
17
18 c_entity_type_LINE CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_LINE;
19
20 ---------------------------------------------------------------------------
21 -- Modules for debugging.
22 ---------------------------------------------------------------------------
23
24 -- The module base for this package.
25 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
26 PO_LOG.get_package_base('PO_VALIDATION_HELPER');
27
28 -- The module base for the subprogram.
29 D_greater_than_zero CONSTANT VARCHAR2(100) :=
30 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'greater_than_zero');
31
32 -- The module base for the subprogram.
33 D_greater_or_equal_zero CONSTANT VARCHAR2(100) :=
34 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'greater_or_equal_zero');
35
36 -- The module base for the subprogram.
37 D_within_percentage_range CONSTANT VARCHAR2(100) :=
38 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'within_percentage_range');
39
40 -- The module base for the subprogram.
41 D_not_null CONSTANT VARCHAR2(100) :=
42 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'not_null');
43
44 -- The module base for the subprogram.
45 D_ensure_null CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'ensure_null');
46
47 -- The module base for the subprogram.
48 D_flag_value_Y_N CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'flag_value_Y_N');
49
50 -- The module base for the subprogram.
51 D_zero CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'zero');
52
53 -- The module base for the subprogram.
54 D_terms_id CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'terms_id');
55
56 -- The module base for the subprogram.
57 D_open_period CONSTANT VARCHAR2(100) :=
58 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'open_period');
59
60 -- The module base for the subprogram.
61 D_gt_zero_order_type_filter CONSTANT VARCHAR2(100) :=
62 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'gt_zero_order_type_filter');
63
64 -- The module base for the subprogram.
65 D_no_timecards_exist CONSTANT VARCHAR2(100) :=
66 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'no_timecards_exist');
67
68 -- The module base for the subprogram.
69 D_amount_notif_ctrl_warning CONSTANT VARCHAR2(100) :=
70 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_notif_ctrl_warning');
71
72 -- The module base for the subprogram.
73 D_child_num_unique CONSTANT VARCHAR2(100) :=
74 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'child_num_unique');
75
76 D_price_diff_value_unique CONSTANT VARCHAR2(100) :=
77 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_diff_value_unique');
78
79 D_start_date_le_end_date CONSTANT VARCHAR2(100) :=
80 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'start_date_le_end_date');
81
82 D_num1_less_or_equal_num2 CONSTANT VARCHAR2(100) :=
83 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'num1_less_or_equal_num2');
84
85 D_gt_zero_opm_filter CONSTANT VARCHAR2(100) :=
86 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'gt_zero_opm_filter');
87
88 D_qtys_within_deviation CONSTANT VARCHAR2(100) :=
89 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'qtys_within_deviation');
90
91 D_secondary_unit_of_measure CONSTANT VARCHAR2(100) :=
92 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'secondary_unit_of_measure');
93
94 D_secondary_quantity CONSTANT VARCHAR2(100) :=
95 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'secondary_quantity');
96
97 D_preferred_grade CONSTANT VARCHAR2(100) :=
98 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'preferred_grade');
99
100 D_secondary_uom_update CONSTANT VARCHAR2(100) :=
101 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'secondary_uom_update');
102
103 D_process_enabled CONSTANT VARCHAR2(100) :=
104 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'process_enabled');
105
106 D_get_converted_unit_of_meas CONSTANT VARCHAR2(100) :=
107 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_converted_unit_of_meas');
108
109 D_get_item_secondary_uom CONSTANT VARCHAR2(100) :=
110 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'get_item_secondary_uom');
111
112 D_validate_desc_flex CONSTANT VARCHAR2(100) :=
113 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_desc_flex');
114
115 -------------------------------------------------------------------------------
116 -- Private Functions/Procedures
117 -------------------------------------------------------------------------------
118 FUNCTION get_item_secondary_uom(p_item_id_tbl IN po_tbl_number,
119 p_organization_id_tbl IN po_tbl_number)
120 RETURN PO_TBL_VARCHAR30;
121
122 -------------------------------------------------------------------------------
123 --Start of Comments
124 --Pre-reqs: None.
125 --Modifies: x_results
126 --Locks: None.
127 --Function:
128 -- Checks that each value is greater than zero,
129 -- and adds an error to x_results if it is not.
130 --Parameters:
131 --IN:
132 --p_calling_module
133 -- The module base of the calling procedure, used for logging.
134 --p_null_allowed_flag
135 -- Indicates whether or not NULL values should produce errors.
136 -- PO_CORE_S.g_parameter_YES - NULL is not an error.
137 -- PO_CORE_S.g_parameter_NO - NULL is an error.
138 --p_value_tbl
139 -- The values to be checked.
140 --p_entity_id_tbl
141 -- The entity id's corresponding to the values to be checked.
142 --p_entity_type
143 --p_column_name
144 --p_message_name
145 -- Values to use in the error results.
146 --IN OUT:
147 --x_results
148 -- Validation errors will be added to this object.
149 -- A new object will be created if NULL is passed in.
150 --OUT:
151 --x_result_type
152 -- Indicates if any validations have failed.
153 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
154 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
155 --End of Comments
156 -------------------------------------------------------------------------------
157 PROCEDURE greater_than_zero(
158 p_calling_module IN VARCHAR2
159 , p_null_allowed_flag IN VARCHAR2 DEFAULT NULL
160 , p_value_tbl IN PO_TBL_NUMBER
161 , p_entity_id_tbl IN PO_TBL_NUMBER
162 , p_entity_type IN VARCHAR2
163 , p_column_name IN VARCHAR2
164 , p_message_name IN VARCHAR2 DEFAULT NULL
165 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
166 , x_result_type OUT NOCOPY VARCHAR2
167 )
168 IS
169 d_mod CONSTANT VARCHAR2(100) := D_greater_than_zero;
170 l_results_count NUMBER;
171 l_null_not_allowed BOOLEAN;
172 l_message_name VARCHAR2(30);
173 BEGIN
174
175 IF PO_LOG.d_proc THEN
176 PO_LOG.proc_begin(p_calling_module);
177 PO_LOG.proc_begin(d_mod,'p_null_allowed_flag',p_null_allowed_flag);
178 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
179 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
180 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
181 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
182 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
183 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
184 END IF;
185
186 IF (x_results IS NULL) THEN
187 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
188 END IF;
189
190 l_results_count := x_results.result_type.COUNT;
191
192 IF (p_null_allowed_flag = PO_CORE_S.g_parameter_YES) THEN
193 l_null_not_allowed := FALSE;
194 ELSE
195 l_null_not_allowed := TRUE;
196 END IF;
197
198 l_message_name := NVL(p_message_name,PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO);
199
200 IF PO_LOG.d_stmt THEN
201 PO_LOG.stmt(d_mod,0,'l_results_count',l_results_count);
202 PO_LOG.stmt(d_mod,0,'l_null_not_allowed',l_null_not_allowed);
203 PO_LOG.stmt(d_mod,0,'l_message_name',l_message_name);
204 END IF;
205
206 FOR i IN 1 .. p_value_tbl.COUNT LOOP
207 IF (p_value_tbl(i) <= 0 OR (l_null_not_allowed AND p_value_tbl(i) IS NULL)) THEN
208 x_results.add_result(
209 p_entity_type => p_entity_type
210 , p_entity_id => p_entity_id_tbl(i)
211 , p_column_name => p_column_name
212 , p_column_val => TO_CHAR(p_value_tbl(i))
213 , p_message_name => l_message_name
214 );
215 END IF;
216 END LOOP;
217
218 IF (l_results_count < x_results.result_type.COUNT) THEN
219 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
220 ELSE
221 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
222 END IF;
223
224 IF PO_LOG.d_proc THEN
225 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
226 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
227 PO_LOG.proc_end(p_calling_module);
228 END IF;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 IF PO_LOG.d_exc THEN
233 PO_LOG.exc(d_mod,0,NULL);
234 PO_LOG.exc(p_calling_module,0,NULL);
235 END IF;
236 RAISE;
237
238 END greater_than_zero;
239
240
241 -------------------------------------------------------------------------------
242 --Start of Comments
243 --Pre-reqs: None.
244 --Modifies: x_results
245 --Locks: None.
246 --Function:
247 -- Checks that each value is greater than or equal to zero,
248 -- and adds an error to x_results if it is not.
249 --Parameters:
250 --IN:
251 --p_calling_module
252 -- The module base of the calling procedure, used for logging.
253 --p_null_allowed_flag
254 -- Indicates whether or not NULL values should produce errors.
255 -- PO_CORE_S.g_parameter_YES - NULL is not an error.
256 -- PO_CORE_S.g_parameter_NO - NULL is an error.
257 --p_value_tbl
258 -- The values to be checked.
259 --p_entity_id_tbl
260 -- The entity id's corresponding to the values to be checked.
261 --p_entity_type
262 --p_column_name
263 --p_message_name
264 -- Values to use in the error results.
265 --IN OUT:
266 --x_results
267 -- Validation errors will be added to this object.
268 -- A new object will be created if NULL is passed in.
269 --OUT:
270 --x_result_type
271 -- Indicates if any validations have failed.
272 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
273 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
274 --End of Comments
275 -------------------------------------------------------------------------------
276 PROCEDURE greater_or_equal_zero(
277 p_calling_module IN VARCHAR2,
278 p_null_allowed_flag IN VARCHAR2 DEFAULT NULL,
279 p_value_tbl IN PO_TBL_NUMBER,
280 p_entity_id_tbl IN PO_TBL_NUMBER,
281 p_entity_type IN VARCHAR2,
282 p_column_name IN VARCHAR2,
283 p_message_name IN VARCHAR2 DEFAULT NULL,
284 p_token1_name IN VARCHAR2 DEFAULT NULL,
285 p_token1_value IN VARCHAR2 DEFAULT NULL,
286 p_token2_name IN VARCHAR2 DEFAULT NULL,
287 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
288 p_validation_id IN NUMBER DEFAULT NULL,
289 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
290 x_result_type OUT NOCOPY VARCHAR2)
291 IS
292
293 d_mod CONSTANT VARCHAR2(100) := D_greater_or_equal_zero;
294 l_results_count NUMBER;
295 l_null_not_allowed BOOLEAN;
296 l_message_name VARCHAR2(30);
297 l_token2_value_tbl PO_TBL_VARCHAR4000;
298
299 BEGIN
300
301 IF PO_LOG.d_proc THEN
302 PO_LOG.proc_begin(p_calling_module);
303 PO_LOG.proc_begin(d_mod,'p_null_allowed_flag',p_null_allowed_flag);
304 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
305 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
306 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
307 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
308 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
309 PO_LOG.proc_begin(d_mod, 'p_token1_name', p_token1_name);
310 PO_LOG.proc_begin(d_mod, 'p_token1_value', p_token1_value);
311 PO_LOG.proc_begin(d_mod, 'p_token2_name', p_token2_name);
312 PO_LOG.proc_begin(d_mod, 'p_token2_value_tbl', p_token2_value_tbl);
313 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
314 END IF;
315
316 IF (x_results IS NULL) THEN
317 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
318 END IF;
319
320 IF (p_token2_value_tbl IS NULL) THEN
321 l_token2_value_tbl := PO_TBL_VARCHAR4000();
322 l_token2_value_tbl.extend(p_value_tbl.COUNT);
323 ELSE
324 l_token2_value_tbl := p_token2_value_tbl;
325 END IF;
326
327 l_results_count := x_results.result_type.COUNT;
328
329 IF (p_null_allowed_flag = PO_CORE_S.g_parameter_YES) THEN
330 l_null_not_allowed := FALSE;
331 ELSE
332 l_null_not_allowed := TRUE;
333 END IF;
334
335 l_message_name := NVL(p_message_name,PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO);
336
337 IF PO_LOG.d_stmt THEN
338 PO_LOG.stmt(d_mod,0,'l_results_count',l_results_count);
339 PO_LOG.stmt(d_mod,0,'l_null_not_allowed',l_null_not_allowed);
340 PO_LOG.stmt(d_mod,0,'l_message_name',l_message_name);
341 END IF;
342
343 FOR i IN 1 .. p_value_tbl.COUNT LOOP
344 IF (p_value_tbl(i) < 0 OR (l_null_not_allowed AND p_value_tbl(i) IS NULL)) THEN
345 x_results.add_result(
346 p_entity_type => p_entity_type,
347 p_entity_id => p_entity_id_tbl(i),
348 p_column_name => p_column_name,
349 p_column_val => TO_CHAR(p_value_tbl(i)),
350 p_token1_name => p_token1_name,
351 p_token1_value => p_token1_value,
352 p_token2_name => p_token2_name,
353 p_token2_value => l_token2_value_tbl(i),
354 p_message_name => l_message_name,
355 p_validation_id => p_validation_id);
356 END IF;
357 END LOOP;
358
359 IF (l_results_count < x_results.result_type.COUNT) THEN
360 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
361 ELSE
362 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
363 END IF;
364
365 IF PO_LOG.d_proc THEN
366 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
367 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
368 PO_LOG.proc_end(p_calling_module);
369 END IF;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 IF PO_LOG.d_exc THEN
374 PO_LOG.exc(d_mod,0,NULL);
375 PO_LOG.exc(p_calling_module,0,NULL);
376 END IF;
377 RAISE;
378
379 END greater_or_equal_zero;
380
381
382 -------------------------------------------------------------------------------
383 --Start of Comments
384 --Pre-reqs: None.
385 --Modifies: x_results
386 --Locks: None.
387 --Function:
388 -- Checks that each value is between 0 and 100, inclusive,
389 -- and adds an error to x_results if it is not.
390 --Parameters:
391 --IN:
392 --p_calling_module
393 -- The module base of the calling procedure, used for logging.
394 --p_null_allowed_flag
395 -- Indicates whether or not NULL values should produce errors.
396 -- PO_CORE_S.g_parameter_YES - NULL is not an error.
397 -- PO_CORE_S.g_parameter_NO - NULL is an error.
398 --p_value_tbl
399 -- The values to be checked.
400 --p_entity_id_tbl
401 -- The entity id's corresponding to the values to be checked.
402 --p_entity_type
403 --p_column_name
404 --p_message_name
405 --p_token1_name
406 --p_token1_value_tbl
407 -- Values to use in the error results.
408 -- If the message does not take tokens,
409 -- NULL may be passed for the token name and values.
410 --IN OUT:
411 --x_results
412 -- Validation errors will be added to this object.
413 -- A new object will be created if NULL is passed in.
414 --OUT:
415 --x_result_type
416 -- Indicates if any validations have failed.
417 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
418 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
419 --End of Comments
420 -------------------------------------------------------------------------------
421 PROCEDURE within_percentage_range(
422 p_calling_module IN VARCHAR2
423 , p_null_allowed_flag IN VARCHAR2 DEFAULT NULL
424 , p_value_tbl IN PO_TBL_NUMBER
425 , p_entity_id_tbl IN PO_TBL_NUMBER
426 , p_entity_type IN VARCHAR2
427 , p_column_name IN VARCHAR2
428 , p_message_name IN VARCHAR2 DEFAULT NULL
429 , p_token1_name IN VARCHAR2 DEFAULT NULL
430 , p_token1_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL
431 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
432 , x_result_type OUT NOCOPY VARCHAR2
433 )
434 IS
435 d_mod CONSTANT VARCHAR2(100) := D_within_percentage_range;
436 l_results_count NUMBER;
437 l_null_not_allowed BOOLEAN;
438 l_message_name VARCHAR2(30);
439 l_token1_value_tbl PO_TBL_VARCHAR4000;
440 BEGIN
441
442 IF PO_LOG.d_proc THEN
443 PO_LOG.proc_begin(p_calling_module);
444 PO_LOG.proc_begin(d_mod,'p_null_allowed_flag',p_null_allowed_flag);
445 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
446 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
447 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
448 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
449 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
450 PO_LOG.proc_begin(d_mod,'p_token1_name',p_token1_name);
451 PO_LOG.proc_begin(d_mod,'p_token1_value_tbl',p_token1_value_tbl);
452 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
453 END IF;
454
455 IF (x_results IS NULL) THEN
456 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
457 END IF;
458
459 l_results_count := x_results.result_type.COUNT;
460
461 IF (p_null_allowed_flag = PO_CORE_S.g_parameter_YES) THEN
462 l_null_not_allowed := FALSE;
463 ELSE
464 l_null_not_allowed := TRUE;
465 END IF;
466
467 l_message_name := NVL(p_message_name,PO_MESSAGE_S.PO_ALL_ENTER_PERCENT);
468
469 IF (p_token1_value_tbl IS NULL) THEN
470 l_token1_value_tbl := PO_TBL_VARCHAR4000();
471 l_token1_value_tbl.extend(p_value_tbl.COUNT);
472 ELSE
473 l_token1_value_tbl := p_token1_value_tbl;
474 END IF;
475
476 IF PO_LOG.d_stmt THEN
477 PO_LOG.stmt(d_mod,0,'l_results_count',l_results_count);
478 PO_LOG.stmt(d_mod,0,'l_null_not_allowed',l_null_not_allowed);
479 PO_LOG.stmt(d_mod,0,'l_message_name',l_message_name);
480 PO_LOG.stmt(d_mod,0,'l_token1_value_tbl',l_token1_value_tbl);
481 END IF;
482
483 FOR i IN 1 .. p_value_tbl.COUNT LOOP
484 IF (p_value_tbl(i) < 0 OR p_value_tbl(i) > 100
485 OR (l_null_not_allowed AND p_value_tbl(i) IS NULL)
486 ) THEN
487 x_results.add_result(
488 p_entity_type => p_entity_type
489 , p_entity_id => p_entity_id_tbl(i)
490 , p_column_name => p_column_name
491 , p_column_val => TO_CHAR(p_value_tbl(i))
492 , p_message_name => l_message_name
493 , p_token1_name => p_token1_name
494 , p_token1_value => l_token1_value_tbl(i)
495 );
496 END IF;
497 END LOOP;
498
499 IF (l_results_count < x_results.result_type.COUNT) THEN
500 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
501 ELSE
502 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
503 END IF;
504
505 IF PO_LOG.d_proc THEN
506 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
507 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
508 PO_LOG.proc_end(p_calling_module);
509 END IF;
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 IF PO_LOG.d_exc THEN
514 PO_LOG.exc(d_mod,0,NULL);
515 PO_LOG.exc(p_calling_module,0,NULL);
516 END IF;
517 RAISE;
518
519 END within_percentage_range;
520
521
522 -------------------------------------------------------------------------------
523 --Start of Comments
524 --Pre-reqs: None.
525 --Modifies: PO_VALIDATION_RESULTS_GT
526 --Locks: None.
527 --Function:
528 -- Checks that the provided dates are in valid periods for Purchasing
529 -- (open or future-enterable, not adjusting) for the corresponding
530 -- set of books.
531 --Parameters:
532 --IN:
533 --p_calling_module
534 -- The module base of the calling procedure, used for logging.
535 --p_date_tbl
536 -- The values to be checked.
537 --p_org_id_tbl
538 -- Specifies the set of books in which the periods should exist.
539 --p_entity_id_tbl
540 -- The entity id's corresponding to the values to be checked.
541 --p_entity_type
542 --p_column_name
543 --p_message_name
544 -- Values to use in the error results.
545 -- If the message does not take tokens,
546 -- NULL may be passed for the token name and values.
547 --IN OUT:
548 --x_result_set_id
549 -- Validation errors will be added to the results table using
550 -- this identifier.
551 --OUT:
552 --x_result_type
553 -- Indicates if any validations have failed.
554 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
555 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
556 --End of Comments
557 -------------------------------------------------------------------------------
558 PROCEDURE open_period(
559 p_calling_module IN VARCHAR2
560 , p_date_tbl IN PO_TBL_DATE
561 , p_org_id_tbl IN PO_TBL_NUMBER
562 , p_entity_id_tbl IN PO_TBL_NUMBER
563 , p_entity_type IN VARCHAR2
564 , p_column_name IN VARCHAR2
565 , p_message_name IN VARCHAR2
566 -- PBWC Message Change Impact: Adding a token
567 , p_token1_name IN VARCHAR2 DEFAULT NULL
568 , p_token1_value IN PO_TBL_NUMBER DEFAULT NULL
569 , x_result_set_id IN OUT NOCOPY NUMBER
570 , x_result_type OUT NOCOPY VARCHAR2
571 )
572 IS
573 d_mod CONSTANT VARCHAR2(100) := D_open_period;
574 l_profile_value VARCHAR2(2000);
575 BEGIN
576
577 IF PO_LOG.d_proc THEN
578 PO_LOG.proc_begin(p_calling_module);
579 PO_LOG.proc_begin(d_mod,'p_date_tbl',p_date_tbl);
580 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
581 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
582 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
583 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
584 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
585 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
586 END IF;
587
588 IF (x_result_set_id IS NULL) THEN
589 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
590 END IF;
591
592 FND_PROFILE.get(PO_PROFILES.PO_CHECK_OPEN_PERIODS,l_profile_value);
593
594 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
595
596 IF PO_LOG.d_stmt THEN
597 PO_LOG.stmt(d_mod,0,'l_profile_value',l_profile_value);
598 PO_LOG.stmt(d_mod,0,'x_result_set_id',x_result_set_id);
599 PO_LOG.stmt(d_mod,0,'x_result_type',x_result_type);
600 END IF;
601
602 IF (l_profile_value = 'Y') THEN
603
604 /* Bug# 6671185: Added the condition "p_date_tbl(i) IS NOT NULL".
605 * This is to ensure that validation do not fail if p_date_tbl(i) is NULL.
606 * Also, moved the call to "PO_VALIDATIONS.log_validation_results_gt" inside
607 * the for loop, so that all the validation errors are captured.
608 */
609 FOR i IN 1 .. p_date_tbl.COUNT LOOP
610 IF p_date_tbl(i) IS NOT NULL THEN
611 INSERT INTO PO_VALIDATION_RESULTS_GT
612 ( result_set_id
613 , entity_type
614 , entity_id
615 , column_name
616 , column_val
617 , message_name
618 -- PBWC Message Change Impact: Adding a token
619 , token1_name
620 , token1_value
621 )
622 SELECT
623 x_result_set_id
624 , p_entity_type
625 , p_entity_id_tbl(i)
626 , p_column_name
627 , TO_CHAR(p_date_tbl(i))
628 , p_message_name
629 -- PBWC Message Change Impact: Adding a token
630 , p_token1_name
631 , to_char(p_token1_value(i))
632 FROM
633 DUAL
634 WHERE NOT EXISTS
635 ( SELECT null
636 FROM
637 GL_PERIOD_STATUSES PO_PERIOD
638 , FINANCIALS_SYSTEM_PARAMS_ALL FSP
639 WHERE
640 FSP.org_id = p_org_id_tbl(i)
641 AND PO_PERIOD.set_of_books_id = FSP.set_of_books_id
642 AND PO_PERIOD.application_id = 201 -- PO
643 AND PO_PERIOD.adjustment_period_flag = 'N'
644 AND PO_PERIOD.closing_status IN ('O','F')
645 AND TRUNC(p_date_tbl(i))
646 BETWEEN TRUNC(PO_PERIOD.start_date) AND TRUNC(PO_PERIOD.end_date)
647 )
648 ;
649
650 IF(SQL%ROWCOUNT > 0) THEN
651 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
652 END IF;
653
654 IF PO_LOG.d_proc THEN
655 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
656 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
657 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
658 PO_LOG.proc_end(p_calling_module);
659 END IF;
660
661 END IF;
662 END LOOP;
663
664 END IF;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 IF PO_LOG.d_exc THEN
669 PO_LOG.exc(d_mod,0,NULL);
670 PO_LOG.exc(p_calling_module,0,NULL);
671 END IF;
672 RAISE;
673
674 END open_period;
675
676
677 -------------------------------------------------------------------------------
678 --Start of Comments
679 --Pre-reqs: None.
680 --Modifies: x_results
681 --Locks: None.
682 --Function:
683 -- Checks that each value is not null,
684 -- and adds an error to x_results if it is.
685 --Parameters:
686 --IN:
687 --p_calling_module
688 -- The module base of the calling procedure, used for logging.
689 --p_value_tbl
690 -- The values to be checked.
691 --p_entity_id_tbl
692 -- The entity id's corresponding to the values to be checked.
693 --p_entity_type
694 --p_column_name
695 --p_message_name
696 -- Values to use in the error results.
697 --IN OUT:
698 --x_results
699 -- Validation errors will be added to this object.
700 -- A new object will be created if NULL is passed in.
701 --OUT:
702 --x_result_type
703 -- Indicates if any validations have failed.
704 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
705 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
706 --End of Comments
707 -------------------------------------------------------------------------------
708 PROCEDURE not_null(
709 p_calling_module IN VARCHAR2,
710 p_value_tbl IN PO_TBL_VARCHAR4000,
711 p_entity_id_tbl IN PO_TBL_NUMBER,
712 p_entity_type IN VARCHAR2,
713 p_column_name IN VARCHAR2,
714 p_message_name IN VARCHAR2,
715 p_token1_name IN VARCHAR2 DEFAULT NULL,
716 p_token1_value IN VARCHAR2 DEFAULT NULL,
717 p_token2_name IN VARCHAR2 DEFAULT NULL,
718 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
719 p_validation_id IN NUMBER DEFAULT NULL,
720 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
721 x_result_type OUT NOCOPY VARCHAR2
722 )
723 IS
724
725 d_mod CONSTANT VARCHAR2(100) := D_not_null;
726 l_results_count NUMBER;
727 l_token2_value_tbl PO_TBL_VARCHAR4000;
728
729 BEGIN
730
731 IF PO_LOG.d_proc THEN
732 PO_LOG.proc_begin(p_calling_module);
733 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
734 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl', p_entity_id_tbl);
735 PO_LOG.proc_begin(d_mod,'p_entity_type', p_entity_type);
736 PO_LOG.proc_begin(d_mod,'p_column_name', p_column_name);
737 PO_LOG.proc_begin(d_mod,'p_message_name', p_message_name);
738 PO_LOG.proc_begin(d_mod, 'p_token1_name', p_token1_name);
739 PO_LOG.proc_begin(d_mod, 'p_token1_value', p_token1_value);
740 PO_LOG.proc_begin(d_mod, 'p_token2_name', p_token2_name);
741 PO_LOG.proc_begin(d_mod, 'p_token2_value_tbl', p_token2_value_tbl);
742 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
743 END IF;
744
745 IF (x_results IS NULL) THEN
746 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
747 END IF;
748
749 IF (p_token2_value_tbl IS NULL) THEN
750 l_token2_value_tbl := PO_TBL_VARCHAR4000();
751 l_token2_value_tbl.extend(p_value_tbl.COUNT);
752 ELSE
753 l_token2_value_tbl := p_token2_value_tbl;
754 END IF;
755
756 l_results_count := x_results.result_type.COUNT;
757
758 IF PO_LOG.d_stmt THEN
759 PO_LOG.stmt(d_mod,0,'l_results_count',l_results_count);
760 END IF;
761
762 FOR i IN 1 .. p_value_tbl.COUNT LOOP
763 IF (p_value_tbl(i) IS NULL) THEN
764 x_results.add_result(
765 p_entity_type => p_entity_type,
766 p_entity_id => p_entity_id_tbl(i),
767 p_column_name => p_column_name,
768 p_column_val => p_value_tbl(i),
769 p_message_name => p_message_name,
770 p_token1_name => p_token1_name,
771 p_token1_value => p_token1_value,
772 p_token2_name => p_token2_name,
773 p_token2_value => l_token2_value_tbl(i),
774 p_validation_id => p_validation_id);
775 END IF;
776 END LOOP;
777
778 IF (l_results_count < x_results.result_type.COUNT) THEN
779 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
780 ELSE
781 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
782 END IF;
783
784 IF PO_LOG.d_proc THEN
785 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
786 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
787 PO_LOG.proc_end(p_calling_module);
788 END IF;
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 IF PO_LOG.d_exc THEN
793 PO_LOG.exc(d_mod,0,NULL);
794 PO_LOG.exc(p_calling_module,0,NULL);
795 END IF;
796 RAISE;
797
798 END not_null;
799
800
801 -------------------------------------------------------------------------------
802 --Start of Comments
803 --Pre-reqs: None.
804 --Modifies: x_results
805 --Locks: None.
806 --Function:
807 -- Checks that each value is null,
808 -- and adds an error to x_results if it is.
809 --Parameters:
810 --IN:
811 --p_calling_module
812 -- The module base of the calling procedure, used for logging.
813 --p_value_tbl
814 -- The values to be checked.
815 --p_entity_id_tbl
816 -- The entity id's corresponding to the values to be checked.
817 --p_entity_type
818 --p_column_name
819 --p_message_name
820 -- Values to use in the error results.
821 --IN OUT:
822 --x_results
823 -- Validation errors will be added to this object.
824 -- A new object will be created if NULL is passed in.
825 --OUT:
826 --x_result_type
827 -- Indicates if any validations have failed.
828 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
829 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
830 --End of Comments
831 -------------------------------------------------------------------------------
832 PROCEDURE ensure_null(p_calling_module IN VARCHAR2,
833 p_value_tbl IN PO_TBL_VARCHAR4000,
834 p_entity_id_tbl IN PO_TBL_NUMBER,
835 p_entity_type IN VARCHAR2,
836 p_column_name IN VARCHAR2,
837 p_message_name IN VARCHAR2,
838 p_token1_name IN VARCHAR2 DEFAULT NULL,
839 p_token1_value IN VARCHAR2 DEFAULT NULL,
840 p_token2_name IN VARCHAR2 DEFAULT NULL,
841 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
842 p_validation_id IN NUMBER DEFAULT NULL,
843 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
844 x_result_type OUT NOCOPY VARCHAR2) IS
845 d_mod CONSTANT VARCHAR2(100) := D_ensure_null;
846 l_results_count NUMBER;
847 l_token2_value_tbl PO_TBL_VARCHAR4000;
848
849 BEGIN
850
851 IF PO_LOG.d_proc THEN
852 PO_LOG.proc_begin(p_calling_module);
853 PO_LOG.proc_begin(d_mod, 'p_value_tbl', p_value_tbl);
854 PO_LOG.proc_begin(d_mod, 'p_entity_id_tbl', p_entity_id_tbl);
855 PO_LOG.proc_begin(d_mod, 'p_entity_type', p_entity_type);
856 PO_LOG.proc_begin(d_mod, 'p_column_name', p_column_name);
857 PO_LOG.proc_begin(d_mod, 'p_message_name', p_message_name);
858 PO_LOG.proc_begin(d_mod, 'p_token1_name', p_token1_name);
859 PO_LOG.proc_begin(d_mod, 'p_token1_value', p_token1_value);
860 PO_LOG.proc_begin(d_mod, 'p_token2_name', p_token2_name);
861 PO_LOG.proc_begin(d_mod, 'p_token2_value_tbl', p_token2_value_tbl);
862 PO_LOG.log(PO_LOG.c_PROC_BEGIN, d_mod, NULL, 'x_results', x_results);
863 END IF;
864
865 IF (x_results IS NULL) THEN
866 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
867 END IF;
868
869 IF (p_token2_value_tbl IS NULL) THEN
870 l_token2_value_tbl := PO_TBL_VARCHAR4000();
871 l_token2_value_tbl.extend(p_value_tbl.COUNT);
872 ELSE
873 l_token2_value_tbl := p_token2_value_tbl;
874 END IF;
875
876 l_results_count := x_results.result_type.COUNT;
877
878 IF PO_LOG.d_stmt THEN
879 PO_LOG.stmt(d_mod, 0, 'l_results_count', l_results_count);
880 END IF;
881
882 FOR i IN 1 .. p_value_tbl.COUNT LOOP
883 IF (p_value_tbl(i) IS NOT NULL) THEN
884 x_results.add_result(p_entity_type => p_entity_type,
885 p_entity_id => p_entity_id_tbl(i),
886 p_column_name => p_column_name,
887 p_column_val => p_value_tbl(i),
888 p_message_name => p_message_name,
889 p_token1_name => p_token1_name,
890 p_token1_value => p_token1_value,
891 p_token2_name => p_token2_name,
892 p_token2_value => l_token2_value_tbl(i),
893 p_validation_id => p_validation_id);
894 END IF;
895 END LOOP;
896
897 IF (l_results_count < x_results.result_type.COUNT) THEN
898 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
899 ELSE
900 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
901 END IF;
902
903 IF PO_LOG.d_proc THEN
904 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
905 PO_LOG.log(PO_LOG.c_PROC_END, d_mod, NULL, 'x_results', x_results);
906 PO_LOG.proc_end(p_calling_module);
907 END IF;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 IF PO_LOG.d_exc THEN
912 PO_LOG.exc(d_mod, 0, NULL);
913 PO_LOG.exc(p_calling_module, 0, NULL);
914 END IF;
915 RAISE;
916
917 END ensure_null;
918
919
920 -------------------------------------------------------------------------------
921 --Start of Comments
922 --Pre-reqs: None.
923 --Modifies: x_results
924 --Locks: None.
925 --Function:
926 -- Checks that each value is equal to 'Y' or 'N',
927 -- and adds an error to x_results if it isn't.
928 --Parameters:
929 --IN:
930 --p_calling_module
931 -- The module base of the calling procedure, used for logging.
932 --p_flag_value_tbl
933 -- The values to be checked.
934 --p_entity_id_tbl
935 -- The entity id's corresponding to the values to be checked.
936 --p_entity_type
937 --p_column_name
938 --p_message_name
939 -- Values to use in the error results.
940 --IN OUT:
941 --x_results
942 -- Validation errors will be added to this object.
943 -- A new object will be created if NULL is passed in.
944 --OUT:
945 --x_result_type
946 -- Indicates if any validations have failed.
947 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
948 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
949 --End of Comments
950 -------------------------------------------------------------------------------
951 PROCEDURE flag_value_Y_N(p_calling_module IN VARCHAR2,
952 p_flag_value_tbl IN PO_TBL_VARCHAR1,
953 p_entity_id_tbl IN PO_TBL_NUMBER,
954 p_entity_type IN VARCHAR2,
955 p_column_name IN VARCHAR2,
956 p_message_name IN VARCHAR2,
957 p_token1_name IN VARCHAR2 DEFAULT NULL,
958 p_token1_value IN VARCHAR2 DEFAULT NULL,
959 p_token2_name IN VARCHAR2 DEFAULT NULL,
960 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
961 p_validation_id IN NUMBER DEFAULT NULL,
962 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
963 x_result_type OUT NOCOPY VARCHAR2) IS
964 d_mod CONSTANT VARCHAR2(100) := D_flag_value_Y_N;
965 l_results_count NUMBER;
966 l_token2_value_tbl PO_TBL_VARCHAR4000;
967
968 BEGIN
969
970 IF PO_LOG.d_proc THEN
971 PO_LOG.proc_begin(p_calling_module);
972 PO_LOG.proc_begin(d_mod, 'p_flag_value_tbl', p_flag_value_tbl);
973 PO_LOG.proc_begin(d_mod, 'p_entity_id_tbl', p_entity_id_tbl);
974 PO_LOG.proc_begin(d_mod, 'p_entity_type', p_entity_type);
975 PO_LOG.proc_begin(d_mod, 'p_column_name', p_column_name);
976 PO_LOG.proc_begin(d_mod, 'p_message_name', p_message_name);
977 PO_LOG.proc_begin(d_mod, 'p_token1_name', p_token1_name);
978 PO_LOG.proc_begin(d_mod, 'p_token1_value', p_token1_value);
979 PO_LOG.proc_begin(d_mod, 'p_token2_name', p_token2_name);
980 PO_LOG.proc_begin(d_mod, 'p_token2_value_tbl', p_token2_value_tbl);
981 PO_LOG.log(PO_LOG.c_PROC_BEGIN, d_mod, NULL, 'x_results', x_results);
982 END IF;
983
984 IF (x_results IS NULL) THEN
985 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
986 END IF;
987
988 IF (p_token2_value_tbl IS NULL) THEN
989 l_token2_value_tbl := PO_TBL_VARCHAR4000();
990 l_token2_value_tbl.extend(p_flag_value_tbl.COUNT);
991 ELSE
992 l_token2_value_tbl := p_token2_value_tbl;
993 END IF;
994
995 l_results_count := x_results.result_type.COUNT;
996
997 IF PO_LOG.d_stmt THEN
998 PO_LOG.stmt(d_mod, 0, 'l_results_count', l_results_count);
999 END IF;
1000
1001 FOR i IN 1 .. p_flag_value_tbl.COUNT LOOP
1002 IF (p_flag_value_tbl(i) NOT IN ('Y', 'N')) THEN
1003 x_results.add_result(p_entity_type => p_entity_type,
1004 p_entity_id => p_entity_id_tbl(i),
1005 p_column_name => p_column_name,
1006 p_column_val => p_flag_value_tbl(i),
1007 p_message_name => p_message_name,
1008 p_token1_name => p_token1_name,
1009 p_token1_value => p_token1_value,
1010 p_token2_name => p_token2_name,
1011 p_token2_value => l_token2_value_tbl(i),
1012 p_validation_id => p_validation_id);
1013 END IF;
1014 END LOOP;
1015
1016 IF (l_results_count < x_results.result_type.COUNT) THEN
1017 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1018 ELSE
1019 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1020 END IF;
1021
1022 IF PO_LOG.d_proc THEN
1023 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
1024 PO_LOG.log(PO_LOG.c_PROC_END, d_mod, NULL, 'x_results', x_results);
1025 PO_LOG.proc_end(p_calling_module);
1026 END IF;
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 IF PO_LOG.d_exc THEN
1031 PO_LOG.exc(d_mod, 0, NULL);
1032 PO_LOG.exc(p_calling_module, 0, NULL);
1033 END IF;
1034 RAISE;
1035
1036 END flag_value_Y_N;
1037
1038 -------------------------------------------------------------------------------
1039 --Start of Comments
1040 --Pre-reqs: None.
1041 --Modifies: x_results
1042 --Locks: None.
1043 --Function:
1044 -- Checks that each value is greater than zero and not null,
1045 -- and adds an error to x_results if it is not.
1046 -- The check is only performed for either quantity-based types
1047 -- or amount-based types, but not both.
1048 --Parameters:
1049 --IN:
1050 --p_calling_module
1051 -- The module base of the calling procedure, used for logging.
1052 --p_value_tbl
1053 -- The values to be checked.
1054 --p_entity_id_tbl
1055 -- The entity id's corresponding to the values to be checked.
1056 --p_order_type_lookup_code_tbl
1057 -- The order_type_lookup_code of the corresponding value.
1058 --p_check_quantity_types_flag
1059 -- Indicates whether values that depend on QUANTITY or AMOUNT
1060 -- should be checked.
1061 -- PO_CORE_S.g_parameter_YES - check QUANTITY types (QUANTITY, AMOUNT).
1062 -- PO_CORE_S.g_parameter_NO - check AMOUNT types (FIXED PRICE, RATE).
1063 --p_entity_type
1064 --p_column_name
1065 -- Values to use in the error results.
1066 --IN OUT:
1067 --x_results
1068 -- Validation errors will be added to this object.
1069 -- A new object will be created if NULL is passed in.
1070 --OUT:
1071 --x_result_type
1072 -- Indicates if any validations have failed.
1073 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
1074 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
1075 --End of Comments
1076 -------------------------------------------------------------------------------
1077 PROCEDURE gt_zero_order_type_filter(
1078 p_calling_module IN VARCHAR2
1079 , p_value_tbl IN PO_TBL_NUMBER
1080 , p_entity_id_tbl IN PO_TBL_NUMBER
1081 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1082 , p_check_quantity_types_flag IN VARCHAR2
1083 , p_entity_type IN VARCHAR2
1084 , p_column_name IN VARCHAR2
1085 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1086 , x_result_type OUT NOCOPY VARCHAR2
1087 )
1088 IS
1089 d_mod CONSTANT VARCHAR2(100) := D_gt_zero_order_type_filter;
1090
1091 l_input_size NUMBER;
1092 l_count NUMBER;
1093 l_keep_quantity BOOLEAN;
1094 l_quantity_type BOOLEAN;
1095
1096 l_entity_id_tbl PO_TBL_NUMBER;
1097 l_value_tbl PO_TBL_NUMBER;
1098
1099 BEGIN
1100
1101 IF PO_LOG.d_proc THEN
1102 PO_LOG.proc_begin(p_calling_module);
1103 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
1104 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
1105 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
1106 PO_LOG.proc_begin(d_mod,'p_check_quantity_types_flag',p_check_quantity_types_flag);
1107 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
1108 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
1109 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1110 END IF;
1111
1112 IF (p_check_quantity_types_flag = PO_CORE_S.g_parameter_YES) THEN
1113 l_keep_quantity := TRUE;
1114 ELSE
1115 l_keep_quantity := FALSE;
1116 END IF;
1117
1118 l_input_size := p_entity_id_tbl.COUNT;
1119
1120 l_entity_id_tbl := PO_TBL_NUMBER();
1121 l_entity_id_tbl.extend(l_input_size);
1122 l_value_tbl := PO_TBL_NUMBER();
1123 l_value_tbl.extend(l_input_size);
1124
1125 l_count := 0;
1126
1127 FOR i IN 1 .. l_input_size LOOP
1128 IF ( ( NOT l_keep_quantity
1129 AND p_order_type_lookup_code_tbl(i) IN (c_RATE, c_FIXED_PRICE))
1130 OR ( l_keep_quantity
1131 AND p_order_type_lookup_code_tbl(i) NOT IN (c_RATE, c_FIXED_PRICE))
1132 ) THEN
1133 l_count := l_count + 1;
1134 l_entity_id_tbl(l_count) := p_entity_id_tbl(i);
1135 l_value_tbl(l_count) := p_value_tbl(i);
1136 END IF;
1137 END LOOP;
1138
1139 l_entity_id_tbl.trim(l_input_size-l_count);
1140 l_value_tbl.trim(l_input_size-l_count);
1141
1142 PO_VALIDATION_HELPER.greater_than_zero(
1143 p_calling_module => p_calling_module
1144 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1145 , p_value_tbl => l_value_tbl
1146 , p_entity_id_tbl => l_entity_id_tbl
1147 , p_entity_type => p_entity_type
1148 , p_column_name => p_column_name
1149 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
1150 , x_results => x_results
1151 , x_result_type => x_result_type
1152 );
1153
1154 IF PO_LOG.d_proc THEN
1155 PO_LOG.proc_end(d_mod);
1156 PO_LOG.proc_end(p_calling_module);
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 IF PO_LOG.d_exc THEN
1162 PO_LOG.exc(d_mod,0,NULL);
1163 PO_LOG.exc(p_calling_module,0,NULL);
1164 END IF;
1165 RAISE;
1166
1167 END gt_zero_order_type_filter;
1168
1169
1170
1171 -------------------------------------------------------------------------------
1172 --Start of Comments
1173 --Pre-reqs: None.
1174 --Modifies: x_results
1175 --Locks: None.
1176 --Function:
1177 -- Checks that no timecards exist for the specified criteria,
1178 -- and adds an error to x_results if any do.
1179 --Parameters:
1180 --IN:
1181 --p_calling_module
1182 -- The module base of the calling procedure, used for logging.
1183 --p_line_id_tbl
1184 -- The po_line_id of the lines to be checked.
1185 --p_start_date_tbl
1186 -- If not NULL, check only those lines whose existing
1187 -- start date is less than the the specified date.
1188 --p_expiration_date_tbl
1189 -- If not NULL, check only those lines whose existing
1190 -- expiration date is less than the specified date.
1191 --p_column_name
1192 --p_message_name
1193 -- Values to use in the error results.
1194 --IN OUT:
1195 --x_results
1196 -- Validation errors will be added to this object.
1197 -- A new object will be created if NULL is passed in.
1198 --OUT:
1199 --x_result_type
1200 -- Indicates if any validations have failed.
1201 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
1202 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
1203 --End of Comments
1204 -------------------------------------------------------------------------------
1205 PROCEDURE no_timecards_exist(
1206 p_calling_module IN VARCHAR2
1207 , p_line_id_tbl IN PO_TBL_NUMBER
1208 , p_start_date_tbl IN PO_TBL_DATE DEFAULT NULL
1209 , p_expiration_date_tbl IN PO_TBL_DATE DEFAULT NULL
1210 , p_column_name IN VARCHAR2
1211 , p_message_name IN VARCHAR2
1212 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1213 , x_result_type OUT NOCOPY VARCHAR2
1214 )
1215 IS
1216 d_mod CONSTANT VARCHAR2(100) := D_no_timecards_exist;
1217
1218 l_results_count NUMBER;
1219 l_input_count NUMBER;
1220
1221 l_ignore_start_date_flag VARCHAR2(1);
1222 l_ignore_expiration_date_flag VARCHAR2(1);
1223
1224 l_start_date_tbl PO_TBL_DATE;
1225 l_expiration_date_tbl PO_TBL_DATE;
1226
1227 l_data_key NUMBER;
1228 l_line_id_tbl PO_TBL_NUMBER;
1229 l_end_date_tbl PO_TBL_DATE;
1230
1231 l_timecard_exists BOOLEAN;
1232 l_return_status VARCHAR2(10);
1233
1234 BEGIN
1235
1236 IF PO_LOG.d_proc THEN
1237 PO_LOG.proc_begin(p_calling_module);
1238 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1239 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
1240 PO_LOG.proc_begin(d_mod,'p_expiration_date_tbl',p_expiration_date_tbl);
1241 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
1242 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
1243 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1244 END IF;
1245
1246 IF (x_results IS NULL) THEN
1247 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1248 END IF;
1249
1250 l_results_count := x_results.result_type.COUNT;
1251
1252 l_input_count := p_line_id_tbl.COUNT;
1253
1254 IF (p_start_date_tbl IS NULL) THEN
1255 l_ignore_start_date_flag := 'Y';
1256 l_start_date_tbl := PO_TBL_DATE();
1257 l_start_date_tbl.extend(l_input_count);
1258 ELSE
1259 l_ignore_start_date_flag := 'N';
1260 l_start_date_tbl := p_start_date_tbl;
1261 END IF;
1262
1263 IF (p_expiration_date_tbl IS NULL) THEN
1264 l_ignore_expiration_date_flag := 'Y';
1265 l_expiration_date_tbl := PO_TBL_DATE();
1266 l_expiration_date_tbl.extend(l_input_count);
1267 ELSE
1268 l_ignore_expiration_date_flag := 'N';
1269 l_expiration_date_tbl := p_expiration_date_tbl;
1270 END IF;
1271
1272
1273 l_data_key := PO_CORE_S.get_session_gt_nextval();
1274
1275 FORALL i IN 1 .. p_line_id_tbl.COUNT
1276 INSERT INTO PO_SESSION_GT SES
1277 ( key
1278 , num1
1279 , date1
1280 , date2
1281 )
1282 VALUES
1283 ( l_data_key
1284 , p_line_id_tbl(i)
1285 , l_start_date_tbl(i)
1286 , l_expiration_date_tbl(i)
1287 );
1288
1289
1290 -- 1) Line has been saved
1291 -- 2a) New start date is greater than the existing start date
1292 -- 2b) New end date is less than the existing end date
1293 -- 3) Document is an SPO
1294 -- 4) Line is rate-based
1295 SELECT
1296 LINE.po_line_id
1297 , SES.date2
1298 BULK COLLECT INTO
1299 l_line_id_tbl
1300 , l_end_date_tbl
1301 FROM
1302 PO_SESSION_GT SES
1303 , PO_LINES_ALL LINE
1304 , PO_HEADERS_ALL HEADER
1305 WHERE
1306 SES.key = l_data_key
1307 AND LINE.po_line_id = SES.num1
1308 AND HEADER.po_header_id = LINE.po_header_id
1309 AND (l_ignore_start_date_flag = 'Y' OR SES.date1 > LINE.start_date)
1310 AND (l_ignore_expiration_date_flag = 'Y' OR SES.date2 < LINE.expiration_date)
1311 AND HEADER.type_lookup_code = c_STANDARD
1312 AND LINE.order_type_lookup_code = c_RATE
1313 ;
1314
1315 IF PO_LOG.d_stmt THEN
1316 PO_LOG.stmt(d_mod,50,'l_line_id_tbl',l_line_id_tbl);
1317 END IF;
1318
1319 FOR i IN 1 .. l_line_id_tbl.COUNT LOOP
1320
1321 -- Call the OTL API for each of these lines and identify the ones where
1322 -- the submitted or approved timecards exist.
1323 PO_HXC_INTERFACE_PVT.check_timecard_exists(
1324 p_api_version => 1.0
1325 , x_return_status => l_return_status
1326 , p_field_name => PO_HXC_INTERFACE_PVT.g_field_PO_LINE_ID
1327 , p_field_value => l_line_id_tbl(i)
1328 , p_end_date => l_end_date_tbl(i)
1329 , x_timecard_exists => l_timecard_exists
1330 );
1331
1332 IF (NVL(l_return_status,'U') <> 'S') THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335
1336 IF l_timecard_exists THEN
1337
1338 x_results.add_result(
1339 p_entity_type => c_ENTITY_TYPE_LINE
1340 , p_entity_id => l_line_id_tbl(i)
1341 , p_column_name => p_column_name
1342 , p_message_name => p_message_name
1343 );
1344
1345 END IF;
1346
1347 END LOOP;
1348
1349 IF (l_results_count < x_results.result_type.COUNT) THEN
1350 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1351 ELSE
1352 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1353 END IF;
1354
1355 IF PO_LOG.d_proc THEN
1356 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1357 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1358 PO_LOG.proc_end(p_calling_module);
1359 END IF;
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 IF PO_LOG.d_exc THEN
1364 PO_LOG.exc(d_mod,0,NULL);
1365 PO_LOG.exc(p_calling_module,0,NULL);
1366 END IF;
1367 RAISE;
1368
1369 END no_timecards_exist;
1370
1371
1372 -------------------------------------------------------------------------------
1373 --Start of Comments
1374 --Pre-reqs: None.
1375 --Modifies: PO_VALIDATION_RESULTS_GT
1376 --Locks: None.
1377 --Function:
1378 -- Checks for notification controls based on amounts,
1379 -- and adds a warning to the results table if any exist.
1380 --Parameters:
1381 --IN:
1382 --p_calling_module
1383 -- The module base of the calling procedure, used for logging.
1384 --p_line_id_tbl
1385 -- The po_line_id of the lines to be checked.
1386 --p_quantity_tbl
1387 -- If not NULL, checks are only performed for lines
1388 -- where the specified value is different from the
1389 -- current line quantity.
1390 --p_column_name
1391 --p_message_name
1392 -- Values to use in the error results.
1393 --IN OUT:
1394 --x_result_set_id
1395 -- Validation errors will be added to the results table using
1396 -- this identifier.
1397 --OUT:
1398 --x_result_type
1399 -- Indicates if any validations have failed.
1400 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
1401 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
1402 --End of Comments
1403 -------------------------------------------------------------------------------
1404 PROCEDURE amount_notif_ctrl_warning(
1405 p_calling_module IN VARCHAR2
1406 , p_line_id_tbl IN PO_TBL_NUMBER
1407 , p_quantity_tbl IN PO_TBL_NUMBER
1408 , p_column_name IN VARCHAR2
1409 , p_message_name IN VARCHAR2
1410 , x_result_set_id IN OUT NOCOPY NUMBER
1411 , x_result_type OUT NOCOPY VARCHAR2
1412 )
1413 IS
1414 d_mod CONSTANT VARCHAR2(100) := D_amount_notif_ctrl_warning;
1415
1416 l_quantity_flag VARCHAR2(1);
1417 l_quantity_tbl PO_TBL_NUMBER;
1418 BEGIN
1419
1420 IF PO_LOG.d_proc THEN
1421 PO_LOG.proc_begin(p_calling_module);
1422 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1423 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
1424 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
1425 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
1426 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1427 END IF;
1428
1429 IF (x_result_set_id IS NULL) THEN
1430 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1431 END IF;
1432
1433 IF (p_quantity_tbl IS NULL) THEN
1434 l_quantity_flag := 'N';
1435 l_quantity_tbl := PO_TBL_NUMBER();
1436 l_quantity_tbl.extend(p_line_id_tbl.COUNT);
1437 ELSE
1438 l_quantity_flag := 'Y';
1439 l_quantity_tbl := p_quantity_tbl;
1440 END IF;
1441
1442 FORALL i IN 1 .. p_line_id_tbl.COUNT
1443 INSERT INTO PO_VALIDATION_RESULTS_GT
1444 ( result_set_id
1445 , result_type
1446 , entity_type
1447 , entity_id
1448 , column_name
1449 , message_name
1450 )
1451 SELECT
1452 x_result_set_id
1453 , PO_VALIDATIONS.c_result_type_WARNING
1454 , c_ENTITY_TYPE_LINE
1455 , p_line_id_tbl(i)
1456 , p_column_name
1457 , p_message_name
1458 FROM
1459 PO_LINES_ALL PO_LINE
1460 WHERE
1461 PO_LINE.po_line_id = p_line_id_tbl(i)
1462 AND
1463 ( l_quantity_flag <> 'Y'
1464 OR
1465 ( l_quantity_tbl(i) IS NOT NULL
1466 -- Quantity is being changed from the transaction quantity:
1467 AND l_quantity_tbl(i) <> PO_LINE.quantity
1468 )
1469 )
1470 AND EXISTS
1471 ( SELECT NULL
1472 FROM PO_NOTIFICATION_CONTROLS NTF
1473 WHERE
1474 NTF.po_header_id = PO_LINE.po_header_id
1475 AND NTF.notification_condition_code <> c_EXPIRATION
1476 )
1477 ;
1478
1479 IF (SQL%ROWCOUNT > 0) THEN
1480 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1481 ELSE
1482 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1483 END IF;
1484
1485 IF PO_LOG.d_proc THEN
1486 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1487 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1488 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1489 PO_LOG.proc_end(p_calling_module);
1490 END IF;
1491
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 IF PO_LOG.d_exc THEN
1495 PO_LOG.exc(d_mod,0,NULL);
1496 PO_LOG.exc(p_calling_module,0,NULL);
1497 END IF;
1498 RAISE;
1499
1500 END amount_notif_ctrl_warning;
1501
1502
1503 -----------------------------------------------------------------------------
1504 -- Validates that all the numbers of the children entities under the parent
1505 -- are unique.
1506 -- Assumption:
1507 -- All of the unposted child data will be passed in
1508 -- to this routine in order to get accurate results.
1509 -- <Complex Work R12 Start>
1510 -- At the shipment and distribution level, uniqueness can be checked
1511 -- across all ships/dists passed in, or only within those shipments
1512 -- and distributions of the same shipment_type or distribution_type.
1513 -- If you need to check uniqueness within the same shipment
1514 -- or distribution type, then pass in the type in the p_entity_type_tbl
1515 -- parameter for each entity. Pass in NULL for this parameter if you
1516 -- would like to check uniqueness across all shipment/dist types.
1517 -- There is no support for this at the line level; currently, we always check
1518 -- uniqueness of line number across all lines; hence, always pass in NULL for
1519 -- parameter p_entity_type_tbl.
1520 -- <Complex Work R12 End>
1521 -----------------------------------------------------------------------------
1522 PROCEDURE child_num_unique(
1523 p_calling_module IN VARCHAR2
1524 , p_entity_type IN VARCHAR2
1525 , p_entity_id_tbl IN PO_TBL_NUMBER
1526 , p_parent_id_tbl IN PO_TBL_NUMBER
1527 , p_entity_num_tbl IN PO_TBL_NUMBER
1528 , x_result_set_id IN OUT NOCOPY NUMBER
1529 , x_result_type OUT NOCOPY VARCHAR2
1530 , p_entity_type_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL -- <Complex Work R12>
1531 )
1532 IS
1533 d_mod CONSTANT VARCHAR2(100) := D_child_num_unique;
1534
1535 l_data_key NUMBER;
1536 l_parent_id_tbl PO_TBL_NUMBER;
1537
1538 l_column_name VARCHAR2(30);
1539 l_message_name VARCHAR2(30);
1540
1541 BEGIN
1542
1543 IF PO_LOG.d_proc THEN
1544 PO_LOG.proc_begin(p_calling_module);
1545 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
1546 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
1547 PO_LOG.proc_begin(d_mod,'p_parent_id_tbl',p_parent_id_tbl);
1548 PO_LOG.proc_begin(d_mod,'p_entity_num_tbl',p_entity_num_tbl);
1549 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1550 PO_LOG.proc_begin(d_mod,'p_entity_type_tbl',p_entity_type_tbl);
1551 END IF;
1552
1553 IF (x_result_set_id IS NULL) THEN
1554 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1555 END IF;
1556
1557 ----------------------------------------------------------------------
1558 -- In order to check that the child number is unique
1559 -- for a parent, first we need to construct a view of the
1560 -- intended document. In order to do this, we need to get
1561 -- all of the in-memory data into database tables, so that
1562 -- we can filter out old data that should be masked by the
1563 -- newer data in memory.
1564 --
1565 -- The session temp table will be used with the following mapping:
1566 --
1567 -- index_num1 - parent id
1568 -- index_num2 - child id (entity id)
1569 -- num1 - unique child num
1570 -- index_char2 - flag to identify NEW data
1571 -- char1 - entity type <Complex Work R12>
1572 --
1573 -- Actual values:
1574 -- Lines Shipments Distributions
1575 -- ----- --------- -------------
1576 -- index_num1 po_header_id po_line_id line_location_id
1577 -- index_num2 po_line_id line_location_id po_distribution_id
1578 -- num1 line_num shipment_num distribution_num
1579 -- char1 NULL shipment_type distribution_type
1580 --
1581 -- A view of the in-memory and stored data will be constructed
1582 -- as follows:
1583 -- 1. Gather the relevant stored data into the temp table.
1584 -- 2. Merge the in-memory data into the temp table.
1585 -- 3. Check the temp table for the uniqueness criteria.
1586 ----------------------------------------------------------------------
1587
1588 -- Get a distinct list of parent ids.
1589 l_parent_id_tbl := PO_TBL_NUMBER() MULTISET UNION DISTINCT p_parent_id_tbl;
1590
1591 -- Put the stored data for these ids into the temp table.
1592
1593 l_data_key := PO_CORE_S.get_session_gt_nextval();
1594
1595 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_DISTRIBUTION) THEN
1596
1597 l_column_name := c_DISTRIBUTION_NUM;
1598 l_message_name := PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_DIST_NUM;
1599
1600 FORALL i IN 1 .. l_parent_id_tbl.COUNT
1601 INSERT INTO PO_SESSION_GT
1602 ( key
1603 , index_num1
1604 , index_num2
1605 , num1
1606 , char1 -- <Complex Work R12>
1607 )
1608 SELECT
1609 l_data_key
1610 , DIST.line_location_id
1611 , DIST.po_distribution_id
1612 , DIST.distribution_num
1613 , DIST.distribution_type -- <Complex Work R12>
1614 FROM
1615 PO_DISTRIBUTIONS_MERGE_V DIST
1616 WHERE
1617 DIST.line_location_id = l_parent_id_tbl(i)
1618 ;
1619
1620 ELSIF (p_entity_type = PO_VALIDATIONS.c_entity_type_LINE_LOCATION) THEN
1621
1622 l_column_name := c_SHIPMENT_NUM;
1623 l_message_name := PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_SHIP_NUM;
1624
1625 FORALL i IN 1 .. l_parent_id_tbl.COUNT
1626 INSERT INTO PO_SESSION_GT
1627 ( key
1628 , index_num1
1629 , index_num2
1630 , num1
1631 , char1 -- <Complex Work R12>
1632 )
1633 SELECT
1634 l_data_key
1635 , LINE_LOC.po_line_id
1636 , LINE_LOC.line_location_id
1637 , LINE_LOC.shipment_num
1638 , LINE_LOC.shipment_type -- <Complex Work R12>
1639 FROM
1640 PO_LINE_LOCATIONS_MERGE_V LINE_LOC
1641 WHERE
1642 LINE_LOC.po_line_id = l_parent_id_tbl(i)
1643 -- <Complex Work R12>: Don't validate shipment_num
1644 -- on advance pay items, since user cannot set, and
1645 -- because deletions do not propagate to the DB before
1646 -- validation, which means the validation would fail often.
1647 AND ( LINE_LOC.payment_type IS NULL
1648 OR LINE_LOC.payment_type <> 'ADVANCE' )
1649 ;
1650
1651 ELSE -- p_entity_type = PO_VALIDATIONS.c_entity_type_LINE
1652
1653 l_column_name := c_LINE_NUM;
1654 l_message_name := PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_LINE_NUM;
1655
1656 FORALL i IN 1 .. l_parent_id_tbl.COUNT
1657 INSERT INTO PO_SESSION_GT
1658 ( key
1659 , index_num1
1660 , index_num2
1661 , num1
1662 , char1 -- <Complex Work R12>
1663 )
1664 SELECT
1665 l_data_key
1666 , LINE.po_header_id
1667 , LINE.po_line_id
1668 , LINE.line_num
1669 , NULL -- <Complex Work R12>
1670 FROM
1671 PO_LINES_MERGE_V LINE
1672 WHERE
1673 LINE.po_header_id = l_parent_id_tbl(i)
1674 ;
1675
1676 END IF;
1677
1678 -- Merge in the new data.
1679
1680 FORALL i IN 1 .. p_entity_id_tbl.COUNT
1681 MERGE INTO PO_SESSION_GT SES
1682 USING DUAL
1683 ON
1684 ( SES.key = l_data_key
1685 AND SES.index_num2 = p_entity_id_tbl(i)
1686 )
1687 WHEN MATCHED THEN UPDATE SET
1688 SES.index_num1 = p_parent_id_tbl(i),
1689 SES.num1 = p_entity_num_tbl(i),
1690 SES.index_char2 = c_NEW
1691 WHEN NOT MATCHED THEN INSERT
1692 ( key
1693 , index_num1
1694 , index_num2
1695 , num1
1696 , index_char2
1697 )
1698 VALUES
1699 ( l_data_key
1700 , p_parent_id_tbl(i)
1701 , p_entity_id_tbl(i)
1702 , p_entity_num_tbl(i)
1703 , c_NEW
1704 );
1705
1706 -- <Complex Work R12 Start>: update session gt with p_entity_type_tbl values
1707 IF (p_entity_type_tbl IS NOT NULL) THEN
1708
1709 FORALL I IN 1..p_entity_id_tbl.COUNT
1710 UPDATE PO_SESSION_GT SES
1711 SET SES.char1 = p_entity_type_tbl(i)
1712 WHERE SES.key = l_data_key
1713 AND SES.index_num2 = p_entity_id_tbl(i);
1714
1715 END IF;
1716 -- <Complex Work R12 End>
1717
1718 -- Check that the child number is unique across the parent.
1719 --<Bug#4586236 Start>
1720 --Added a decode around char1 so that the appropriate message gets inserted into
1721 --PO_VALIDATION_RESULTS_GT.message_name column
1722 --If it is null we would would go with the default message populated in l_message_name
1723 INSERT INTO PO_VALIDATION_RESULTS_GT
1724 ( result_set_id
1725 , entity_type
1726 , entity_id
1727 , column_name
1728 , column_val
1729 , message_name
1730 )
1731 SELECT
1732 x_result_set_id
1733 , p_entity_type
1734 , CHILD.index_num2
1735 , l_column_name
1736 , TO_CHAR(CHILD.num1)
1737 , decode(char1,
1738 PO_CONSTANTS_SV.SHIP_TYPE_PRICE_BREAK, PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_PRC_BRK_NUM,
1739 l_message_name)
1740 FROM
1741 PO_SESSION_GT CHILD
1742 WHERE
1743 CHILD.key = l_data_key
1744 AND CHILD.index_char2 = c_NEW
1745 AND EXISTS
1746 ( SELECT null
1747 FROM PO_SESSION_GT SIBLING
1748 WHERE
1749 SIBLING.key = l_data_key
1750 AND SIBLING.index_num1 = CHILD.index_num1 -- parent id
1751 AND SIBLING.num1 = CHILD.num1 -- child num
1752 AND SIBLING.index_num2 <> CHILD.index_num2 -- child id
1753 -- <Complex Work R12 Start>: if using filter, check that char1s match
1754 AND ((p_entity_type_tbl IS NULL) OR (SIBLING.char1 = CHILD.char1))
1755 -- <Complex Work R12 End>
1756 )
1757 ;
1758
1759 IF (SQL%ROWCOUNT > 0) THEN
1760 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1761 ELSE
1762 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1763 END IF;
1764
1765 IF PO_LOG.d_proc THEN
1766 PO_LOG.stmt_session_gt(d_mod,9,l_data_key);
1767 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1768 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1769 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1770 PO_LOG.proc_end(p_calling_module);
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 IF PO_LOG.d_exc THEN
1776 PO_LOG.exc(d_mod,0,NULL);
1777 PO_LOG.exc(p_calling_module,0,NULL);
1778 END IF;
1779 RAISE;
1780
1781 END child_num_unique;
1782
1783
1784
1785 -----------------------------------------------------------------------------
1786 -- Validates that all the numbers of the children entities under the parent
1787 -- are unique.
1788 -- Assumption:
1789 -- All of the unposted child data will be passed in
1790 -- to this routine in order to get accurate results.
1791 -----------------------------------------------------------------------------
1792 PROCEDURE price_diff_value_unique(
1793 p_calling_module IN VARCHAR2
1794 , p_price_diff_id_tbl IN PO_TBL_NUMBER
1795 , p_entity_id_tbl IN PO_TBL_NUMBER
1796 , p_entity_type_tbl IN PO_TBL_VARCHAR30
1797 , p_unique_value_tbl IN PO_TBL_VARCHAR4000
1798 , p_column_name IN VARCHAR2
1799 , p_message_name IN VARCHAR2
1800 , x_result_set_id IN OUT NOCOPY NUMBER
1801 , x_result_type OUT NOCOPY VARCHAR2
1802 )
1803 IS
1804 d_mod CONSTANT VARCHAR2(100) := D_price_diff_value_unique;
1805
1806 l_data_key NUMBER;
1807 l_parent_id_tbl PO_TBL_NUMBER;
1808
1809 BEGIN
1810
1811 IF PO_LOG.d_proc THEN
1812 PO_LOG.proc_begin(p_calling_module);
1813 PO_LOG.proc_begin(d_mod,'p_price_diff_id_tbl',p_price_diff_id_tbl);
1814 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
1815 PO_LOG.proc_begin(d_mod,'p_entity_type_tbl',p_entity_type_tbl);
1816 PO_LOG.proc_begin(d_mod,'p_unique_value_tbl',p_unique_value_tbl);
1817 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
1818 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
1819 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1820 END IF;
1821
1822 IF (x_result_set_id IS NULL) THEN
1823 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1824 END IF;
1825
1826 ----------------------------------------------------------------------
1827 -- See the discussion in child_num_unique for the
1828 -- method in which uniqueness is determined.
1829 --
1830 -- The session temp table will be used with the following mapping
1831 -- for Price Differentials:
1832 --
1833 -- index_num1 - parent id - entity_id
1834 -- index_char1 - parent type - entity_type
1835 -- index_num2 - child id - price_differential_id
1836 -- char1 - unique child data - price_differential_num or price_type
1837 -- index_char2 - flag to identify NEW data
1838 --
1839 ----------------------------------------------------------------------
1840
1841 -- Get a distinct list of parent ids.
1842 l_parent_id_tbl := PO_TBL_NUMBER() MULTISET UNION DISTINCT p_entity_id_tbl;
1843
1844 -- Put the stored data for these ids into the temp table.
1845
1846 l_data_key := PO_CORE_S.get_session_gt_nextval();
1847
1848 FORALL i IN 1 .. l_parent_id_tbl.COUNT
1849 INSERT INTO PO_SESSION_GT
1850 ( key
1851 , index_num1
1852 , index_char1
1853 , index_num2
1854 , char1
1855 )
1856 SELECT
1857 l_data_key
1858 , PRICE_DIFF.entity_id
1859 , PRICE_DIFF.entity_type
1860 , PRICE_DIFF.price_differential_id
1861 , DECODE(p_column_name
1862 , c_PRICE_TYPE, PRICE_DIFF.price_type
1863 , TO_CHAR(PRICE_DIFF.price_differential_num)
1864 )
1865 FROM
1866 PO_PRICE_DIFF_MERGE_V PRICE_DIFF
1867 WHERE
1868 PRICE_DIFF.entity_id = l_parent_id_tbl(i)
1869 ;
1870
1871 -- Merge in the new data.
1872
1873 FORALL i IN 1 .. p_price_diff_id_tbl.COUNT
1874 MERGE INTO PO_SESSION_GT SES
1875 USING DUAL
1876 ON
1877 ( SES.key = l_data_key
1878 AND SES.index_num2 = p_price_diff_id_tbl(i)
1879 )
1880 WHEN MATCHED THEN UPDATE SET
1881 SES.index_num1 = p_entity_id_tbl(i)
1882 , SES.index_char1 = p_entity_type_tbl(i)
1883 , SES.char1 = p_unique_value_tbl(i)
1884 , SES.index_char2 = c_NEW
1885 WHEN NOT MATCHED THEN INSERT
1886 ( key
1887 , index_num1
1888 , index_char1
1889 , index_num2
1890 , char1
1891 , index_char2
1892 )
1893 VALUES
1894 ( l_data_key
1895 , p_entity_id_tbl(i)
1896 , p_entity_type_tbl(i)
1897 , p_price_diff_id_tbl(i)
1898 , p_unique_value_tbl(i)
1899 , c_NEW
1900 );
1901
1902
1903 -- Check that the child number is unique across the parent.
1904 INSERT INTO PO_VALIDATION_RESULTS_GT
1905 ( result_set_id
1906 , entity_type
1907 , entity_id
1908 , column_name
1909 , column_val
1910 , message_name
1911 , token1_name --bug #4956116
1912 , token1_value --bug #4956116
1913 )
1914 SELECT
1915 x_result_set_id
1916 , PO_VALIDATIONS.c_entity_type_PRICE_DIFF
1917 , CHILD.index_num2
1918 , p_column_name
1919 , CHILD.char1
1920 , p_message_name
1921 , p_column_name --bug #4956116
1922 , (select displayed_field from po_lookup_codes where lookup_code = CHILD.char1 and lookup_type = 'PRICE DIFFERENTIALS') --bug #4956116
1923 FROM
1924 PO_SESSION_GT CHILD
1925 WHERE
1926 CHILD.key = l_data_key
1927 AND CHILD.index_char2 = c_NEW
1928 AND EXISTS
1929 ( SELECT null
1930 FROM PO_SESSION_GT SIBLING
1931 WHERE
1932 SIBLING.key = l_data_key
1933 AND SIBLING.index_num1 = CHILD.index_num1 -- entity_id
1934 AND SIBLING.index_char1 = CHILD.index_char1 -- entity_type
1935 AND SIBLING.index_num2 <> CHILD.index_num2 -- price_differential_id
1936 AND SIBLING.char1 = CHILD.char1 -- price_differential_num or price_type
1937 )
1938 ;
1939
1940 IF (SQL%ROWCOUNT > 0) THEN
1941 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1942 ELSE
1943 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1944 END IF;
1945
1946 IF PO_LOG.d_proc THEN
1947 PO_LOG.stmt_session_gt(d_mod,9,l_data_key);
1948 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1949 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1950 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1951 PO_LOG.proc_end(p_calling_module);
1952 END IF;
1953
1954 EXCEPTION
1955 WHEN OTHERS THEN
1956 IF PO_LOG.d_exc THEN
1957 PO_LOG.exc(d_mod,0,NULL);
1958 PO_LOG.exc(p_calling_module,0,NULL);
1959 END IF;
1960 RAISE;
1961
1962 END price_diff_value_unique;
1963
1964
1965 -------------------------------------------------------------------
1966 -- Validates that the specified start date is less than or equal to
1967 -- the specified end date.
1968 --p_column_val_selector
1969 -- Used to indicate which value should be recorded in the
1970 -- column_val field of the result.
1971 -- c_START_DATE - value from p_start_date_tbl
1972 -- c_END_DATE - value from p_end_date_tbl
1973 -- NULL - null
1974 -------------------------------------------------------------------
1975 PROCEDURE start_date_le_end_date(
1976 p_calling_module IN VARCHAR2
1977 , p_start_date_tbl IN PO_TBL_DATE
1978 , p_end_date_tbl IN PO_TBL_DATE
1979 , p_entity_id_tbl IN PO_TBL_NUMBER
1980 , p_entity_type IN VARCHAR2
1981 , p_column_name IN VARCHAR2
1982 , p_column_val_selector IN VARCHAR2
1983 , p_message_name IN VARCHAR2
1984 , p_validation_id IN NUMBER DEFAULT NULL
1985 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1986 , x_result_type OUT NOCOPY VARCHAR2
1987 )
1988 IS
1989 d_mod CONSTANT VARCHAR2(100) := D_start_date_le_end_date;
1990
1991 l_results_count NUMBER;
1992 l_column_val DATE;
1993 BEGIN
1994
1995 IF PO_LOG.d_proc THEN
1996 PO_LOG.proc_begin(p_calling_module);
1997 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
1998 PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
1999 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
2000 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2001 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
2002 PO_LOG.proc_begin(d_mod,'p_column_val_selector',p_column_val_selector);
2003 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
2004 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2005 END IF;
2006
2007 IF (x_results IS NULL) THEN
2008 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2009 END IF;
2010
2011 l_results_count := x_results.result_type.COUNT;
2012
2013 FOR i IN 1 .. p_start_date_tbl.COUNT LOOP
2014 IF (p_start_date_tbl(i) > p_end_date_tbl(i)) THEN
2015
2016 IF (p_column_val_selector = c_START_DATE) THEN
2017 l_column_val := p_start_date_tbl(i);
2018 ELSIF (p_column_val_selector = c_END_DATE) THEN
2019 l_column_val := p_end_date_tbl(i);
2020 ELSE
2021 l_column_val := NULL;
2022 END IF;
2023
2024 x_results.add_result(
2025 p_entity_type => p_entity_type
2026 , p_entity_id => p_entity_id_tbl(i)
2027 , p_column_name => p_column_name
2028 , p_column_val => TO_CHAR(l_column_val)
2029 , p_message_name => p_message_name
2030 , p_validation_id => p_validation_id
2031 );
2032
2033 END IF;
2034 END LOOP;
2035
2036 IF (l_results_count < x_results.result_type.COUNT) THEN
2037 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2038 ELSE
2039 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2040 END IF;
2041
2042 IF PO_LOG.d_proc THEN
2043 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2044 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2045 PO_LOG.proc_end(p_calling_module);
2046 END IF;
2047
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 IF PO_LOG.d_exc THEN
2051 PO_LOG.exc(d_mod,0,NULL);
2052 PO_LOG.exc(p_calling_module,0,NULL);
2053 END IF;
2054 RAISE;
2055
2056 END start_date_le_end_date;
2057
2058 -------------------------------------------------------------------
2059 -- Validates that the specified number num1 is less than or equal
2060 -- to num2.
2061 -------------------------------------------------------------------
2062 PROCEDURE num1_less_or_equal_num2(
2063 p_calling_module IN VARCHAR2
2064 , p_num1_tbl IN PO_TBL_NUMBER
2065 , p_num2_tbl IN PO_TBL_NUMBER
2066 , p_entity_id_tbl IN PO_TBL_NUMBER
2067 , p_entity_type IN VARCHAR2
2068 , p_column_name IN VARCHAR2
2069 , p_message_name IN VARCHAR2
2070 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2071 , x_result_type OUT NOCOPY VARCHAR2
2072 )
2073 IS
2074 d_mod CONSTANT VARCHAR2(100) := D_num1_less_or_equal_num2;
2075 l_results_count NUMBER;
2076 BEGIN
2077
2078 IF PO_LOG.d_proc THEN
2079 PO_LOG.proc_begin(p_calling_module);
2080 PO_LOG.proc_begin(d_mod,'p_num1_tbl',p_num1_tbl);
2081 PO_LOG.proc_begin(d_mod,'p_num2_tbl',p_num2_tbl);
2082 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
2083 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2084 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
2085 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
2086 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2087 END IF;
2088
2089 IF (x_results IS NULL) THEN
2090 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2091 END IF;
2092
2093 l_results_count := x_results.result_type.COUNT;
2094
2095 FOR i IN 1 .. p_entity_id_tbl.COUNT LOOP
2096 IF (p_num1_tbl(i) > p_num2_tbl(i)) THEN
2097 x_results.add_result(
2098 p_entity_type => p_entity_type
2099 , p_entity_id => p_entity_id_tbl(i)
2100 , p_column_name => p_column_name
2101 , p_message_name => p_message_name
2102 );
2103 END IF;
2104 END LOOP;
2105
2106 IF (l_results_count < x_results.result_type.COUNT) THEN
2107 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2108 ELSE
2109 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2110 END IF;
2111
2112 IF PO_LOG.d_proc THEN
2113 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2114 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2115 PO_LOG.proc_end(p_calling_module);
2116 END IF;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 IF PO_LOG.d_exc THEN
2121 PO_LOG.exc(d_mod,0,NULL);
2122 PO_LOG.exc(p_calling_module,0,NULL);
2123 END IF;
2124 RAISE;
2125
2126 END num1_less_or_equal_num2;
2127
2128
2129 -------------------------------------------------------------------------------
2130 --Start of Comments
2131 --Pre-reqs: None.
2132 --Modifies: x_results
2133 --Locks: None.
2134 --Function:
2135 -- Checks that each value is zero
2136 -- and adds an error to x_results if it isn't.
2137 --Parameters:
2138 --IN:
2139 --p_calling_module
2140 -- The module base of the calling procedure, used for logging.
2141 --p_value_tbl
2142 -- The values to be checked.
2143 --p_entity_id_tbl
2144 -- The entity id's corresponding to the values to be checked.
2145 --p_entity_type
2146 --p_column_name
2147 --p_message_name
2148 -- Values to use in the error results.
2149 --IN OUT:
2150 --x_results
2151 -- Validation errors will be added to this object.
2152 -- A new object will be created if NULL is passed in.
2153 --OUT:
2154 --x_result_type
2155 -- Indicates if any validations have failed.
2156 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
2157 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
2158 --End of Comments
2159 -------------------------------------------------------------------------------
2160 PROCEDURE zero(p_calling_module IN VARCHAR2,
2161 p_value_tbl IN PO_TBL_NUMBER,
2162 p_entity_id_tbl IN PO_TBL_NUMBER,
2163 p_entity_type IN VARCHAR2,
2164 p_column_name IN VARCHAR2,
2165 p_message_name IN VARCHAR2,
2166 p_token1_name IN VARCHAR2 DEFAULT NULL,
2167 p_token1_value IN VARCHAR2 DEFAULT NULL,
2168 p_token2_name IN VARCHAR2 DEFAULT NULL,
2169 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
2170 p_validation_id IN NUMBER DEFAULT NULL,
2171 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2172 x_result_type OUT NOCOPY VARCHAR2) IS
2173
2174 d_mod CONSTANT VARCHAR2(100) := D_zero;
2175 l_results_count NUMBER;
2176 l_token2_value_tbl PO_TBL_VARCHAR4000;
2177
2178 BEGIN
2179
2180 IF PO_LOG.d_proc THEN
2181 PO_LOG.proc_begin(p_calling_module);
2182 PO_LOG.proc_begin(d_mod, 'p_value_tbl', p_value_tbl);
2183 PO_LOG.proc_begin(d_mod, 'p_entity_id_tbl', p_entity_id_tbl);
2184 PO_LOG.proc_begin(d_mod, 'p_entity_type', p_entity_type);
2185 PO_LOG.proc_begin(d_mod, 'p_column_name', p_column_name);
2186 PO_LOG.proc_begin(d_mod, 'p_message_name', p_message_name);
2187 PO_LOG.proc_begin(d_mod, 'p_token1_name', p_token1_name);
2188 PO_LOG.proc_begin(d_mod, 'p_token1_value', p_token1_value);
2189 PO_LOG.proc_begin(d_mod, 'p_token2_name', p_token2_name);
2190 PO_LOG.proc_begin(d_mod, 'p_token2_value_tbl', p_token2_value_tbl);
2191 PO_LOG.log(PO_LOG.c_PROC_BEGIN, d_mod, NULL, 'x_results', x_results);
2192 END IF;
2193
2194 IF (x_results IS NULL) THEN
2195 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2196 END IF;
2197
2198 IF (p_token2_value_tbl IS NULL) THEN
2199 l_token2_value_tbl := PO_TBL_VARCHAR4000();
2200 l_token2_value_tbl.extend(p_value_tbl.COUNT);
2201 ELSE
2202 l_token2_value_tbl := p_token2_value_tbl;
2203 END IF;
2204
2205 l_results_count := x_results.result_type.COUNT;
2206
2207 IF PO_LOG.d_stmt THEN
2208 PO_LOG.stmt(d_mod, 0, 'l_results_count', l_results_count);
2209 END IF;
2210
2211 FOR i IN 1 .. p_value_tbl.COUNT LOOP
2212 IF (p_value_tbl(i) <> 0) THEN
2213 x_results.add_result(p_entity_type => p_entity_type,
2214 p_entity_id => p_entity_id_tbl(i),
2215 p_column_name => p_column_name,
2216 p_column_val => p_value_tbl(i),
2217 p_message_name => p_message_name,
2218 p_token1_name => p_token1_name,
2219 p_token1_value => p_token1_value,
2220 p_token2_name => p_token2_name,
2221 p_token2_value => l_token2_value_tbl(i),
2222 p_validation_id => p_validation_id);
2223 END IF;
2224 END LOOP;
2225
2226 IF (l_results_count < x_results.result_type.COUNT) THEN
2227 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2228 ELSE
2229 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2230 END IF;
2231
2232 IF PO_LOG.d_proc THEN
2233 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
2234 PO_LOG.log(PO_LOG.c_PROC_END, d_mod, NULL, 'x_results', x_results);
2235 PO_LOG.proc_end(p_calling_module);
2236 END IF;
2237
2238 EXCEPTION
2239 WHEN OTHERS THEN
2240 IF PO_LOG.d_exc THEN
2241 PO_LOG.exc(d_mod, 0, NULL);
2242 PO_LOG.exc(p_calling_module, 0, NULL);
2243 END IF;
2244 RAISE;
2245
2246 END zero;
2247
2248 -------------------------------------------------------------------------------
2249 --Start of Comments
2250 --Pre-reqs: None.
2251 --Modifies: PO_VALIDATION_RESULTS_GT
2252 --Locks: None.
2253 --Function:
2254 -- Validates the terms_id agains ap_terms
2255 -- and adds an error to the results table if it's invalid.
2256 --Parameters:
2257 --IN:
2258 --p_calling_module
2259 -- The module base of the calling procedure, used for logging.
2260 --p_terms_id_tbl
2261 -- terms_id
2262 --p_entity_id_tbl
2263 -- The entity id's corresponding to the values to be checked.
2264 --p_entity_type
2265 --IN OUT:
2266 --x_result_set_id
2267 -- Validation errors will be added to the results table using
2268 -- this identifier.
2269 --OUT:
2270 --x_result_type
2271 -- Indicates if any validations have failed.
2272 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
2273 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
2274 --End of Comments
2275 -------------------------------------------------------------------------------
2276 PROCEDURE terms_id(p_calling_module IN VARCHAR2,
2277 p_terms_id_tbl IN PO_TBL_NUMBER,
2278 p_entity_id_tbl IN PO_TBL_NUMBER,
2279 p_entity_type IN VARCHAR2,
2280 p_validation_id IN NUMBER DEFAULT NULL,
2281 x_result_set_id IN OUT NOCOPY NUMBER,
2282 x_result_type OUT NOCOPY VARCHAR2) IS
2283
2284 d_mod CONSTANT VARCHAR2(100) := D_terms_id;
2285
2286 BEGIN
2287 IF x_result_set_id IS NULL THEN
2288 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2289 END IF;
2290
2291 IF PO_LOG.d_proc THEN
2292 PO_LOG.proc_begin(p_calling_module);
2293 PO_LOG.proc_begin(d_mod, 'p_terms_id_tbl', p_terms_id_tbl);
2294 PO_LOG.proc_begin(d_mod, 'p_entity_id_tbl', p_entity_id_tbl);
2295 PO_LOG.proc_begin(d_mod, 'p_entity_type', p_entity_type);
2296 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2297 END IF;
2298
2299 FORALL i IN 1 .. p_entity_id_tbl.COUNT
2300 INSERT INTO PO_VALIDATION_RESULTS_GT
2301 (result_set_id,
2302 result_type,
2303 entity_type,
2304 entity_id,
2305 message_name,
2306 column_name,
2307 column_val,
2308 token1_name,
2309 token1_value,
2310 validation_id)
2311 SELECT x_result_set_id,
2312 PO_VALIDATIONS.c_result_type_FAILURE,
2313 p_entity_type,
2314 p_entity_id_tbl(i),
2315 'PO_PDOI_INVALID_PAY_TERMS',
2316 'TERMS_ID',
2317 p_terms_id_tbl(i),
2318 'VALUE',
2319 p_terms_id_tbl(i),
2320 p_validation_id
2321 FROM DUAL
2322 WHERE p_terms_id_tbl(i) IS NOT NULL
2323 AND NOT EXISTS
2324 (SELECT 1
2325 FROM AP_TERMS APT
2326 WHERE p_terms_id_tbl(i) = APT.TERM_ID
2327 AND sysdate BETWEEN
2328 nvl(APT.start_date_active, sysdate - 1) AND
2329 nvl(APT.end_date_active, sysdate + 1)); -- END WHERE, FORALL
2330
2331 IF (SQL%ROWCOUNT > 0) THEN
2332 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2333 ELSE
2334 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2335 END IF;
2336
2337 IF PO_LOG.d_proc THEN
2338 PO_VALIDATIONS.log_validation_results_gt(d_mod, 9, x_result_set_id);
2339 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
2340 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2341 PO_LOG.proc_end(p_calling_module);
2342 END IF;
2343
2344 EXCEPTION
2345 WHEN OTHERS THEN
2346 IF PO_LOG.d_exc THEN
2347 PO_LOG.exc(d_mod, 0, NULL);
2348 PO_LOG.exc(p_calling_module, 0, NULL);
2349 END IF;
2350 RAISE;
2351
2352 END terms_id;
2353
2354 ---------------------------------------------------------------------------
2355 -- OPM Integration R12
2356 -- Start of Comments
2357 --Pre-reqs: None.
2358 --Modifies: x_results
2359 --Locks: None.
2360 --Function:
2361 -- Checks that each value is greater than zero and not null,
2362 -- and adds an error to x_results if it is not.
2363 -- The check is only performed for opm dual uom items
2364 --Parameters:
2365 --IN:
2366 --p_calling_module
2367 -- The module base of the calling procedure, used for logging.
2368 --p_value_tbl
2369 -- The values to be checked.
2370 --p_entity_id_tbl
2371 -- The entity id's corresponding to the values to be checked.
2372 --p_sec_default_ind_tbl
2373 -- The secondary default indicator of the corresponding value.
2374 --p_entity_type
2375 --p_column_name
2376 -- Values to use in the error results.
2377 --IN OUT:
2378 --x_results
2379 -- Validation errors will be added to this object.
2380 -- A new object will be created if NULL is passed in.
2381 --OUT:
2382 --x_result_type
2383 -- Indicates if any validations have failed.
2384 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
2385 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
2386 --End of Comments
2387 -------------------------------------------------------------------------------
2388 PROCEDURE gt_zero_opm_filter(
2389 p_calling_module IN VARCHAR2
2390 , p_value_tbl IN PO_TBL_NUMBER
2391 , p_entity_id_tbl IN PO_TBL_NUMBER
2392 , p_item_id_tbl IN PO_TBL_NUMBER
2393 , p_inv_org_id_tbl IN PO_TBL_NUMBER
2394 , p_entity_type IN VARCHAR2
2395 , p_column_name IN VARCHAR2
2396 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2397 , x_result_type OUT NOCOPY VARCHAR2
2398 )
2399 IS
2400 d_mod CONSTANT VARCHAR2(100) := D_gt_zero_opm_filter;
2401
2402 l_input_size NUMBER;
2403 l_count NUMBER;
2404 l_entity_id_tbl PO_TBL_NUMBER;
2405 l_value_tbl PO_TBL_NUMBER;
2406
2407 l_sec_default_ind VARCHAR2(1);
2408
2409 BEGIN
2410
2411 IF PO_LOG.d_proc THEN
2412 PO_LOG.proc_begin(p_calling_module);
2413 PO_LOG.proc_begin(d_mod,'p_value_tbl',p_value_tbl);
2414 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
2415 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2416 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
2417 PO_LOG.proc_begin(d_mod,'p_inv_org_id_tbl',p_inv_org_id_tbl);
2418 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
2419 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2420 END IF;
2421
2422 l_input_size := p_entity_id_tbl.COUNT;
2423
2424 l_entity_id_tbl := PO_TBL_NUMBER();
2425 l_entity_id_tbl.extend(l_input_size);
2426 l_value_tbl := PO_TBL_NUMBER();
2427 l_value_tbl.extend(l_input_size);
2428
2429 l_count := 0;
2430
2431 IF p_item_id_tbl is null OR
2432 p_inv_org_id_tbl is null
2433 THEN
2434 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2435 RETURN;
2436 END IF;
2437
2438 FOR i IN 1 .. l_input_size LOOP
2439 Begin
2440 -- SQL What : Get the sec indicator
2441 -- SQL Why : To check if we need to do opm validations
2442 SELECT decode(msi.tracking_quantity_ind,'PS',msi.secondary_default_ind,NULL)
2443 INTO l_sec_default_ind
2444 FROM mtl_system_items msi
2445 WHERE msi.organization_id = p_inv_org_id_tbl(i)
2446 AND msi.inventory_item_id = p_item_id_tbl(i);
2447 Exception
2448 When no_data_found then
2449 l_sec_default_ind := null;
2450 End;
2451
2452 IF ( l_sec_default_ind is not null)
2453 THEN
2454 l_count := l_count + 1;
2455 l_entity_id_tbl(l_count) := p_entity_id_tbl(i);
2456 l_value_tbl(l_count) := p_value_tbl(i);
2457 END IF;
2458 END LOOP;
2459
2460 l_entity_id_tbl.trim(l_input_size-l_count);
2461 l_value_tbl.trim(l_input_size-l_count);
2462
2463 PO_VALIDATION_HELPER.greater_than_zero(
2464 p_calling_module => p_calling_module
2465 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
2466 , p_value_tbl => l_value_tbl
2467 , p_entity_id_tbl => l_entity_id_tbl
2468 , p_entity_type => p_entity_type
2469 , p_column_name => p_column_name
2470 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
2471 , x_results => x_results
2472 , x_result_type => x_result_type
2473 );
2474
2475 IF PO_LOG.d_proc THEN
2476 PO_LOG.proc_end(d_mod);
2477 PO_LOG.proc_end(p_calling_module);
2478 END IF;
2479
2480 EXCEPTION
2481 WHEN OTHERS THEN
2482 IF PO_LOG.d_exc THEN
2483 PO_LOG.exc(d_mod,0,NULL);
2484 PO_LOG.exc(p_calling_module,0,NULL);
2485 END IF;
2486 RAISE;
2487
2488 END gt_zero_opm_filter;
2489
2490 -------------------------------------------------------------------------------
2491 -- OPM Integration R12
2492 --Start of Comments
2493 --Pre-reqs: None.
2494 --Modifies: x_results
2495 --Locks: None.
2496 --Function:
2497 -- Checks if the quantity and secondary quantity within deviation if both are provided
2498 -- and the secondary default indicator is 'N'
2499 --Parameters:
2500 --IN:
2501 --p_calling_module
2502 -- The module base of the calling procedure, used for logging.
2503 --p_entity_id_tbl
2504 -- The po_line_id of the lines to be checked.
2505 --p_item_id_tbl
2506 -- item id
2507 --p_ship_to_org_id_tbl
2508 -- Ship To Organization
2509 --p_quantity_tbl
2510 -- primary qty
2511 --p_secondary_qty_tbl
2512 -- Secondary Qty
2513 --p_primary_uom_tbl
2514 -- primary uom
2515 --p_secondary_uom_tbl
2516 -- Secondary uom
2517 --p_sec_default_ind_tbl
2518 -- Determines if the item is opm dual uom contrilled item
2519 --p_column_name
2520 --IN OUT:
2521 --x_results
2522 -- Validation errors will be added to this object.
2523 -- A new object will be created if NULL is passed in.
2524 --OUT:
2525 --x_result_type
2526 -- Indicates if any validations have failed.
2527 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
2528 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
2529 --End of Comments
2530 -------------------------------------------------------------------------------
2531 PROCEDURE qtys_within_deviation(
2532 p_calling_module IN VARCHAR2
2533 , p_entity_id_tbl IN PO_TBL_NUMBER
2534 , p_item_id_tbl IN PO_TBL_NUMBER
2535 , p_inv_org_id_tbl IN PO_TBL_NUMBER
2536 , p_quantity_tbl IN PO_TBL_NUMBER
2537 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
2538 , p_sec_quantity_tbl IN PO_TBL_NUMBER
2539 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
2540 , p_column_name IN VARCHAR2
2541 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2542 , x_result_type OUT NOCOPY VARCHAR2
2543 )
2544 IS
2545 d_mod CONSTANT VARCHAR2(100) := D_qtys_within_deviation;
2546
2547 l_results_count NUMBER;
2548 l_input_count NUMBER;
2549 l_quantity BOOLEAN;
2550 l_return_status VARCHAR2(10);
2551 l_msg_data VARCHAR2(2000);
2552 l_api_error_msg VARCHAR2(2000);
2553 l_wrapper_error_msg VARCHAR2(2000);
2554
2555 l_sec_default_ind VARCHAR2(1);
2556
2557 BEGIN
2558
2559 IF PO_LOG.d_proc THEN
2560 PO_LOG.proc_begin(p_calling_module);
2561 PO_LOG.proc_begin(d_mod,'p_entity_id_tbl',p_entity_id_tbl);
2562 PO_LOG.proc_begin(d_mod,'p_column_name',p_column_name);
2563 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2564 END IF;
2565
2566 IF (x_results IS NULL) THEN
2567 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2568 END IF;
2569
2570 l_results_count := x_results.result_type.COUNT;
2571
2572 l_input_count := p_entity_id_tbl.COUNT;
2573
2574 IF p_item_id_tbl is null OR
2575 p_inv_org_id_tbl is null
2576 THEN
2577 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2578 RETURN;
2579 END IF;
2580
2581 FOR i IN 1 .. l_input_count LOOP
2582 Begin
2583 -- SQL What : Get the sec indicator
2584 -- SQL Why : To check if we need to do opm validations
2585 SELECT decode(msi.tracking_quantity_ind,'PS',msi.secondary_default_ind,NULL)
2586 INTO l_sec_default_ind
2587 FROM mtl_system_items msi
2588 WHERE msi.organization_id = p_inv_org_id_tbl(i)
2589 AND msi.inventory_item_id = p_item_id_tbl(i);
2590 Exception
2591 When no_data_found then
2592 l_sec_default_ind := null;
2593 End;
2594
2595 IF l_sec_default_ind IS NOT NULL AND p_quantity_tbl(i) IS NOT NULL AND
2596 p_sec_quantity_tbl(i) IS NOT NULL
2597 THEN
2598 -- Call the INV API to validate dual quantities
2599 PO_INV_INTEGRATION_GRP.within_deviation(
2600 p_api_version => 1.0,
2601 p_organization_id => p_inv_org_id_tbl(i),
2602 p_item_id => p_item_id_tbl(i),
2603 p_pri_quantity => p_quantity_tbl(i),
2604 p_sec_quantity => p_sec_quantity_tbl(i),
2605 p_pri_unit_of_measure => p_primary_uom_tbl(i),
2606 p_sec_unit_of_measure => p_secondary_uom_tbl(i),
2607 x_return_status => l_return_status,
2608 x_msg_data => l_msg_data);
2609
2610 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2611 THEN
2612 l_api_error_msg := fnd_msg_pub.get(1,'F');
2613 FND_MESSAGE.set_name('PO', 'PO_WRAPPER_MESSAGE');
2614 FND_MESSAGE.set_token('MESSAGE', l_api_error_msg);
2615 l_wrapper_error_msg := FND_MESSAGE.get_string('PO', 'PO_WRAPPER_MESSAGE');
2616
2617 x_results.add_result(
2618 p_entity_type => c_ENTITY_TYPE_LINE,
2619 p_entity_id => p_entity_id_tbl(i),
2620 p_column_name => p_column_name,
2621 p_message_name =>l_wrapper_error_msg );
2622 END IF;
2623
2624 END IF; -- Qty's not null and secondary indicator is not null
2625
2626 END LOOP;
2627
2628 IF (l_results_count < x_results.result_type.COUNT) THEN
2629 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2630 ELSE
2631 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2632 END IF;
2633
2634 IF PO_LOG.d_proc THEN
2635 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2636 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2637 PO_LOG.proc_end(p_calling_module);
2638 END IF;
2639
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 IF PO_LOG.d_exc THEN
2643 PO_LOG.exc(d_mod,0,NULL);
2644 PO_LOG.exc(p_calling_module,0,NULL);
2645 END IF;
2646 RAISE;
2647
2648 END qtys_within_deviation;
2649
2650
2651 --------------------------------------------------------------------------------------------
2652 -- Validate secondary_unit_of_measure.
2653 -- To be called only for BLANKET AND STANDARD.
2654 --------------------------------------------------------------------------------------------
2655 PROCEDURE secondary_unit_of_measure(
2656 p_id_tbl IN po_tbl_number,
2657 p_entity_type IN VARCHAR2,
2658 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
2659 p_item_id_tbl IN po_tbl_number,
2660 p_item_tbl IN po_tbl_varchar2000,
2661 p_organization_id_tbl IN po_tbl_number,
2662 p_doc_type IN VARCHAR2,
2663 p_create_or_update_item_flag IN VARCHAR2,
2664 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2665 x_result_type OUT NOCOPY VARCHAR2)
2666 IS
2667
2668 d_mod CONSTANT VARCHAR2(100) := d_secondary_unit_of_measure;
2669 l_secondary_unit_of_meas_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
2670 l_validation_id NUMBER;
2671
2672 BEGIN
2673
2674 IF PO_LOG.d_proc THEN
2675 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
2676 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2677 PO_LOG.proc_begin(d_mod,'p_secondary_unit_of_meas_tbl',p_secondary_unit_of_meas_tbl);
2678 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
2679 PO_LOG.proc_begin(d_mod,'p_item_tbl',p_item_tbl);
2680 PO_LOG.proc_begin(d_mod,'p_organization_id_tbl',p_organization_id_tbl);
2681 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
2682 PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
2683 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2684 END IF;
2685
2686 IF (x_results IS NULL) THEN
2687 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2688 END IF;
2689
2690 x_result_type := po_validations.c_result_type_success;
2691
2692 l_secondary_unit_of_meas_tbl.extend(p_item_id_tbl.COUNT);
2693
2694 l_secondary_unit_of_meas_tbl := get_item_secondary_uom(p_item_id_tbl,
2695 p_organization_id_tbl);
2696
2697 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2698 -- for one time item , error out..
2699 -- x_item is not derived for existing items..
2700 IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
2701 (p_create_or_update_item_flag = 'N' and p_item_id_tbl(i) IS NULL)) AND
2702 p_secondary_unit_of_meas_tbl(i) IS NOT NULL THEN
2703 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2704 l_validation_id := PO_VAL_CONSTANTS.c_line_secondary_uom_null;
2705 ELSE
2706 l_validation_id := PO_VAL_CONSTANTS.c_loc_secondary_uom_null;
2707 END IF;
2708 x_results.add_result(p_entity_type => p_entity_type,
2709 p_entity_id => p_id_tbl(i),
2710 p_column_name => 'SECONDARY_UNIT_OF_MEASURE',
2711 p_column_val => p_secondary_unit_of_meas_tbl(i),
2712 p_message_name => 'PO_SECONDARY_UOM_NOT_REQUIRED',
2713 p_validation_id => l_validation_id);
2714 x_result_type := po_validations.c_result_type_failure;
2715 END IF;
2716
2717 IF l_secondary_unit_of_meas_tbl(i) IS NULL THEN
2718 IF (p_secondary_unit_of_meas_tbl(i) IS NOT NULL) THEN
2719 -- Item is not dual control
2720 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2721 l_validation_id := PO_VAL_CONSTANTS.c_line_secondary_uom_null;
2722 ELSE
2723 l_validation_id := PO_VAL_CONSTANTS.c_loc_secondary_uom_null;
2724 END IF;
2725 x_results.add_result(p_entity_type => p_entity_type,
2726 p_entity_id => p_id_tbl(i),
2727 p_column_name => 'SECONDARY_UNIT_OF_MEASURE',
2728 p_column_val => p_secondary_unit_of_meas_tbl(i),
2729 p_message_name => 'PO_SECONDARY_UOM_NOT_REQUIRED',
2730 p_validation_id => l_validation_id);
2731 x_result_type := po_validations.c_result_type_failure;
2732 END IF;
2733 ELSE -- l_secondary_unit_of_measure is not null
2734 IF p_secondary_unit_of_meas_tbl(i) IS NULL THEN
2735 -- Secondary UOM missing for dual control item
2736 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2737 l_validation_id := PO_VAL_CONSTANTS.c_line_secondary_uom_not_null;
2738 ELSE
2739 l_validation_id := PO_VAL_CONSTANTS.c_loc_secondary_uom_not_null;
2740 END IF;
2741 x_results.add_result(p_entity_type => p_entity_type,
2742 p_entity_id => p_id_tbl(i),
2743 p_column_name => 'SECONDARY_UNIT_OF_MEASURE',
2744 p_column_val => p_secondary_unit_of_meas_tbl(i),
2745 p_message_name => 'PO_SECONDARY_UOM_REQUIRED',
2746 p_validation_id => l_validation_id);
2747 x_result_type := po_validations.c_result_type_failure;
2748 END IF;
2749
2750 IF l_secondary_unit_of_meas_tbl(i) <> p_secondary_unit_of_meas_tbl(i) THEN
2751 -- Secondary UOM specified is incorrect.
2752 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2753 l_validation_id := PO_VAL_CONSTANTS.c_line_secondary_uom_correct;
2754 ELSE
2755 l_validation_id := PO_VAL_CONSTANTS.c_loc_secondary_uom_correct;
2756 END IF;
2757 x_results.add_result(p_entity_type => p_entity_type,
2758 p_entity_id => p_id_tbl(i),
2759 p_column_name => 'SECONDARY_UNIT_OF_MEASURE',
2760 p_column_val => p_secondary_unit_of_meas_tbl(i),
2761 p_message_name => 'PO_INCORRECT_SECONDARY_UOM',
2762 p_validation_id => l_validation_id);
2763 x_result_type := po_validations.c_result_type_failure;
2764 END IF;
2765 END IF;
2766 END LOOP;
2767
2768 IF (SQL%ROWCOUNT > 0) THEN
2769 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2770 END IF;
2771
2772 IF PO_LOG.d_proc THEN
2773 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2774 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2775 END IF;
2776
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 IF PO_LOG.d_exc THEN
2780 PO_LOG.exc(d_mod,0,NULL);
2781 END IF;
2782 RAISE;
2783
2784 END secondary_unit_of_measure;
2785
2786 --------------------------------------------------------------------------------------------
2787 -- Validate secondary_quantity.
2788 -- To be called for all doc types, but we need to pass it doc type for certain validations.
2789 --------------------------------------------------------------------------------------------
2790 PROCEDURE secondary_quantity(
2791 p_id_tbl IN po_tbl_number,
2792 p_entity_type IN VARCHAR2,
2793 p_secondary_quantity_tbl IN po_tbl_number,
2794 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2795 p_item_id_tbl IN po_tbl_number,
2796 p_item_tbl IN po_tbl_varchar2000,
2797 p_organization_id_tbl IN po_tbl_number,
2798 p_doc_type IN VARCHAR2,
2799 p_create_or_update_item_flag IN VARCHAR2,
2800 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2801 x_result_type OUT NOCOPY VARCHAR2)
2802 IS
2803
2804 d_mod CONSTANT VARCHAR2(100) := d_secondary_quantity;
2805 l_secondary_unit_of_meas_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
2806 l_validation_id NUMBER;
2807 d_position NUMBER := 0;
2808
2809 BEGIN
2810
2811 IF PO_LOG.d_proc THEN
2812 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
2813 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2814 PO_LOG.proc_begin(d_mod,'p_secondary_quantity_tbl',p_secondary_quantity_tbl);
2815 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
2816 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
2817 PO_LOG.proc_begin(d_mod,'p_item_tbl',p_item_tbl);
2818 PO_LOG.proc_begin(d_mod,'p_organization_id_tbl',p_organization_id_tbl);
2819 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
2820 PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
2821 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2822 END IF;
2823
2824 IF (x_results IS NULL) THEN
2825 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2826 END IF;
2827
2828 x_result_type := po_validations.c_result_type_success;
2829
2830 l_secondary_unit_of_meas_tbl.extend(p_item_id_tbl.COUNT);
2831
2832 l_secondary_unit_of_meas_tbl := get_item_secondary_uom(p_item_id_tbl,
2833 p_organization_id_tbl);
2834 d_position := 10;
2835
2836 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2837 IF p_order_type_lookup_code_tbl(i) IN ('FIXED PRICE', 'RATE') AND
2838 p_secondary_quantity_tbl(i) IS NOT NULL THEN
2839 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2840 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_null;
2841 ELSE
2842 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_null;
2843 END IF;
2844 x_results.add_result(p_entity_type => p_entity_type,
2845 p_entity_id => p_id_tbl(i),
2846 p_column_name => 'SECONDARY_QUANTITY',
2847 p_column_val => p_secondary_quantity_tbl(i),
2848 p_message_name => 'PO_SVC_NO_QTY',
2849 p_validation_id => l_validation_id);
2850 x_result_type := po_validations.c_result_type_failure;
2851 END IF;
2852
2853 d_position := 20;
2854
2855 IF p_order_type_lookup_code_tbl(i) NOT IN ('FIXED PRICE', 'RATE') AND
2856 p_secondary_quantity_tbl(i) IS NOT NULL AND
2857 p_secondary_quantity_tbl(i) < 0
2858 THEN
2859 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2860 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_ge_zero;
2861 ELSE
2862 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_ge_zero;
2863 END IF;
2864 x_results.add_result(p_entity_type => p_entity_type,
2865 p_entity_id => p_id_tbl(i),
2866 p_column_name => 'SECONDARY_QUANTITY',
2867 p_column_val => p_secondary_quantity_tbl(i),
2868 p_message_name => 'PO_PDOI_LT_ZERO',
2869 p_validation_id => l_validation_id);
2870 x_result_type := po_validations.c_result_type_failure;
2871 END IF;
2872
2873 d_position := 30;
2874
2875 IF p_doc_type = 'STANDARD' AND
2876 p_secondary_quantity_tbl(i) IS NOT NULL AND
2877 p_secondary_quantity_tbl(i) = 0
2878 THEN
2879 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2880 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_not_zero;
2881 ELSE
2882 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_not_zero;
2883 END IF;
2884 x_results.add_result(p_entity_type => p_entity_type,
2885 p_entity_id => p_id_tbl(i),
2886 p_column_name => 'SECONDARY_QUANTITY',
2887 p_column_val => p_secondary_quantity_tbl(i),
2888 p_message_name => 'PO_PDOI_QTY_ZERO',
2889 p_validation_id => l_validation_id);
2890 x_result_type := po_validations.c_result_type_failure;
2891 END IF;
2892
2893 d_position := 40;
2894
2895 -- for one time item , error out..
2896 -- x_item is not derived for existing items..
2897 IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
2898 (p_create_or_update_item_flag = 'N' AND p_item_id_tbl(i) IS NULL )) AND
2899 p_secondary_quantity_tbl(i) IS NOT NULL AND
2900 p_doc_type IN ('STANDARD', 'BLANKET') THEN
2901 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2902 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_not_reqd;
2903 ELSE
2904 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_not_reqd;
2905 END IF;
2906 x_results.add_result(p_entity_type => p_entity_type,
2907 p_entity_id => p_id_tbl(i),
2908 p_column_name => 'SECONDARY_QUANTITY',
2909 p_column_val => p_secondary_quantity_tbl(i),
2910 p_message_name => 'PO_SECONDARY_QTY_NOT_REQUIRED',
2911 p_validation_id => l_validation_id);
2912 x_result_type := po_validations.c_result_type_failure;
2913 END IF;
2914
2915 d_position := 50;
2916
2917 IF l_secondary_unit_of_meas_tbl(i) IS NULL THEN
2918
2919 d_position := 60;
2920
2921 IF p_secondary_quantity_tbl(i) IS NOT NULL AND p_doc_type IN ('STANDARD', 'BLANKET') THEN
2922 -- Item is not dual control
2923 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2924 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_no_req_uom;
2925 ELSE
2926 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_not_req_uom;
2927 END IF;
2928 x_results.add_result(p_entity_type => p_entity_type,
2929 p_entity_id => p_id_tbl(i),
2930 p_column_name => 'SECONDARY_QUANTITY',
2931 p_column_val => p_secondary_quantity_tbl(i),
2932 p_message_name => 'PO_SECONDARY_QTY_NOT_REQUIRED',
2933 p_validation_id => l_validation_id);
2934 x_result_type := po_validations.c_result_type_failure;
2935 END IF;
2936 ELSE -- l_secondary_unit_of_measure is not null
2937
2938 d_position := 70;
2939
2940 IF p_secondary_quantity_tbl(i) IS NULL AND p_doc_type='STANDARD' THEN
2941 -- Secondary Quantity missing for dual control item
2942 IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
2943 l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_req_uom;
2944 ELSE
2945 l_validation_id := PO_VAL_CONSTANTS.c_loc_sec_quantity_req_uom;
2946 END IF;
2947 x_results.add_result(p_entity_type => p_entity_type,
2948 p_entity_id => p_id_tbl(i),
2949 p_column_name => 'SECONDARY_QUANTITY',
2950 p_column_val => p_secondary_quantity_tbl(i),
2951 p_message_name => 'PO_SECONDARY_QTY_REQUIRED',
2952 p_validation_id => l_validation_id);
2953 x_result_type := po_validations.c_result_type_failure;
2954 END IF;
2955 END IF;
2956 END LOOP;
2957
2958 d_position := 80;
2959
2960 IF (SQL%ROWCOUNT > 0) THEN
2961 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2962 END IF;
2963
2964 IF PO_LOG.d_proc THEN
2965 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2966 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
2967 END IF;
2968
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 IF PO_LOG.d_exc THEN
2972 PO_LOG.exc(d_mod,d_position,'Exception in secondary_quantity');
2973 END IF;
2974 RAISE;
2975
2976 END secondary_quantity;
2977
2978 --------------------------------------------------------------------------------------------
2979 -- Validate secondary_unit_of_measure for the update case.
2980 -- To be called only for BLANKET AND STANDARD.
2981 --------------------------------------------------------------------------------------------
2982 PROCEDURE secondary_uom_update(
2983 p_id_tbl IN po_tbl_number,
2984 p_entity_type IN VARCHAR2,
2985 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
2986 p_item_id_tbl IN po_tbl_number,
2987 p_organization_id_tbl IN po_tbl_number,
2988 p_create_or_update_item_flag IN VARCHAR2,
2989 x_result_set_id IN OUT NOCOPY NUMBER,
2990 x_result_type OUT NOCOPY VARCHAR2)
2991 IS
2992 d_mod CONSTANT VARCHAR2(100) := d_secondary_uom_update;
2993 BEGIN
2994
2995 IF PO_LOG.d_proc THEN
2996 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
2997 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
2998 PO_LOG.proc_begin(d_mod,'p_secondary_unit_of_meas_tbl',p_secondary_unit_of_meas_tbl);
2999 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
3000 PO_LOG.proc_begin(d_mod,'p_organization_id_tbl',p_organization_id_tbl);
3001 PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
3002 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3003 END IF;
3004
3005 IF x_result_set_id IS NULL THEN
3006 x_result_set_id := po_validations.next_result_set_id();
3007 END IF;
3008
3009 x_result_type := po_validations.c_result_type_success;
3010
3011 FORALL i IN 1 .. p_id_tbl.COUNT
3012 INSERT INTO po_validation_results_gt
3013 (result_set_id,
3014 result_type,
3015 entity_type,
3016 entity_id,
3017 message_name,
3018 column_name,
3019 token1_name,
3020 token2_name,
3021 token3_name,
3022 token1_value,
3023 token2_value,
3024 token3_value)
3025 SELECT x_result_set_id,
3026 po_validations.c_result_type_failure,
3027 p_entity_type,
3028 p_id_tbl(i),
3029 'PO_PDOI_ITEM_RELATED_INFO',
3030 'SECONDARY_UNIT_OF_MEASURE',
3031 'COLUMN_NAME',
3032 'VALUE',
3033 'ITEM',
3034 'SECONDARY_UNIT_OF_MEASURE',
3035 p_secondary_unit_of_meas_tbl(i),
3036 p_item_id_tbl(i)
3037 FROM DUAL
3038 WHERE p_item_id_tbl(i) IS NOT NULL
3039 AND p_organization_id_tbl(i) IS NOT NULL
3040 AND p_secondary_unit_of_meas_tbl(i) IS NOT NULL
3041 AND EXISTS(
3042 SELECT 1
3043 FROM mtl_system_items msi
3044 WHERE msi.inventory_item_id = p_item_id_tbl(i)
3045 AND msi.organization_id = p_organization_id_tbl(i)
3046 AND p_secondary_unit_of_meas_tbl(i) <> msi.secondary_uom_code);
3047
3048 IF (SQL%ROWCOUNT > 0) THEN
3049 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3050 END IF;
3051
3052 IF PO_LOG.d_proc THEN
3053 PO_VALIDATIONS.log_validation_results_gt(d_mod, 9, x_result_set_id);
3054 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
3055 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3056 END IF;
3057
3058 EXCEPTION
3059 WHEN OTHERS THEN
3060 IF PO_LOG.d_exc THEN
3061 PO_LOG.exc(d_mod, 0, NULL);
3062 END IF;
3063 RAISE;
3064
3065 END secondary_uom_update;
3066
3067 --------------------------------------------------------------------------------------------
3068 -- If grade is populated, then check whether item is lot grade controlled for the FSP
3069 -- validation organization. If item is not lot controlled grade enabled then log an
3070 -- exception error out. Otherwise validate the grade value against the grade master.
3071 -- If grade doesn't exist in the grade master table then log an exception error out.
3072 -- To be called only for BLANKET and STANDARD.
3073 --------------------------------------------------------------------------------------------
3074 PROCEDURE preferred_grade(
3075 p_id_tbl IN po_tbl_number,
3076 p_entity_type IN VARCHAR2,
3077 p_preferred_grade_tbl IN po_tbl_varchar2000,
3078 p_item_id_tbl IN po_tbl_number,
3079 p_item_tbl IN po_tbl_varchar2000,
3080 p_organization_id_tbl IN po_tbl_number,
3081 p_create_or_update_item_flag IN VARCHAR2,
3082 p_validation_id IN NUMBER DEFAULT NULL,
3083 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3084 x_result_set_id IN OUT NOCOPY NUMBER,
3085 x_result_type OUT NOCOPY VARCHAR2)
3086 IS
3087 d_mod CONSTANT VARCHAR2(100) := d_preferred_grade;
3088 l_validation_id NUMBER;
3089 BEGIN
3090
3091 IF PO_LOG.d_proc THEN
3092 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
3093 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
3094 PO_LOG.proc_begin(d_mod,'p_preferred_grade_tbl',p_preferred_grade_tbl);
3095 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
3096 PO_LOG.proc_begin(d_mod,'p_item_tbl',p_item_tbl);
3097 PO_LOG.proc_begin(d_mod,'p_organization_id_tbl',p_organization_id_tbl);
3098 PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
3099 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3100 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
3101 END IF;
3102
3103 IF x_result_set_id IS NULL THEN
3104 x_result_set_id := po_validations.next_result_set_id();
3105 END IF;
3106
3107 IF (x_results IS NULL) THEN
3108 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3109 END IF;
3110
3111 x_result_type := po_validations.c_result_type_success;
3112 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3113 -- for one time item , error out..
3114 -- x_item is not derived for existing items..
3115 IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
3116 (p_create_or_update_item_flag = 'N' AND p_item_id_tbl(i) IS NULL )) AND
3117 p_preferred_grade_tbl(i) IS NOT NULL THEN
3118 IF (p_validation_id = PO_VAL_CONSTANTS.c_line_preferred_grade) THEN
3119 l_validation_id := PO_VAL_CONSTANTS.c_line_preferred_grade_item;
3120 ELSE
3121 l_validation_id := PO_VAL_CONSTANTS.c_loc_preferred_grade_item;
3122 END IF;
3123 x_results.add_result(p_entity_type => p_entity_type,
3124 p_entity_id => p_id_tbl(i),
3125 p_column_name => 'PREFERRED_GRADE',
3126 p_column_val => p_preferred_grade_tbl(i),
3127 p_message_name => 'PO_ITEM_NOT_GRADE_CTRL',
3128 p_validation_id => l_validation_id);
3129 x_result_type := po_validations.c_result_type_failure;
3130 END IF;
3131 END LOOP;
3132
3133 FORALL i IN 1 .. p_id_tbl.COUNT
3134 INSERT INTO po_validation_results_gt
3135 (result_set_id,
3136 result_type,
3137 entity_type,
3138 entity_id,
3139 message_name,
3140 column_name,
3141 column_val,
3142 validation_id)
3143 SELECT x_result_set_id,
3144 po_validations.c_result_type_failure,
3145 p_entity_type,
3146 p_id_tbl(i),
3147 'PO_ITEM_NOT_GRADE_CTRL',
3148 'PREFERRED_GRADE',
3149 p_preferred_grade_tbl(i),
3150 DECODE(p_validation_id, PO_VAL_CONSTANTS.c_line_preferred_grade,
3151 PO_VAL_CONSTANTS.c_line_preferred_grade_item,
3152 PO_VAL_CONSTANTS.c_loc_preferred_grade_item)
3153 FROM DUAL
3154 WHERE p_preferred_grade_tbl(i) IS NOT NULL
3155 AND p_item_id_tbl(i) IS NOT NULL
3156 AND EXISTS(
3157 SELECT 1
3158 FROM mtl_system_items msi
3159 WHERE msi.inventory_item_id = p_item_id_tbl(i)
3160 AND msi.organization_id = p_organization_id_tbl(i)
3161 AND nvl(msi.grade_control_flag,'N') = 'N');
3162
3163 IF (SQL%ROWCOUNT > 0) THEN
3164 x_result_type := po_validations.c_result_type_failure;
3165 END IF;
3166
3167 FORALL i IN 1 .. p_id_tbl.COUNT
3168 INSERT INTO po_validation_results_gt
3169 (result_set_id,
3170 result_type,
3171 entity_type,
3172 entity_id,
3173 message_name,
3174 column_name,
3175 column_val,
3176 validation_id)
3177 SELECT x_result_set_id,
3178 po_validations.c_result_type_failure,
3179 p_entity_type,
3180 p_id_tbl(i),
3181 'PO_INVALID_GRADE_CODE',
3182 'PREFERRED_GRADE',
3183 p_preferred_grade_tbl(i),
3184 DECODE(p_validation_id, PO_VAL_CONSTANTS.c_line_preferred_grade,
3185 PO_VAL_CONSTANTS.c_line_preferred_grade_valid,
3186 PO_VAL_CONSTANTS.c_loc_preferred_grade_valid)
3187 FROM DUAL
3188 WHERE p_preferred_grade_tbl(i) IS NOT NULL
3189 AND NOT EXISTS(
3190 SELECT 1
3191 FROM mtl_grades_b mgb
3192 WHERE mgb.grade_code = p_preferred_grade_tbl(i) AND
3193 mgb.disable_flag = 'N');
3194
3195 IF (SQL%ROWCOUNT > 0) THEN
3196 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3197 END IF;
3198
3199 IF PO_LOG.d_proc THEN
3200 PO_VALIDATIONS.log_validation_results_gt(d_mod, 9, x_result_set_id);
3201 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
3202 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3203 END IF;
3204
3205 EXCEPTION
3206 WHEN OTHERS THEN
3207 IF PO_LOG.d_exc THEN
3208 PO_LOG.exc(d_mod, 0, NULL);
3209 END IF;
3210 RAISE;
3211
3212 END preferred_grade;
3213
3214
3215 PROCEDURE process_enabled(
3216 p_id_tbl IN po_tbl_number,
3217 p_entity_type IN VARCHAR2,
3218 p_ship_to_organization_id_tbl IN po_tbl_number,
3219 p_item_id_tbl IN po_tbl_number,
3220 x_result_set_id IN OUT NOCOPY NUMBER,
3221 x_result_type OUT NOCOPY VARCHAR2)
3222 IS
3223 d_mod CONSTANT VARCHAR2(100) := d_secondary_unit_of_measure;
3224 BEGIN
3225
3226 IF PO_LOG.d_proc THEN
3227 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
3228 PO_LOG.proc_begin(d_mod,'p_entity_type',p_entity_type);
3229 PO_LOG.proc_begin(d_mod,'p_ship_to_organization_id_tbl',p_ship_to_organization_id_tbl);
3230 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
3231 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3232 END IF;
3233
3234 IF x_result_set_id IS NULL THEN
3235 x_result_set_id := po_validations.next_result_set_id();
3236 END IF;
3237
3238 x_result_type := po_validations.c_result_type_success;
3239
3240 -- check if ship to org is process. Currently we don't support OSP items for process orgs.
3241 FORALL i IN 1 .. p_id_tbl.COUNT
3242 INSERT INTO po_validation_results_gt
3243 (result_set_id,
3244 result_type,
3245 entity_type,
3246 entity_id,
3247 message_name,
3248 column_name,
3249 column_val)
3250 SELECT x_result_set_id,
3251 po_validations.c_result_type_failure,
3252 p_entity_type,
3253 p_id_tbl(i),
3254 'PO_OPS_ITEM_PROCESS_ORG',
3255 'SHIP_TO_ORGANIZATION_ID',
3256 p_ship_to_organization_id_tbl(i)
3257 FROM DUAL
3258 WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
3259 AND p_item_id_tbl(i) IS NOT NULL
3260 AND EXISTS(
3261 SELECT 1
3262 FROM mtl_system_items msi,
3263 mtl_parameters mp
3264 WHERE msi.inventory_item_id = p_item_id_tbl(i)
3265 AND msi.organization_id = p_ship_to_organization_id_tbl(i)
3266 AND msi.organization_id = mp.organization_id
3267 AND msi.outside_operation_flag = 'Y'
3268 AND mp.process_enabled_flag = 'Y');
3269
3270 IF (SQL%ROWCOUNT > 0) THEN
3271 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3272 END IF;
3273
3274 IF PO_LOG.d_proc THEN
3275 PO_VALIDATIONS.log_validation_results_gt(d_mod, 9, x_result_set_id);
3276 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
3277 PO_LOG.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3278 END IF;
3279
3280 EXCEPTION
3281 WHEN OTHERS THEN
3282 IF PO_LOG.d_exc THEN
3283 PO_LOG.exc(d_mod, 0, NULL);
3284 END IF;
3285 RAISE;
3286
3287 END process_enabled;
3288
3289 --------------------------------------------------------------------------------------------
3290 -- Private function to get the converted unit of measure from mtl_system_items.
3291 --------------------------------------------------------------------------------------------
3292 FUNCTION get_item_secondary_uom(
3293 p_item_id_tbl IN po_tbl_number,
3294 p_organization_id_tbl IN po_tbl_number)
3295 RETURN PO_TBL_VARCHAR30
3296 IS
3297
3298 d_mod CONSTANT VARCHAR2(100) := d_get_item_secondary_uom;
3299 l_secondary_unit_of_meas_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
3300
3301 -- key value used to identify rows in po_session_gt table
3302 l_key po_session_gt.key%TYPE;
3303 l_index_tbl DBMS_SQL.NUMBER_TABLE;
3304 l_index1_tbl PO_TBL_NUMBER;
3305 l_result1_tbl PO_TBL_VARCHAR30;
3306
3307 BEGIN
3308
3309 IF PO_LOG.d_proc THEN
3310 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
3311 PO_LOG.proc_begin(d_mod,'p_organization_id_tbl',p_organization_id_tbl);
3312 END IF;
3313
3314 l_secondary_unit_of_meas_tbl.extend(p_item_id_tbl.COUNT);
3315
3316 l_key := PO_CORE_S.get_session_gt_nextval;
3317
3318 FOR i IN 1..p_item_id_tbl.COUNT LOOP
3319 l_index_tbl(i) := i;
3320 END LOOP;
3321
3322 FORALL i IN 1..p_item_id_tbl.COUNT
3323 INSERT INTO po_session_gt(key, num1, char1)
3324 SELECT l_key, l_index_tbl(i), uom.unit_of_measure
3325 FROM mtl_system_items msi,
3326 mtl_units_of_measure uom
3327 WHERE msi.inventory_item_id = p_item_id_tbl(i)
3328 AND msi.organization_id = p_organization_id_tbl(i)
3329 AND msi.tracking_quantity_ind = 'PS'
3330 AND msi.secondary_uom_code = uom.uom_code;
3331
3332 DELETE FROM po_session_gt
3333 WHERE key = l_key
3334 RETURNING num1, char1 BULK COLLECT INTO l_index1_tbl, l_result1_tbl;
3335
3336 FOR i IN 1..l_index1_tbl.COUNT LOOP
3337 l_secondary_unit_of_meas_tbl(l_index1_tbl(i)) := l_result1_tbl(i);
3338 END LOOP;
3339
3340 IF PO_LOG.d_proc THEN
3341 PO_LOG.proc_end(d_mod, 'l_secondary_unit_of_meas_tbl', l_secondary_unit_of_meas_tbl);
3342 END IF;
3343
3344 RETURN l_secondary_unit_of_meas_tbl;
3345
3346 EXCEPTION
3347 WHEN OTHERS THEN
3348 IF PO_LOG.d_exc THEN
3349 PO_LOG.exc(d_mod, 0, NULL);
3350 END IF;
3351 RAISE;
3352
3353 END get_item_secondary_uom;
3354
3355 -------------------------------------------------------------------------------
3356 --Start of Comments
3357 --Bug 7516824: New procedure added
3358 --Pre-reqs: None.
3359 --Modifies: x_results, x_result_type
3360 --Locks: None.
3361 --Function:
3362 -- Validates if the DFF's segment values passed are valid.
3363 -- It used the FND API FND_FLEX_DESCVAL.validate_desccols to validate.
3364 --Parameters:
3365 --IN:
3366 --p_calling_module
3367 -- The module base of the calling procedure, used for logging.
3368 --p_id_tbl
3369 -- List of ids. It can be lines, shipments or dist ids.
3370 --p_desc_flex_name
3371 -- Name of DFF which is to be validated.
3372 --p_attribute_category_tbl
3373 -- List of DFF Context / Category values
3374 --p_attribute1_tbl .. p_attribute15_tbl
3375 -- List of DFF Segment values
3376 --p_entity_type
3377 -- Entity type to be validated. (Line/Shipment/Dist)
3378 --IN OUT:
3379 --x_results
3380 -- Validation errors will be added to this object.
3381 -- A new object will be created if NULL is passed in.
3382 --OUT:
3383 --x_result_type
3384 -- Indicates if any validations have failed.
3385 -- PO_VALIDATIONS.c_result_type_SUCCESS - no failures.
3386 -- PO_VALIDATIONS.c_result_type_FAILURE - failures.
3387 --End of Comments
3388 -------------------------------------------------------------------------------
3389 PROCEDURE validate_desc_flex (
3390 p_calling_module IN VARCHAR2,
3391 p_id_tbl IN po_tbl_number,
3392 p_desc_flex_name IN fnd_descr_flex_column_usages.descriptive_flexfield_name%TYPE,
3393 p_attribute_category_tbl IN PO_TBL_VARCHAR30,
3394 p_attribute1_tbl IN PO_TBL_VARCHAR2000,
3395 p_attribute2_tbl IN PO_TBL_VARCHAR2000,
3396 p_attribute3_tbl IN PO_TBL_VARCHAR2000,
3397 p_attribute4_tbl IN PO_TBL_VARCHAR2000,
3398 p_attribute5_tbl IN PO_TBL_VARCHAR2000,
3399 p_attribute6_tbl IN PO_TBL_VARCHAR2000,
3400 p_attribute7_tbl IN PO_TBL_VARCHAR2000,
3401 p_attribute8_tbl IN PO_TBL_VARCHAR2000,
3402 p_attribute9_tbl IN PO_TBL_VARCHAR2000,
3403 p_attribute10_tbl IN PO_TBL_VARCHAR2000,
3404 p_attribute11_tbl IN PO_TBL_VARCHAR2000,
3405 p_attribute12_tbl IN PO_TBL_VARCHAR2000,
3406 p_attribute13_tbl IN PO_TBL_VARCHAR2000,
3407 p_attribute14_tbl IN PO_TBL_VARCHAR2000,
3408 p_attribute15_tbl IN PO_TBL_VARCHAR2000,
3409 p_entity_type IN VARCHAR2,
3410 x_results IN OUT NOCOPY po_validation_results_type,
3411 x_result_type OUT NOCOPY VARCHAR2)
3412 IS
3413 d_mod CONSTANT VARCHAR2(100) := D_validate_desc_flex;
3414 c_message_name CONSTANT VARCHAR2(30) := 'PO_DFF_SEGMENT_NOT_VALID';
3415 c_dff_name CONSTANT VARCHAR2(30) := 'DFF_NAME';
3416 c_segment_num CONSTANT VARCHAR2(30) := 'SEGMENT_NUM';
3417 c_attribute CONSTANT VARCHAR2(30) := 'ATTRIBUTE';
3418 c_po_appl CONSTANT VARCHAR2(30) := 'PO';
3419 BEGIN
3420
3421 IF PO_LOG.d_proc THEN
3422 PO_LOG.proc_begin(p_calling_module);
3423 PO_LOG.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3424 PO_LOG.proc_begin(d_mod, 'p_desc_flex_name', p_desc_flex_name);
3425 PO_LOG.proc_begin(d_mod, 'p_attribute_category_tbl', p_attribute_category_tbl);
3426 PO_LOG.proc_begin(d_mod, 'p_attribute1_tbl', p_attribute1_tbl);
3427 PO_LOG.proc_begin(d_mod, 'p_attribute2_tbl', p_attribute2_tbl);
3428 PO_LOG.proc_begin(d_mod, 'p_attribute3_tbl', p_attribute3_tbl);
3429 PO_LOG.proc_begin(d_mod, 'p_attribute4_tbl', p_attribute4_tbl);
3430 PO_LOG.proc_begin(d_mod, 'p_attribute5_tbl', p_attribute5_tbl);
3431 PO_LOG.proc_begin(d_mod, 'p_attribute6_tbl', p_attribute6_tbl);
3432 PO_LOG.proc_begin(d_mod, 'p_attribute7_tbl', p_attribute7_tbl);
3433 PO_LOG.proc_begin(d_mod, 'p_attribute8_tbl', p_attribute8_tbl);
3434 PO_LOG.proc_begin(d_mod, 'p_attribute9_tbl', p_attribute9_tbl);
3435 PO_LOG.proc_begin(d_mod, 'p_attribute10_tbl', p_attribute10_tbl);
3436 PO_LOG.proc_begin(d_mod, 'p_attribute11_tbl', p_attribute11_tbl);
3437 PO_LOG.proc_begin(d_mod, 'p_attribute12_tbl', p_attribute12_tbl);
3438 PO_LOG.proc_begin(d_mod, 'p_attribute13_tbl', p_attribute13_tbl);
3439 PO_LOG.proc_begin(d_mod, 'p_attribute14_tbl', p_attribute14_tbl);
3440 PO_LOG.proc_begin(d_mod, 'p_attribute15_tbl', p_attribute15_tbl);
3441 END IF;
3442
3443 IF (x_results IS NULL) THEN
3444 x_results := po_validation_results_type.new_instance();
3445 END IF;
3446
3447 x_result_type := po_validations.c_result_type_success;
3448
3449 -- Loop through all the ids
3450 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3451 -- Set attribute category (context) and all attribute values
3452 fnd_flex_descval.set_column_value('ATTRIBUTE_CATEGORY', p_attribute_category_tbl(i));
3453 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1_tbl(i));
3454 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2_tbl(i));
3455 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3_tbl(i));
3456 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4_tbl(i));
3457 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5_tbl(i));
3458 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6_tbl(i));
3459 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7_tbl(i));
3460 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8_tbl(i));
3461 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9_tbl(i));
3462 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10_tbl(i));
3463 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11_tbl(i));
3464 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12_tbl(i));
3465 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13_tbl(i));
3466 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14_tbl(i));
3467 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15_tbl(i));
3468
3469 -- If DFF validation fails
3470 IF NOT FND_FLEX_DESCVAL.validate_desccols(c_po_appl, p_desc_flex_name)
3471 THEN
3472 x_results.add_result(p_entity_type => p_entity_type,
3473 p_entity_id => p_id_tbl(i),
3474 p_column_name => c_attribute || FND_FLEX_DESCVAL.error_segment,
3475 p_column_val => NULL,
3476 p_message_name => c_message_name,
3477 p_token1_name => c_dff_name,
3478 p_token1_value => p_desc_flex_name,
3479 p_token2_name => c_segment_num,
3480 p_token2_value => FND_FLEX_DESCVAL.error_segment);
3481 x_result_type := po_validations.c_result_type_failure;
3482 END IF;
3483
3484 END LOOP;
3485
3486 IF PO_LOG.d_proc THEN
3487 PO_LOG.proc_end(d_mod, 'x_result_type', x_result_type);
3488 PO_LOG.log(PO_LOG.c_PROC_END, d_mod, NULL, 'x_results', x_results);
3489 PO_LOG.proc_end(p_calling_module);
3490 END IF;
3491
3492 EXCEPTION
3493 WHEN OTHERS THEN
3494 IF PO_LOG.d_exc THEN
3495 PO_LOG.exc(d_mod,0,NULL);
3496 PO_LOG.exc(p_calling_module, 0, NULL);
3497 END IF;
3498 RAISE;
3499 END validate_desc_flex;
3500
3501 END PO_VALIDATION_HELPER;
[Home] [Help]