eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_VAL_PRICE_BREAKS

Source


1 PACKAGE BODY PO_VAL_PRICE_BREAKS AS
2 -- $Header: PO_VAL_PRICE_BREAKS.plb 120.0 2005/06/01 18:52:44 appldev noship $
3 
4 c_entity_type_LINE_LOCATION CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_LINE_LOCATION;
5 
6 -- Constants for columns
7 c_PRICE_DISCOUNT CONSTANT VARCHAR2(30) := 'PRICE_DISCOUNT';
8 c_PRICE_OVERRIDE CONSTANT VARCHAR2(30) := 'PRICE_OVERRIDE';
9 c_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
10 c_START_DATE CONSTANT VARCHAR2(30) := 'START_DATE';
11 c_END_DATE CONSTANT VARCHAR2(30) := 'END_DATE';
12 
13 -- The module base for this package.
14 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
15   PO_LOG.get_package_base('PO_VAL_PRICE_BREAKS');
16 
17 -- The module base for the subprogram.
18 D_at_least_one_required_field CONSTANT VARCHAR2(100) :=
19   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'at_least_one_required_field');
20 
21 D_price_discount_in_percent CONSTANT VARCHAR2(100) :=
22   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_discount_in_percent');
23 
24 D_price_override_gt_zero CONSTANT VARCHAR2(100) :=
25   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_override_gt_zero');
26 
27 D_quantity_ge_zero CONSTANT VARCHAR2(100) :=
28   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_zero');
29 
30 D_start_date_le_end_date CONSTANT VARCHAR2(100) :=
31   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'start_date_le_end_date');
32 
33 D_break_start_ge_blanket_start CONSTANT VARCHAR2(100) :=
34   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_start_ge_blanket_start');
35 
36 D_break_start_le_blanket_end CONSTANT VARCHAR2(100) :=
37   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_start_le_blanket_end');
38 
39 D_break_start_le_expiration CONSTANT VARCHAR2(100) :=
40   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_start_le_expiration');
41 
42 D_break_end_le_expiration CONSTANT VARCHAR2(100) :=
43   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_end_le_expiration');
44 
45 D_break_end_ge_blanket_start CONSTANT VARCHAR2(100) :=
46   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_end_ge_blanket_start');
47 
48 D_break_end_le_blanket_end CONSTANT VARCHAR2(100) :=
49   PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'break_end_le_blanket_end');
50 
51 
52 -----------------------------------------------------------------------------
53 --  This procedure determines if for each price break, at least one of the
54 --  the following fields is filled out: start date, end date, quantity, ship to
55 --  org, ship to loction. If not, return a failure.
56 ------------------------------------------------------------------------------
57 PROCEDURE at_least_one_required_field(
58   p_line_loc_id_tbl IN  PO_TBL_NUMBER
59 , p_start_date_tbl  IN  PO_TBL_DATE
60 , p_end_date_tbl    IN  PO_TBL_DATE
61 , p_quantity_tbl    IN  PO_TBL_NUMBER
62 , p_ship_to_org_id_tbl  IN  PO_TBL_NUMBER
63 , p_ship_to_loc_id_tbl  IN  PO_TBL_NUMBER
64 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
65 , x_result_type   OUT NOCOPY    VARCHAR2
66 )
67 IS
68 d_mod CONSTANT VARCHAR2(100) := D_at_least_one_required_field;
69 l_results_count NUMBER;
70 BEGIN
71 
72 IF PO_LOG.d_proc THEN
73   PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
74   PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
75   PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
76   PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
77   PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
78   PO_LOG.proc_begin(d_mod,'p_ship_to_loc_id_tbl',p_ship_to_loc_id_tbl);
79   PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
80 END IF;
81 
82 IF (x_results IS NULL) THEN
83   x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
84 END IF;
85 
86 l_results_count := x_results.result_type.COUNT;
87 
88 FOR i IN 1 .. p_line_loc_id_tbl.COUNT LOOP
89   IF (    p_start_date_tbl(i) IS NULL
90       AND p_end_date_tbl(i) IS NULL
91       AND NVL(p_quantity_tbl(i),0) = 0
92       AND p_ship_to_org_id_tbl(i) IS NULL
93       AND p_ship_to_loc_id_tbl(i) IS NULL
94     )
95   THEN
96     x_results.add_result(
97       p_entity_type => c_entity_type_LINE_LOCATION
98     , p_entity_id => p_line_loc_id_tbl(i)
99     , p_column_name => NULL
100     , p_message_name => PO_MESSAGE_S.POX_PRICEBREAK_ITEM_FAILED
101     );
102   END IF;
103 END LOOP;
104 
105 IF (l_results_count < x_results.result_type.COUNT) THEN
106   x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
107 ELSE
108   x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
109 END IF;
110 
111 IF PO_LOG.d_proc THEN
112   PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
113   PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
114 END IF;
115 
116 EXCEPTION
117 WHEN OTHERS THEN
118   IF PO_LOG.d_exc THEN
119     PO_LOG.exc(d_mod,0,NULL);
120   END IF;
121   RAISE;
122 
123 END at_least_one_required_field;
124 
125 ------------------------------------------------------------------------------
126 --  This procedure determines if Price Discounts is a number between 0 and 100.
127 --  If not, return a failure.
128 -------------------------------------------------------------------------------
129 PROCEDURE price_discount_in_percent(
130   p_line_loc_id_tbl IN  PO_TBL_NUMBER
131 , p_price_discount_tbl IN PO_TBL_NUMBER
132 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
133 , x_result_type   OUT NOCOPY    VARCHAR2
134 )
135 IS
136 BEGIN
137 
138 PO_VALIDATION_HELPER.within_percentage_range(
139   p_calling_module => D_price_discount_in_percent
140 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
141 , p_value_tbl => p_price_discount_tbl
142 , p_entity_id_tbl => p_line_loc_id_tbl
143 , p_entity_type => c_entity_type_LINE_LOCATION
144 , p_column_name => c_PRICE_DISCOUNT
145 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
146 , x_results => x_results
147 , x_result_type => x_result_type
148 );
149 
150 END price_discount_in_percent;
151 
152 -------------------------------------------------------------------------------
153 --  This procedure determines if Price Override('Break Price') is greater
154 --  than zero. If not, return a failure.
155 -------------------------------------------------------------------------------
156 PROCEDURE price_override_gt_zero(
157   p_line_loc_id_tbl IN  PO_TBL_NUMBER
158 , p_price_override_tbl  IN  PO_TBL_NUMBER
159 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
160 , x_result_type   OUT NOCOPY    VARCHAR2
161 )
162 IS
163 BEGIN
164 
165 PO_VALIDATION_HELPER.greater_than_zero(
166   p_calling_module => D_price_override_gt_zero
167 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
168 , p_value_tbl => p_price_override_tbl
169 , p_entity_id_tbl => p_line_loc_id_tbl
170 , p_entity_type => c_entity_type_LINE_LOCATION
171 , p_column_name => c_PRICE_OVERRIDE
172 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
173 , x_results => x_results
174 , x_result_type => x_result_type
175 );
176 
177 END price_override_gt_zero;
178 
179 -------------------------------------------------------------------------------
180 --  This procedure determines if Quantity is greater than or equal to zero.
181 --  If not, return a failure.
182 -------------------------------------------------------------------------------
183 PROCEDURE quantity_ge_zero(
184   p_line_loc_id_tbl IN  PO_TBL_NUMBER
185 , p_quantity_tbl    IN  PO_TBL_NUMBER
186 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
187 , x_result_type   OUT NOCOPY    VARCHAR2
188 )
189 IS
190 BEGIN
191 
192 PO_VALIDATION_HELPER.greater_or_equal_zero(
193   p_calling_module => D_quantity_ge_zero
194 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
195 , p_value_tbl => p_quantity_tbl
196 , p_entity_id_tbl => p_line_loc_id_tbl
197 , p_entity_type => c_entity_type_LINE_LOCATION
198 , p_column_name => c_QUANTITY
199 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
200 , x_results => x_results
201 , x_result_type => x_result_type
202 );
203 
204 END quantity_ge_zero;
205 
206 
207 ------------------------------------------------------------------------
208 -- Validates that the Effective From date is less than or equal to
209 -- the Effective End date.
210 ------------------------------------------------------------------------
211 PROCEDURE start_date_le_end_date(
212   p_line_loc_id_tbl IN  PO_TBL_NUMBER
213 , p_start_date_tbl  IN  PO_TBL_DATE
214 , p_end_date_tbl    IN  PO_TBL_DATE
215 , x_results         IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
216 , x_result_type     OUT NOCOPY    VARCHAR2
217 )
218 IS
219 BEGIN
220 
221 PO_VALIDATION_HELPER.start_date_le_end_date(
222   p_calling_module => D_start_date_le_end_date
223 , p_start_date_tbl => p_start_date_tbl
224 , p_end_date_tbl => p_end_date_tbl
225 , p_entity_id_tbl => p_line_loc_id_tbl
226 , p_entity_type => c_entity_type_LINE_LOCATION
227 , p_column_name => NULL
228 , p_column_val_selector => NULL
229 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES3
230 , x_results => x_results
231 , x_result_type => x_result_type
232 );
233 
234 END start_date_le_end_date;
235 
236 
237 -----------------------------------------------------------------------------
238 -- Validates that the Effective From date of the price break
239 -- is greater than or equal to the Effective From date
240 -- of the Agreement.
241 -----------------------------------------------------------------------------
242 PROCEDURE break_start_ge_blanket_start(
243   p_line_loc_id_tbl             IN  PO_TBL_NUMBER
244 , p_blanket_start_date_tbl      IN  PO_TBL_DATE
245 , p_price_break_start_date_tbl  IN  PO_TBL_DATE
246 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
247 , x_result_type   OUT NOCOPY    VARCHAR2
248 )
249 IS
250 BEGIN
251 
252 PO_VALIDATION_HELPER.start_date_le_end_date(
253   p_calling_module => D_break_start_ge_blanket_start
254 , p_start_date_tbl => p_blanket_start_date_tbl
255 , p_end_date_tbl => p_price_break_start_date_tbl
256 , p_entity_id_tbl => p_line_loc_id_tbl
257 , p_entity_type => c_entity_type_LINE_LOCATION
258 , p_column_name => c_START_DATE
259 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
260 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES1
261 , x_results => x_results
262 , x_result_type => x_result_type
263 );
264 
265 END break_start_ge_blanket_start;
266 
267 
268 -----------------------------------------------------------------------------
269 -- Validates that the Effective From date of the price break
270 -- is less than or equal to the Effective To date
271 -- of the Agreement.
272 -----------------------------------------------------------------------------
273 PROCEDURE break_start_le_blanket_end(
274   p_line_loc_id_tbl             IN  PO_TBL_NUMBER
275 , p_blanket_end_date_tbl        IN  PO_TBL_DATE
276 , p_price_break_start_date_tbl  IN  PO_TBL_DATE
277 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
278 , x_result_type   OUT NOCOPY    VARCHAR2
279 )
280 IS
281 BEGIN
282 
283 PO_VALIDATION_HELPER.start_date_le_end_date(
284   p_calling_module => D_break_start_le_blanket_end
285 , p_start_date_tbl => p_price_break_start_date_tbl
286 , p_end_date_tbl => p_blanket_end_date_tbl
287 , p_entity_id_tbl => p_line_loc_id_tbl
288 , p_entity_type => c_entity_type_LINE_LOCATION
289 , p_column_name => c_START_DATE
290 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
291 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES4
292 , x_results => x_results
293 , x_result_type => x_result_type
294 );
295 
296 END break_start_le_blanket_end;
297 
298 
299 -----------------------------------------------------------------------------
300 -- Validates that the Effective From date of the price break
301 -- is less than or equal to the Expiration date of the line.
302 -----------------------------------------------------------------------------
303 PROCEDURE break_start_le_expiration(
304   p_line_loc_id_tbl             IN  PO_TBL_NUMBER
305 , p_expiration_date_tbl         IN  PO_TBL_DATE
306 , p_price_break_start_date_tbl  IN  PO_TBL_DATE
307 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
308 , x_result_type   OUT NOCOPY    VARCHAR2
309 )
310 IS
311 BEGIN
312 
313 PO_VALIDATION_HELPER.start_date_le_end_date(
314   p_calling_module => D_break_start_le_expiration
315 , p_start_date_tbl => p_price_break_start_date_tbl
316 , p_end_date_tbl => p_expiration_date_tbl
317 , p_entity_id_tbl => p_line_loc_id_tbl
318 , p_entity_type => c_entity_type_LINE_LOCATION
319 , p_column_name => c_START_DATE
320 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
321 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES6
322 , x_results => x_results
323 , x_result_type => x_result_type
324 );
325 
326 END break_start_le_expiration;
327 
328 
329 -----------------------------------------------------------------------------
330 -- Validates that the Effective To date of the price break
331 -- is less than or equal to the Expiration date of the line.
332 -----------------------------------------------------------------------------
333 PROCEDURE break_end_le_expiration(
334   p_line_loc_id_tbl             IN  PO_TBL_NUMBER
335 , p_expiration_date_tbl         IN  PO_TBL_DATE
336 , p_price_break_end_date_tbl    IN  PO_TBL_DATE
337 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
338 , x_result_type   OUT NOCOPY    VARCHAR2
339 )
340 IS
341 BEGIN
342 
343 PO_VALIDATION_HELPER.start_date_le_end_date(
344   p_calling_module => D_break_end_le_expiration
345 , p_start_date_tbl => p_price_break_end_date_tbl
346 , p_end_date_tbl => p_expiration_date_tbl
347 , p_entity_id_tbl => p_line_loc_id_tbl
348 , p_entity_type => c_entity_type_LINE_LOCATION
349 , p_column_name => c_END_DATE
350 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
351 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES2
352 , x_results => x_results
353 , x_result_type => x_result_type
354 );
355 
356 END break_end_le_expiration;
357 
358 
359 -----------------------------------------------------------------------------
360 -- Validates that the Effective To date of the price break
361 -- is greater than or equal to the Effective From date
362 -- of the Agreement.
363 -----------------------------------------------------------------------------
364 PROCEDURE break_end_ge_blanket_start(
365   p_line_loc_id_tbl           IN  PO_TBL_NUMBER
366 , p_blanket_start_date_tbl    IN  PO_TBL_DATE
367 , p_price_break_end_date_tbl  IN  PO_TBL_DATE
368 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
369 , x_result_type   OUT NOCOPY    VARCHAR2
370 )
371 IS
372 BEGIN
373 
374 PO_VALIDATION_HELPER.start_date_le_end_date(
375   p_calling_module => D_break_end_ge_blanket_start
376 , p_start_date_tbl => p_blanket_start_date_tbl
377 , p_end_date_tbl => p_price_break_end_date_tbl
378 , p_entity_id_tbl => p_line_loc_id_tbl
379 , p_entity_type => c_entity_type_LINE_LOCATION
380 , p_column_name => c_END_DATE
381 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
382 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES5
383 , x_results => x_results
384 , x_result_type => x_result_type
385 );
386 
387 END break_end_ge_blanket_start;
388 
389 
390 -----------------------------------------------------------------------------
391 -- Validates that the Effective To date of the price break
392 -- is less than or equal to the Effective To date of the
393 -- Agreement.
394 -----------------------------------------------------------------------------
395 PROCEDURE break_end_le_blanket_end(
396   p_line_loc_id_tbl             IN  PO_TBL_NUMBER
397 , p_blanket_end_date_tbl        IN  PO_TBL_DATE
398 , p_price_break_end_date_tbl    IN  PO_TBL_DATE
399 , x_results       IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
400 , x_result_type   OUT NOCOPY    VARCHAR2
401 )
402 IS
403 BEGIN
404 
405 PO_VALIDATION_HELPER.start_date_le_end_date(
406   p_calling_module => D_break_end_le_blanket_end
407 , p_start_date_tbl => p_price_break_end_date_tbl
408 , p_end_date_tbl => p_blanket_end_date_tbl
409 , p_entity_id_tbl => p_line_loc_id_tbl
410 , p_entity_type => c_entity_type_LINE_LOCATION
411 , p_column_name => c_END_DATE
412 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
413 , p_message_name => PO_MESSAGE_S.POX_EFFECTIVE_DATES
414 , x_results => x_results
415 , x_result_type => x_result_type
416 );
417 
418 END break_end_le_blanket_end;
419 
420 
421 
422 
423 END PO_VAL_PRICE_BREAKS;