4 -- The module base for this package.
1 PACKAGE BODY PO_VAL_PRICE_DIFFS2 AS
2 -- $Header: PO_VAL_PRICE_DIFFS2.plb 120.8 2006/08/02 23:14:51 jinwang noship $
3 c_entity_type_price_diff CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_PRICE_DIFF;
5 d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_PRICE_DIFFS2');
6
7 -- The module base for the subprogram.
8 d_price_type CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_TYPE');
9 d_multiple_price_diff CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'MULTIPLE_PRICE_DIFF');
10 d_entity_type CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ENTITY_TYPE');
11 d_multiplier CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'MULTIPLIER');
12 d_min_multiplier CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'MIN_MULTIPLIER');
13 d_max_multiplier CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'MAX_MULTIPLIER');
14 d_style_related_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'STYLE_RELATED_INFO');
15
16 -- Indicates that the calling program is PDOI.
17 c_program_pdoi CONSTANT VARCHAR2(10) := 'PDOI';
18 -- The application name of PO.
19 c_po CONSTANT VARCHAR2(2) := 'PO';
20
21 -------------------------------------------------------------------------
22 -- Price type cannot be NULL and must be valid in PO_PRICE_DIFF_LOOKUPS_V
23 -------------------------------------------------------------------------
24 PROCEDURE price_type(
25 p_id_tbl IN po_tbl_number,
26 p_price_type_tbl IN po_tbl_varchar30,
27 x_result_set_id IN OUT NOCOPY NUMBER,
31 BEGIN
28 x_result_type OUT NOCOPY VARCHAR2)
29 IS
30 d_mod CONSTANT VARCHAR2(100) := d_price_type;
32 IF x_result_set_id IS NULL THEN
33 x_result_set_id := po_validations.next_result_set_id();
34 END IF;
35
36 x_result_type := po_validations.c_result_type_success;
37
38 IF po_log.d_proc THEN
39 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
40 po_log.proc_begin(d_mod, 'p_price_type_tbl', p_price_type_tbl);
41 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
42 END IF;
43
44 -- Price type cannot be Null and must be valid in PO_PRICE_DIFF_LOOKUPS_V
45 FORALL i IN 1 .. p_id_tbl.COUNT
46 INSERT INTO po_validation_results_gt
47 (result_set_id,
48 result_type,
49 entity_type,
50 entity_id,
51 message_name,
52 column_name,
53 column_val,
54 token1_name,
55 token1_value,
56 validation_id)
57 SELECT x_result_set_id,
58 po_validations.c_result_type_failure,
59 c_entity_type_price_diff,
60 p_id_tbl(i),
61 DECODE(p_price_type_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_SVC_INVALID_PRICE_TYPE'),
62 'PRICE_TYPE',
63 p_price_type_tbl(i),
64 'PRICE_TYPE',
65 p_price_type_tbl(i),
66 DECODE(p_price_type_tbl(i), NULL, PO_VAL_CONSTANTS.c_price_type_not_null,
67 PO_VAL_CONSTANTS.c_price_type_valid)
68 FROM DUAL
69 WHERE p_price_type_tbl(i) IS NULL
70 OR(p_price_type_tbl(i) IS NOT NULL AND
71 NOT EXISTS(SELECT 1
72 FROM po_price_diff_lookups_v
73 WHERE price_differential_type = p_price_type_tbl(i)));
74
75 IF (SQL%ROWCOUNT > 0) THEN
76 x_result_type := po_validations.c_result_type_failure;
77 END IF;
78
79 IF po_log.d_proc THEN
80 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
81 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
82 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
83 END IF;
84 EXCEPTION
85 WHEN OTHERS THEN
86 IF po_log.d_exc THEN
87 po_log.exc(d_mod, 0, NULL);
88 END IF;
89
90 RAISE;
91 END price_type;
92
93 -------------------------------------------------------------------------
94 -- Multiple price differential records of the same type for a line/price break record are not allowed.
95 -------------------------------------------------------------------------
96 PROCEDURE multiple_price_diff(
97 p_id_tbl IN po_tbl_number,
98 p_price_type_tbl IN po_tbl_varchar30,
99 p_entity_type_tbl IN po_tbl_varchar30,
100 p_entity_id_tbl IN PO_TBL_NUMBER,
101 x_result_set_id IN OUT NOCOPY NUMBER,
102 x_result_type OUT NOCOPY VARCHAR2)
103 IS
104 d_mod CONSTANT VARCHAR2(100) := d_multiple_price_diff;
105 l_gt_key NUMBER;
106 BEGIN
107 IF x_result_set_id IS NULL THEN
108 x_result_set_id := po_validations.next_result_set_id();
109 END IF;
110
111 x_result_type := po_validations.c_result_type_success;
112
113 IF po_log.d_proc THEN
114 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
115 po_log.proc_begin(d_mod, 'p_price_type_tbl', p_price_type_tbl);
116 po_log.proc_begin(d_mod, 'p_entity_type_tbl', p_entity_type_tbl);
117 po_log.proc_begin(d_mod, 'p_entity_id_tbl', p_entity_id_tbl);
118 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
119 END IF;
120
121 l_gt_key := PO_CORE_S.get_session_gt_nextval();
122 -- insert the data in current group into temp table for comparason
123 FORALL i IN 1..p_id_tbl.COUNT
124 INSERT INTO po_session_gt
125 (key,
126 num1,
127 index_num1,
128 char1,
129 char2)
130 SELECT l_gt_key,
131 p_id_tbl(i),
132 p_entity_id_tbl(i),
133 p_entity_type_tbl(i),
134 p_price_type_tbl(i)
135 FROM DUAL;
136
137 -- Check that we are not creating multiple price differential
138 -- records of the same type for a line/price break record against
139 -- txn table or draft table or records within the same group
140 FORALL i IN 1 .. p_id_tbl.COUNT
141 INSERT INTO po_validation_results_gt
142 (result_set_id,
143 result_type,
144 entity_type,
145 entity_id,
146 message_name,
147 column_name,
148 column_val,
149 token1_name,
150 token1_value,
151 validation_id)
152 SELECT x_result_set_id,
153 po_validations.c_result_type_failure,
154 c_entity_type_price_diff,
155 p_id_tbl(i),
156 'PO_PDOI_SVC_NO_MULTI_DIFF',
157 'PRICE_TYPE',
158 p_price_type_tbl(i),
159 NULL,
160 NULL,
164 AND p_entity_type_tbl(i) IS NOT NULL
161 PO_VAL_CONSTANTS.c_multiple_price_diff
162 FROM DUAL
163 WHERE p_price_type_tbl(i) IS NOT NULL
165 AND p_entity_id_tbl(i) IS NOT NULL
166 AND (EXISTS(SELECT 1
167 FROM po_price_differentials
168 WHERE entity_id = p_entity_id_tbl(i)
169 AND entity_type = p_entity_type_tbl(i)
170 AND price_type = p_price_type_tbl(i))
171 OR
172 EXISTS(SELECT 1
173 FROM po_price_diff_draft
174 WHERE entity_id = p_entity_id_tbl(i)
175 AND entity_type = p_entity_type_tbl(i)
176 AND price_type = p_price_type_tbl(i))
177 OR
178 EXISTS(SELECT 1
179 FROM po_session_gt
180 WHERE key = l_gt_key
181 AND num1 < p_id_tbl(i)
182 AND index_num1 = p_entity_id_tbl(i)
183 AND char1 = p_entity_type_tbl(i)
184 AND char2 = p_price_type_tbl(i)));
185
186 -- remove the records from temp table
187 DELETE FROM po_session_gt
188 WHERE key = l_gt_key;
189
190 IF (SQL%ROWCOUNT > 0) THEN
191 x_result_type := po_validations.c_result_type_failure;
192 END IF;
193
194 IF po_log.d_proc THEN
195 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
196 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
197 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
198 END IF;
199 EXCEPTION
200 WHEN OTHERS THEN
201 IF po_log.d_exc THEN
202 po_log.exc(d_mod, 0, NULL);
203 END IF;
204
205 RAISE;
206
207 END multiple_price_diff;
208
209 -------------------------------------------------------------------------
210 -- Validate price differential is tied to a valid entity type for
211 -- different document type
212 -------------------------------------------------------------------------
213 PROCEDURE entity_type(
214 p_id_tbl IN po_tbl_number,
215 p_entity_type_tbl IN po_tbl_varchar30,
216 p_doc_type IN VARCHAR2,
217 x_results IN OUT NOCOPY po_validation_results_type,
218 x_result_type OUT NOCOPY VARCHAR2)
219 IS
220 d_mod CONSTANT VARCHAR2(100) := d_entity_type;
221 BEGIN
222 IF (x_results IS NULL) THEN
223 x_results := po_validation_results_type.new_instance();
224 END IF;
225
226 x_result_type := po_validations.c_result_type_success;
227
228 IF po_log.d_proc THEN
229 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
230 po_log.proc_begin(d_mod, 'p_entity_type_tbl', p_entity_type_tbl);
231 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
232 END IF;
233
234 -- bug 4700377
235 -- validate entity_type value based on document types
236 FOR i IN 1 .. p_id_tbl.COUNT LOOP
237 IF ((p_doc_type = 'BLANKET' AND p_entity_type_tbl(i) NOT IN('BLANKET LINE', 'PRICE BREAK'))
238 OR
239 (p_doc_type = 'STANDARD' AND p_entity_type_tbl(i) <> 'PO LINE')) THEN
240 x_results.add_result(p_entity_type => c_entity_type_price_diff,
241 p_entity_id => p_id_tbl(i),
242 p_column_name => 'ENTITY_TYPE',
243 p_column_val => p_entity_type_tbl(i),
244 p_message_name => 'PO_PDOI_SVC_INVALID_ENT_TYPE',
245 p_validation_id => PO_VAL_CONSTANTS.c_entity_type);
246 x_result_type := po_validations.c_result_type_failure;
247 END IF;
248 END LOOP;
249
250 IF po_log.d_proc THEN
251 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
252 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
253 END IF;
254 EXCEPTION
255 WHEN OTHERS THEN
256 IF po_log.d_exc THEN
257 po_log.exc(d_mod, 0, NULL);
258 END IF;
259
260 RAISE;
261 END entity_type;
262
263 -------------------------------------------------------------------------
264 -- If the entity type is PO LINE, the multiplier column must not be null.
265 -- If the entity type is BLANKET LINE or PRICE BREAK, the multiplier column
266 -- must be null.
267 -------------------------------------------------------------------------
268 PROCEDURE multiplier(
269 p_id_tbl IN po_tbl_number,
270 p_entity_type_tbl IN po_tbl_varchar30,
271 p_multiplier_tbl IN po_tbl_number,
272 x_results IN OUT NOCOPY po_validation_results_type,
273 x_result_type OUT NOCOPY VARCHAR2)
274 IS
275 d_mod CONSTANT VARCHAR2(100) := d_multiplier;
276 BEGIN
277 IF (x_results IS NULL) THEN
278 x_results := po_validation_results_type.new_instance();
279 END IF;
280
281 x_result_type := po_validations.c_result_type_success;
282
283 IF po_log.d_proc THEN
284 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
285 po_log.proc_begin(d_mod, 'p_entity_type_tbl', p_entity_type_tbl);
286 po_log.proc_begin(d_mod, 'p_multiplier_tbl', p_multiplier_tbl);
290 FOR i IN 1 .. p_id_tbl.COUNT LOOP
287 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
288 END IF;
289
291 IF p_entity_type_tbl(i) = 'PO LINE' AND p_multiplier_tbl(i) IS NULL THEN
292 x_results.add_result(p_entity_type => c_entity_type_price_diff,
293 p_entity_id => p_id_tbl(i),
294 p_column_name => 'MUTLIPLIER',
295 p_column_val => p_multiplier_tbl(i),
296 p_message_name => 'PO_PDOI_SVC_MUST_MULTIPLIER',
297 p_validation_id => PO_VAL_CONSTANTS.c_multiplier_not_null);
298 x_result_type := po_validations.c_result_type_failure;
299 ELSIF p_entity_type_tbl(i) IN('BLANKET LINE', 'PRICE BREAK') AND p_multiplier_tbl(i) IS NOT NULL THEN
300 x_results.add_result(p_entity_type => c_entity_type_price_diff,
301 p_entity_id => p_id_tbl(i),
302 p_column_name => 'MULTIPLIER',
303 p_column_val => p_multiplier_tbl(i),
304 p_message_name => 'PO_PDOI_SVC_NO_MULTIPLIER',
305 p_validation_id => PO_VAL_CONSTANTS.c_multiplier_null);
306 x_result_type := po_validations.c_result_type_failure;
307 END IF;
308 END LOOP;
309
310 IF po_log.d_proc THEN
311 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
312 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
313 END IF;
314 EXCEPTION
315 WHEN OTHERS THEN
316 IF po_log.d_exc THEN
317 po_log.exc(d_mod, 0, NULL);
318 END IF;
319
320 RAISE;
321 END multiplier;
322
323 -------------------------------------------------------------------------
324 -- If the entity type is PO LINE, the min multiplier column must be null.
325 -- If the entity type is BLANKET LINE or PRICE BREAK, the min multiplier column
326 -- must not be null.
327 -------------------------------------------------------------------------
328 PROCEDURE min_multiplier(
329 p_id_tbl IN po_tbl_number,
330 p_entity_type_tbl IN po_tbl_varchar30,
331 p_min_multiplier_tbl IN po_tbl_number,
332 x_results IN OUT NOCOPY po_validation_results_type,
333 x_result_type OUT NOCOPY VARCHAR2)
334 IS
335 d_mod CONSTANT VARCHAR2(100) := d_min_multiplier;
336 BEGIN
337 IF (x_results IS NULL) THEN
338 x_results := po_validation_results_type.new_instance();
339 END IF;
340
341 x_result_type := po_validations.c_result_type_success;
342
343 IF po_log.d_proc THEN
344 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
345 po_log.proc_begin(d_mod, 'p_entity_type_tbl', p_entity_type_tbl);
346 po_log.proc_begin(d_mod, 'p_min_multiplier_tbl', p_min_multiplier_tbl);
347 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
348 END IF;
349
350 FOR i IN 1 .. p_id_tbl.COUNT LOOP
351 IF p_entity_type_tbl(i) = 'PO LINE' AND p_min_multiplier_tbl(i) IS NOT NULL THEN
352 x_results.add_result(p_entity_type => c_entity_type_price_diff,
353 p_entity_id => p_id_tbl(i),
354 p_column_name => 'MIN_MULTIPLIER',
355 p_column_val => p_min_multiplier_tbl(i),
356 p_message_name => 'PO_PDOI_SVC_NO_MIN_MULT',
357 p_validation_id => PO_VAL_CONSTANTS.c_min_multiplier_null);
358 x_result_type := po_validations.c_result_type_failure;
359 ELSIF p_entity_type_tbl(i) IN('BLANKET LINE', 'PRICE BREAK') AND p_min_multiplier_tbl(i) IS NULL THEN
360 x_results.add_result(p_entity_type => c_entity_type_price_diff,
361 p_entity_id => p_id_tbl(i),
362 p_column_name => 'MIN_MULTIPLIER',
363 p_column_val => p_min_multiplier_tbl(i),
364 p_message_name => 'PO_PDOI_SVC_MUST_MIN_MULT',
365 p_validation_id => PO_VAL_CONSTANTS.c_min_multiplier_not_null);
366 x_result_type := po_validations.c_result_type_failure;
367 END IF;
368 END LOOP;
369
370 IF po_log.d_proc THEN
371 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
372 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
373 END IF;
374 EXCEPTION
375 WHEN OTHERS THEN
376 IF po_log.d_exc THEN
377 po_log.exc(d_mod, 0, NULL);
378 END IF;
379
380 RAISE;
381 END min_multiplier;
382
383 -------------------------------------------------------------------------
384 -- If the entity type is PO LINE, the multiplier column must be null.
385 -- If the entity type is BLANKET LINE or PRICE BREAK, the multiplier column
386 -- must not be null.
387 -------------------------------------------------------------------------
388 PROCEDURE max_multiplier(
389 p_id_tbl IN po_tbl_number,
390 p_entity_type_tbl IN po_tbl_varchar30,
391 p_max_multiplier_tbl IN po_tbl_number,
392 x_results IN OUT NOCOPY po_validation_results_type,
393 x_result_type OUT NOCOPY VARCHAR2)
394 IS
395 d_mod CONSTANT VARCHAR2(100) := d_max_multiplier;
396 BEGIN
397 IF (x_results IS NULL) THEN
398 x_results := po_validation_results_type.new_instance();
399 END IF;
400
401 x_result_type := po_validations.c_result_type_success;
402
403 IF po_log.d_proc THEN
407 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
404 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
405 po_log.proc_begin(d_mod, 'p_entity_type_tbl', p_entity_type_tbl);
406 po_log.proc_begin(d_mod, 'p_max_multiplier_tbl', p_max_multiplier_tbl);
408 END IF;
409
410 FOR i IN 1 .. p_id_tbl.COUNT LOOP
411 IF p_entity_type_tbl(i) = 'PO LINE' AND p_max_multiplier_tbl(i) IS NOT NULL THEN
412 x_results.add_result(p_entity_type => c_entity_type_price_diff,
413 p_entity_id => p_id_tbl(i),
414 p_column_name => 'MAX_MULTIPLIER',
415 p_column_val => p_max_multiplier_tbl(i),
416 p_message_name => 'PO_PDOI_SVC_NO_MAX_MULT',
417 p_validation_id => PO_VAL_CONSTANTS.c_max_multiplier_null);
418 x_result_type := po_validations.c_result_type_failure;
419 END IF;
420 END LOOP;
421
422 IF po_log.d_proc THEN
423 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
424 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
425 END IF;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 IF po_log.d_exc THEN
430 po_log.exc(d_mod, 0, NULL);
431 END IF;
432
433 RAISE;
434 END max_multiplier;
435
436 -------------------------------------------------------------------------------------
437 -- Validate price_differentials_flag = Y for the given style
438 -------------------------------------------------------------------------------------
439 PROCEDURE style_related_info(
440 p_id_tbl IN po_tbl_number,
441 p_style_id_tbl IN po_tbl_number,
442 x_result_set_id IN OUT NOCOPY NUMBER,
443 x_result_type OUT NOCOPY VARCHAR2)
444 IS
445 d_mod CONSTANT VARCHAR2(100) := d_style_related_info;
446 BEGIN
447 IF x_result_set_id IS NULL THEN
448 x_result_set_id := po_validations.next_result_set_id();
449 END IF;
450
451 IF po_log.d_proc THEN
452 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
453 po_log.proc_begin(d_mod, 'p_style_id_tbl', p_style_id_tbl);
454 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
455 END IF;
456
457 x_result_type := po_validations.c_result_type_success;
458
459 -- bug5130037
460 -- Have NVL() around pdsh.price_differentials_flag
461 FORALL i IN 1 .. p_id_tbl.COUNT
462 INSERT INTO po_validation_results_gt
463 (result_set_id,
464 result_type,
465 entity_type,
466 entity_id,
467 message_name,
468 column_name,
469 column_val,
470 token1_name,
471 token1_value,
472 validation_id)
473 SELECT x_result_set_id,
474 po_validations.c_result_type_failure,
475 c_entity_type_price_diff,
476 p_id_tbl(i),
477 'PO_PDOI_PRICE_DIFF_STYLE',
478 'STYLE_ID',
479 p_style_id_tbl(i),
480 'STYLE_ID',
481 p_style_id_tbl(i),
482 PO_VAL_CONSTANTS.c_price_diff_style_info
483 FROM DUAL
484 WHERE EXISTS(SELECT 1
485 FROM po_doc_style_headers pdsh
486 WHERE pdsh.style_id = p_style_id_tbl(i) AND
487 NVL(pdsh.price_differentials_flag, 'N') = 'N');
488
489 IF (SQL%ROWCOUNT > 0) THEN
490 x_result_type := po_validations.c_result_type_failure;
491 END IF;
492
493 IF po_log.d_proc THEN
494 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
495 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
496 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
497 END IF;
498 EXCEPTION
499 WHEN OTHERS THEN
500 IF po_log.d_exc THEN
501 po_log.exc(d_mod, 0, NULL);
502 END IF;
503 RAISE;
504
505 END style_related_info;
506
507
508 END PO_VAL_PRICE_DIFFS2;
[Home] [Help]