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