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