1 PACKAGE BODY PO_VAL_SHIPMENTS2 AS
2 -- $Header: PO_VAL_SHIPMENTS2.plb 120.17.12010000.2 2008/10/29 11:47:48 lswamina ship $
3 c_entity_type_line_location CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_LINE_LOCATION;
4 -- The module base for this package.
5 d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_SHIPMENTS2');
6
7 -- The module base for the subprogram.
8 d_need_by_date CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'NEED_BY_DATE');
9 d_promised_date CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PROMISED_DATE');
10 d_shipment_type CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIPMENT_TYPE');
11 d_shipment_num CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIPMENT_NUM');
12 d_quantity CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'QUANTITY');
13 d_price_override CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_OVERRIDE');
14 d_price_discount CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_DISCOUNT');
15 d_ship_to_organization_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'SHIP_TO_ORGANIZATION_ID');
16 d_price_break_attributes CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_BREAK_ATTRIBUTES');
17 d_effective_dates CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'EFFECTIVE_DATES');
18 d_qty_rcv_exception_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'QTY_RCV_EXCEPTION_CODE');
19 d_enforce_ship_to_loc_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ENFORCE_SHIP_TO_LOC_CODE');
20 d_allow_sub_receipts_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ALLOW_SUB_RECEIPTS_FLAG');
21 d_days_early_receipt_allowed CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DAYS_EARLY_RECEIPT_ALLOWD');
22 d_receipt_days_exception_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'RECEIPT_DAYS_EXCEPTION_CODE');
23 d_invoice_close_tolerance CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'INVOICE_CLOSE_TOLERANCE');
24 d_receive_close_tolerance CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'RECEIVE_CLOSE_TOLERANCE');
25 d_receiving_routing_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'RECEIVING_ROUTING_ID');
26 d_accrue_on_receipt_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ACCRUE_ON_RECEIPT_FLAG');
27 d_fob_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'FOB_LOOKUP_CODE');
28 d_freight_terms CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'FREIGHT_TERMS');
29 d_freight_carrier CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'FREIGHT_CARRIER');
30 d_style_related_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'STYLE_RELATED_INFO');
31 d_tax_name CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'TAX_NAME');
32 d_price_break CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_BREAK');
33
34 -- Indicates that the calling program is PDOI.
35 c_program_pdoi CONSTANT VARCHAR2(10) := 'PDOI';
36 -- The application name of PO.
37 c_po CONSTANT VARCHAR2(2) := 'PO';
38
39 -------------------------------------------------------------------------
40 -- if purchase_basis is 'TEMP LABOR', the need_by_date column must be null
41 -------------------------------------------------------------------------
42 PROCEDURE need_by_date(
43 p_id_tbl IN po_tbl_number,
44 p_purchase_basis_tbl IN po_tbl_varchar30,
45 p_need_by_date_tbl IN po_tbl_date,
46 x_results IN OUT NOCOPY po_validation_results_type,
47 x_result_type OUT NOCOPY VARCHAR2)
48 IS
49 d_mod CONSTANT VARCHAR2(100) := d_need_by_date;
50 BEGIN
51 IF (x_results IS NULL) THEN
52 x_results := po_validation_results_type.new_instance();
53 END IF;
54
55 x_result_type := po_validations.c_result_type_success;
56
57 IF po_log.d_proc THEN
58 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
59 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
60 po_log.proc_begin(d_mod, 'p_need_by_date_tbl', p_need_by_date_tbl);
61 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
62 END IF;
63
64 -- if purchase_basis is 'TEMP LABOR', the need_by_date column must be null
65 FOR i IN 1 .. p_id_tbl.COUNT LOOP
66 IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_need_by_date_tbl(i) IS NOT NULL THEN
67 x_results.add_result(p_entity_type => c_entity_type_line_location,
68 p_entity_id => p_id_tbl(i),
69 p_column_name => 'NEED_BY_DATE',
70 p_column_val => p_need_by_date_tbl(i),
71 p_message_name => 'PO_SVC_NO_NEED_PROMISE_DATE',
72 p_validation_id => PO_VAL_CONSTANTS.c_loc_need_by_date);
73 x_result_type := po_validations.c_result_type_failure;
74 END IF;
75 END LOOP;
76
77 IF po_log.d_proc THEN
78 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
79 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
80 END IF;
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF po_log.d_exc THEN
84 po_log.exc(d_mod, 0, NULL);
85 END IF;
86
87 RAISE;
88 END need_by_date;
89
90 -------------------------------------------------------------------------
91 -- if purchase_basis is 'TEMP LABOR', the promised_date must be null
92 -------------------------------------------------------------------------
93 PROCEDURE promised_date(
94 p_id_tbl IN po_tbl_number,
95 p_purchase_basis_tbl IN po_tbl_varchar30,
96 p_promised_date_tbl IN po_tbl_date,
97 x_results IN OUT NOCOPY po_validation_results_type,
98 x_result_type OUT NOCOPY VARCHAR2)
99 IS
100 d_mod CONSTANT VARCHAR2(100) := d_promised_date;
101 BEGIN
102 IF (x_results IS NULL) THEN
103 x_results := po_validation_results_type.new_instance();
104 END IF;
105
106 x_result_type := po_validations.c_result_type_success;
107
108 IF po_log.d_proc THEN
109 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
110 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
111 po_log.proc_begin(d_mod, 'p_promised_date_tbl', p_promised_date_tbl);
112 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
113 END IF;
114
115 -- if purchase_basis is 'TEMP LABOR', the need_by_date column must be null
116 FOR i IN 1 .. p_id_tbl.COUNT LOOP
117 IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_promised_date_tbl(i) IS NOT NULL THEN
118 x_results.add_result(p_entity_type => c_entity_type_line_location,
119 p_entity_id => p_id_tbl(i),
120 p_column_name => 'PROMISE_DATE',
121 p_column_val => p_promised_date_tbl(i),
122 p_message_name => 'PO_SVC_NO_NEED_PROMISE_DATE',
123 p_validation_id => PO_VAL_CONSTANTS.c_loc_promised_date);
124 x_result_type := po_validations.c_result_type_failure;
125 END IF;
126 END LOOP;
127
128 IF po_log.d_proc THEN
129 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
130 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF po_log.d_exc THEN
135 po_log.exc(d_mod, 0, NULL);
136 END IF;
137
138 RAISE;
139 END promised_date;
140
141 -------------------------------------------------------------------------
142 -- validate shipment type
143 -------------------------------------------------------------------------
144 PROCEDURE shipment_type(
145 p_id_tbl IN po_tbl_number,
146 p_shipment_type_tbl IN po_tbl_varchar30,
147 p_doc_type IN VARCHAR2,
148 x_results IN OUT NOCOPY po_validation_results_type,
149 x_result_type OUT NOCOPY VARCHAR2)
150 IS
151 d_mod CONSTANT VARCHAR2(100) := d_shipment_type;
152 BEGIN
153 IF (x_results IS NULL) THEN
154 x_results := po_validation_results_type.new_instance();
155 END IF;
156
157 x_result_type := po_validations.c_result_type_success;
158
159 IF po_log.d_proc THEN
160 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
161 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
162 po_log.proc_begin(d_mod, 'p_doc_type', p_doc_type);
163 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
164 END IF;
165
166 -- if shipment_type is Null
167 FOR i IN 1 .. p_id_tbl.COUNT LOOP
168 IF p_shipment_type_tbl(i) IS NULL THEN
169 x_results.add_result(p_entity_type => c_entity_type_line_location,
170 p_entity_id => p_id_tbl(i),
171 p_column_name => 'SHIPMENT_TYPE',
172 p_column_val => p_shipment_type_tbl(i),
173 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
174 p_validation_id => PO_VAL_CONSTANTS.c_shipment_type_not_null);
175 x_result_type := po_validations.c_result_type_failure;
176 ELSIF (p_doc_type = 'QUOTATION' AND p_shipment_type_tbl(i) <> 'QUOTATION')
177 OR (p_doc_type = 'BLANKET' AND p_shipment_type_tbl(i) <> 'PRICE BREAK')
178 OR (p_doc_type = 'STANDARD' AND p_shipment_type_tbl(i) <> 'STANDARD') THEN
179 x_results.add_result(p_entity_type => c_entity_type_line_location,
180 p_entity_id => p_id_tbl(i),
181 p_column_name => 'SHIPMENT_TYPE',
182 p_column_val => p_shipment_type_tbl(i),
183 p_message_name => 'PO_PDOI_INVALID_SHIPMENT_TYPE',
184 p_validation_id => PO_VAL_CONSTANTS.c_shipment_type_valid);
185 x_result_type := po_validations.c_result_type_failure;
186 END IF;
187 END LOOP;
188
189 IF po_log.d_proc THEN
190 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
191 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
192 END IF;
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF po_log.d_exc THEN
196 po_log.exc(d_mod, 0, NULL);
197 END IF;
198
199 RAISE;
200 END shipment_type;
201
202 -------------------------------------------------------------------------
203 -- validate shipment num is not null, greater than zero and unique
204 -------------------------------------------------------------------------
205 PROCEDURE shipment_num(
206 p_id_tbl IN po_tbl_number,
207 p_shipment_num_tbl IN po_tbl_number,
208 p_shipment_type_tbl IN po_tbl_varchar30,
209 p_po_header_id_tbl IN po_tbl_number,
210 p_po_line_id_tbl IN po_tbl_number,
211 p_draft_id_tbl IN PO_TBL_NUMBER, -- bug 4642348
212 p_doc_type IN VARCHAR2, -- bug 4642348
213 x_result_set_id IN OUT NOCOPY NUMBER,
214 x_results IN OUT NOCOPY po_validation_results_type,
215 x_result_type OUT NOCOPY VARCHAR2)
216 IS
217 d_mod CONSTANT VARCHAR2(100) := d_shipment_num;
218 BEGIN
219 IF x_result_set_id IS NULL THEN
220 x_result_set_id := po_validations.next_result_set_id();
221 END IF;
222
223 IF (x_results IS NULL) THEN
224 x_results := po_validation_results_type.new_instance();
225 END IF;
226
227 IF po_log.d_proc THEN
228 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
229 po_log.proc_begin(d_mod, 'p_shipment_num_tbl', p_shipment_num_tbl);
230 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
231 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
232 po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
233 po_log.proc_begin(d_mod, 'p_draft_id_tbl', p_draft_id_tbl);
234 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
235 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
236 END IF;
237
238 x_result_type := po_validations.c_result_type_success;
239
240 -- if shipment_type is Null
241 FOR i IN 1 .. p_id_tbl.COUNT LOOP
242 IF p_shipment_num_tbl(i) IS NULL THEN
243 x_results.add_result(p_entity_type => c_entity_type_line_location,
244 p_entity_id => p_id_tbl(i),
245 p_column_name => 'SHIPMENT_NUM',
246 p_column_val => p_shipment_num_tbl(i),
247 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
248 p_token1_name => 'COLUMN_NAME',
249 p_token1_value => 'SHIPMENT_NUM',
250 p_validation_id => PO_VAL_CONSTANTS.c_shipment_num_not_null);
251 x_result_type := po_validations.c_result_type_failure;
252 ELSIF p_shipment_num_tbl(i) <= 0 THEN
253 x_results.add_result(p_entity_type => c_entity_type_line_location,
254 p_entity_id => p_id_tbl(i),
255 p_column_name => 'SHIPMENT_NUM',
256 p_column_val => p_shipment_num_tbl(i),
257 p_message_name => 'PO_PDOI_LT_ZERO',
258 p_token1_name => 'COLUMN_NAME',
259 p_token1_value => 'SHIPMENT_NUM',
260 p_token2_name => 'VALUE',
261 p_token2_value => p_shipment_num_tbl(i),
262 p_validation_id => PO_VAL_CONSTANTS.c_shipment_num_gt_zero);
263 x_result_type := po_validations.c_result_type_failure;
264 END IF;
265 END LOOP;
266
267 -- Validate shipment number is unique
268 FORALL i IN 1 .. p_id_tbl.COUNT
269 INSERT INTO po_validation_results_gt
270 (result_set_id,
271 result_type,
272 entity_type,
273 entity_id,
274 message_name,
275 column_name,
276 column_val,
277 token1_name,
278 token1_value,
279 validation_id)
280 SELECT x_result_set_id,
281 po_validations.c_result_type_failure,
282 'PO_LINE_LOCATIONS_DRAFT_ALL',
283 p_id_tbl(i),
284 'PO_PDOI_SHIPMENT_NUM_UNIQUE',
285 'SHIPMENT_NUM',
286 p_shipment_num_tbl(i),
287 'VALUE',
288 p_shipment_num_tbl(i),
289 PO_VAL_CONSTANTS.c_shipment_num_unique
290 FROM DUAL
291 WHERE p_shipment_num_tbl(i) IS NOT NULL AND
292 p_po_header_id_tbl(i) IS NOT NULL AND
293 p_po_line_id_tbl(i) IS NOT NULL AND
294 p_shipment_type_tbl(i) IS NOT NULL AND
295 (EXISTS(SELECT 1
296 FROM po_line_locations_all
297 WHERE po_header_id = p_po_header_id_tbl(i)
298 AND po_line_id = p_po_line_id_tbl(i)
299 AND shipment_num = p_shipment_num_tbl(i)
300 AND p_doc_type = 'BLANKET') -- bug 4642348
301 OR EXISTS(SELECT 1
302 FROM po_line_locations_draft_all
303 WHERE po_header_id = p_po_header_id_tbl(i)
304 AND po_line_id = p_po_line_id_tbl(i)
305 AND draft_id = p_draft_id_tbl(i) -- bug 4642348
306 AND shipment_num = p_shipment_num_tbl(i)
307 AND NVL(delete_flag, 'N') = 'N')); -- bug 4642348
308
309
310 IF (SQL%ROWCOUNT > 0) THEN
311 x_result_type := po_validations.c_result_type_failure;
312 END IF;
313
314 IF po_log.d_proc THEN
315 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
316 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
317 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
318 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
319 END IF;
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF po_log.d_exc THEN
323 po_log.exc(d_mod, 0, NULL);
324 END IF;
325
326 RAISE;
327 END shipment_num;
328
329 -------------------------------------------------------------------------
330 -- If order_type_lookup_code is RATE or FIXED PRICE, quantity must be null;
331 -- If order_type_lookup_code is not RATE or FIXED PRICE and quantity is
332 -- not null, quantity must be greater than or equal to zero.
333 -------------------------------------------------------------------------
334 PROCEDURE quantity(
335 p_id_tbl IN po_tbl_number,
336 p_quantity_tbl IN po_tbl_number,
337 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
338 x_results IN OUT NOCOPY po_validation_results_type,
339 x_result_type OUT NOCOPY VARCHAR2)
340 IS
341 d_mod CONSTANT VARCHAR2(100) := d_quantity;
342 BEGIN
343 IF (x_results IS NULL) THEN
344 x_results := po_validation_results_type.new_instance();
345 END IF;
346
347 x_result_type := po_validations.c_result_type_success;
348
349 IF po_log.d_proc THEN
350 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
351 po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
352 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
353 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
354 END IF;
355
356 -- If order_type_lookup_code is RATE or FIXED PRICE,
357 -- validate quantity must be null
358 FOR i IN 1 .. p_id_tbl.COUNT LOOP
359 IF (p_order_type_lookup_code_tbl(i) = 'RATE' OR p_order_type_lookup_code_tbl(i) = 'FIXED PRICE')
360 AND p_quantity_tbl(i) IS NOT NULL THEN
361 x_results.add_result(p_entity_type => c_entity_type_line_location,
362 p_entity_id => p_id_tbl(i),
363 p_column_name => 'QUANTITY',
364 p_column_val => p_quantity_tbl(i),
365 p_message_name => 'PO_PDOI_SVC_PB_NO_QTY',
366 p_validation_id => PO_VAL_CONSTANTS.c_loc_quantity);
367 x_result_type := po_validations.c_result_type_failure;
368 ELSIF (p_order_type_lookup_code_tbl(i) NOT IN ('FIXED PRICE', 'RATE')
369 AND p_quantity_tbl(i) IS NOT NULL
370 AND p_quantity_tbl(i) < 0) THEN
371 x_results.add_result(p_entity_type => c_entity_type_line_location,
372 p_entity_id => p_id_tbl(i),
373 p_column_name => 'QUANTITY',
374 p_column_val => p_quantity_tbl(i),
375 p_message_name => 'PO_PDOI_LT_ZERO',
376 p_token1_name => 'COLUMN_NAME',
377 p_token1_value => 'QUANTITY',
378 p_token2_name => 'VALUE',
379 p_token2_value => p_quantity_tbl(i),
380 p_validation_id => PO_VAL_CONSTANTS.c_loc_quantity_ge_zero);
381 x_result_type := po_validations.c_result_type_failure;
382
383 END IF;
384 END LOOP;
385
386 IF po_log.d_proc THEN
387 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
388 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
389 END IF;
390 EXCEPTION
391 WHEN OTHERS THEN
392 IF po_log.d_exc THEN
393 po_log.exc(d_mod, 0, NULL);
394 END IF;
395
396 RAISE;
397 END quantity;
398
399 -------------------------------------------------------------------------
400 -- If order_type_lookup_code is not FIXED PRICE, price_override cannot be null
401 -------------------------------------------------------------------------
402 PROCEDURE price_override(
403 p_id_tbl IN po_tbl_number,
404 p_price_override_tbl IN po_tbl_number,
405 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
406 x_results IN OUT NOCOPY po_validation_results_type,
407 x_result_type OUT NOCOPY VARCHAR2)
408 IS
409 d_mod CONSTANT VARCHAR2(100) := d_price_override;
410 BEGIN
411 IF (x_results IS NULL) THEN
412 x_results := po_validation_results_type.new_instance();
413 END IF;
414
415 x_result_type := po_validations.c_result_type_success;
416
417 IF po_log.d_proc THEN
418 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
419 po_log.proc_begin(d_mod, 'p_price_override_tbl', p_price_override_tbl);
420 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
421 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
422 END IF;
423
424 -- If order_type_lookup_code is not FIXED PRICE, price_override cannot be null
425 FOR i IN 1 .. p_id_tbl.COUNT LOOP
426 IF (p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_price_override_tbl(i) IS NULL) THEN
427 x_results.add_result(p_entity_type => c_entity_type_line_location,
428 p_entity_id => p_id_tbl(i),
429 p_column_name => 'PRICE_OVERRIDE',
430 p_column_val => p_price_override_tbl(i),
431 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
432 p_token1_name => 'COLUMN_NAME',
433 p_token1_value => 'PRICE_OVERRIDE',
434 p_validation_id => PO_VAL_CONSTANTS.c_loc_price_override_not_null);
435 x_result_type := po_validations.c_result_type_failure;
436 ELSIF p_price_override_tbl(i) < 0 THEN
437 x_results.add_result(p_entity_type => c_entity_type_line_location,
438 p_entity_id => p_id_tbl(i),
439 p_column_name => 'PRICE_OVERRIDE',
440 p_column_val => p_price_override_tbl(i),
441 p_message_name => 'PO_PDOI_LT_ZERO',
442 p_token1_name => 'COLUMN_NAME',
443 p_token1_value => 'PRICE_OVERRIDE',
444 p_token2_name => 'VALUE',
445 p_token2_value => p_price_override_tbl(i),
446 p_validation_id => PO_VAL_CONSTANTS.c_loc_price_override_ge_zero);
447 x_result_type := po_validations.c_result_type_failure;
448 END IF;
449 END LOOP;
450
451 IF po_log.d_proc THEN
452 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
453 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
454 END IF;
455 EXCEPTION
456 WHEN OTHERS THEN
457 IF po_log.d_exc THEN
458 po_log.exc(d_mod, 0, NULL);
459 END IF;
460
461 RAISE;
462 END price_override;
463
464 -------------------------------------------------------------------------
465 -- If order_type_lookup_code is not FIXED PRICE, price_discount cannot be null
466 -- and price discount cannot be greater than 100
467 -------------------------------------------------------------------------
468 PROCEDURE price_discount(
469 p_id_tbl IN po_tbl_number,
470 p_price_discount_tbl IN po_tbl_number,
471 p_price_override_tbl IN po_tbl_number,
472 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
473 x_results IN OUT NOCOPY po_validation_results_type,
474 x_result_type OUT NOCOPY VARCHAR2)
475 IS
476 d_mod CONSTANT VARCHAR2(100) := d_price_discount;
477 BEGIN
478 IF (x_results IS NULL) THEN
479 x_results := po_validation_results_type.new_instance();
480 END IF;
481
482 x_result_type := po_validations.c_result_type_success;
483
484 IF po_log.d_proc THEN
485 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
486 po_log.proc_begin(d_mod, 'p_price_discount_tbl', p_price_discount_tbl);
487 po_log.proc_begin(d_mod, 'p_price_override_tbl', p_price_override_tbl);
488 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
489 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
490 END IF;
491
492 -- If order_type_lookup_code is not FIXED PRICE and price_discount/price override cannot both be null
493 -- and price discount cannot be greater than 100
494 FOR i IN 1 .. p_id_tbl.COUNT LOOP
495 IF (p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND
496 p_price_discount_tbl(i) IS NULL AND p_price_override_tbl(i) IS NULL) THEN
497 x_results.add_result(p_entity_type => c_entity_type_line_location,
498 p_entity_id => p_id_tbl(i),
499 p_column_name => 'PRICE_DISCOUNT',
500 p_column_val => p_price_discount_tbl(i),
501 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
502 p_token1_name => 'COLUMN_NAME',
503 p_token1_value => 'PRICE_DISCOUNT',
504 p_validation_id => PO_VAL_CONSTANTS.c_loc_price_discount_not_null);
505 x_result_type := po_validations.c_result_type_failure;
506
507 -- Bug 6614819 -- Commented the code below --
508 -- Since negative price break discount is allowed from the form,
509 -- now it is allowed from PDOI also.
510 -- Therefore removing the check for negative price discount below.
511 ELSIF( p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_price_discount_tbl(i) IS NOT NULL AND
512 (/*p_price_discount_tbl(i) < 0 OR */p_price_discount_tbl(i) > 100)) THEN
513 x_results.add_result(p_entity_type => c_entity_type_line_location,
514 p_entity_id => p_id_tbl(i),
515 p_column_name => 'PRICE_DISCOUNT',
516 p_column_val => p_price_discount_tbl(i),
517 p_message_name => 'PO_PDOI_INVALID_DISCOUNT',
518 p_token1_name => 'VALUE',
519 p_token1_value => p_price_discount_tbl(i),
520 p_validation_id => PO_VAL_CONSTANTS.c_loc_price_discount_valid);
521 x_result_type := po_validations.c_result_type_failure;
522 END IF;
523 END LOOP;
524
525 IF po_log.d_proc THEN
526 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
527 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
528 END IF;
529 EXCEPTION
530 WHEN OTHERS THEN
531 IF po_log.d_exc THEN
532 po_log.exc(d_mod, 0, NULL);
533 END IF;
534
535 RAISE;
536 END price_discount;
537
538 -------------------------------------------------------------------------
539 -- validate ship_to_organization_id
540 -------------------------------------------------------------------------
541 PROCEDURE ship_to_organization_id(
542 p_id_tbl IN po_tbl_number,
543 p_ship_to_organization_id_tbl IN po_tbl_number,
544 p_item_id_tbl IN po_tbl_number,
545 p_item_revision_tbl IN po_tbl_varchar5,
546 p_ship_to_location_id_tbl IN po_tbl_number,
547 x_result_set_id IN OUT NOCOPY NUMBER,
548 x_result_type OUT NOCOPY VARCHAR2)
549 IS
550 d_mod CONSTANT VARCHAR2(100) := d_ship_to_organization_id;
551 BEGIN
552 IF x_result_set_id IS NULL THEN
553 x_result_set_id := po_validations.next_result_set_id();
554 END IF;
555
556 x_result_type := po_validations.c_result_type_success;
557
558 IF po_log.d_proc THEN
559 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
560 po_log.proc_begin(d_mod, 'p_ship_to_organization_id_tbl', p_ship_to_organization_id_tbl);
561 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
562 po_log.proc_begin(d_mod, 'p_item_revision_tbl', p_item_revision_tbl);
563 po_log.proc_begin(d_mod, 'p_ship_to_location_id_tbl', p_ship_to_location_id_tbl);
564 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
565 END IF;
566
567 -- If item_id is not null and ship_to_organization_id is not null, and
568 -- item_revision is not null and no record exists in mtl_item_revisions.
569 FORALL i IN 1 .. p_id_tbl.COUNT
570 INSERT INTO po_validation_results_gt
571 (result_set_id,
572 result_type,
573 entity_type,
574 entity_id,
575 message_name,
576 column_name,
577 column_val,
578 token1_name,
579 token1_value,
580 validation_id)
581 SELECT x_result_set_id,
582 po_validations.c_result_type_failure,
583 c_entity_type_line_location,
584 p_id_tbl(i),
585 'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
586 'SHIP_TO_ORGANIZATION_ID',
587 p_ship_to_organization_id_tbl(i),
588 'SHIP_TO_ORGANIZATION_ID',
589 p_ship_to_organization_id_tbl(i),
590 PO_VAL_CONSTANTS.c_ship_to_organization_id
591 FROM DUAL
592 WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
593 AND p_item_revision_tbl(i) IS NOT NULL
594 AND p_item_id_tbl(i) IS NOT NULL
595 AND NOT EXISTS(
596 SELECT 1
597 FROM mtl_item_revisions mir
598 WHERE mir.inventory_item_id = p_item_id_tbl(i)
599 AND mir.revision = p_item_revision_tbl(i)
600 AND mir.organization_id = p_ship_to_organization_id_tbl(i));
601
602 IF (SQL%ROWCOUNT > 0) THEN
603 x_result_type := po_validations.c_result_type_failure;
604 END IF;
605
606 -- If item_id is not null and ship_to_organization_id is not null, and
607 -- item_revision is null, and no record exists in mtl_system_items.
608 -- Bug7513119 - Non revision controlled items were also getting validated
609 -- against mtl_item_revisions, changed this to mtl_system_items.
610 FORALL i IN 1 .. p_id_tbl.COUNT
611 INSERT INTO po_validation_results_gt
612 (result_set_id,
613 result_type,
614 entity_type,
615 entity_id,
616 message_name,
617 column_name,
618 column_val,
619 token1_name,
620 token1_value,
621 validation_id)
622 SELECT x_result_set_id,
623 po_validations.c_result_type_failure,
624 c_entity_type_line_location,
625 p_id_tbl(i),
626 'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
627 'SHIP_TO_ORGANIZATION_ID',
628 p_ship_to_organization_id_tbl(i),
629 'SHIP_TO_ORGANIZATION_ID',
630 p_ship_to_organization_id_tbl(i),
631 PO_VAL_CONSTANTS.c_ship_to_organization_id
632 FROM DUAL
633 WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
634 AND p_item_revision_tbl(i) IS NULL
635 AND p_item_id_tbl(i) IS NOT NULL
636 AND NOT EXISTS(SELECT 1
637 FROM mtl_system_items msi --Bug7513119
638 WHERE msi.inventory_item_id = p_item_id_tbl(i)
639 AND msi.organization_id = p_ship_to_organization_id_tbl(i));
640
641
642 IF (SQL%ROWCOUNT > 0) THEN
643 x_result_type := po_validations.c_result_type_failure;
644 END IF;
645
646 -- If item_id is null, and ship_to_organization_id is not null,
647 -- validate ship_to_organization_id against org_organization_definitions
648 FORALL i IN 1 .. p_id_tbl.COUNT
649 INSERT INTO po_validation_results_gt
650 (result_set_id,
651 result_type,
652 entity_type,
653 entity_id,
654 message_name,
655 column_name,
656 column_val,
657 token1_name,
658 token1_value,
659 validation_id)
660 SELECT x_result_set_id,
661 po_validations.c_result_type_failure,
662 c_entity_type_line_location,
663 p_id_tbl(i),
664 'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
665 'SHIP_TO_ORGANIZATION_ID',
666 p_ship_to_organization_id_tbl(i),
667 'SHIP_TO_ORGANIZATION_ID',
668 p_ship_to_organization_id_tbl(i),
669 PO_VAL_CONSTANTS.c_ship_to_organization_id
670 FROM DUAL
671 WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
672 AND p_item_id_tbl(i) IS NULL
673 AND NOT EXISTS(
674 SELECT 1
675 FROM org_organization_definitions ood
676 WHERE ood.organization_id = p_ship_to_organization_id_tbl(i)
677 AND SYSDATE < NVL(ood.disable_date, SYSDATE + 1)
678 AND ood.inventory_enabled_flag = 'Y');
679
680 IF (SQL%ROWCOUNT > 0) THEN
681 x_result_type := po_validations.c_result_type_failure;
682 END IF;
683
684 -- If ship_to_location_id is not null, check if record exists in
685 -- po_locations_val_v by ship_to_location_id and ship_to_organization_id.
686 -- If no record exists, or multiple records exist, then error.
687 FORALL i IN 1 .. p_id_tbl.COUNT
688 INSERT INTO po_validation_results_gt
689 (result_set_id,
690 result_type,
691 entity_type,
692 entity_id,
693 message_name,
694 column_name,
695 column_val,
696 token1_name,
697 token1_value,
698 validation_id)
699 SELECT x_result_set_id,
700 po_validations.c_result_type_failure,
701 c_entity_type_line_location,
702 p_id_tbl(i),
703 'PO_PDOI_INVALID_SHIP_TO_LOC_ID',
704 'SHIP_TO_LOCATION_ID',
705 p_ship_to_location_id_tbl(i),
706 'SHIP_TO_LOCATION_ID',
707 p_ship_to_location_id_tbl(i),
708 PO_VAL_CONSTANTS.c_loc_ship_to_loc_id_valid
709 FROM DUAL
710 WHERE p_ship_to_location_id_tbl(i) IS NOT NULL
711 AND NOT EXISTS(
712 SELECT 1
713 FROM po_locations_val_v PLV
714 WHERE PLV.location_id = p_ship_to_location_id_tbl(i)
715 AND ship_to_site_flag = 'Y'
716 AND ( PLV.inventory_organization_id IS NULL
717 OR PLV.inventory_organization_id = p_ship_to_organization_id_tbl(i)
718 OR p_ship_to_organization_id_tbl(i) IS NULL));
719
720 IF (SQL%ROWCOUNT > 0) THEN
721 x_result_type := po_validations.c_result_type_failure;
722 END IF;
723
724 IF po_log.d_proc THEN
725 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
726 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
727 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
728 END IF;
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF po_log.d_exc THEN
732 po_log.exc(d_mod, 0, NULL);
733 END IF;
734
735 RAISE;
736 END ship_to_organization_id;
737
738 -------------------------------------------------------------------------
739 -- validate price break attributes
740 -------------------------------------------------------------------------
741 PROCEDURE price_break_attributes(
742 p_id_tbl IN po_tbl_number,
743 p_from_date_tbl IN po_tbl_date,
744 p_to_date_tbl IN po_tbl_date,
745 p_quantity_tbl IN po_tbl_number,
746 p_ship_to_org_id_tbl IN po_tbl_number,
747 p_ship_to_location_id_tbl IN po_tbl_number,
748 x_results IN OUT NOCOPY po_validation_results_type,
749 x_result_type OUT NOCOPY VARCHAR2)
750 IS
751 d_mod CONSTANT VARCHAR2(100) := d_price_break_attributes;
752 BEGIN
753 IF (x_results IS NULL) THEN
754 x_results := po_validation_results_type.new_instance();
755 END IF;
756
757 x_result_type := po_validations.c_result_type_success;
758
759 IF po_log.d_proc THEN
760 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
761 po_log.proc_begin(d_mod, 'p_from_date_tbl', p_from_date_tbl);
762 po_log.proc_begin(d_mod, 'p_to_date_tbl', p_to_date_tbl);
763 po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
764 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
765 po_log.proc_begin(d_mod, 'p_ship_to_location_id_tbl', p_ship_to_location_id_tbl);
766 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
767 END IF;
768
769 -- validate price break attributes
770 FOR i IN 1 .. p_id_tbl.COUNT LOOP
771 IF ( p_from_date_tbl(i) IS NULL
772 AND p_to_date_tbl(i) IS NULL
773 AND (p_quantity_tbl(i) IS NULL OR p_quantity_tbl(i) <= 0)
774 AND p_ship_to_org_id_tbl(i) IS NULL
775 AND p_ship_to_location_id_tbl(i) IS NULL) THEN
776 x_results.add_result(p_entity_type => c_entity_type_line_location,
777 p_entity_id => p_id_tbl(i),
778 p_column_name => 'PRICE_BREAK_ATTRIBUTES',
779 p_message_name => 'POX_PRICEBREAK_ITEM_FAILED');
780 x_result_type := po_validations.c_result_type_failure;
781 END IF;
782 END LOOP;
783
784 IF po_log.d_proc THEN
785 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
786 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
787 END IF;
788 EXCEPTION
789 WHEN OTHERS THEN
790 IF po_log.d_exc THEN
791 po_log.exc(d_mod, 0, NULL);
792 END IF;
793
794 RAISE;
795 END price_break_attributes;
796
797 -------------------------------------------------------------------------
798 -- validate_effective_dates
799 -------------------------------------------------------------------------
800 PROCEDURE effective_dates(
801 p_id_tbl IN po_tbl_number,
802 p_line_expiration_date_tbl IN po_tbl_date,
803 p_to_date_tbl IN po_tbl_date,
804 p_from_date_tbl IN po_tbl_date,
805 p_header_start_date_tbl IN po_tbl_date,
806 p_header_end_date_tbl IN po_tbl_date,
807 p_price_break_lookup_code_tbl IN PO_TBL_VARCHAR30, -- bug5016163
808 x_results IN OUT NOCOPY po_validation_results_type,
809 x_result_type OUT NOCOPY VARCHAR2)
810 IS
811 d_mod CONSTANT VARCHAR2(100) := d_effective_dates;
812 BEGIN
813 IF (x_results IS NULL) THEN
814 x_results := po_validation_results_type.new_instance();
815 END IF;
816
817 x_result_type := po_validations.c_result_type_success;
818
819 IF po_log.d_proc THEN
820 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
821 po_log.proc_begin(d_mod, 'p_line_expiration_date_tbl', p_line_expiration_date_tbl);
822 po_log.proc_begin(d_mod, 'p_to_date_tbl', p_to_date_tbl);
823 po_log.proc_begin(d_mod, 'p_from_date_tbl', p_from_date_tbl);
824 po_log.proc_begin(d_mod, 'p_header_start_date_tbl', p_header_start_date_tbl);
825 po_log.proc_begin(d_mod, 'p_header_end_date_tbl', p_header_end_date_tbl);
826 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
827 END IF;
828
829 FOR i IN 1 .. p_id_tbl.COUNT LOOP
830 -- Pricebreak effective from date cannot be earlier than blanket
831 -- agreement header start date
832 IF (p_from_date_tbl(i) IS NOT NULL AND p_from_date_tbl(i) < p_header_start_date_tbl(i)) THEN
833 x_results.add_result(p_entity_type => c_entity_type_line_location,
834 p_entity_id => p_id_tbl(i),
835 p_column_name => 'EFFECTIVE_DATE',
836 p_column_val => p_from_date_tbl(i),
837 p_message_name => 'POX_EFFECTIVE_DATES1',
838 p_validation_id => PO_VAL_CONSTANTS.c_loc_from_date_ge_hdr_start);
839 x_result_type := po_validations.c_result_type_failure;
840 END IF;
841
842 -- Pricebreak effective from date cannot be later than blanket
843 -- agreement header end date
844 IF (p_from_date_tbl(i) IS NOT NULL AND p_from_date_tbl(i) > p_header_end_date_tbl(i)) THEN
845 x_results.add_result(p_entity_type => c_entity_type_line_location,
846 p_entity_id => p_id_tbl(i),
847 p_column_name => 'EFFECTIVE_DATE',
848 p_column_val => p_from_date_tbl(i),
849 p_message_name => 'POX_EFFECTIVE_DATES4',
850 p_validation_id => PO_VAL_CONSTANTS.c_loc_from_date_le_hdr_end);
851 x_result_type := po_validations.c_result_type_failure;
852 END IF;
853
854 -- Pricebreak effective From Date cannot be later than pricebreak
855 -- effective To Date
856 IF (p_from_date_tbl(i) IS NOT NULL AND p_to_date_tbl(i) IS NOT NULL
857 AND p_from_date_tbl(i) > p_to_date_tbl(i)) THEN
858 x_results.add_result(p_entity_type => c_entity_type_line_location,
859 p_entity_id => p_id_tbl(i),
860 p_column_name => 'EFFECTIVE_DATE',
861 p_column_val => p_from_date_tbl(i),
862 p_message_name => 'POX_EFFECTIVE_DATES3',
863 p_validation_id => PO_VAL_CONSTANTS.c_loc_from_date_le_loc_end);
864 x_result_type := po_validations.c_result_type_failure;
865 END IF;
866
867 -- Pricebreak From Date cannot be greater than the pricebreak line
868 -- expiration Date
869 IF (p_from_date_tbl(i) IS NOT NULL AND p_from_date_tbl(i) > p_line_expiration_date_tbl(i)) THEN
870 x_results.add_result(p_entity_type => c_entity_type_line_location,
871 p_entity_id => p_id_tbl(i),
872 p_column_name => 'EFFECTIVE_DATE',
873 p_column_val => p_from_date_tbl(i),
874 p_message_name => 'POX_EFFECTIVE_DATES6',
875 p_validation_id => PO_VAL_CONSTANTS.c_loc_from_date_le_line_end);
876 x_result_type := po_validations.c_result_type_failure;
877 END IF;
878
879 -- Pricebreak effective To Date cannot be later than
880 -- expiration Date, if expiration date exists
881 IF (p_line_expiration_date_tbl(i) IS NOT NULL AND p_to_date_tbl(i) > p_line_expiration_date_tbl(i)) THEN
882 x_results.add_result(p_entity_type => c_entity_type_line_location,
883 p_entity_id => p_id_tbl(i),
884 p_column_name => 'EXPIRATION_DATE',
885 p_column_val => p_line_expiration_date_tbl(i),
886 p_message_name => 'POX_EFFECTIVE_DATES2',
887 p_validation_id => PO_VAL_CONSTANTS.c_loc_end_date_le_line_end);
888 x_result_type := po_validations.c_result_type_failure;
889 END IF;
890
891 -- Pricebreak To Date is greater than Header End date
892 IF (p_header_end_date_tbl(i) IS NOT NULL AND p_to_date_tbl(i) > p_header_end_date_tbl(i)) THEN
893 x_results.add_result(p_entity_type => c_entity_type_line_location,
894 p_entity_id => p_id_tbl(i),
895 p_column_name => 'EXPIRATION_DATE',
896 p_column_val => p_line_expiration_date_tbl(i),
897 p_message_name => 'POX_EFFECTIVE_DATES',
898 p_validation_id => PO_VAL_CONSTANTS.c_loc_end_date_le_hdr_end);
899 x_result_type := po_validations.c_result_type_failure;
900 END IF;
901
902 -- Pricebreak To Date cannot be earlier than Header Start date
903 IF (p_header_start_date_tbl(i) IS NOT NULL AND p_to_date_tbl(i) < p_header_start_date_tbl(i)) THEN
904 x_results.add_result(p_entity_type => c_entity_type_line_location,
905 p_entity_id => p_id_tbl(i),
906 p_column_name => 'START_DATE',
907 p_column_val => p_header_start_date_tbl(i),
908 p_message_name => 'POX_EFFECTIVE_DATES5',
909 p_validation_id => PO_VAL_CONSTANTS.c_loc_end_date_ge_hdr_start);
910 x_result_type := po_validations.c_result_type_failure;
911 END IF;
912
913 -- Pricebreak effective To Date cannot be earlier than Pricebreak
914 -- effective From Date
915 IF (p_to_date_tbl(i) IS NOT NULL AND p_from_date_tbl(i) IS NOT NULL
916 AND p_to_date_tbl(i) < p_from_date_tbl(i)) THEN
917 x_results.add_result(p_entity_type => c_entity_type_line_location,
918 p_entity_id => p_id_tbl(i),
919 p_column_name => 'EFFECTIVE_DATE',
920 p_column_val => p_to_date_tbl(i),
921 p_message_name => 'POX_EFFECTIVE_DATES3',
922 p_validation_id => PO_VAL_CONSTANTS.c_loc_from_date_le_loc_end);
923 x_result_type := po_validations.c_result_type_failure;
924 END IF;
925
926 -- bug5016163
927 -- Time phased pricing cannot go with cumulative pricing
928 IF (p_price_break_lookup_code_tbl(i) = 'CUMULATIVE') THEN
929 IF (p_from_date_tbl(i) IS NOT NULL) THEN
930
931 -- bug5262146
932 -- Added p_validation_id
933
934 x_results.add_result
935 ( p_entity_type => c_entity_type_line_location,
936 p_entity_id => p_id_tbl(i),
937 p_column_name => 'EFFECTIVE_DATE',
938 p_column_val => p_from_date_tbl(i),
939 p_message_name => 'PO_PDOI_CUMULATIVE_FAILED',
940 p_validation_id => PO_VAL_CONSTANTS.c_dates_cumulative_failed);
941
942 x_result_type := po_validations.c_result_type_failure;
943
944 ELSIF (p_to_date_tbl(i) IS NOT NULL) THEN
945
946 -- bug5262146
947 -- Added p_validation_id
948
949 x_results.add_result
950 ( p_entity_type => c_entity_type_line_location,
951 p_entity_id => p_id_tbl(i),
952 p_column_name => 'EFFECTIVE_DATE',
953 p_column_val => p_to_date_tbl(i),
954 p_message_name => 'PO_PDOI_CUMULATIVE_FAILED',
955 p_validation_id => PO_VAL_CONSTANTS.c_dates_cumulative_failed);
956
957 x_result_type := po_validations.c_result_type_failure;
958 END IF;
959 END IF;
960
961 END LOOP;
962
963 IF po_log.d_proc THEN
964 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
965 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
966 END IF;
967 EXCEPTION
968 WHEN OTHERS THEN
969 IF po_log.d_exc THEN
970 po_log.exc(d_mod, 0, NULL);
971 END IF;
972
973 RAISE;
974 END effective_dates;
975
976 -------------------------------------------------------------------------
977 -- validate qty_rcv_exception_code against PO_LOOKUP_CODES
978 -------------------------------------------------------------------------
979 PROCEDURE qty_rcv_exception_code(
980 p_id_tbl IN po_tbl_number,
981 p_qty_rcv_exception_code_tbl IN po_tbl_varchar30,
982 x_result_set_id IN OUT NOCOPY NUMBER,
983 x_result_type OUT NOCOPY VARCHAR2)
984 IS
985 d_mod CONSTANT VARCHAR2(100) := d_qty_rcv_exception_code;
986 BEGIN
987 IF x_result_set_id IS NULL THEN
988 x_result_set_id := po_validations.next_result_set_id();
989 END IF;
990
991 x_result_type := po_validations.c_result_type_success;
992
993 IF po_log.d_proc THEN
994 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
995 po_log.proc_begin(d_mod, 'p_qty_rcv_exception_code_tbl', p_qty_rcv_exception_code_tbl);
996 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
997 END IF;
998
999 FORALL i IN 1 .. p_id_tbl.COUNT
1000 INSERT INTO po_validation_results_gt
1001 (result_set_id,
1002 result_type,
1003 entity_type,
1004 entity_id,
1005 message_name,
1006 column_name,
1007 column_val,
1008 token1_name,
1009 token1_value,
1010 validation_id)
1011 SELECT x_result_set_id,
1012 po_validations.c_result_type_failure,
1013 c_entity_type_line_location,
1014 p_id_tbl(i),
1015 'PO_PDOI_INVALID_RCV_EXCEP_CD',
1016 'QTY_RCV_EXCEPTION_CODE',
1017 p_qty_rcv_exception_code_tbl(i),
1018 'QTY_RCV_EXCEPTION_CODE',
1019 p_qty_rcv_exception_code_tbl(i),
1020 PO_VAL_CONSTANTS.c_qty_ecv_exception_code
1021 FROM DUAL
1022 WHERE p_qty_rcv_exception_code_tbl(i) IS NOT NULL
1023 AND NOT EXISTS(
1024 SELECT 1
1025 FROM po_lookup_codes plc
1026 WHERE p_qty_rcv_exception_code_tbl(i) = plc.lookup_code
1027 AND plc.lookup_type = 'RECEIVING CONTROL LEVEL'
1028 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1));
1029
1030 IF (SQL%ROWCOUNT > 0) THEN
1031 x_result_type := po_validations.c_result_type_failure;
1032 ELSE
1033 x_result_type := po_validations.c_result_type_success;
1034 END IF;
1035
1036 IF po_log.d_proc THEN
1037 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1038 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1039 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1040 END IF;
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 IF po_log.d_exc THEN
1044 po_log.exc(d_mod, 0, NULL);
1045 END IF;
1046
1047 RAISE;
1048 END qty_rcv_exception_code;
1049
1050 -------------------------------------------------------------------------
1051 -- If shipment_type is STANDARD and enforce_ship_to_loc_code is not equal
1052 -- to NONE, REJECT or WARNING
1053 -------------------------------------------------------------------------
1054 PROCEDURE enforce_ship_to_loc_code(
1055 p_id_tbl IN po_tbl_number,
1056 p_enforce_ship_to_loc_code_tbl IN po_tbl_varchar30,
1057 p_shipment_type_tbl IN po_tbl_varchar30,
1058 x_results IN OUT NOCOPY po_validation_results_type,
1059 x_result_type OUT NOCOPY VARCHAR2)
1060 IS
1061 d_mod CONSTANT VARCHAR2(100) := d_enforce_ship_to_loc_code;
1062 BEGIN
1063 IF (x_results IS NULL) THEN
1064 x_results := po_validation_results_type.new_instance();
1065 END IF;
1066
1067 x_result_type := po_validations.c_result_type_success;
1068
1069 IF po_log.d_proc THEN
1070 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1071 po_log.proc_begin(d_mod, 'p_enforce_ship_to_loc_code_tbl', p_enforce_ship_to_loc_code_tbl);
1072 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1073 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1074 END IF;
1075
1076 -- If shipment_type is STANDARD and enforce_ship_to_loc_code is not equal
1077 -- to NONE, REJECT or WARNING
1078 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1079 IF ( p_shipment_type_tbl(i) = 'STANDARD'
1080 AND p_enforce_ship_to_loc_code_tbl(i) IS NOT NULL
1081 AND (p_enforce_ship_to_loc_code_tbl(i) NOT IN('NONE', 'REJECT', 'WARNING'))) THEN
1082 x_results.add_result(p_entity_type => c_entity_type_line_location,
1083 p_entity_id => p_id_tbl(i),
1084 p_column_name => 'ENFORCE_SHIP_TO_LOC_CODE',
1085 p_column_val => p_enforce_ship_to_loc_code_tbl(i),
1086 p_message_name => 'PO_PDOI_INVALID_EN_SH_LOC_CODE'
1087 );
1088 x_result_type := po_validations.c_result_type_failure;
1089 END IF;
1090 END LOOP;
1091
1092 IF po_log.d_proc THEN
1093 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1094 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1095 END IF;
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 IF po_log.d_exc THEN
1099 po_log.exc(d_mod, 0, NULL);
1100 END IF;
1101
1102 RAISE;
1103 END enforce_ship_to_loc_code;
1104
1105 -------------------------------------------------------------------------
1106 -- If shipment_type is STANDARD and allow_sub_receipts_flag is not equal
1107 -- to NONE, REJECT or WARNING
1108 -------------------------------------------------------------------------
1109 PROCEDURE allow_sub_receipts_flag(
1110 p_id_tbl IN po_tbl_number,
1111 p_shipment_type_tbl IN po_tbl_varchar30,
1112 p_allow_sub_receipts_flag_tbl IN po_tbl_varchar1,
1113 x_results IN OUT NOCOPY po_validation_results_type,
1114 x_result_type OUT NOCOPY VARCHAR2)
1115 IS
1116 d_mod CONSTANT VARCHAR2(100) := d_allow_sub_receipts_flag;
1117 BEGIN
1118 IF (x_results IS NULL) THEN
1119 x_results := po_validation_results_type.new_instance();
1120 END IF;
1121
1122 x_result_type := po_validations.c_result_type_success;
1123
1124 IF po_log.d_proc THEN
1125 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1126 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1127 po_log.proc_begin(d_mod, 'p_allow_sub_receipts_flag_tbl', p_allow_sub_receipts_flag_tbl);
1128 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1129 END IF;
1130
1131 -- If shipment_type is STANDARD and allow_sub_receipts_flag is not null and
1132 -- not equal to NONE, REJECT or WARNING
1133 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1134 IF p_shipment_type_tbl(i) = 'STANDARD' AND p_allow_sub_receipts_flag_tbl(i) IS NOT NULL
1135 AND p_allow_sub_receipts_flag_tbl(i) NOT IN('Y', 'N') THEN
1136 x_results.add_result(p_entity_type => c_entity_type_line_location,
1137 p_entity_id => p_id_tbl(i),
1138 p_column_name => 'ALLOW_SUB_RECEIPTS_FLAG',
1139 p_column_val => p_allow_sub_receipts_flag_tbl(i),
1140 p_message_name => 'PO_PDOI_ALLOW_SUB_REC_FLAG');
1141 x_result_type := po_validations.c_result_type_failure;
1142 END IF;
1143 END LOOP;
1144
1145 IF po_log.d_proc THEN
1146 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1147 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1148 END IF;
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 IF po_log.d_exc THEN
1152 po_log.exc(d_mod, 0, NULL);
1153 END IF;
1154
1155 RAISE;
1156 END allow_sub_receipts_flag;
1157
1158 -------------------------------------------------------------------------
1159 -- If shipment_type is STANDARD and days_early_receipt_allowed is not null
1160 -- and less than zero.
1161 -------------------------------------------------------------------------
1162 PROCEDURE days_early_receipt_allowed(
1163 p_id_tbl IN po_tbl_number,
1164 p_shipment_type_tbl IN po_tbl_varchar30,
1165 p_days_early_rcpt_allowed_tbl IN po_tbl_number,
1166 x_results IN OUT NOCOPY po_validation_results_type,
1167 x_result_type OUT NOCOPY VARCHAR2)
1168 IS
1169 d_mod CONSTANT VARCHAR2(100) := d_days_early_receipt_allowed;
1170 BEGIN
1171 IF (x_results IS NULL) THEN
1172 x_results := po_validation_results_type.new_instance();
1173 END IF;
1174
1175 x_result_type := po_validations.c_result_type_success;
1176
1177 IF po_log.d_proc THEN
1178 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1179 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1180 po_log.proc_begin(d_mod, 'p_days_early_rcpt_allowed_tbl', p_days_early_rcpt_allowed_tbl);
1181 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1182 END IF;
1183
1184 -- If shipment_type is STANDARD and days_early_receipt_allowed is not null
1185 -- and less than zero.
1186 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1187 IF (p_shipment_type_tbl(i) = 'STANDARD'
1188 AND p_days_early_rcpt_allowed_tbl(i) IS NOT NULL
1189 AND p_days_early_rcpt_allowed_tbl(i) < 0) THEN
1190 x_results.add_result(p_entity_type => c_entity_type_line_location,
1191 p_entity_id => p_id_tbl(i),
1192 p_column_name => 'DAYS_EARLY_RECEIPT_ALLOWED',
1193 p_column_val => p_days_early_rcpt_allowed_tbl(i),
1194 p_message_name => 'PO_PDOI_DAYS_EARLY_REC_ALLOWED');
1195 x_result_type := po_validations.c_result_type_failure;
1196 END IF;
1197 END LOOP;
1198
1199 IF po_log.d_proc THEN
1200 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1201 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1202 END IF;
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 IF po_log.d_exc THEN
1206 po_log.exc(d_mod, 0, NULL);
1207 END IF;
1208
1209 RAISE;
1210 END days_early_receipt_allowed;
1211
1212 -------------------------------------------------------------------------
1213 -- If shipment_type is STANDARD and receipt_days_expection_code is not null
1214 -- and not 'NONE', 'REJECT' not 'WARNING'
1215 -------------------------------------------------------------------------
1216 PROCEDURE receipt_days_exception_code(
1217 p_id_tbl IN po_tbl_number,
1218 p_shipment_type_tbl IN po_tbl_varchar30,
1219 p_rcpt_days_exception_code_tbl IN po_tbl_varchar30,
1220 x_results IN OUT NOCOPY po_validation_results_type,
1221 x_result_type OUT NOCOPY VARCHAR2)
1222 IS
1223 d_mod CONSTANT VARCHAR2(100) := d_receipt_days_exception_code;
1224 BEGIN
1225 IF (x_results IS NULL) THEN
1226 x_results := po_validation_results_type.new_instance();
1227 END IF;
1228
1229 x_result_type := po_validations.c_result_type_success;
1230
1231 IF po_log.d_proc THEN
1232 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1233 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1234 po_log.proc_begin(d_mod, 'p_days_early_exception_code_tbl', p_rcpt_days_exception_code_tbl);
1235 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1236 END IF;
1237
1238 -- If shipment_type is STANDARD and receipt_days_expection_code is not null
1239 -- and not 'NONE', 'REJECT' not 'WARNING'
1240 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1241 IF p_shipment_type_tbl(i) = 'STANDARD'
1242 AND p_rcpt_days_exception_code_tbl(i) IS NOT NULL
1243 AND p_rcpt_days_exception_code_tbl(i) NOT IN('NONE', 'REJECT', 'WARNING') THEN
1244 x_results.add_result(p_entity_type => c_entity_type_line_location,
1245 p_entity_id => p_id_tbl(i),
1246 p_column_name => 'RECEIPT_DAYS_EXPECTION_CODE',
1247 p_column_val => p_rcpt_days_exception_code_tbl(i),
1248 p_message_name => 'PO_PDOI_INV_REC_DAYS_EX_CODE');
1249 x_result_type := po_validations.c_result_type_failure;
1250 END IF;
1251 END LOOP;
1252
1253 IF po_log.d_proc THEN
1254 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1255 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1256 END IF;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 IF po_log.d_exc THEN
1260 po_log.exc(d_mod, 0, NULL);
1261 END IF;
1262
1263 RAISE;
1264 END receipt_days_exception_code;
1265
1266 -------------------------------------------------------------------------
1267 -- If shipment_type is STANDARD and invoice_close_tolerance is not null
1268 -- and less than or equal to zero or greater than or equal to 100.
1269 -------------------------------------------------------------------------
1270 PROCEDURE invoice_close_tolerance(
1271 p_id_tbl IN po_tbl_number,
1272 p_shipment_type_tbl IN po_tbl_varchar30,
1273 p_invoice_close_tolerance_tbl IN po_tbl_number,
1274 x_results IN OUT NOCOPY po_validation_results_type,
1275 x_result_type OUT NOCOPY VARCHAR2)
1276 IS
1277 d_mod CONSTANT VARCHAR2(100) := d_invoice_close_tolerance;
1278 BEGIN
1279 IF (x_results IS NULL) THEN
1280 x_results := po_validation_results_type.new_instance();
1281 END IF;
1282
1283 x_result_type := po_validations.c_result_type_success;
1284
1285 IF po_log.d_proc THEN
1286 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1287 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1288 po_log.proc_begin(d_mod, 'p_invoice_close_tolerance_tbl', p_invoice_close_tolerance_tbl);
1289 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1290 END IF;
1291
1292 -- If shipment_type is STANDARD and invoice_close_tolerance is not null
1293 -- and less than or equal to zero or greater than or equal to 100.
1294 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1295 IF p_shipment_type_tbl(i) = 'STANDARD' AND
1296 p_invoice_close_tolerance_tbl(i) IS NOT NULL AND
1297 (p_invoice_close_tolerance_tbl(i) < 0 OR p_invoice_close_tolerance_tbl(i) > 100) THEN
1298 x_results.add_result(p_entity_type => c_entity_type_line_location,
1299 p_entity_id => p_invoice_close_tolerance_tbl(i),
1300 p_column_name => 'INVOICE_CLOSE_TOLERANCE',
1301 p_column_val => p_invoice_close_tolerance_tbl(i),
1302 p_message_name => 'PO_PDOI_INV_CLOSE_TOLERANCE');
1303 x_result_type := po_validations.c_result_type_failure;
1304 END IF;
1305 END LOOP;
1306
1307 IF po_log.d_proc THEN
1308 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1309 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1310 END IF;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 IF po_log.d_exc THEN
1314 po_log.exc(d_mod, 0, NULL);
1315 END IF;
1316
1317 RAISE;
1318 END invoice_close_tolerance;
1319
1320 -------------------------------------------------------------------------
1321 -- If shipment_type is STANDARD and receive_close_tolerance is not null
1322 -- and less than or equal to zero or greater than or equal to 100.
1323 -------------------------------------------------------------------------
1324 PROCEDURE receive_close_tolerance(
1325 p_id_tbl IN po_tbl_number,
1326 p_shipment_type_tbl IN po_tbl_varchar30,
1327 p_receive_close_tolerance_tbl IN po_tbl_number,
1328 x_results IN OUT NOCOPY po_validation_results_type,
1329 x_result_type OUT NOCOPY VARCHAR2)
1330 IS
1331 d_mod CONSTANT VARCHAR2(100) := d_receive_close_tolerance;
1332 BEGIN
1333 IF (x_results IS NULL) THEN
1334 x_results := po_validation_results_type.new_instance();
1335 END IF;
1336
1337 x_result_type := po_validations.c_result_type_success;
1338
1339 IF po_log.d_proc THEN
1340 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1341 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1342 po_log.proc_begin(d_mod, 'p_receive_close_tolerance_tbl', p_receive_close_tolerance_tbl);
1343 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1344 END IF;
1345
1346 -- If shipment_type is STANDARD and receive_close_tolerance is not null
1347 -- and less than or equal to zero or greater than or equal to 100.
1348 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1349 IF p_shipment_type_tbl(i) = 'STANDARD' AND
1350 p_receive_close_tolerance_tbl(i) IS NOT NULL AND
1351 (p_receive_close_tolerance_tbl(i) < 0 OR p_receive_close_tolerance_tbl(i) > 100) THEN
1352 x_results.add_result(p_entity_type => c_entity_type_line_location,
1353 p_entity_id => p_id_tbl(i),
1354 p_column_name => 'RECEIVE_CLOSE_TOLERANCE',
1355 p_column_val => p_receive_close_tolerance_tbl(i),
1356 p_message_name => 'PO_PDOI_RCT_CLOSE_TOLERANCE');
1357 x_result_type := po_validations.c_result_type_failure;
1358 END IF;
1359 END LOOP;
1360
1361 IF po_log.d_proc THEN
1362 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1363 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1364 END IF;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 IF po_log.d_exc THEN
1368 po_log.exc(d_mod, 0, NULL);
1369 END IF;
1370
1371 RAISE;
1372 END receive_close_tolerance;
1373
1374 -------------------------------------------------------------------------
1375 -- Validate that receiving routing id exists in rcv_routing_headers
1376 -------------------------------------------------------------------------
1377 PROCEDURE receiving_routing_id(
1378 p_id_tbl IN po_tbl_number,
1379 p_shipment_type_tbl IN po_tbl_varchar30,
1380 p_receiving_routing_id_tbl IN po_tbl_number,
1381 x_result_set_id IN OUT NOCOPY NUMBER,
1382 x_result_type OUT NOCOPY VARCHAR2)
1383 IS
1384 d_mod CONSTANT VARCHAR2(100) := d_receiving_routing_id;
1385 BEGIN
1386 IF x_result_set_id IS NULL THEN
1387 x_result_set_id := po_validations.next_result_set_id();
1388 END IF;
1389
1390 x_result_type := po_validations.c_result_type_success;
1391
1392 IF po_log.d_proc THEN
1393 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1394 po_log.proc_begin(d_mod, 'p_shipment_type_tbl', p_shipment_type_tbl);
1395 po_log.proc_begin(d_mod, 'p_receiving_routing_id_tbl', p_receiving_routing_id_tbl);
1396 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1397 END IF;
1398
1399 -- Validate that receiving routing id exists in rcv_routing_headers
1400 FORALL i IN 1 .. p_id_tbl.COUNT
1401 INSERT INTO po_validation_results_gt
1402 (result_set_id,
1403 result_type,
1404 entity_type,
1405 entity_id,
1406 message_name,
1407 column_name,
1408 column_val,
1409 token1_name,
1410 token1_value)
1411 SELECT x_result_set_id,
1412 po_validations.c_result_type_failure,
1413 'PO_LINE_LOCATIONS_DRAFT_ALL',
1414 p_id_tbl(i),
1415 'PO_PDOI_INVALID_ROUTING_ID',
1416 'RECEIVING_ROUTING_ID',
1417 p_receiving_routing_id_tbl(i),
1418 'RECEIVING_ROUTING_ID',
1419 p_receiving_routing_id_tbl(i)
1420 FROM DUAL
1421 WHERE p_receiving_routing_id_tbl(i) IS NOT NULL
1422 AND p_shipment_type_tbl(i) = 'STANDARD'
1423 AND NOT EXISTS(SELECT 1
1424 FROM rcv_routing_headers rrh
1425 WHERE rrh.routing_header_id = p_receiving_routing_id_tbl(i));
1426
1427 IF (SQL%ROWCOUNT > 0) THEN
1428 x_result_type := po_validations.c_result_type_failure;
1429 END IF;
1430
1431 IF po_log.d_proc THEN
1432 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1433 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1434 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1435 END IF;
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 IF po_log.d_exc THEN
1439 po_log.exc(d_mod, 0, NULL);
1440 END IF;
1441
1442 RAISE;
1443 END receiving_routing_id;
1444
1445 -------------------------------------------------------------------------
1446 -- Validate accrue_on_receipt_flag is Y or N, if not null.
1447 -------------------------------------------------------------------------
1448 PROCEDURE accrue_on_receipt_flag(
1449 p_id_tbl IN po_tbl_number,
1450 p_accrue_on_receipt_flag_tbl IN po_tbl_varchar1,
1451 x_results IN OUT NOCOPY po_validation_results_type,
1452 x_result_type OUT NOCOPY VARCHAR2)
1453 IS
1454 d_mod CONSTANT VARCHAR2(100) := d_accrue_on_receipt_flag;
1455 BEGIN
1456 IF (x_results IS NULL) THEN
1457 x_results := po_validation_results_type.new_instance();
1458 END IF;
1459
1460 x_result_type := po_validations.c_result_type_success;
1461
1462 IF po_log.d_proc THEN
1463 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1464 po_log.proc_begin(d_mod, 'p_accrue_on_receipt_flag_tbl', p_accrue_on_receipt_flag_tbl);
1465 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1466 END IF;
1467
1468 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1469 IF (p_accrue_on_receipt_flag_tbl(i) IS NOT NULL AND p_accrue_on_receipt_flag_tbl(i) NOT IN('N', 'Y')) THEN
1470 x_results.add_result(p_entity_type => c_entity_type_line_location,
1471 p_entity_id => p_id_tbl(i),
1472 p_column_name => 'ACCRUE_ON_RECEIPT_FLAG',
1473 p_column_val => p_accrue_on_receipt_flag_tbl(i),
1474 p_message_name => 'PO_PDOI_INVALID_VALUE');
1475 x_result_type := po_validations.c_result_type_failure;
1476 END IF;
1477 END LOOP;
1478
1479 IF po_log.d_proc THEN
1480 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1481 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1482 END IF;
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 IF po_log.d_exc THEN
1486 po_log.exc(d_mod, 0, NULL);
1487 END IF;
1488
1489 RAISE;
1490 END accrue_on_receipt_flag;
1491
1492 -------------------------------------------------------------------------------------
1493 -- Validate price_breaks_flag = Y for the given style
1494 -------------------------------------------------------------------------------------
1495 PROCEDURE style_related_info(
1496 p_id_tbl IN po_tbl_number,
1497 p_style_id_tbl IN po_tbl_number,
1498 x_result_set_id IN OUT NOCOPY NUMBER,
1499 x_result_type OUT NOCOPY VARCHAR2)
1500 IS
1501 d_mod CONSTANT VARCHAR2(100) := d_style_related_info;
1502 BEGIN
1503 IF x_result_set_id IS NULL THEN
1504 x_result_set_id := po_validations.next_result_set_id();
1505 END IF;
1506
1507 IF po_log.d_proc THEN
1508 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1509 po_log.proc_begin(d_mod, 'p_style_id_tbl', p_style_id_tbl);
1510 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1511 END IF;
1512
1513 x_result_type := po_validations.c_result_type_success;
1514
1515 -- bug5262146
1516 -- Added NVL() around pdsh.price_breaks_flag
1517
1518 FORALL i IN 1 .. p_id_tbl.COUNT
1519 INSERT INTO po_validation_results_gt
1520 (result_set_id,
1521 result_type,
1522 entity_type,
1523 entity_id,
1524 message_name,
1525 column_name,
1526 column_val,
1527 token1_name,
1528 token1_value,
1529 validation_id)
1530 SELECT x_result_set_id,
1531 po_validations.c_result_type_failure,
1532 c_entity_type_line_location,
1533 p_id_tbl(i),
1534 'PO_PDOI_PRICE_BREAK_STYLE',
1535 'STYLE_ID',
1536 p_style_id_tbl(i),
1537 'STYLE_ID',
1538 p_style_id_tbl(i),
1539 PO_VAL_CONSTANTS.c_loc_style_related_info
1540 FROM DUAL
1541 WHERE EXISTS(SELECT 1
1542 FROM po_doc_style_headers pdsh
1543 WHERE pdsh.style_id = p_style_id_tbl(i) AND
1544 NVL(pdsh.price_breaks_flag, 'N') = 'N');
1545
1546 IF (SQL%ROWCOUNT > 0) THEN
1547 x_result_type := po_validations.c_result_type_failure;
1548 END IF;
1549
1550 IF po_log.d_proc THEN
1551 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1552 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1553 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1554 END IF;
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 IF po_log.d_exc THEN
1558 po_log.exc(d_mod, 0, NULL);
1559 END IF;
1560 RAISE;
1561
1562 END style_related_info;
1563
1564 -------------------------------------------------------------------------
1565 -- tax_name must be valid if it is not null;
1566 -- If tax_name and tax_code_id are both not null,
1567 -- then tax_code_id and tax_name must be a valid combination in zx_id_tcc_mapping
1568 -------------------------------------------------------------------------
1569 PROCEDURE tax_name(
1570 p_id_tbl IN po_tbl_number,
1571 p_tax_name_tbl IN po_tbl_varchar30,
1572 p_tax_code_id_tbl IN po_tbl_number,
1573 p_need_by_date_tbl IN po_tbl_date,
1574 p_allow_tax_code_override IN VARCHAR2,
1575 p_operating_unit IN NUMBER,
1576 x_result_set_id IN OUT NOCOPY NUMBER,
1577 x_result_type OUT NOCOPY VARCHAR2)
1578 IS
1579 d_mod CONSTANT VARCHAR2(100) := d_tax_name;
1580 BEGIN
1581 IF x_result_set_id IS NULL THEN
1582 x_result_set_id := po_validations.next_result_set_id();
1583 END IF;
1584
1585 IF po_log.d_proc THEN
1586 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1587 po_log.proc_begin(d_mod, 'p_tax_code_id_tbl', p_tax_code_id_tbl);
1588 po_log.proc_begin(d_mod, 'p_tax_name_tbl', p_tax_name_tbl);
1589 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1590 END IF;
1591
1592 x_result_type := po_validations.c_result_type_success;
1593
1594 -- Bug 4965755. Modified both queries to include -99 as valid orgs in zx
1595 -- tax_name must be valid if not null
1596 FORALL i IN 1 .. p_id_tbl.COUNT
1597 INSERT INTO po_validation_results_gt
1598 (result_set_id,
1599 result_type,
1600 entity_type,
1601 entity_id,
1602 message_name,
1603 column_name,
1604 column_val,
1605 token1_name,
1606 token1_value,
1607 validation_id)
1608 SELECT x_result_set_id,
1609 po_validations.c_result_type_failure,
1610 c_entity_type_line_location,
1611 p_id_tbl(i),
1612 'PO_PDOI_INVALID_TAX_NAME',
1613 'TAX_NAME',
1614 p_tax_name_tbl(i),
1615 'VALUE',
1616 p_tax_name_tbl(i),
1617 PO_VAL_CONSTANTS.c_tax_name
1618 FROM DUAL
1619 WHERE p_tax_name_tbl(i) IS NOT NULL
1620 AND p_tax_code_id_tbl(i) IS NULL
1621 AND NOT EXISTS(SELECT 'Y'
1622 FROM ZX_INPUT_CLASSIFICATIONS_V zicv
1623 WHERE zicv.lookup_code = p_tax_name_tbl(i)
1624 AND zicv.org_id in (p_operating_unit, -99)
1625 AND zicv.enabled_flag = 'Y'
1626 AND NVL(p_need_by_date_tbl(i),SYSDATE) BETWEEN
1627 NVL(zicv.start_date_active, SYSDATE) AND
1628 COALESCE(zicv.end_date_active,
1629 p_need_by_date_tbl(i),
1630 SYSDATE)
1631 AND p_allow_tax_code_override = 'Y');
1632
1633 IF (SQL%ROWCOUNT > 0) THEN
1634 x_result_type := po_validations.c_result_type_failure;
1635 END IF;
1636
1637 -- tax_code_id and tax_name must be a valid combination in zx_id_tcc_mapping
1638 -- if both are not null
1639 FORALL i IN 1 .. p_id_tbl.COUNT
1640 INSERT INTO po_validation_results_gt
1641 (result_set_id,
1642 result_type,
1643 entity_type,
1644 entity_id,
1645 message_name,
1646 column_name,
1647 column_val,
1648 token1_name,
1649 token1_value,
1650 validation_id)
1651 SELECT x_result_set_id,
1652 po_validations.c_result_type_failure,
1653 c_entity_type_line_location,
1654 p_id_tbl(i),
1655 'PO_PDOI_INVALID_TAX_NAME',
1656 'TAX_NAME',
1657 p_tax_name_tbl(i),
1658 'VALUE',
1659 p_tax_name_tbl(i),
1660 PO_VAL_CONSTANTS.c_tax_name
1661 FROM DUAL
1662 WHERE p_tax_code_id_tbl(i) IS NOT NULL
1663 AND p_tax_name_tbl(i) IS NOT NULL
1664 AND NOT EXISTS(SELECT 'Y'
1665 FROM ZX_ID_TCC_MAPPING
1666 WHERE tax_rate_code_id = p_tax_code_id_tbl(i)
1667 AND tax_classification_code = p_tax_name_tbl(i)
1668 AND NVL(p_need_by_date_tbl(i),SYSDATE) BETWEEN
1669 NVL(effective_from, SYSDATE) AND
1670 COALESCE(effective_to,
1671 p_need_by_date_tbl(i),
1672 SYSDATE)
1673 AND tax_class = 'INPUT'
1674 AND org_id IN (p_operating_unit, -99)
1675 AND source = 'AP'
1676 AND active_flag = 'Y'
1677 AND p_allow_tax_code_override = 'Y');
1678
1679 IF (SQL%ROWCOUNT > 0) THEN
1680 x_result_type := po_validations.c_result_type_failure;
1681 END IF;
1682
1683 IF po_log.d_proc THEN
1684 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1685 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1686 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1687 END IF;
1688 EXCEPTION
1689 WHEN OTHERS THEN
1690 IF po_log.d_exc THEN
1691 po_log.exc(d_mod, 0, NULL);
1692 END IF;
1693
1694 RAISE;
1695 END tax_name;
1696
1697 -------------------------------------------------------------------------
1698 -- fob_lookup_code must be valid in PO_LOOKUP_CODES
1699 -------------------------------------------------------------------------
1700 PROCEDURE fob_lookup_code(p_id_tbl IN po_tbl_number,
1701 p_fob_lookup_code_tbl IN po_tbl_varchar30,
1702 x_result_set_id IN OUT NOCOPY NUMBER,
1703 x_result_type OUT NOCOPY VARCHAR2)
1704 IS
1705 d_mod CONSTANT VARCHAR2(100) := d_fob_lookup_code;
1706 BEGIN
1707 IF x_result_set_id IS NULL THEN
1708 x_result_set_id := po_validations.next_result_set_id();
1709 END IF;
1710
1711 IF po_log.d_proc THEN
1712 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1713 po_log.proc_begin(d_mod, 'p_fob_lookup_code_tbl', p_fob_lookup_code_tbl);
1714 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1715 END IF;
1716
1717 x_result_type := po_validations.c_result_type_success;
1718
1719 -- fob_lookup_code must be valid if not null
1720 FORALL i IN 1 .. p_id_tbl.COUNT
1721 INSERT INTO po_validation_results_gt
1722 (result_set_id,
1723 result_type,
1724 entity_type,
1725 entity_id,
1726 message_name,
1727 column_name,
1728 column_val,
1729 token1_name,
1730 token1_value,
1731 validation_id)
1732 SELECT x_result_set_id,
1733 po_validations.c_result_type_failure,
1734 c_entity_type_line_location,
1735 p_id_tbl(i),
1736 'PO_PDOI_INVALID_FOB',
1737 'FOB_LOOKUP_CODE',
1738 p_fob_lookup_code_tbl(i),
1739 'VALUE',
1740 p_fob_lookup_code_tbl(i),
1741 PO_VAL_CONSTANTS.c_loc_fob_lookup_code
1742 FROM DUAL
1743 WHERE p_fob_lookup_code_tbl(i) IS NOT NULL
1744 AND NOT EXISTS(SELECT 1
1745 FROM PO_LOOKUP_CODES
1746 WHERE lookup_type = 'FOB' AND
1747 sysdate < nvl(inactive_date, sysdate + 1) AND
1748 lookup_code = p_fob_lookup_code_tbl(i));
1749
1750 IF (SQL%ROWCOUNT > 0) THEN
1751 x_result_type := po_validations.c_result_type_failure;
1752 END IF;
1753
1754 IF po_log.d_proc THEN
1755 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1756 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1757 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1758 END IF;
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 IF po_log.d_exc THEN
1762 po_log.exc(d_mod, 0, NULL);
1763 END IF;
1764 RAISE;
1765
1766 END fob_lookup_code;
1767
1768 -------------------------------------------------------------------------
1769 -- freight_terms must be valid in PO_LOOKUP_CODES
1770 -------------------------------------------------------------------------
1771 PROCEDURE freight_terms(p_id_tbl IN po_tbl_number,
1772 p_freight_terms_tbl IN po_tbl_varchar30,
1773 x_result_set_id IN OUT NOCOPY NUMBER,
1774 x_result_type OUT NOCOPY VARCHAR2)
1775 IS
1776 d_mod CONSTANT VARCHAR2(100) := d_freight_terms;
1777 BEGIN
1778 IF x_result_set_id IS NULL THEN
1779 x_result_set_id := po_validations.next_result_set_id();
1780 END IF;
1781
1782 IF po_log.d_proc THEN
1783 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1784 po_log.proc_begin(d_mod, 'p_freight_terms_tbl', p_freight_terms_tbl);
1785 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1786 END IF;
1787
1788 x_result_type := po_validations.c_result_type_success;
1789
1790 -- freight_terms must be valid if not null
1791 FORALL i IN 1 .. p_id_tbl.COUNT
1792 INSERT INTO po_validation_results_gt
1793 (result_set_id,
1794 result_type,
1795 entity_type,
1796 entity_id,
1797 message_name,
1798 column_name,
1799 column_val,
1800 token1_name,
1801 token1_value,
1802 validation_id)
1803 SELECT x_result_set_id,
1804 po_validations.c_result_type_failure,
1805 c_entity_type_line_location,
1806 p_id_tbl(i),
1807 'PO_PDOI_INVALID_FREIGHT_TERMS',
1808 'FREIGHT_TERMS',
1809 p_freight_terms_tbl(i),
1810 'VALUE',
1811 p_freight_terms_tbl(i),
1812 PO_VAL_CONSTANTS.c_loc_freight_terms
1813 FROM DUAL
1814 WHERE p_freight_terms_tbl(i) IS NOT NULL
1815 AND NOT EXISTS(SELECT 1
1816 FROM PO_LOOKUP_CODES
1817 WHERE lookup_type = 'FREIGHT TERMS' AND
1818 sysdate < nvl(inactive_date, sysdate + 1) AND
1819 lookup_code = p_freight_terms_tbl(i));
1820
1821 IF (SQL%ROWCOUNT > 0) THEN
1822 x_result_type := po_validations.c_result_type_failure;
1823 END IF;
1824
1825 IF po_log.d_proc THEN
1826 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1827 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1828 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1829 END IF;
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 IF po_log.d_exc THEN
1833 po_log.exc(d_mod, 0, NULL);
1834 END IF;
1835 RAISE;
1836
1837 END freight_terms;
1838
1839 -------------------------------------------------------------------------
1840 -- freight_carrier must be valid in ORG_FREIGHT
1841 -------------------------------------------------------------------------
1842 PROCEDURE freight_carrier(p_id_tbl IN po_tbl_number,
1843 p_freight_carrier_tbl IN po_tbl_varchar30,
1844 p_inventory_org_id IN NUMBER,
1845 x_result_set_id IN OUT NOCOPY NUMBER,
1846 x_result_type OUT NOCOPY VARCHAR2)
1847 IS
1848 d_mod CONSTANT VARCHAR2(100) := d_freight_carrier;
1849 BEGIN
1850 IF x_result_set_id IS NULL THEN
1851 x_result_set_id := po_validations.next_result_set_id();
1852 END IF;
1853
1854 IF po_log.d_proc THEN
1855 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1856 po_log.proc_begin(d_mod, 'p_freight_carrier_tbl', p_freight_carrier_tbl);
1857 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1858 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1859 END IF;
1860
1861 x_result_type := po_validations.c_result_type_success;
1862
1863 -- freight_carrier must be valid if not null
1864 FORALL i IN 1 .. p_id_tbl.COUNT
1865 INSERT INTO po_validation_results_gt
1866 (result_set_id,
1867 result_type,
1868 entity_type,
1869 entity_id,
1870 message_name,
1871 column_name,
1872 column_val,
1873 token1_name,
1874 token1_value,
1875 validation_id)
1876 SELECT x_result_set_id,
1877 po_validations.c_result_type_failure,
1878 c_entity_type_line_location,
1879 p_id_tbl(i),
1880 'PO_PDOI_INVALID_FREIGHT_CARR',
1881 'FREIGHT_CARRIER',
1882 p_freight_carrier_tbl(i),
1883 'VALUE',
1884 p_freight_carrier_tbl(i),
1885 PO_VAL_CONSTANTS.c_loc_freight_carrier
1886 FROM DUAL
1887 WHERE p_freight_carrier_tbl(i) IS NOT NULL
1888 AND NOT EXISTS(SELECT 1
1889 FROM ORG_FREIGHT
1890 WHERE freight_code = p_freight_carrier_tbl(i) AND
1891 organization_id = p_inventory_org_id AND
1892 nvl(disable_date, sysdate + 1) > sysdate);
1893
1894 IF (SQL%ROWCOUNT > 0) THEN
1895 x_result_type := po_validations.c_result_type_failure;
1896 END IF;
1897
1898 IF po_log.d_proc THEN
1899 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1900 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1901 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1902 END IF;
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 IF po_log.d_exc THEN
1906 po_log.exc(d_mod, 0, NULL);
1907 END IF;
1908 RAISE;
1909
1910 END freight_carrier;
1911
1912 -------------------------------------------------------------------------
1913 -- Cannot create price breaks for Amount-Based or Fixed Price lines in a
1914 -- Blanket Purchase Agreement.
1915 -------------------------------------------------------------------------
1916 PROCEDURE price_break(
1917 p_id_tbl IN po_tbl_number,
1918 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1919 x_results IN OUT NOCOPY po_validation_results_type,
1920 x_result_type OUT NOCOPY VARCHAR2)
1921 IS
1922 d_mod CONSTANT VARCHAR2(100) := d_price_break;
1923 BEGIN
1924 IF (x_results IS NULL) THEN
1925 x_results := po_validation_results_type.new_instance();
1926 END IF;
1927
1928 x_result_type := po_validations.c_result_type_success;
1929
1930 IF po_log.d_proc THEN
1931 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1932 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1933 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1934 END IF;
1935
1936 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1937 IF (p_order_type_lookup_code_tbl(i) IN ('AMOUNT', 'FIXED PRICE')) THEN
1938 x_results.add_result(p_entity_type => c_entity_type_line_location,
1939 p_entity_id => p_id_tbl(i),
1940 p_column_name => NULL,
1941 p_column_val => NULL,
1942 p_message_name => 'PO_PDOI_PRICE_BRK_AMT_BASED_LN',
1943 p_validation_id => PO_VAL_CONSTANTS.c_price_break_not_allowed);
1944 x_result_type := po_validations.c_result_type_failure;
1945 END IF;
1946 END LOOP;
1947
1948 IF po_log.d_proc THEN
1949 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1950 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1951 END IF;
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 IF po_log.d_exc THEN
1955 po_log.exc(d_mod, 0, NULL);
1956 END IF;
1957
1958 RAISE;
1959 END price_break;
1960
1961 END PO_VAL_SHIPMENTS2;
[Home] [Help]