[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_LINES2
Source
1 PACKAGE BODY PO_VAL_LINES2 AS
2 -- $Header: PO_VAL_LINES2.plb 120.29.12000000.2 2007/07/18 12:24:19 puppulur ship $
3 c_entity_type_line CONSTANT VARCHAR2(30) := po_validations.c_entity_type_line;
4 -- The module base for this package.
5 d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_LINES2');
6
7 -- The module base for the subprogram.
8 d_over_tolerance_error_flag CONSTANT VARCHAR2(100)
9 := po_log.get_subprogram_base(d_package_base, 'OVER_TOLERANCE_ERROR_FLAG');
10 d_expiration_date_blanket CONSTANT VARCHAR2(100)
11 := po_log.get_subprogram_base(d_package_base, 'EXPIRATION_DATE_BLANKET');
12 d_global_agreement_flag CONSTANT VARCHAR2(100)
13 := po_log.get_subprogram_base(d_package_base, 'GLOBAL_AGREEMENT_FLAG');
14 d_amount_blanket CONSTANT VARCHAR2(100)
15 := po_log.get_subprogram_base(d_package_base, 'AMOUNT_BLANKET');
16 d_order_type_lookup_code CONSTANT VARCHAR2(100)
17 := po_log.get_subprogram_base(d_package_base, 'ORDER_TYPE_LOOKUP_CODE');
18 d_contractor_name CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CONTRACTOR_NAME');
19 d_job_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'JOB_ID');
20 d_job_business_group_id CONSTANT VARCHAR2(100)
21 := po_log.get_subprogram_base(d_package_base, 'JOB_BUSINESS_GROUP_ID');
22 d_capital_expense_flag CONSTANT VARCHAR2(100)
23 := po_log.get_subprogram_base(d_package_base, 'CAPITAL_EXPENSE_FLAG');
24 d_un_number_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UN_NUMBER_ID');
25 d_hazard_class_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'HAZARD_CLASS_ID');
26 d_item_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_ID');
27 d_item_description CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_DESCRIPTION');
28 d_unit_meas_lookup_code CONSTANT VARCHAR2(100)
29 := po_log.get_subprogram_base(d_package_base, 'UNIT_MEAS_LOOKUP_CODE');
30 d_item_revision CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_REVISION');
31 d_category_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CATEGORY_ID');
32 d_ip_category_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'IP_CATEGORY_ID');
33 d_unit_price CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UNIT_PRICE');
34 d_quantity CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'QUANTITY');
35 d_amount CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT');
36 d_rate_type CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'RATE_TYPE');
37 d_line_num CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'LINE_NUM');
38 d_po_line_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PO_LINE_ID');
39 d_line_type_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'LINE_TYPE_ID');
40 d_style_related_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'STYLE_RELATED_INFO');
41 d_price_type_lookup_code CONSTANT VARCHAR2(100)
42 := po_log.get_subprogram_base(d_package_base, 'PRICE_TYPE_LOOKUP_CODE');
43 d_start_date_standard CONSTANT VARCHAR2(100)
44 := po_log.get_subprogram_base(d_package_base, 'START_DATE_STANDARD');
45 d_item_id_standard CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_ID_STANDARD');
46 d_quantity_standard CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'QUANTITY_STANDARD');
47 d_amount_standard CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_STANDARD');
48 d_price_break_lookup_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PRICE_BREAK_LOOKUP_CODE');
49 d_not_to_exceed_price CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'NOT_TO_EXCEED_PRICE');
50 d_ip_category_id_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'IP_CATEGORY_ID_UPDATE');
51 d_uom_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UOM_UPDATE');
52 d_item_desc_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_DESC_UPDATE');
53 d_negotiated_by_preparer CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'NEGOTIATED_BY_PREPARER');
54 d_negotiated_by_prep_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'NEGOTIATED_BY_PREPARER_UPDATE');
55 d_category_id_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CATEGORY_ID_UPDATE');
56 d_unit_price_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UNIT_PRICE_UPDATE');
57 d_amount_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_UPDATE');
58
59 -- Indicates that the calling program is PDOI.
60 c_program_PDOI CONSTANT VARCHAR2(10) := 'PDOI';
61
62 -------------------------------------------------------------------------
63 -- The lookup code specified in over_tolerance_error_flag with the lookup type
64 -- 'RECEIVING CONTROL LEVEL' has to exist in po_lookup_codes and still active.
65 -- This method is called only for Standard PO and quotation documents
66 -------------------------------------------------------------------------
67 PROCEDURE over_tolerance_err_flag(
68 p_id_tbl IN po_tbl_number,
69 p_over_tolerance_err_flag_tbl IN po_tbl_varchar30,
70 x_result_set_id IN OUT NOCOPY NUMBER,
71 x_result_type OUT NOCOPY VARCHAR2)
72 IS
73 d_mod CONSTANT VARCHAR2(100) := d_over_tolerance_error_flag;
74 BEGIN
75 IF x_result_set_id IS NULL THEN
76 x_result_set_id := po_validations.next_result_set_id();
77 END IF;
78
79 IF po_log.d_proc THEN
80 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
81 po_log.proc_begin(d_mod, 'p_over_tolerance_err_flag_tbl', p_over_tolerance_err_flag_tbl);
82 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
83 END IF;
84
85 FORALL i IN 1 .. p_id_tbl.COUNT
86 INSERT INTO po_validation_results_gt
87 (result_set_id,
88 result_type,
89 entity_type,
90 entity_id,
91 message_name,
92 column_name,
93 column_val,
94 token1_name,
95 token1_value,
96 validation_id)
97 SELECT x_result_set_id,
98 po_validations.c_result_type_failure,
99 c_entity_type_line,
100 p_id_tbl(i),
101 'PO_PDOI_INVALID_OVER_TOL_ERROR',
102 'OVER_TOLERANCE_ERROR_FLAG',
103 p_over_tolerance_err_flag_tbl(i),
104 'OVER_TOLERANCE_ERROR_FLAG',
105 p_over_tolerance_err_flag_tbl(i),
106 PO_VAL_CONSTANTS.c_over_tolerance_error_flag
107 FROM DUAL
108 WHERE p_over_tolerance_err_flag_tbl(i) IS NOT NULL AND
109 NOT EXISTS(
110 SELECT 1
111 FROM po_lookup_codes plc
112 WHERE plc.lookup_type = 'RECEIVING CONTROL LEVEL'
113 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
114 AND plc.lookup_code = p_over_tolerance_err_flag_tbl(i));
115
116 IF (SQL%ROWCOUNT > 0) THEN
117 x_result_type := po_validations.c_result_type_failure;
118 ELSE
119 x_result_type := po_validations.c_result_type_success;
120 END IF;
121
122 IF po_log.d_proc THEN
123 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
124 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
125 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
126 END IF;
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF po_log.d_exc THEN
130 po_log.exc(d_mod, 0, NULL);
131 END IF;
132
133 RAISE;
134 END over_tolerance_err_flag;
135
136 -------------------------------------------------------------------------
137 -- Expiration date on the line cannot be earlier than the header effective start date and
138 -- cannot be later than header effective end date
139 -------------------------------------------------------------------------
140 PROCEDURE expiration_date_blanket(
141 p_id_tbl IN po_tbl_number,
142 p_expiration_date_tbl IN po_tbl_date,
143 p_header_start_date_tbl IN po_tbl_date,
144 p_header_end_date_tbl IN po_tbl_date,
145 x_results IN OUT NOCOPY po_validation_results_type,
146 x_result_type OUT NOCOPY VARCHAR2)
147 IS
148 d_mod CONSTANT VARCHAR2(100) := d_expiration_date_blanket;
149 BEGIN
150 IF (x_results IS NULL) THEN
151 x_results := po_validation_results_type.new_instance();
152 END IF;
153
154 x_result_type := po_validations.c_result_type_success;
155
156 IF po_log.d_proc THEN
157 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
158 po_log.proc_begin(d_mod, 'p_expiration_date_tbl', p_expiration_date_tbl);
159 po_log.proc_begin(d_mod, 'p_header_start_date_tbl', p_header_start_date_tbl);
160 po_log.proc_begin(d_mod, 'p_header_end_date_tbl', p_header_end_date_tbl);
161 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
162 END IF;
163
164 FOR i IN 1 .. p_id_tbl.COUNT LOOP
165 IF p_expiration_date_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_DATE THEN
166 x_results.add_result(p_entity_type => c_entity_type_line,
167 p_entity_id => p_id_tbl(i),
168 p_column_name => 'EXPIRATION_DATE',
169 p_column_val => p_expiration_date_tbl(i),
170 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
171 p_token1_name => 'COLUMN_NAME',
172 p_token1_value => 'EXPIRATION_DATE',
173 p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_not_null);
174 x_result_type := po_validations.c_result_type_failure;
175 ELSIF p_expiration_date_tbl(i) IS NOT NULL
176 AND (p_header_start_date_tbl(i) > p_expiration_date_tbl(i)
177 OR p_header_end_date_tbl(i) < p_expiration_date_tbl(i)) THEN
178 x_results.add_result(p_entity_type => c_entity_type_line,
179 p_entity_id => p_id_tbl(i),
180 p_column_name => 'EXPIRATION_DATE',
181 p_column_val => p_expiration_date_tbl(i),
182 p_message_name => 'POX_EXPIRATION_DATES',
183 p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_exc_hdr);
184 x_result_type := po_validations.c_result_type_failure;
185 END IF;
186 END LOOP;
187
188 IF po_log.d_proc THEN
189 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
190 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF po_log.d_exc THEN
195 po_log.exc(d_mod, 0, NULL);
196 END IF;
197
198 RAISE;
199 END expiration_date_blanket;
200
201 -------------------------------------------------------------------------
202 -- For blanket document with purchase type 'TEMP LABOR', the global agreement
203 -- flag has to be 'Y'. Global_agreement_flag and outside operation flag
204 -- cannot both be 'Y'
205 -------------------------------------------------------------------------
206 PROCEDURE global_agreement_flag(
207 p_id_tbl IN po_tbl_number,
208 p_global_agreement_flag_tbl IN po_tbl_varchar1,
209 p_purchase_basis_tbl IN po_tbl_varchar30,
210 p_line_type_id_tbl IN po_tbl_number,
211 x_result_set_id IN OUT NOCOPY NUMBER,
212 x_results IN OUT NOCOPY po_validation_results_type,
213 x_result_type OUT NOCOPY VARCHAR2)
214 IS
215 d_mod CONSTANT VARCHAR2(100) := d_global_agreement_flag;
216 BEGIN
217 IF x_result_set_id IS NULL THEN
218 x_result_set_id := po_validations.next_result_set_id();
219 END IF;
220
221 IF (x_results IS NULL) THEN
222 x_results := po_validation_results_type.new_instance();
223 END IF;
224
225 IF po_log.d_proc THEN
226 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
227 po_log.proc_begin(d_mod, 'p_global_agreement_flag_tbl', p_global_agreement_flag_tbl);
228 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
229 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
230 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
231 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
232 END IF;
233
234 x_result_type := po_validations.c_result_type_success;
235
236 FOR i IN 1 .. p_id_tbl.COUNT LOOP
237 IF NVL(p_global_agreement_flag_tbl(i), 'N') = 'N'
238 AND p_purchase_basis_tbl(i) = 'TEMP LABOR' THEN
239 x_results.add_result(p_entity_type => c_entity_type_line,
240 p_entity_id => p_id_tbl(i),
241 p_column_name => 'GLOBAL_AGREEMENT_FLAG',
242 p_column_val => p_global_agreement_flag_tbl(i),
243 p_message_name => 'PO_PDOI_SVC_NO_LOCAL_BLANKET',
244 p_validation_id => PO_VAL_CONSTANTS.c_ga_flag_temp_labor);
245 x_result_type := po_validations.c_result_type_failure;
246 END IF;
247 END LOOP;
248
249 FORALL i IN 1 .. p_id_tbl.COUNT
250 INSERT INTO po_validation_results_gt
251 (result_set_id,
252 result_type,
253 entity_type,
254 entity_id,
255 message_name,
256 validation_id)
257 SELECT x_result_set_id,
258 po_validations.c_result_type_failure,
259 c_entity_type_line,
260 p_id_tbl(i),
261 'PO_PDOI_GA_OSP_NA',
262 PO_VAL_CONSTANTS.c_ga_flag_op
263 FROM DUAL
264 WHERE p_line_type_id_tbl(i) IS NOT NULL
265 AND EXISTS(
266 SELECT 1
267 FROM po_line_types_b plt
268 WHERE p_line_type_id_tbl(i) = plt.line_type_id
269 AND NVL(p_global_agreement_flag_tbl(i), 'N') = 'Y'
270 AND NVL(plt.outside_operation_flag, 'N') = 'Y');
271
272 IF (SQL%ROWCOUNT > 0) THEN
273 x_result_type := po_validations.c_result_type_failure;
274 END IF;
275
276 IF po_log.d_proc THEN
277 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
278 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
279 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
280 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
281 END IF;
282 EXCEPTION
283 WHEN OTHERS THEN
284 IF po_log.d_exc THEN
285 po_log.exc(d_mod, 0, NULL);
286 END IF;
287
288 RAISE;
289 END global_agreement_flag;
290
291 -------------------------------------------------------------------------
292 -- If order_type_lookup_code is 'RATE', amount has to be null;
293 -- If order_type_lookup_code is 'FIXED PRICE',and amount is not empty,
294 -- then amount must be greater than or equal to zero
295 -------------------------------------------------------------------------
296 PROCEDURE amount_blanket(
297 p_id_tbl IN po_tbl_number,
298 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
299 p_amount_tbl IN po_tbl_number,
300 x_results IN OUT NOCOPY po_validation_results_type,
301 x_result_type OUT NOCOPY VARCHAR2)
302 IS
303 d_mod CONSTANT VARCHAR2(100) := d_amount_blanket;
304 BEGIN
305 IF (x_results IS NULL) THEN
306 x_results := po_validation_results_type.new_instance();
307 END IF;
308
309 IF po_log.d_proc THEN
310 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
311 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
312 po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
313 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
314 END IF;
315
316 x_result_type := po_validations.c_result_type_success;
317
318 -- If order_type_lookup_code is 'RATE', amount has to be null
319 FOR i IN 1 .. p_id_tbl.COUNT LOOP
320 IF p_order_type_lookup_code_tbl(i) = 'RATE' AND p_amount_tbl(i) IS NOT NULL THEN
321 x_results.add_result(p_entity_type => c_entity_type_line,
322 p_entity_id => p_id_tbl(i),
323 p_column_name => 'AMOUNT',
324 p_column_val => p_amount_tbl(i),
325 p_message_name => 'PO_PDOI_SVC_BLKT_NO_AMT',
326 p_validation_id => PO_VAL_CONSTANTS.c_amount_blanket);
327 x_result_type := po_validations.c_result_type_failure;
328 ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE'
329 AND p_amount_tbl(i) IS NOT NULL
330 AND p_amount_tbl(i) < 0) THEN
331 x_results.add_result(p_entity_type => c_entity_type_line,
332 p_entity_id => p_id_tbl(i),
333 p_column_name => 'AMOUNT',
334 p_column_val => p_amount_tbl(i),
335 p_message_name => 'PO_PDOI_LT_ZERO',
336 p_token1_name => 'COLUMN_NAME',
337 p_token1_value => 'AMOUNT',
338 p_token2_name => 'VALUE',
339 p_token2_value => p_amount_tbl(i),
340 p_validation_id => PO_VAL_CONSTANTS.c_amount_ge_zero);
341 x_result_type := po_validations.c_result_type_failure;
342 END IF;
343 END LOOP;
344
345 IF po_log.d_proc THEN
346 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
347 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
348 END IF;
349 EXCEPTION
350 WHEN OTHERS THEN
351 IF po_log.d_exc THEN
352 po_log.exc(d_mod, 0, NULL);
353 END IF;
354
355 RAISE;
356 END amount_blanket;
357
358 -------------------------------------------------------------------------
359 -- If services procurement is not enabled, the order_type_lookup_code cannot
360 -- be 'FIXED PRICE' or 'RATE'.
361 -------------------------------------------------------------------------
362 PROCEDURE order_type_lookup_code(
363 p_id_tbl IN po_tbl_number,
364 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
365 x_results IN OUT NOCOPY po_validation_results_type,
366 x_result_type OUT NOCOPY VARCHAR2)
367 IS
368 d_mod CONSTANT VARCHAR2(100) := d_order_type_lookup_code;
369 BEGIN
370 IF (x_results IS NULL) THEN
371 x_results := po_validation_results_type.new_instance();
372 END IF;
373
374 IF po_log.d_proc THEN
375 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
376 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
377 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
378 END IF;
379
380 x_result_type := po_validations.c_result_type_success;
381
382 -- If services procurement not enabled, order_type_lookup_code
383 -- cannot be 'FIXED PRICE' or 'RATE'
384 IF (po_setup_s1.get_services_enabled_flag = 'N') THEN
385 FOR i IN 1 .. p_id_tbl.COUNT LOOP
386 IF p_order_type_lookup_code_tbl(i) = 'RATE'
387 OR p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' THEN
388 x_results.add_result(p_entity_type => c_entity_type_line,
389 p_entity_id => p_id_tbl(i),
390 p_column_name => 'ORDER_TYPE_LOOKUP_CODE',
391 p_column_val => p_order_type_lookup_code_tbl(i),
392 p_message_name => 'PO_SVC_NOT_ENABLED',
393 p_validation_id => PO_VAL_CONSTANTS.c_order_type_lookup_code);
394 x_result_type := po_validations.c_result_type_failure;
395 END IF;
396 END LOOP;
397 END IF;
398
399 IF po_log.d_proc THEN
400 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
401 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
402 END IF;
403 EXCEPTION
404 WHEN OTHERS THEN
405 IF po_log.d_exc THEN
406 po_log.exc(d_mod, 0, NULL);
407 END IF;
408
409 RAISE;
410 END order_type_lookup_code;
411
412 -------------------------------------------------------------------------
413 -- If purchase basis is 'TEMP LABOR' and document type is SPO,
414 -- contractor first name and last name fields could be populated;
415 -- otherwise, they should be empty
416 -------------------------------------------------------------------------
417 PROCEDURE contractor_name(
418 p_id_tbl IN po_tbl_number,
419 p_doc_type IN VARCHAR2,
420 p_purchase_basis_tbl IN po_tbl_varchar30,
421 p_contractor_last_name_tbl IN po_tbl_varchar2000,
422 p_contractor_first_name_tbl IN po_tbl_varchar2000,
423 x_results IN OUT NOCOPY po_validation_results_type,
424 x_result_type OUT NOCOPY VARCHAR2)
425 IS
426 d_mod CONSTANT VARCHAR2(100) := d_contractor_name;
427 BEGIN
428 IF (x_results IS NULL) THEN
429 x_results := po_validation_results_type.new_instance();
430 END IF;
431
432 IF po_log.d_proc THEN
433 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
434 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
435 po_log.proc_begin(d_mod, 'p_contractor_last_name_tbl', p_contractor_last_name_tbl);
436 po_log.proc_begin(d_mod, 'p_contractor_first_name_tbl', p_contractor_first_name_tbl);
437 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
438 END IF;
439
440 x_result_type := po_validations.c_result_type_success;
441
442 FOR i IN 1 .. p_id_tbl.COUNT LOOP
443 IF (p_purchase_basis_tbl(i) <> 'TEMP LABOR'
444 OR p_doc_type <> 'STANDARD')
445 AND (p_contractor_last_name_tbl(i) IS NOT NULL
446 OR p_contractor_first_name_tbl(i) IS NOT NULL) THEN
447 x_results.add_result(p_entity_type => c_entity_type_line,
448 p_entity_id => p_id_tbl(i),
449 p_column_name => 'CONTRACTOR FIRST/LAST NAME',
450 p_column_val => p_contractor_last_name_tbl(i),
451 p_message_name => 'PO_PDOI_SVC_NO_NAME',
452 p_validation_id => PO_VAL_CONSTANTS.c_contractor_name);
453 x_result_type := po_validations.c_result_type_failure;
454 END IF;
455 END LOOP;
456
457 IF po_log.d_proc THEN
458 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
459 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
460 END IF;
461 EXCEPTION
462 WHEN OTHERS THEN
463 IF po_log.d_exc THEN
464 po_log.exc(d_mod, 0, NULL);
465 END IF;
466
467 RAISE;
468 END contractor_name;
469
470 -------------------------------------------------------------------------
471 -- If purchase basis is TEMP LABOR, then job id must not be null
472 -------------------------------------------------------------------------
473 PROCEDURE job_id(
474 p_id_tbl IN po_tbl_number,
475 p_job_id_tbl IN po_tbl_number,
476 p_job_business_group_id_tbl IN po_tbl_number,
477 p_purchase_basis_tbl IN po_tbl_varchar30,
478 p_category_id_tbl IN po_tbl_number,
479 x_result_set_id IN OUT NOCOPY NUMBER,
480 x_results IN OUT NOCOPY po_validation_results_type,
481 x_result_type OUT NOCOPY VARCHAR2)
482 IS
483 d_mod CONSTANT VARCHAR2(100) := d_job_id;
484 BEGIN
485 IF x_result_set_id IS NULL THEN
486 x_result_set_id := po_validations.next_result_set_id();
487 END IF;
488
489 IF (x_results IS NULL) THEN
490 x_results := po_validation_results_type.new_instance();
491 END IF;
492
493 IF po_log.d_proc THEN
494 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
495 po_log.proc_begin(d_mod, 'p_job_id_tbl', p_job_id_tbl);
496 po_log.proc_begin(d_mod, 'p_job_business_group_id_tbl', p_job_business_group_id_tbl);
497 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
498 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
499 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
500 END IF;
501
502 x_result_type := po_validations.c_result_type_success;
503
504 FOR i IN 1 .. p_id_tbl.COUNT LOOP
505 IF p_purchase_basis_tbl(i) <> 'TEMP LABOR' AND p_job_id_tbl(i) IS NOT NULL THEN
506 x_results.add_result(p_entity_type => c_entity_type_line,
507 p_entity_id => p_id_tbl(i),
508 p_column_name => 'JOB_ID',
509 p_column_val => p_job_id_tbl(i),
510 p_message_name => 'PO_PDOI_SVC_NO_JOB',
511 p_validation_id => PO_VAL_CONSTANTS.c_job_id_null );
512 x_result_type := po_validations.c_result_type_failure;
513 ELSIF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_job_id_tbl(i) IS NULL THEN
514 x_results.add_result(p_entity_type => c_entity_type_line,
515 p_entity_id => p_id_tbl(i),
516 p_column_name => 'JOB_ID',
517 p_column_val => p_job_id_tbl(i),
518 p_message_name => 'PO_PDOI_SVC_MUST_JOB',
519 p_validation_id => PO_VAL_CONSTANTS.c_job_id_not_null );
520 x_result_type := po_validations.c_result_type_failure;
521 END IF;
522 END LOOP;
523
524 --check that job_id is valid within the relevant business group.
525 IF NVL(hr_general.get_xbg_profile, 'N') = 'N' THEN
526 -- xbg profile is N or job business_group_id is null
527 FORALL i IN 1 .. p_id_tbl.COUNT
528 INSERT INTO po_validation_results_gt
529 (result_set_id,
530 result_type,
531 entity_type,
532 entity_id,
533 message_name,
534 column_name,
535 column_val,
536 token1_name,
537 token1_value,
538 token2_name,
539 token2_value,
540 validation_id)
541 SELECT x_result_set_id,
542 po_validations.c_result_type_failure,
543 c_entity_type_line,
544 p_id_tbl(i),
545 'PO_PDOI_SVC_INVALID_JOB',
546 'JOB_ID',
547 p_job_id_tbl(i),
548 'JOB_ID',
549 p_job_id_tbl(i),
550 'JOB_BG_ID',
551 p_job_business_group_id_tbl(i),
552 PO_VAL_CONSTANTS.c_job_id_valid
553 FROM DUAL
554 WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
555 AND NOT EXISTS(
556 SELECT 1
557 FROM per_jobs_vl pj, financials_system_parameters fsp
558 WHERE pj.job_id = p_job_id_tbl(i)
559 AND pj.business_group_id = fsp.business_group_id
560 AND fsp.business_group_id = NVL(p_job_business_group_id_tbl(i),
561 fsp.business_group_id)
562 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from),
563 TRUNC(SYSDATE))
564 AND NVL(TRUNC(pj.date_to),
565 TRUNC(SYSDATE)));
566
567 IF (SQL%ROWCOUNT > 0) THEN
568 x_result_type := po_validations.c_result_type_failure;
569 END IF;
570 ELSE
571 -- Cross Business group profile is 'Y'
572 FORALL i IN 1 .. p_id_tbl.COUNT
573 INSERT INTO po_validation_results_gt
574 (result_set_id,
575 result_type,
576 entity_type,
577 entity_id,
578 message_name,
579 column_name,
580 column_val,
581 token1_name,
582 token1_value,
583 token2_name,
584 token2_value,
585 validation_id)
586 SELECT x_result_set_id,
587 po_validations.c_result_type_failure,
588 c_entity_type_line,
589 p_id_tbl(i),
590 'PO_PDOI_SVC_INVALID_JOB',
591 'JOB_ID',
592 p_job_id_tbl(i),
593 'JOB_ID',
594 p_job_id_tbl(i),
595 'JOB_BG_ID',
596 p_job_business_group_id_tbl(i),
597 PO_VAL_CONSTANTS.c_job_id_valid
598 FROM DUAL
599 WHERE p_job_business_group_id_tbl(i) IS NOT NULL
600 AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
601 AND NOT EXISTS(
602 SELECT 1
603 FROM per_jobs_vl pj, per_business_groups_perf pbg
604 WHERE pj.job_id = p_job_id_tbl(i)
605 AND pj.business_group_id = p_job_business_group_id_tbl(i)
606 AND pj.business_group_id = pbg.business_group_id
607 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from),
608 TRUNC(SYSDATE))
609 AND NVL(TRUNC(pj.date_to),
610 TRUNC(SYSDATE))
611 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from),
612 TRUNC(SYSDATE))
613 AND NVL(TRUNC(pbg.date_to),
614 TRUNC(SYSDATE)));
615 IF (SQL%ROWCOUNT > 0) THEN
616 x_result_type := po_validations.c_result_type_failure;
617 END IF;
618 END IF;
619
620 -- job must be valid for the specified category
621 FORALL i IN 1 .. p_id_tbl.COUNT
622 INSERT INTO po_validation_results_gt
623 (result_set_id,
624 result_type,
625 entity_type,
626 entity_id,
627 message_name,
628 column_name,
629 column_val,
630 validation_id)
631 SELECT x_result_set_id,
632 po_validations.c_result_type_failure,
633 c_entity_type_line,
634 p_id_tbl(i),
635 'PO_PDOI_SVC_INVALID_JOB_CAT',
636 'JOB_ID',
637 p_job_id_tbl(i),
638 PO_VAL_CONSTANTS.c_job_id_valid_cat
639 FROM DUAL
640 WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
641 AND p_category_id_tbl(i) IS NOT NULL
642 AND NOT EXISTS(
643 SELECT 1
644 FROM po_job_associations_b pja, per_jobs_vl pj
645 WHERE pja.job_id = p_job_id_tbl(i)
646 AND pja.category_id = p_category_id_tbl(i)
647 AND pja.job_id = pj.job_id
648 AND NVL(TRUNC(pja.inactive_date), TRUNC(sysdate)) >= TRUNC(sysdate)
649 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from), TRUNC(SYSDATE))
650 AND NVL(TRUNC(pj.date_to), TRUNC(SYSDATE)));
651
652 IF (SQL%ROWCOUNT > 0) THEN
653 x_result_type := po_validations.c_result_type_failure;
654 END IF;
655
656 IF po_log.d_proc THEN
657 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
658 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
659 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
660 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
661 END IF;
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF po_log.d_exc THEN
665 po_log.exc(d_mod, 0, NULL);
666 END IF;
667
668 RAISE;
669 END job_id;
670
671 -------------------------------------------------------------------------
672 -- If services procurement not enabled, order_type_lookup_code cannot be
673 -- 'FIXED PRICE' or 'RATE'
674 -------------------------------------------------------------------------
675 PROCEDURE job_business_group_id(
676 p_id_tbl IN po_tbl_number,
677 p_job_id_tbl IN po_tbl_number,
678 p_job_business_group_id_tbl IN po_tbl_number,
679 p_purchase_basis_tbl IN po_tbl_varchar30,
680 x_result_set_id IN OUT NOCOPY NUMBER,
681 x_result_type OUT NOCOPY VARCHAR2)
682 IS
683 d_mod CONSTANT VARCHAR2(100) := d_job_business_group_id;
684 BEGIN
685 IF x_result_set_id IS NULL THEN
686 x_result_set_id := po_validations.next_result_set_id();
687 END IF;
688
689 IF po_log.d_proc THEN
690 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
691 po_log.proc_begin(d_mod, 'p_job_id_tbl', p_job_id_tbl);
692 po_log.proc_begin(d_mod, 'p_job_business_group_id_tbl', p_job_business_group_id_tbl);
693 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
694 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
695 END IF;
696
697 x_result_type := po_validations.c_result_type_success;
698
699 IF NVL(hr_general.get_xbg_profile, 'N') = 'N' THEN
700 -- xbg profile is N but job_business_group_id not in FSP
701 FORALL i IN 1 .. p_id_tbl.COUNT
702 INSERT INTO po_validation_results_gt
703 (result_set_id,
704 result_type,
705 entity_type,
706 entity_id,
707 message_name,
708 column_name,
709 column_val,
710 validation_id)
711 SELECT x_result_set_id,
712 po_validations.c_result_type_failure,
713 c_entity_type_line,
714 p_id_tbl(i),
715 'PO_PDOI_SVC_CANNOT_CROSS_BG',
716 'JOB_BUSINESS_GROUP_ID',
717 p_job_business_group_id_tbl(i),
718 PO_VAL_CONSTANTS.c_job_bg_id_not_cross_bg
719 FROM DUAL
720 WHERE p_job_business_group_id_tbl(i) IS NOT NULL
721 AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
722 AND NOT EXISTS(SELECT 1
723 FROM financials_system_parameters fsp
724 WHERE fsp.business_group_id = p_job_business_group_id_tbl(i));
725
726 IF (SQL%ROWCOUNT > 0) THEN
727 x_result_type := po_validations.c_result_type_failure;
728 END IF;
729 ELSE
730 -- Cross Business group profile is 'Y', need to validate job business group id
731 FORALL i IN 1 .. p_id_tbl.COUNT
732 INSERT INTO po_validation_results_gt
733 (result_set_id,
734 result_type,
735 entity_type,
736 entity_id,
737 message_name,
738 column_name,
739 column_val,
740 validation_id)
741 SELECT x_result_set_id,
742 po_validations.c_result_type_failure,
743 c_entity_type_line,
744 p_id_tbl(i),
745 'PO_PDOI_SVC_INVALID_BG',
746 'JOB_BUSINESS_GROUP_ID',
747 p_job_business_group_id_tbl(i),
748 PO_VAL_CONSTANTS.c_job_business_group_id_valid
749 FROM DUAL
750 WHERE p_job_business_group_id_tbl(i) IS NOT NULL
751 AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
752 AND NOT EXISTS(
753 SELECT 1
754 FROM per_business_groups_perf pbg
755 WHERE pbg.business_group_id = p_job_business_group_id_tbl(i)
756 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from), TRUNC(SYSDATE))
757 AND NVL(TRUNC(pbg.date_to), TRUNC(SYSDATE)));
758
759 IF (SQL%ROWCOUNT > 0) THEN
760 x_result_type := po_validations.c_result_type_failure;
761 END IF;
762 END IF;
763
764 IF po_log.d_proc THEN
765 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
766 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
767 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
768 END IF;
769 EXCEPTION
770 WHEN OTHERS THEN
771 IF po_log.d_exc THEN
772 po_log.exc(d_mod, 0, NULL);
773 END IF;
774
775 RAISE;
776 END job_business_group_id;
777
778 -------------------------------------------------------------------------
779 -- If purchase_basis = 'TEMP LABOR', then capital_expense_flag cannot = 'Y'
780 -------------------------------------------------------------------------
781 PROCEDURE capital_expense_flag(
782 p_id_tbl IN po_tbl_number,
783 p_purchase_basis_tbl IN po_tbl_varchar30,
784 p_capital_expense_flag_tbl IN po_tbl_varchar1,
785 x_results IN OUT NOCOPY po_validation_results_type,
786 x_result_type OUT NOCOPY VARCHAR2)
787 IS
788 d_mod CONSTANT VARCHAR2(100) := d_capital_expense_flag;
789 BEGIN
790 IF (x_results IS NULL) THEN
791 x_results := po_validation_results_type.new_instance();
792 END IF;
793
794 IF po_log.d_proc THEN
795 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
796 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
797 po_log.proc_begin(d_mod, 'p_capital_expense_flag_tbl', p_capital_expense_flag_tbl);
798 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
799 END IF;
800
801 x_result_type := po_validations.c_result_type_success;
802
803 FOR i IN 1 .. p_id_tbl.COUNT LOOP
804 IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_capital_expense_flag_tbl(i) = 'Y' THEN
805 x_results.add_result(p_entity_type => c_entity_type_line,
806 p_entity_id => p_id_tbl(i),
807 p_column_name => 'CAPITAL_EXPENSE_FLAG',
808 p_column_val => p_capital_expense_flag_tbl(i),
809 p_message_name => 'PO_SVC_NO_CAP_EXPENSE',
810 p_validation_id => PO_VAL_CONSTANTS.c_capital_expense_flag_null);
811 x_result_type := po_validations.c_result_type_failure;
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 capital_expense_flag;
827
828 -------------------------------------------------------------------------
829 -- If purchase_basis = 'TEMP LABOR', then un_number must be null
830 -------------------------------------------------------------------------
831 PROCEDURE un_number_id(
832 p_id_tbl IN po_tbl_number,
833 p_purchase_basis_tbl IN po_tbl_varchar30,
834 p_un_number_id_tbl IN po_tbl_number,
835 x_result_set_id IN OUT NOCOPY NUMBER,
836 x_results IN OUT NOCOPY po_validation_results_type,
837 x_result_type OUT NOCOPY VARCHAR2)
838 IS
839 d_mod CONSTANT VARCHAR2(100) := d_un_number_id;
840 BEGIN
841 IF x_result_set_id IS NULL THEN
842 x_result_set_id := po_validations.next_result_set_id();
843 END IF;
844
845 IF (x_results IS NULL) THEN
846 x_results := po_validation_results_type.new_instance();
847 END IF;
848
849 IF po_log.d_proc THEN
850 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
851 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
852 po_log.proc_begin(d_mod, 'p_un_number_id_tbl', p_un_number_id_tbl);
853 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
854 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
855 END IF;
856
857 x_result_type := po_validations.c_result_type_success;
858
859 FOR i IN 1 .. p_id_tbl.COUNT LOOP
860 IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_un_number_id_tbl(i) IS NOT NULL THEN
861 x_results.add_result(p_entity_type => c_entity_type_line,
862 p_entity_id => p_id_tbl(i),
863 p_column_name => 'UN_NUMBER',
864 p_column_val => p_un_number_id_tbl(i),
865 p_message_name => 'PO_PDOI_SVC_NO_UNNUMBER',
866 p_validation_id => PO_VAL_CONSTANTS.c_un_number_id_null);
867 x_result_type := po_validations.c_result_type_failure;
868 END IF;
869 END LOOP;
870
871 FORALL i IN 1 .. p_id_tbl.COUNT
872 INSERT INTO po_validation_results_gt
873 (result_set_id,
874 result_type,
875 entity_type,
876 entity_id,
877 message_name,
878 column_name,
879 column_val,
880 token1_name,
881 token1_value,
882 validation_id)
883 SELECT x_result_set_id,
884 po_validations.c_result_type_failure,
885 c_entity_type_line,
886 p_id_tbl(i),
887 'PO_PDOI_INVALID_UN_NUMBER_ID',
888 'UN_NUMBER_ID',
889 p_un_number_id_tbl(i),
890 'VALUE',
891 p_un_number_id_tbl(i),
892 PO_VAL_CONSTANTS.c_un_number_id_valid
893 FROM DUAL
894 WHERE p_un_number_id_tbl(i) IS NOT NULL
895 AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
896 AND NOT EXISTS(SELECT 1
897 FROM po_un_numbers_val_v pun
898 WHERE pun.un_number_id = p_un_number_id_tbl(i));
899
900 IF (SQL%ROWCOUNT > 0) THEN
901 x_result_type := po_validations.c_result_type_failure;
902 END IF;
903
904 IF po_log.d_proc THEN
905 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
906 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
907 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
908 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
909 END IF;
910 EXCEPTION
911 WHEN OTHERS THEN
912 IF po_log.d_exc THEN
913 po_log.exc(d_mod, 0, NULL);
914 END IF;
915
916 RAISE;
917 END un_number_id;
918
919 -------------------------------------------------------------------------
920 -- If purchase_basis = 'TEMP LABOR', then un_number must be null
921 -------------------------------------------------------------------------
922 PROCEDURE hazard_class_id(
923 p_id_tbl IN po_tbl_number,
924 p_purchase_basis_tbl IN po_tbl_varchar30,
925 p_hazard_class_id_tbl IN po_tbl_number,
926 x_result_set_id IN OUT NOCOPY NUMBER,
927 x_results IN OUT NOCOPY po_validation_results_type,
928 x_result_type OUT NOCOPY VARCHAR2)
929 IS
930 d_mod CONSTANT VARCHAR2(100) := d_hazard_class_id;
931 BEGIN
932 IF x_result_set_id IS NULL THEN
933 x_result_set_id := po_validations.next_result_set_id();
934 END IF;
935
936 IF (x_results IS NULL) THEN
937 x_results := po_validation_results_type.new_instance();
938 END IF;
939
940 IF po_log.d_proc THEN
941 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
942 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
943 po_log.proc_begin(d_mod, 'p_hazard_class_id_tbl', p_hazard_class_id_tbl);
944 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
945 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
946 END IF;
947
948 x_result_type := po_validations.c_result_type_success;
949
950 FOR i IN 1 .. p_id_tbl.COUNT LOOP
951 IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_hazard_class_id_tbl(i) IS NOT NULL THEN
952 x_results.add_result(p_entity_type => c_entity_type_line,
953 p_entity_id => p_id_tbl(i),
954 p_column_name => 'HAZARD_CLASS',
955 p_column_val => p_hazard_class_id_tbl(i),
956 p_message_name => 'PO_PDOI_SVC_NO_HAZARD_CLASS',
957 p_validation_id => PO_VAL_CONSTANTS.c_hazard_class_id_null );
958 x_result_type := po_validations.c_result_type_failure;
959 END IF;
960 END LOOP;
961
962 FORALL i IN 1 .. p_id_tbl.COUNT
963 INSERT INTO po_validation_results_gt
964 (result_set_id,
965 result_type,
966 entity_type,
967 entity_id,
968 message_name,
969 column_name,
970 column_val,
971 token1_name,
972 token1_value,
973 validation_id)
974 SELECT x_result_set_id,
975 po_validations.c_result_type_failure,
976 c_entity_type_line,
977 p_id_tbl(i),
978 'PO_PDOI_INVALID_HAZ_ID',
979 'HAZARD_CLASS_ID',
980 p_hazard_class_id_tbl(i),
981 'VALUE',
982 p_hazard_class_id_tbl(i),
983 PO_VAL_CONSTANTS.c_hazard_class_id_valid
984 FROM DUAL
985 WHERE p_hazard_class_id_tbl(i) IS NOT NULL
986 AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
987 AND NOT EXISTS(SELECT 'Y'
988 FROM po_hazard_classes_val_v phc
989 WHERE phc.hazard_class_id = p_hazard_class_id_tbl(i));
990
991 IF (SQL%ROWCOUNT > 0) THEN
992 x_result_type := po_validations.c_result_type_failure;
993 END IF;
994
995 IF po_log.d_proc THEN
996 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
997 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
998 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
999 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1000 END IF;
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 IF po_log.d_exc THEN
1004 po_log.exc(d_mod, 0, NULL);
1005 END IF;
1006
1007 RAISE;
1008 END hazard_class_id;
1009
1010 -------------------------------------------------------------------------
1011 -- If order_type_lookup_code is 'FIXED PRICE', 'RATE', or 'AMOUNT', item_id has to be null
1012 -------------------------------------------------------------------------
1013 PROCEDURE item_id(
1014 p_id_tbl IN po_tbl_number,
1015 p_item_id_tbl IN po_tbl_number,
1016 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1017 p_line_type_id_tbl IN po_tbl_number,
1018 p_inventory_org_id IN NUMBER,
1019 x_result_set_id IN OUT NOCOPY NUMBER,
1020 x_results IN OUT NOCOPY po_validation_results_type,
1021 x_result_type OUT NOCOPY VARCHAR2)
1022 IS
1023 d_mod CONSTANT VARCHAR2(100) := d_item_id;
1024 BEGIN
1025 IF x_result_set_id IS NULL THEN
1026 x_result_set_id := po_validations.next_result_set_id();
1027 END IF;
1028
1029 IF (x_results IS NULL) THEN
1030 x_results := po_validation_results_type.new_instance();
1031 END IF;
1032
1033 IF po_log.d_proc THEN
1034 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1035 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
1036 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1037 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
1038 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1039 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1040 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1041 END IF;
1042
1043 x_result_type := po_validations.c_result_type_success;
1044
1045 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1046 IF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE')
1047 AND p_item_id_tbl(i) IS NOT NULL THEN
1048 x_results.add_result(p_entity_type => c_entity_type_line,
1049 p_entity_id => p_id_tbl(i),
1050 p_column_name => 'ITEM_ID',
1051 p_column_val => p_item_id_tbl(i),
1052 p_message_name => 'PO_PDOI_COLUMN_NULL',
1053 p_token1_name => 'COLUMN_NAME',
1054 p_token1_value => 'ITEM_ID',
1055 p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1056 x_result_type := po_validations.c_result_type_failure;
1057 ELSIF p_order_type_lookup_code_tbl(i) = 'AMOUNT' AND p_item_id_tbl(i) IS NOT NULL THEN
1058 x_results.add_result(p_entity_type => c_entity_type_line,
1059 p_entity_id => p_id_tbl(i),
1060 p_column_name => 'ITEM_ID',
1061 p_column_val => p_item_id_tbl(i),
1062 p_message_name => 'PO_PDOI_COLUMN_NULL',
1063 p_token1_name => 'COLUMN_NAME',
1064 p_token1_value => 'ITEM_ID',
1065 p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1066 x_result_type := po_validations.c_result_type_failure;
1067 END IF;
1068 END LOOP;
1069
1070 -- If order_type_lookup_code is Quantity and outside_operation flag is 'Y'
1071 -- , then the item_id cannot be null
1072 FORALL i IN 1 .. p_id_tbl.COUNT
1073 INSERT INTO po_validation_results_gt
1074 (result_set_id,
1075 result_type,
1076 entity_type,
1077 entity_id,
1078 message_name,
1079 column_name,
1080 column_val,
1081 validation_id)
1082 SELECT x_result_set_id,
1083 po_validations.c_result_type_failure,
1084 c_entity_type_line,
1085 p_id_tbl(i),
1086 'PO_PDOI_ITEM_NOT_NULL',
1087 'ITEM_ID',
1088 p_item_id_tbl(i),
1089 PO_VAL_CONSTANTS.c_item_id_not_null
1090 FROM DUAL
1091 WHERE p_order_type_lookup_code_tbl(i) = 'QUANTITY'
1092 AND p_item_id_tbl(i) IS NULL
1093 AND EXISTS(
1094 SELECT 1
1095 FROM po_line_types_b plt
1096 WHERE p_line_type_id_tbl(i) IS NOT NULL
1097 AND p_line_type_id_tbl(i) = plt.line_type_id
1098 AND plt.outside_operation_flag = 'Y');
1099
1100 IF (SQL%ROWCOUNT > 0) THEN
1101 x_result_type := po_validations.c_result_type_failure;
1102 END IF;
1103
1104 -- if item id is not null, it has to exist in mtl_system_items table
1105 FORALL i IN 1 .. p_id_tbl.COUNT
1106 INSERT INTO po_validation_results_gt
1107 (result_set_id,
1108 result_type,
1109 entity_type,
1110 entity_id,
1111 message_name,
1112 column_name,
1113 column_val,
1114 token1_name,
1115 token1_value,
1116 validation_id)
1117 SELECT x_result_set_id,
1118 po_validations.c_result_type_failure,
1119 c_entity_type_line,
1120 p_id_tbl(i),
1121 DECODE(plt.outside_operation_flag, 'N', 'PO_PDOI_INVALID_ITEM_ID', 'PO_PDOI_INVALID_OP_ITEM_ID'),
1122 'ITEM_ID',
1123 p_item_id_tbl(i),
1124 'VALUE',
1125 p_item_id_tbl(i),
1126 DECODE(plt.outside_operation_flag, 'N', PO_VAL_CONSTANTS.c_item_id_valid,
1127 PO_VAL_CONSTANTS.c_item_id_op_valid)
1128 FROM po_line_types_b plt
1129 WHERE p_item_id_tbl(i) IS NOT NULL
1130 AND p_line_type_id_tbl(i) IS NOT NULL
1131 AND p_line_type_id_tbl(i) = plt.line_type_id
1132 AND plt.outside_operation_flag IS NOT NULL
1133 AND NOT EXISTS(
1134 SELECT 1
1135 FROM mtl_system_items msi
1136 WHERE msi.inventory_item_id = p_item_id_tbl(i)
1137 AND msi.organization_id = p_inventory_org_id
1138 AND msi.enabled_flag = 'Y'
1139 AND msi.purchasing_item_flag = 'Y'
1140 AND msi.purchasing_enabled_flag = 'Y'
1141 AND msi.outside_operation_flag = plt.outside_operation_flag
1142 AND TRUNC(NVL(msi.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1143 AND TRUNC(NVL(msi.end_date_active, SYSDATE)) >= TRUNC(SYSDATE));
1144
1145 IF (SQL%ROWCOUNT > 0) THEN
1146 x_result_type := po_validations.c_result_type_failure;
1147 END IF;
1148
1149 IF po_log.d_proc THEN
1150 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1151 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1152 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1153 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1154 END IF;
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 IF po_log.d_exc THEN
1158 po_log.exc(d_mod, 0, NULL);
1159 END IF;
1160
1161 RAISE;
1162 END item_id;
1163
1164 -------------------------------------------------------------------------
1165 -- Make sure that the item_description is populated, and also need to find out if it is different
1166 -- from what is setup for the item. Would not allow item_description update if item attribute
1167 -- allow_item_desc_update_flag is N.
1168 -------------------------------------------------------------------------
1169 PROCEDURE item_description(
1170 p_id_tbl IN po_tbl_number,
1171 p_item_description_tbl IN po_tbl_varchar2000,
1172 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1173 p_item_id_tbl IN po_tbl_number,
1174 p_create_or_update_item IN VARCHAR2,
1175 p_inventory_org_id IN NUMBER,
1176 x_result_set_id IN OUT NOCOPY NUMBER,
1177 x_result_type OUT NOCOPY VARCHAR2)
1178 IS
1179 d_mod CONSTANT VARCHAR2(100) := d_item_description;
1180 BEGIN
1181 IF x_result_set_id IS NULL THEN
1182 x_result_set_id := po_validations.next_result_set_id();
1183 END IF;
1184
1185 IF po_log.d_proc THEN
1186 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1187 po_log.proc_begin(d_mod, 'p_item_description_tbl', p_item_description_tbl);
1188 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1189 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
1190 po_log.proc_begin(d_mod, 'p_create_or_update_item', p_create_or_update_item);
1191 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1192 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1193 END IF;
1194
1195 /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
1196 x_result_type := po_validations.c_result_type_success;
1197 FORALL i IN 1 .. p_id_tbl.COUNT
1198 INSERT INTO po_validation_results_gt
1199 (result_set_id,
1200 result_type,
1201 entity_type,
1202 entity_id,
1203 message_name,
1204 column_name,
1205 column_val,
1206 token1_name,
1207 token1_value,
1208 validation_id)
1209 SELECT x_result_set_id,
1210 po_validations.c_result_type_failure,
1211 c_entity_type_line,
1212 p_id_tbl(i),
1213 DECODE(p_item_description_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_DIFF_ITEM_DESC'),
1214 'ITEM_DESCRIPTION',
1215 p_item_description_tbl(i),
1216 DECODE(p_item_description_tbl(i), NULL, 'COLUMN_NAME', NULL),
1217 DECODE(p_item_description_tbl(i), NULL, 'ITEM_DESCRIPTION', NULL),
1218 DECODE(p_item_description_tbl(i), NULL, PO_VAL_CONSTANTS.c_item_desc_not_null,
1219 PO_VAL_CONSTANTS.c_item_desc_not_updatable)
1220 FROM DUAL
1221 WHERE p_item_description_tbl(i) IS NULL
1222 OR ( p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1223 AND p_item_id_tbl(i) IS NOT NULL
1224 AND EXISTS(
1225 SELECT 1 FROM
1226 mtl_system_items msi,mtl_system_items_tl mtl
1227 where msi.inventory_item_id = p_item_id_tbl(i) AND
1228 mtl.inventory_item_id = msi.inventory_item_id
1229 AND msi.organization_id = p_inventory_org_id
1230 AND mtl.organization_id = msi.organization_id
1231 AND msi.allow_item_desc_update_flag = 'N'
1232 and mtl.language = USERENV('LANG')
1233 AND p_item_description_tbl(i) <> mtl.description
1234 AND p_create_or_update_item = 'N'));
1235
1236
1237
1238
1239
1240 IF (SQL%ROWCOUNT > 0) THEN
1241 x_result_type := po_validations.c_result_type_failure;
1242 END IF;
1243
1244 IF po_log.d_proc THEN
1245 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1246 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1247 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1248 END IF;
1249 EXCEPTION
1250 WHEN OTHERS THEN
1251 IF po_log.d_exc THEN
1252 po_log.exc(d_mod, 0, NULL);
1253 END IF;
1254
1255 RAISE;
1256 END item_description;
1257
1258 -------------------------------------------------------------------------
1259 -- check to see if x_item_unit_of_measure is valid in mtl_item_uoms_view
1260 -------------------------------------------------------------------------
1261 PROCEDURE unit_meas_lookup_code(
1262 p_id_tbl IN po_tbl_number,
1263 p_unit_meas_lookup_code_tbl IN po_tbl_varchar30,
1264 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1265 p_item_id_tbl IN po_tbl_number,
1266 p_line_type_id_tbl IN po_tbl_number,
1267 p_inventory_org_id IN NUMBER,
1268 x_result_set_id IN OUT NOCOPY NUMBER,
1269 x_results IN OUT NOCOPY po_validation_results_type,
1270 x_result_type OUT NOCOPY VARCHAR2)
1271 IS
1272 d_mod CONSTANT VARCHAR2(100) := d_unit_meas_lookup_code;
1273 l_service_uom_class VARCHAR2(2000);
1274 BEGIN
1275 IF x_result_set_id IS NULL THEN
1276 x_result_set_id := po_validations.next_result_set_id();
1277 END IF;
1278
1279 IF (x_results IS NULL) THEN
1280 x_results := po_validation_results_type.new_instance();
1281 END IF;
1282
1283 IF po_log.d_proc THEN
1284 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1285 po_log.proc_begin(d_mod, 'p_unit_meas_lookup_code_tbl', p_unit_meas_lookup_code_tbl);
1286 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1287 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
1288 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
1289 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1290 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1291 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1292 END IF;
1293
1294 x_result_type := po_validations.c_result_type_success;
1295
1296 -- If order_type_lookup_code is 'FIXED PRICE', unit_meas_lookup_code has
1297 -- to be null. Otherwise, it cannot be null.
1298 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1299 IF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL) THEN
1300 x_results.add_result(p_entity_type => c_entity_type_line,
1301 p_entity_id => p_id_tbl(i),
1302 p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1303 p_column_val => p_unit_meas_lookup_code_tbl(i),
1304 p_message_name => 'PO_PDOI_SVC_NO_UOM',
1305 p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_null);
1306 x_result_type := po_validations.c_result_type_failure;
1307 ELSIF (p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_meas_lookup_code_tbl(i) IS NULL) THEN
1308 x_results.add_result(p_entity_type => c_entity_type_line,
1309 p_entity_id => p_id_tbl(i),
1310 p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1311 p_column_val => p_unit_meas_lookup_code_tbl(i),
1312 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1313 p_token1_name => 'COLUMN_NAME',
1314 p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
1315 p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_not_null);
1316 x_result_type := po_validations.c_result_type_failure;
1317 END IF;
1318 END LOOP;
1319
1320 FORALL i IN 1 .. p_id_tbl.COUNT
1321 INSERT INTO po_validation_results_gt
1322 (result_set_id,
1323 result_type,
1324 entity_type,
1325 entity_id,
1326 message_name,
1327 column_name,
1328 column_val, -- bug5252804
1329 token1_name,
1330 token2_name,
1331 token3_name,
1332 token1_value,
1333 token2_value,
1334 token3_value,
1335 validation_id)
1336 SELECT x_result_set_id,
1337 po_validations.c_result_type_failure,
1338 c_entity_type_line,
1339 p_id_tbl(i),
1340 'PO_PDOI_ITEM_RELATED_INFO',
1341 'UNIT_MEAS_LOOKUP_CODE',
1342 p_unit_meas_lookup_code_tbl(i), -- bug5252804
1343 'COLUMN_NAME',
1344 'VALUE',
1345 'ITEM',
1346 'UNIT_MEAS_LOOKUP_CODE',
1347 p_unit_meas_lookup_code_tbl(i),
1348 p_item_id_tbl(i),
1349 PO_VAL_CONSTANTS.c_unit_meas_lookup_item
1350 FROM DUAL
1351 WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1352 AND p_item_id_tbl(i) IS NOT NULL
1353 AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
1354 AND NOT EXISTS(
1355 SELECT 1
1356 FROM mtl_item_uoms_view miuv
1357 WHERE miuv.inventory_item_id = p_item_id_tbl(i)
1358 AND miuv.organization_id = p_inventory_org_id
1359 AND miuv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1360
1361 IF (SQL%ROWCOUNT > 0) THEN
1362 x_result_type := po_validations.c_result_type_failure;
1363 END IF;
1364
1365 -- check to see if x_uom_code is valid in po_units_of_measure_val_v
1366 FORALL i IN 1 .. p_id_tbl.COUNT
1367 INSERT INTO po_validation_results_gt
1368 (result_set_id,
1369 result_type,
1370 entity_type,
1371 entity_id,
1372 message_name,
1373 column_name,
1374 column_val,
1375 token1_name,
1376 token1_value,
1377 validation_id)
1378 SELECT x_result_set_id,
1379 po_validations.c_result_type_failure,
1380 c_entity_type_line,
1381 p_id_tbl(i),
1382 'PO_PDOI_INVALID_UOM_CODE',
1383 'UNIT_MEAS_LOOKUP_CODE',
1384 p_unit_meas_lookup_code_tbl(i),
1385 'VALUE',
1386 p_unit_meas_lookup_code_tbl(i),
1387 PO_VAL_CONSTANTS.c_unit_meas_lookup_valid
1388 FROM DUAL
1389 WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1390 AND p_item_id_tbl(i) IS NULL
1391 AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
1392 AND NOT EXISTS(SELECT 1
1393 FROM po_units_of_measure_val_v pumv
1394 WHERE pumv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1395
1396 IF (SQL%ROWCOUNT > 0) THEN
1397 x_result_type := po_validations.c_result_type_failure;
1398 END IF;
1399
1400 -- validation for AMOUNT based line type
1401 FORALL i IN 1 .. p_id_tbl.COUNT
1402 INSERT INTO po_validation_results_gt
1403 (result_set_id,
1404 result_type,
1405 entity_type,
1406 entity_id,
1407 message_name,
1408 column_name,
1409 column_val,
1410 token1_name,
1411 token2_name,
1412 token3_name,
1413 token1_value,
1414 token2_value,
1415 token3_value,
1416 validation_id)
1417 SELECT x_result_set_id,
1418 po_validations.c_result_type_failure,
1419 c_entity_type_line,
1420 p_id_tbl(i),
1421 'PO_PDOI_INVALID_LINE_TYPE_INFO',
1422 'UNIT_MEAS_LOOKUP_CODE',
1423 p_unit_meas_lookup_code_tbl(i),
1424 'COLUMN_NAME',
1425 'VALUE',
1426 'LINE_TYPE',
1427 'UNIT_MEAS_LOOKUP_CODE',
1428 p_unit_meas_lookup_code_tbl(i),
1429 pltb.unit_of_measure,
1430 PO_VAL_CONSTANTS.c_unit_meas_lookup_line_type
1431 FROM PO_LINE_TYPES_B pltb
1432 WHERE pltb.line_type_id = p_line_type_id_tbl(i)
1433 AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
1434 AND p_unit_meas_lookup_code_tbl(i) <> pltb.unit_of_measure;
1435
1436 IF (SQL%ROWCOUNT > 0) THEN
1437 x_result_type := po_validations.c_result_type_failure;
1438 END IF;
1439
1440 -- validation for 'RATE' based line
1441 l_service_uom_class := NVL(FND_PROFILE.value('PO_RATE_UOM_CLASS'), '999');
1442 FORALL i IN 1 .. p_id_tbl.COUNT
1443 INSERT INTO po_validation_results_gt
1444 (result_set_id,
1445 result_type,
1446 entity_type,
1447 entity_id,
1448 message_name,
1449 column_name,
1450 column_val,
1451 token1_name,
1452 token2_name,
1453 token1_value,
1454 token2_value,
1455 validation_id)
1456 SELECT x_result_set_id,
1457 po_validations.c_result_type_failure,
1458 c_entity_type_line,
1459 p_id_tbl(i),
1460 'PO_PDOI_SVC_INVALID_UOM',
1461 'UNIT_MEAS_LOOKUP_CODE',
1462 p_unit_meas_lookup_code_tbl(i),
1463 'COLUMN_NAME',
1464 'VALUE',
1465 'UNIT_MEAS_LOOKUP_CODE',
1466 p_unit_meas_lookup_code_tbl(i),
1467 PO_VAL_CONSTANTS.c_unit_meas_lookup_svc_valid
1468 FROM DUAL
1469 WHERE p_order_type_lookup_code_tbl(i) = 'RATE'
1470 AND NOT EXISTS(SELECT 1
1471 FROM mtl_units_of_measure_vl muomv
1472 WHERE muomv.uom_class = l_service_uom_class
1473 AND muomv.unit_of_measure = p_unit_meas_lookup_code_tbl(i)
1474 AND TRUNC(sysdate) < NVL(muomv.disable_date, TRUNC(sysdate) + 1));
1475
1476 IF (SQL%ROWCOUNT > 0) THEN
1477 x_result_type := po_validations.c_result_type_failure;
1478 END IF;
1479
1480 IF po_log.d_proc THEN
1481 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1482 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1483 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1484 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1485 END IF;
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 IF po_log.d_exc THEN
1489 po_log.exc(d_mod, 0, NULL);
1490 END IF;
1491
1492 RAISE;
1493 END unit_meas_lookup_code;
1494
1495 -------------------------------------------------------------------------
1496 -- If order_type_lookup_code is FIXED PRICE or RATE, or item id is null, then item revision has to
1497 -- be NULL. Check to see if there are x_item_revision exists in mtl_item_revisions table
1498 -------------------------------------------------------------------------
1499 PROCEDURE item_revision(
1500 p_id_tbl IN po_tbl_number,
1501 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1502 p_item_revision_tbl IN po_tbl_varchar5,
1503 p_item_id_tbl IN po_tbl_number,
1504 x_result_set_id IN OUT NOCOPY NUMBER,
1505 x_results IN OUT NOCOPY po_validation_results_type,
1506 x_result_type OUT NOCOPY VARCHAR2)
1507 IS
1508 d_mod CONSTANT VARCHAR2(100) := d_item_revision;
1509 BEGIN
1510 IF x_result_set_id IS NULL THEN
1511 x_result_set_id := po_validations.next_result_set_id();
1512 END IF;
1513
1514 IF po_log.d_proc THEN
1515 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1516 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1517 po_log.proc_begin(d_mod, 'p_item_revision_tbl', p_item_revision_tbl);
1518 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
1519 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1520 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1521 END IF;
1522
1523 x_result_type := po_validations.c_result_type_success;
1524
1525 -- if order_type_lookup_code is FIXED PRICE or RATE, or item id is null,
1526 -- then item revision has to be NULL
1527 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1528 IF (((p_order_type_lookup_code_tbl(i) = 'RATE'
1529 OR p_order_type_lookup_code_tbl(i) = 'FIXED PRICE')
1530 OR p_item_id_tbl(i) IS NULL) AND p_item_revision_tbl(i) IS NOT NULL) THEN
1531 x_results.add_result(p_entity_type => c_entity_type_line,
1532 p_entity_id => p_id_tbl(i),
1533 p_column_name => 'ITEM_REVISION',
1534 p_column_val => p_item_revision_tbl(i),
1535 p_message_name => 'PO_PDOI_COLUMN_NULL',
1536 p_validation_id => PO_VAL_CONSTANTS.c_item_revision_null);
1537 x_result_type := po_validations.c_result_type_failure;
1538 END IF;
1539 END LOOP;
1540
1541 -- check to see if there are x_item_revision exists in mtl_item_revisions
1542 -- table
1543 FORALL i IN 1 .. p_id_tbl.COUNT
1544 INSERT INTO po_validation_results_gt
1545 (result_set_id,
1546 result_type,
1547 entity_type,
1548 entity_id,
1549 message_name,
1550 column_name,
1551 column_val,
1552 token1_name,
1553 token2_name,
1554 token3_name,
1555 token1_value,
1556 token2_value,
1557 token3_value,
1558 validation_id)
1559 SELECT x_result_set_id,
1560 po_validations.c_result_type_failure,
1561 c_entity_type_line,
1562 p_id_tbl(i),
1563 'PO_PDOI_ITEM_RELATED_INFO',
1564 'ITEM_REVISION',
1565 p_item_revision_tbl(i),
1566 'COLUMN_NAME',
1567 'VALUE',
1568 'ITEM',
1569 'item_revision',
1570 p_item_revision_tbl(i),
1571 p_item_id_tbl(i),
1572 PO_VAL_CONSTANTS.c_item_revision_item
1573 FROM DUAL
1574 WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1575 AND p_item_revision_tbl(i) IS NOT NULL
1576 AND p_item_id_tbl(i) IS NOT NULL
1577 AND NOT EXISTS(SELECT 1
1578 FROM mtl_item_revisions mir
1579 WHERE mir.inventory_item_id = p_item_id_tbl(i)
1580 AND mir.revision = p_item_revision_tbl(i));
1581
1582 IF (SQL%ROWCOUNT > 0) THEN
1583 x_result_type := po_validations.c_result_type_failure;
1584 END IF;
1585
1586 IF po_log.d_proc THEN
1587 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1588 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1589 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1590 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1591 END IF;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF po_log.d_exc THEN
1595 po_log.exc(d_mod, 0, NULL);
1596 END IF;
1597
1598 RAISE;
1599 END item_revision;
1600
1601 -------------------------------------------------------------------------
1602 -- Validate and make sure category_id is a valid category within the default category set for Purchasing.
1603 -- Validate if X_category_id belong to the X_item. Check if the Purchasing Category set has
1604 -- 'Validate flag' ON. If Yes, we will validate the Category to exist in the 'Valid Category List'.
1605 -- If No, we will just validate if the category is Enable and Active.
1606 -------------------------------------------------------------------------
1607 PROCEDURE category_id(
1608 p_id_tbl IN po_tbl_number,
1609 p_category_id_tbl IN po_tbl_number,
1610 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1611 p_item_id_tbl IN po_tbl_number,
1612 p_inventory_org_id IN NUMBER,
1613 x_result_set_id IN OUT NOCOPY NUMBER,
1614 x_results IN OUT NOCOPY po_validation_results_type,
1615 x_result_type OUT NOCOPY VARCHAR2)
1616 IS
1617 d_mod CONSTANT VARCHAR2(100) := d_category_id;
1618 x_flag mtl_category_sets_v.validate_flag%TYPE;
1619 x_category_set_id mtl_category_sets_v.category_set_id%TYPE;
1620 BEGIN
1621 IF x_result_set_id IS NULL THEN
1622 x_result_set_id := po_validations.next_result_set_id();
1623 END IF;
1624
1625 IF (x_results IS NULL) THEN
1626 x_results := po_validation_results_type.new_instance();
1627 END IF;
1628
1629 IF po_log.d_proc THEN
1630 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1631 po_log.proc_begin(d_mod, 'p_category_id_tbl', p_category_id_tbl);
1632 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1633 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1634 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1635 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
1636 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
1637 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1638 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1639 END IF;
1640
1641 x_result_type := po_validations.c_result_type_success;
1642
1643 -- Find out the default category_set_id and flag for function_area
1644 -- of PURCHASING".
1645 SELECT validate_flag,
1646 category_set_id
1647 INTO x_flag,
1648 x_category_set_id
1649 FROM mtl_category_sets_v
1650 WHERE category_set_id = (SELECT category_set_id
1651 FROM mtl_default_category_sets
1652 WHERE functional_area_id = 2); /*** purchasing***/
1653
1654 -- Validate if X_category_id belong to the X_item
1655 FORALL i IN 1 .. p_id_tbl.COUNT
1656 INSERT INTO po_validation_results_gt
1657 (result_set_id,
1658 result_type,
1659 entity_type,
1660 entity_id,
1661 message_name,
1662 column_name,
1663 column_val,
1664 token1_name,
1665 token1_value,
1666 token2_name,
1667 token2_value,
1668 token3_name,
1669 token3_value,
1670 validation_id)
1671 SELECT x_result_set_id,
1672 po_validations.c_result_type_failure,
1673 c_entity_type_line,
1674 p_id_tbl(i),
1675 'PO_PDOI_ITEM_RELATED_INFO',
1676 'CATEGORY_ID',
1677 p_category_id_tbl(i),
1678 'COLUMN_NAME',
1679 'CATEGORY_ID',
1680 'VALUE',
1681 p_category_id_tbl(i),
1682 'ITEM',
1683 p_item_id_tbl(i),
1684 PO_VAL_CONSTANTS.c_category_id_item
1685 FROM DUAL
1686 WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1687 AND p_item_id_tbl(i) IS NOT NULL
1688 AND p_category_id_tbl(i) IS NOT NULL
1689 AND NOT EXISTS(
1690 SELECT 1
1691 FROM mtl_item_categories mic, mtl_categories mcs
1692 WHERE mic.category_id = mcs.category_id
1693 AND mic.category_set_id = x_category_set_id
1694 AND mic.category_id = p_category_id_tbl(i)
1695 AND mic.inventory_item_id = p_item_id_tbl(i)
1696 AND mic.organization_id = p_inventory_org_id
1697 AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1698 AND mcs.enabled_flag = 'Y');
1699
1700 IF (SQL%ROWCOUNT > 0) THEN
1701 x_result_type := po_validations.c_result_type_failure;
1702 END IF;
1703
1704 IF x_flag = 'Y' THEN
1705 FORALL i IN 1 .. p_id_tbl.COUNT
1706 INSERT INTO po_validation_results_gt
1707 (result_set_id,
1708 result_type,
1709 entity_type,
1710 entity_id,
1711 message_name,
1712 column_name,
1713 column_val,
1714 token1_name,
1715 token1_value,
1716 validation_id)
1717 SELECT x_result_set_id,
1718 po_validations.c_result_type_failure,
1719 c_entity_type_line,
1720 p_id_tbl(i),
1721 'PO_PDOI_INVALID_CATEGORY_ID',
1722 'CATEGORY_ID',
1723 p_category_id_tbl(i),
1724 'VALUE',
1725 p_category_id_tbl(i),
1726 PO_VAL_CONSTANTS.c_category_id_valid
1727 FROM DUAL
1728 WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1729 AND p_item_id_tbl(i) IS NULL
1730 AND p_category_id_tbl(i) IS NOT NULL
1731 AND NOT EXISTS(
1732 SELECT 'Y'
1733 FROM mtl_categories_vl mcs, mtl_category_set_valid_cats mcsvc
1734 WHERE mcs.category_id = p_category_id_tbl(i)
1735 AND mcs.category_id = mcsvc.category_id
1736 AND mcsvc.category_set_id = x_category_set_id
1737 AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1738 AND mcs.enabled_flag = 'Y');
1739
1740 -- bug5111418
1741 -- fail the record if this validation fails
1742 IF (SQL%ROWCOUNT > 0) THEN
1743 x_result_type := po_validations.c_result_type_failure;
1744 END IF;
1745 ELSE
1746 FORALL i IN 1 .. p_id_tbl.COUNT
1747 INSERT INTO po_validation_results_gt
1748 (result_set_id,
1749 result_type,
1750 entity_type,
1751 entity_id,
1752 message_name,
1753 column_name,
1754 column_val,
1755 token1_name,
1756 token1_value,
1757 validation_id)
1758 SELECT x_result_set_id,
1759 po_validations.c_result_type_failure,
1760 c_entity_type_line,
1761 p_id_tbl(i),
1762 'PO_PDOI_INVALID_CATEGORY_ID',
1763 'CATEGORY_ID',
1764 p_category_id_tbl(i),
1765 'VALUE',
1766 p_category_id_tbl(i),
1767 PO_VAL_CONSTANTS.c_category_id_valid
1768 FROM DUAL
1769 WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1770 AND p_item_id_tbl(i) IS NULL
1771 AND p_category_id_tbl(i) IS NOT NULL
1772 AND NOT EXISTS(
1773 SELECT 1
1774 FROM mtl_categories_vl mcs
1775 WHERE mcs.category_id = p_category_id_tbl(i)
1776 AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1777 AND mcs.enabled_flag = 'Y');
1778
1779 IF (SQL%ROWCOUNT > 0) THEN
1780 x_result_type := po_validations.c_result_type_failure;
1781 END IF;
1782 END IF;
1783
1784 IF po_log.d_proc THEN
1785 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1786 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1787 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1788 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1789 END IF;
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 IF po_log.d_exc THEN
1793 po_log.exc(d_mod, 0, NULL);
1794 END IF;
1795
1796 RAISE;
1797 END category_id;
1798
1799 -------------------------------------------------------------------------
1800 -- Validate ip_category_id is not empty for Blanket and Quotation;
1801 -- Validate ip_category_id is valid if not empty
1802 -------------------------------------------------------------------------
1803 PROCEDURE ip_category_id(
1804 p_id_tbl IN po_tbl_number,
1805 p_ip_category_id_tbl IN po_tbl_number,
1806 x_result_set_id IN OUT NOCOPY NUMBER,
1807 x_results IN OUT NOCOPY po_validation_results_type,
1808 x_result_type OUT NOCOPY VARCHAR2)
1809 IS
1810 d_mod CONSTANT VARCHAR2(100) := d_ip_category_id;
1811 BEGIN
1812 IF x_result_set_id IS NULL THEN
1813 x_result_set_id := po_validations.next_result_set_id();
1814 END IF;
1815
1816 IF (x_results IS NULL) THEN
1817 x_results := po_validation_results_type.new_instance();
1818 END IF;
1819
1820 IF po_log.d_proc THEN
1821 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1822 po_log.proc_begin(d_mod, 'p_ip_category_id_tbl', p_ip_category_id_tbl);
1823 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1824 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1825 END IF;
1826
1827 x_result_type := po_validations.c_result_type_success;
1828 -- validate ip_category_id is not null
1829 po_validation_helper.not_null(p_calling_module => c_program_pdoi,
1830 p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ip_category_id_tbl),
1831 p_entity_id_tbl => p_id_tbl,
1832 p_entity_type => c_entity_type_line,
1833 p_column_name => 'IP_CATEGORY_ID',
1834 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1835 p_token1_name => 'COLUMN_NAME',
1836 p_token1_value => 'IP_CATEGORY_ID',
1837 p_token2_name => 'VALUE',
1838 p_token2_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ip_category_id_tbl),
1839 p_validation_id => PO_VAL_CONSTANTS.c_ip_category_id_not_null,
1840 x_results => x_results,
1841 x_result_type => x_result_type);
1842
1843 -- Validate if x_category_id is valid if not empty
1844 FORALL i IN 1 .. p_id_tbl.COUNT
1845 INSERT INTO po_validation_results_gt
1846 (result_set_id,
1847 result_type,
1848 entity_type,
1849 entity_id,
1850 message_name,
1851 column_name,
1852 column_val,
1853 token1_name,
1854 token1_value,
1855 validation_id)
1856 SELECT x_result_set_id,
1857 po_validations.c_result_type_failure,
1858 c_entity_type_line,
1859 p_id_tbl(i),
1860 'PO_PDOI_INVALID_IP_CATEGORY_ID',
1861 'IP_CATEGORY_ID',
1862 p_ip_category_id_tbl(i),
1863 'VALUE',
1864 p_ip_category_id_tbl(i),
1865 PO_VAL_CONSTANTS.c_ip_category_id_valid
1866 FROM DUAL
1867 WHERE p_ip_category_id_tbl(i) IS NOT NULL
1868 AND p_ip_category_id_tbl(i) <> -2
1869 AND NOT EXISTS(
1870 SELECT 'Y'
1871 FROM icx_cat_categories_v
1872 WHERE rt_category_id = p_ip_category_id_tbl(i));
1873
1874 IF (SQL%ROWCOUNT > 0) THEN
1875 x_result_type := po_validations.c_result_type_failure;
1876 END IF;
1877
1878 IF po_log.d_proc THEN
1879 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1880 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1881 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1882 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1883 END IF;
1884 EXCEPTION
1885 WHEN OTHERS THEN
1886 IF po_log.d_exc THEN
1887 po_log.exc(d_mod, 0, NULL);
1888 END IF;
1889
1890 RAISE;
1891 END ip_category_id;
1892
1893 -------------------------------------------------------------------------
1894 --If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be null
1895 -- and cannot be less than zero.
1896 --If line_type_id is not null and order_type_lookup_code is 'AMOUNT',
1897 -- unit_price should be the same as the one defined in the line_type.
1898 --If order_type_lookup_code is 'FIXED PRICE', unit_price has to be null.
1899 -------------------------------------------------------------------------
1900 PROCEDURE unit_price(
1901 p_id_tbl IN po_tbl_number,
1902 p_unit_price_tbl IN po_tbl_number,
1903 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
1904 p_line_type_id_tbl IN po_tbl_number,
1905 x_result_set_id IN OUT NOCOPY NUMBER,
1906 x_results IN OUT NOCOPY po_validation_results_type,
1907 x_result_type OUT NOCOPY VARCHAR2)
1908 IS
1909 d_mod CONSTANT VARCHAR2(100) := d_unit_price;
1910 BEGIN
1911 IF x_result_set_id IS NULL THEN
1912 x_result_set_id := po_validations.next_result_set_id();
1913 END IF;
1914
1915 IF (x_results IS NULL) THEN
1916 x_results := po_validation_results_type.new_instance();
1917 END IF;
1918
1919 IF po_log.d_proc THEN
1920 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1921 po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
1922 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
1923 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
1924 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1925 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1926 END IF;
1927
1928 x_result_type := po_validations.c_result_type_success;
1929 -- If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be
1930 -- null and cannot be less than zero.
1931 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1932 IF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) IS NULL THEN
1933 x_results.add_result(p_entity_type => c_entity_type_line,
1934 p_entity_id => p_id_tbl(i),
1935 p_column_name => 'UNIT_PRICE',
1936 p_column_val => p_unit_price_tbl(i),
1937 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1938 p_token1_name => 'COLUMN_NAME',
1939 p_token1_value => 'UNIT_PRICE',
1940 p_validation_id => PO_VAL_CONSTANTS.c_unit_price_not_null);
1941 x_result_type := po_validations.c_result_type_failure;
1942 ELSIF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) < 0 THEN
1943 x_results.add_result(p_entity_type => c_entity_type_line,
1944 p_entity_id => p_id_tbl(i),
1945 p_column_name => 'UNIT_PRICE',
1946 p_column_val => p_unit_price_tbl(i),
1947 p_message_name => 'PO_PDOI_LT_ZERO',
1948 p_token1_name => 'COLUMN_NAME',
1949 p_token1_value => 'UNIT_PRICE',
1950 p_token2_name => 'VALUE',
1951 p_token2_value => p_unit_price_tbl(i),
1952 p_validation_id => PO_VAL_CONSTANTS.c_unit_price_ge_zero);
1953 x_result_type := po_validations.c_result_type_failure;
1954 END IF;
1955 END LOOP;
1956
1957
1958 -- bug5130037
1959 -- Provide correct token names/values for the message
1960
1961 -- If line_type_id is not null and order_type_lookup_code is 'AMOUNT',
1962 -- unit_price should be the same as the one defined in the line_type.
1963 FORALL i IN 1 .. p_id_tbl.COUNT
1964 INSERT INTO po_validation_results_gt
1965 (result_set_id,
1966 result_type,
1967 entity_type,
1968 entity_id,
1969 message_name,
1970 column_name,
1971 column_val,
1972 token1_name,
1973 token2_name,
1974 token1_value,
1975 token2_value,
1976 validation_id)
1977 SELECT x_result_set_id,
1978 po_validations.c_result_type_failure,
1979 c_entity_type_line,
1980 p_id_tbl(i),
1981 'PO_PDOI_INVALID_LINE_TYPE_INFO',
1982 'UNIT_PRICE',
1983 p_unit_price_tbl(i),
1984 'COLUMN_NAME',
1985 'VALUE',
1986 'UNIT_PRICE',
1987 p_unit_price_tbl(i),
1988 PO_VAL_CONSTANTS.c_unit_price_line_type
1989 FROM DUAL
1990 WHERE p_line_type_id_tbl(i) IS NOT NULL
1991 AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
1992 AND NOT EXISTS(SELECT 1
1993 FROM po_line_types_b plt
1994 WHERE p_line_type_id_tbl(i) = plt.line_type_id
1995 AND p_unit_price_tbl(i) = plt.unit_price);
1996
1997 IF (SQL%ROWCOUNT > 0) THEN
1998 x_result_type := po_validations.c_result_type_failure;
1999 END IF;
2000
2001 -- If order_type_lookup_code is 'FIXED PRICE', unit_price has to be null
2002 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2003 IF p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND p_unit_price_tbl(i) IS NOT NULL THEN
2004 x_results.add_result(p_entity_type => c_entity_type_line,
2005 p_entity_id => p_id_tbl(i),
2006 p_column_name => 'UNIT_PRICE',
2007 p_column_val => p_unit_price_tbl(i),
2008 p_message_name => 'PO_PDOI_SVC_NO_PRICE',
2009 p_validation_id => PO_VAL_CONSTANTS.c_unit_price_null);
2010 x_result_type := po_validations.c_result_type_failure;
2011 END IF;
2012 END LOOP;
2013
2014 IF po_log.d_proc THEN
2015 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2016 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2017 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2018 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2019 END IF;
2020 EXCEPTION
2021 WHEN OTHERS THEN
2022 IF po_log.d_exc THEN
2023 po_log.exc(d_mod, 0, NULL);
2024 END IF;
2025
2026 RAISE;
2027 END unit_price;
2028
2029 -------------------------------------------------------------------------
2030 -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', quantity cannot be less than zero
2031 -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE', quantity has to be null.
2032 -------------------------------------------------------------------------
2033 PROCEDURE quantity(
2034 p_id_tbl IN po_tbl_number,
2035 p_quantity_tbl IN po_tbl_number,
2036 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2037 x_results IN OUT NOCOPY po_validation_results_type,
2038 x_result_type OUT NOCOPY VARCHAR2)
2039 IS
2040 d_mod CONSTANT VARCHAR2(100) := d_quantity;
2041 BEGIN
2042 IF (x_results IS NULL) THEN
2043 x_results := po_validation_results_type.new_instance();
2044 END IF;
2045
2046 IF po_log.d_proc THEN
2047 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2048 po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
2049 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2050 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2051 END IF;
2052
2053 x_result_type := po_validations.c_result_type_success;
2054
2055 -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', quantity cannot
2056 -- be less than zero
2057 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2058 IF (p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
2059 AND p_quantity_tbl(i) IS NOT NULL
2060 AND p_quantity_tbl(i) < 0) THEN
2061 x_results.add_result(p_entity_type => c_entity_type_line,
2062 p_entity_id => p_id_tbl(i),
2063 p_column_name => 'QUANTITY',
2064 p_column_val => p_quantity_tbl(i),
2065 p_message_name => 'PO_PDOI_LT_ZERO',
2066 p_token1_name => 'COLUMN_NAME',
2067 p_token1_value => 'QUANTITY',
2068 p_token2_name => 'VALUE',
2069 p_token2_value => p_quantity_tbl(i),
2070 p_validation_id => PO_VAL_CONSTANTS.c_quantity_ge_zero);
2071 x_result_type := po_validations.c_result_type_failure;
2072 -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE', quantity
2073 -- must be null
2074 ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE') AND
2075 p_quantity_tbl(i) IS NOT NULL THEN
2076 x_results.add_result(p_entity_type => c_entity_type_line,
2077 p_entity_id => p_id_tbl(i),
2078 p_column_name => 'QUANTITY',
2079 p_column_val => p_quantity_tbl(i),
2080 p_message_name => 'PO_SVC_NO_QTY',
2081 p_validation_id => PO_VAL_CONSTANTS.c_quantity_null);
2082 x_result_type := po_validations.c_result_type_failure;
2083 END IF;
2084 END LOOP;
2085
2086 IF po_log.d_proc THEN
2087 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2088 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2089 END IF;
2090 EXCEPTION
2091 WHEN OTHERS THEN
2092 IF po_log.d_exc THEN
2093 po_log.exc(d_mod, 0, NULL);
2094 END IF;
2095
2096 RAISE;
2097 END quantity;
2098
2099 -------------------------------------------------------------------------
2100 -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', amount has to be null
2101 -------------------------------------------------------------------------
2102 PROCEDURE amount(
2103 p_id_tbl IN po_tbl_number,
2104 p_amount_tbl IN po_tbl_number,
2105 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2106 x_results IN OUT NOCOPY po_validation_results_type,
2107 x_result_type OUT NOCOPY VARCHAR2)
2108 IS
2109 d_mod CONSTANT VARCHAR2(100) := d_amount;
2110 BEGIN
2111 IF (x_results IS NULL) THEN
2112 x_results := po_validation_results_type.new_instance();
2113 END IF;
2114
2115 IF po_log.d_proc THEN
2116 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2117 po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
2118 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2119 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2120 END IF;
2121
2122 x_result_type := po_validations.c_result_type_success;
2123
2124 -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', amount has to
2125 -- be null
2126 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2127 IF (p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
2128 AND p_amount_tbl(i) IS NOT NULL) THEN
2129 x_results.add_result(p_entity_type => c_entity_type_line,
2130 p_entity_id => p_id_tbl(i),
2131 p_column_name => 'AMOUNT',
2132 p_column_val => p_amount_tbl(i),
2133 p_message_name => 'PO_SVC_NO_AMT',
2134 p_validation_id => PO_VAL_CONSTANTS.c_amount_null);
2135 x_result_type := po_validations.c_result_type_failure;
2136 END IF;
2137 END LOOP;
2138
2139 IF po_log.d_proc THEN
2140 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2141 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2142 END IF;
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 IF po_log.d_exc THEN
2146 po_log.exc(d_mod, 0, NULL);
2147 END IF;
2148
2149 RAISE;
2150 END amount;
2151
2152 -------------------------------------------------------------------------
2153 -- For rate based temp labor line, the currency rate_type cannot be 'user'
2154 -------------------------------------------------------------------------
2155 PROCEDURE rate_type(
2156 p_id_tbl IN po_tbl_number,
2157 p_rate_type_tbl IN po_tbl_varchar30,
2158 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2159 x_results IN OUT NOCOPY po_validation_results_type,
2160 x_result_type OUT NOCOPY VARCHAR2)
2161 IS
2162 d_mod CONSTANT VARCHAR2(100) := d_rate_type;
2163 BEGIN
2164 IF (x_results IS NULL) THEN
2165 x_results := po_validation_results_type.new_instance();
2166 END IF;
2167
2168 IF po_log.d_proc THEN
2169 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2170 po_log.proc_begin(d_mod, 'p_rate_type_tbl', p_rate_type_tbl);
2171 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2172 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2173 END IF;
2174
2175 x_result_type := po_validations.c_result_type_success;
2176
2177 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2178 IF (p_order_type_lookup_code_tbl(i) = 'RATE' AND
2179 p_rate_type_tbl(i) = 'User') THEN
2180 x_results.add_result(p_entity_type => c_entity_type_line,
2181 p_entity_id => p_id_tbl(i),
2182 p_column_name => 'RATE_TYPE',
2183 p_column_val => p_rate_type_tbl(i),
2184 p_message_name => 'PO_PDOI_SVC_RATE_TYPE_NO_USR',
2185 p_validation_id => PO_VAL_CONSTANTS.c_rate_type_no_usr);
2186 x_result_type := po_validations.c_result_type_failure;
2187 END IF;
2188 END LOOP;
2189
2190 IF po_log.d_proc THEN
2191 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2192 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2193 END IF;
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 IF po_log.d_exc THEN
2197 po_log.exc(d_mod, 0, NULL);
2198 END IF;
2199
2200 RAISE;
2201 END rate_type;
2202
2203 -------------------------------------------------------------------------
2204 -- Line num must be populated and cannot be <= 0.
2205 -- Line num has to be unique in a requisition.
2206 -------------------------------------------------------------------------
2207 PROCEDURE line_num(
2208 p_id_tbl IN po_tbl_number,
2209 p_po_header_id_tbl IN po_tbl_number,
2210 p_line_num_tbl IN po_tbl_number,
2211 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2212 p_draft_id_tbl IN PO_TBL_NUMBER, -- bug5129752
2213 x_result_set_id IN OUT NOCOPY NUMBER,
2214 x_results IN OUT NOCOPY po_validation_results_type,
2215 x_result_type OUT NOCOPY VARCHAR2)
2216 IS
2217 d_mod CONSTANT VARCHAR2(100) := d_line_num;
2218 BEGIN
2219 IF (x_results IS NULL) THEN
2220 x_results := po_validation_results_type.new_instance();
2221 END IF;
2222
2223 IF x_result_set_id IS NULL THEN
2224 x_result_set_id := po_validations.next_result_set_id();
2225 END IF;
2226
2227 IF po_log.d_proc THEN
2228 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2229 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
2230 po_log.proc_begin(d_mod, 'p_line_num_tbl', p_line_num_tbl);
2231 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2232 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2233 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2234 END IF;
2235
2236 x_result_type := po_validations.c_result_type_success;
2237
2238 -- Line num must be populated and cannot be <= 0.
2239 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2240 IF (p_line_num_tbl(i) IS NULL) THEN
2241 x_results.add_result(p_entity_type => c_entity_type_line,
2242 p_entity_id => p_id_tbl(i),
2243 p_column_name => 'LINE_NUM',
2244 p_column_val => p_line_num_tbl(i),
2245 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
2246 p_token1_name => 'COLUMN_NAME',
2247 p_token1_value => 'LINE_NUM',
2248 p_validation_id => PO_VAL_CONSTANTS.c_line_num_not_null);
2249 x_result_type := po_validations.c_result_type_failure;
2250 ELSIF p_line_num_tbl(i) <= 0 THEN
2251 x_results.add_result(p_entity_type => c_entity_type_line,
2252 p_entity_id => p_id_tbl(i),
2253 p_column_name => 'LINE_NUM',
2254 p_column_val => p_line_num_tbl(i),
2255 p_message_name => 'PO_PDOI_LT_ZERO',
2256 p_token1_name => 'COLUMN_NAME',
2257 p_token1_value => 'LINE_NUM',
2258 p_token2_name => 'VALUE',
2259 p_token2_value => p_line_num_tbl(i),
2260 p_validation_id => PO_VAL_CONSTANTS.c_line_num_gt_zero);
2261 x_result_type := po_validations.c_result_type_failure;
2262 END IF;
2263 END LOOP;
2264
2265
2266 -- bug 5129752
2267 -- Log error if there's line number duplicate in draft table as well
2268
2269 -- check for uniqueness
2270 FORALL i IN 1 .. p_id_tbl.COUNT
2271 INSERT INTO po_validation_results_gt
2272 (result_set_id,
2273 result_type,
2274 entity_type,
2275 entity_id,
2276 message_name,
2277 column_name,
2278 column_val,
2279 token1_name,
2280 token1_value,
2281 validation_id)
2282 SELECT x_result_set_id,
2283 po_validations.c_result_type_failure,
2284 c_entity_type_line,
2285 p_id_tbl(i),
2286 'PO_PDOI_LINE_NUM_UNIQUE',
2287 'LINE_NUM',
2288 p_line_num_tbl(i),
2289 'VALUE',
2290 p_line_num_tbl(i),
2291 PO_VAL_CONSTANTS.c_line_num_unique
2292 FROM DUAL
2293 WHERE p_po_header_id_tbl(i) IS NOT NULL
2294 AND p_line_num_tbl(i) IS NOT NULL
2295 AND (EXISTS(SELECT 'Y'
2296 FROM po_lines_all pln
2297 WHERE pln.po_header_id = p_po_header_id_tbl(i)
2298 AND pln.line_num = p_line_num_tbl(i)
2299 AND NOT EXISTS (SELECT 'Y'
2300 FROM po_lines_draft_all PLD
2301 WHERE PLN.po_line_id = PLD.po_line_id
2302 AND PLD.draft_id = p_draft_id_tbl(i)))
2303 OR
2304 EXISTS (SELECT 'Y'
2305 FROM po_lines_draft_all PLD
2306 WHERE PLD.draft_id = p_draft_id_tbl(i)
2307 AND PLD.po_header_id = p_po_header_id_tbl(i)
2308 AND PLD.line_num = p_line_num_tbl(i)
2309 AND NVL(PLD.delete_flag, 'N') <> 'Y'));
2310
2311 IF (SQL%ROWCOUNT > 0) THEN
2312 x_result_type := po_validations.c_result_type_failure;
2313 END IF;
2314
2315 IF po_log.d_proc THEN
2316 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2317 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2318 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2319 END IF;
2320 EXCEPTION
2321 WHEN OTHERS THEN
2322 IF po_log.d_exc THEN
2323 po_log.exc(d_mod, 0, NULL);
2324 END IF;
2325
2326 RAISE;
2327 END line_num;
2328
2329 -------------------------------------------------------------------------
2330 -- Po_line_id must be populated and unique.
2331 -------------------------------------------------------------------------
2332 PROCEDURE po_line_id(
2333 p_id_tbl IN po_tbl_number,
2334 p_po_line_id_tbl IN po_tbl_number,
2335 p_po_header_id_tbl IN po_tbl_number,
2336 x_result_set_id IN OUT NOCOPY NUMBER,
2337 x_result_type OUT NOCOPY VARCHAR2)
2338 IS
2339 d_mod CONSTANT VARCHAR2(100) := d_po_line_id;
2340 BEGIN
2341 IF x_result_set_id IS NULL THEN
2342 x_result_set_id := po_validations.next_result_set_id();
2343 END IF;
2344
2345 IF po_log.d_proc THEN
2346 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2347 po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
2348 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
2349 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2350 END IF;
2351
2352 x_result_type := po_validations.c_result_type_success;
2353 FORALL i IN 1 .. p_id_tbl.COUNT
2354 INSERT INTO po_validation_results_gt
2355 (result_set_id,
2356 result_type,
2357 entity_type,
2358 entity_id,
2359 message_name,
2360 column_name,
2361 column_val,
2362 token1_name,
2363 token1_value,
2364 token2_name,
2365 token2_value,
2366 validation_id)
2367 SELECT x_result_set_id,
2368 po_validations.c_result_type_failure,
2369 c_entity_type_line,
2370 p_id_tbl(i),
2371 DECODE(p_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_LINE_ID_UNIQUE'),
2372 'PO_LINE_ID',
2373 p_po_line_id_tbl(i),
2374 'COLUMN',
2375 'PO_LINE_ID',
2376 'VALUE',
2377 p_po_line_id_tbl(i),
2378 DECODE(p_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_line_id_not_null,
2379 PO_VAL_CONSTANTS.c_po_line_id_unique)
2380 FROM DUAL
2381 WHERE p_po_line_id_tbl(i) IS NULL
2382 OR ( p_po_header_id_tbl(i) IS NOT NULL
2383 AND EXISTS(SELECT 1
2384 FROM po_lines pln
2385 WHERE pln.po_header_id = p_po_header_id_tbl(i)
2386 AND pln.po_line_id = p_po_line_id_tbl(i)));
2387
2388 IF (SQL%ROWCOUNT > 0) THEN
2389 x_result_type := po_validations.c_result_type_failure;
2390 END IF;
2391
2392 IF po_log.d_proc THEN
2393 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2394 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2395 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2396 END IF;
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 IF po_log.d_exc THEN
2400 po_log.exc(d_mod, 0, NULL);
2401 END IF;
2402
2403 RAISE;
2404 END po_line_id;
2405
2406 -------------------------------------------------------------------------
2407 -- Line type id must be populated and exist in po_line_types_val_v
2408 -------------------------------------------------------------------------
2409 PROCEDURE line_type_id(
2410 p_id_tbl IN po_tbl_number,
2411 p_line_type_id_tbl IN po_tbl_number,
2412 x_result_set_id IN OUT NOCOPY NUMBER,
2413 x_result_type OUT NOCOPY VARCHAR2)
2414 IS
2415 d_mod CONSTANT VARCHAR2(100) := d_line_type_id;
2416 BEGIN
2417 IF x_result_set_id IS NULL THEN
2418 x_result_set_id := po_validations.next_result_set_id();
2419 END IF;
2420
2421 IF po_log.d_proc THEN
2422 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2423 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2424 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2425 END IF;
2426
2427 x_result_type := po_validations.c_result_type_success;
2428 FORALL i IN 1 .. p_id_tbl.COUNT
2429 INSERT INTO po_validation_results_gt
2430 (result_set_id,
2431 result_type,
2432 entity_type,
2433 entity_id,
2434 message_name,
2435 column_name,
2436 column_val,
2437 token1_name,
2438 token1_value,
2439 token2_name,
2440 token2_value,
2441 validation_id)
2442 SELECT x_result_set_id,
2443 po_validations.c_result_type_failure,
2444 c_entity_type_line,
2445 p_id_tbl(i),
2446 DECODE(p_line_type_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_LINE_TYPE_ID'),
2447 'LINE_TYPE_ID',
2448 p_line_type_id_tbl(i),
2449 'COLUMN_NAME',
2450 'LINE_TYPE_ID',
2451 'VALUE',
2452 p_line_type_id_tbl(i),
2453 DECODE(p_line_type_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_line_type_id_not_null,
2454 PO_VAL_CONSTANTS.c_line_type_id_valid)
2455 FROM DUAL
2456 WHERE p_line_type_id_tbl(i) IS NULL OR
2457 NOT EXISTS(SELECT 1
2458 FROM po_line_types_val_v pltv
2459 WHERE pltv.line_type_id = p_line_type_id_tbl(i));
2460
2461 IF (SQL%ROWCOUNT > 0) THEN
2462 x_result_type := po_validations.c_result_type_failure;
2463 END IF;
2464
2465 IF po_log.d_proc THEN
2466 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2467 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2468 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2469 END IF;
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 IF po_log.d_exc THEN
2473 po_log.exc(d_mod, 0, NULL);
2474 END IF;
2475
2476 RAISE;
2477 END line_type_id;
2478
2479 -------------------------------------------------------------------------
2480 -- Validate style_id related information.
2481 -------------------------------------------------------------------------
2482 PROCEDURE style_related_info(
2483 p_id_tbl IN po_tbl_number,
2484 p_style_id_tbl IN po_tbl_number,
2485 p_line_type_id_tbl IN po_tbl_number,
2486 p_purchase_basis_tbl IN po_tbl_varchar30,
2487 x_result_set_id IN OUT NOCOPY NUMBER,
2488 x_result_type OUT NOCOPY VARCHAR2)
2489 IS
2490 d_mod CONSTANT VARCHAR2(100) := d_style_related_info;
2491 BEGIN
2492 IF x_result_set_id IS NULL THEN
2493 x_result_set_id := po_validations.next_result_set_id();
2494 END IF;
2495
2496 IF po_log.d_proc THEN
2497 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2498 po_log.proc_begin(d_mod, 'p_style_id_tbl', p_style_id_tbl);
2499 po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2500 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2501 END IF;
2502
2503 x_result_type := po_validations.c_result_type_success;
2504
2505 -- validate line_type_id is valid for the given style
2506 FORALL i IN 1 .. p_id_tbl.COUNT
2507 INSERT INTO po_validation_results_gt
2508 (result_set_id,
2509 result_type,
2510 entity_type,
2511 entity_id,
2512 message_name,
2513 column_name,
2514 column_val,
2515 token1_name,
2516 token1_value,
2517 token2_name,
2518 token2_value,
2519 validation_id)
2520 SELECT x_result_set_id,
2521 po_validations.c_result_type_failure,
2522 c_entity_type_line,
2523 p_id_tbl(i),
2524 'PO_PDOI_LINE_TYPE_ID_STYLE',
2525 'LINE_TYPE_ID',
2526 p_line_type_id_tbl(i),
2527 'STYLE_ID',
2528 p_style_id_tbl(i),
2529 'LINE_TYPE_ID',
2530 p_line_type_id_tbl(i),
2531 PO_VAL_CONSTANTS.c_line_style_on_line_type
2532 FROM po_doc_style_headers pdsh
2533 WHERE p_style_id_tbl(i) IS NOT NULL AND
2534 pdsh.style_id = p_style_id_tbl(i) AND
2535 pdsh.line_type_allowed = 'SPECIFIED' AND
2536 NOT EXISTS(SELECT 1
2537 FROM po_doc_style_values pdv
2538 WHERE pdv.style_id = pdsh.style_id
2539 AND pdv.style_attribute_name = 'LINE_TYPES'
2540 AND pdv.style_allowed_value = to_char(p_line_type_id_tbl(i))
2541 AND nvl(pdv.enabled_flag, 'N') = 'Y');
2542
2543 IF (SQL%ROWCOUNT > 0) THEN
2544 x_result_type := po_validations.c_result_type_failure;
2545 END IF;
2546
2547 -- validate the purchase_basis is valid for the given style
2548 FORALL i IN 1 .. p_id_tbl.COUNT
2549 INSERT INTO po_validation_results_gt
2550 (result_set_id,
2551 result_type,
2552 entity_type,
2553 entity_id,
2554 message_name,
2555 column_name,
2556 column_val,
2557 token1_name,
2558 token1_value,
2559 token2_name,
2560 token2_value,
2561 validation_id)
2562 SELECT x_result_set_id,
2563 po_validations.c_result_type_failure,
2564 c_entity_type_line,
2565 p_id_tbl(i),
2566 'PO_PDOI_PURCHASE_BASIS_STYLE',
2567 'PURCHASE_BASIS',
2568 p_purchase_basis_tbl(i),
2569 'STYLE_ID',
2570 p_style_id_tbl(i),
2571 'PURCHASE_BASIS',
2572 p_purchase_basis_tbl(i),
2573 PO_VAL_CONSTANTS.c_line_style_on_purchase_basis
2574 FROM DUAL
2575 WHERE NOT EXISTS(SELECT 1
2576 FROM po_doc_style_values pdsv
2577 WHERE pdsv.style_id = p_style_id_tbl(i)
2578 AND pdsv.style_attribute_name = 'PURCHASE_BASES'
2579 AND pdsv.style_allowed_value = p_purchase_basis_tbl(i)
2580 AND nvl(pdsv.enabled_flag, 'N') = 'Y');
2581
2582 IF (SQL%ROWCOUNT > 0) THEN
2583 x_result_type := po_validations.c_result_type_failure;
2584 END IF;
2585
2586 IF po_log.d_proc THEN
2587 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2588 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2589 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2590 END IF;
2591 EXCEPTION
2592 WHEN OTHERS THEN
2593 IF po_log.d_exc THEN
2594 po_log.exc(d_mod, 0, NULL);
2595 END IF;
2596
2597 RAISE;
2598
2599 END style_related_info;
2600
2601 -------------------------------------------------------------------------
2602 -- If price_type_lookup_code is not null, it has to be a valid price type in po_lookup_codes
2603 -------------------------------------------------------------------------
2604 PROCEDURE price_type_lookup_code(
2605 p_id_tbl IN po_tbl_number,
2606 p_price_type_lookup_code_tbl IN po_tbl_varchar30,
2607 x_result_set_id IN OUT NOCOPY NUMBER,
2608 x_result_type OUT NOCOPY VARCHAR2)
2609 IS
2610 d_mod CONSTANT VARCHAR2(100) := d_price_type_lookup_code;
2611 BEGIN
2612 IF x_result_set_id IS NULL THEN
2613 x_result_set_id := po_validations.next_result_set_id();
2614 END IF;
2615
2616 IF po_log.d_proc THEN
2617 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2618 po_log.proc_begin(d_mod, 'p_price_type_lookup_code_tbl', p_price_type_lookup_code_tbl);
2619 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2620 END IF;
2621
2622 x_result_type := po_validations.c_result_type_success;
2623 FORALL i IN 1 .. p_id_tbl.COUNT
2624 INSERT INTO po_validation_results_gt
2625 (result_set_id,
2626 result_type,
2627 entity_type,
2628 entity_id,
2629 message_name,
2630 column_name,
2631 column_val,
2632 token1_name,
2633 token1_value,
2634 validation_id)
2635 SELECT x_result_set_id,
2636 po_validations.c_result_type_failure,
2637 c_entity_type_line,
2638 p_id_tbl(i),
2639 'PO_PDOI_INVALID_PRICE_TYPE',
2640 'PRICE_TYPE_LOOKUP_CODE',
2641 p_price_type_lookup_code_tbl(i),
2642 'VALUE',
2643 p_price_type_lookup_code_tbl(i),
2644 PO_VAL_CONSTANTS.c_price_type_lookup_code
2645 FROM DUAL
2646 WHERE p_price_type_lookup_code_tbl(i) IS NOT NULL
2647 AND NOT EXISTS(
2648 SELECT 'Y'
2649 FROM po_lookup_codes plc
2650 WHERE plc.lookup_type = 'PRICE TYPE'
2651 AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
2652 AND plc.lookup_code = p_price_type_lookup_code_tbl(i));
2653
2654 IF (SQL%ROWCOUNT > 0) THEN
2655 x_result_type := po_validations.c_result_type_failure;
2656 END IF;
2657
2658 IF po_log.d_proc THEN
2659 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2660 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2661 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2662 END IF;
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 IF po_log.d_exc THEN
2666 po_log.exc(d_mod, 0, NULL);
2667 END IF;
2668
2669 RAISE;
2670 END price_type_lookup_code;
2671
2672 -------------------------------------------------------------------------
2673 --Start date is required for Standard PO with purchase basis 'TEMP LABOR'
2674 --Expiration date if provided should be later than the start date
2675 --If purchase basis is not 'TEMP LABOR', start_date and expiration_date have to be null
2676 -------------------------------------------------------------------------
2677 PROCEDURE start_date_standard(
2678 p_id_tbl IN po_tbl_number,
2679 p_start_date_tbl IN po_tbl_date,
2680 p_expiration_date_tbl IN po_tbl_date,
2681 p_purchase_basis_tbl IN po_tbl_varchar30,
2682 x_results IN OUT NOCOPY po_validation_results_type,
2683 x_result_type OUT NOCOPY VARCHAR2)
2684 IS
2685 d_mod CONSTANT VARCHAR2(100) := d_start_date_standard;
2686 BEGIN
2687 IF (x_results IS NULL) THEN
2688 x_results := po_validation_results_type.new_instance();
2689 END IF;
2690
2691 IF po_log.d_proc THEN
2692 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2693 po_log.proc_begin(d_mod, 'p_start_date_tbl', p_start_date_tbl);
2694 po_log.proc_begin(d_mod, 'p_expiration_date_tbl', p_expiration_date_tbl);
2695 po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
2696 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2697 END IF;
2698
2699 x_result_type := po_validations.c_result_type_success;
2700
2701 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2702 IF (p_purchase_basis_tbl(i) = 'TEMP LABOR') THEN
2703 IF (p_start_date_tbl(i) IS NULL) THEN
2704 -- --Start date is required for Standard PO with purchase basis 'TEMP LABOR'
2705 x_results.add_result(p_entity_type => c_entity_type_line,
2706 p_entity_id => p_id_tbl(i),
2707 p_column_name => 'START_DATE',
2708 p_column_val => p_start_date_tbl(i),
2709 p_message_name => 'PO_PDOI_SVC_MUST_START_DATE');
2710 x_result_type := po_validations.c_result_type_failure;
2711 ELSIF(NVL(p_expiration_date_tbl(i), p_start_date_tbl(i)) < p_start_date_tbl(i)) THEN
2712 -- If expiration date provided, it must be later than the start date
2713 x_results.add_result(p_entity_type => c_entity_type_line,
2714 p_entity_id => p_id_tbl(i),
2715 p_column_name => 'START_DATE',
2716 p_column_val => p_start_date_tbl(i),
2717 p_message_name => 'PO_SVC_NO_START_END_DATE');
2718 x_result_type := po_validations.c_result_type_failure;
2719 END IF;
2720 ELSE
2721 -- purchase basis is not 'TEMP LABOR'
2722 IF (p_start_date_tbl(i) IS NOT NULL) THEN
2723 -- start date must be null
2724 x_results.add_result(p_entity_type => c_entity_type_line,
2725 p_entity_id => p_id_tbl(i),
2726 p_column_name => 'START_DATE',
2727 p_column_val => p_start_date_tbl(i),
2728 p_message_name => 'PO_SVC_NO_START_END_DATE');
2729 x_result_type := po_validations.c_result_type_failure;
2730 END IF;
2731
2732 IF (p_start_date_tbl(i) IS NOT NULL) THEN
2733 -- expiration date must be null
2734 x_results.add_result(p_entity_type => c_entity_type_line,
2735 p_entity_id => p_id_tbl(i),
2736 p_column_name => 'EXPIRATION_DATE',
2737 p_column_val => p_expiration_date_tbl(i),
2738 p_message_name => 'PO_SVC_NO_START_END_DATE');
2739 x_result_type := po_validations.c_result_type_failure;
2740 END IF;
2741 END IF;
2742 END LOOP;
2743
2744 IF po_log.d_proc THEN
2745 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2746 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2747 END IF;
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 IF po_log.d_exc THEN
2751 po_log.exc(d_mod, 0, NULL);
2752 END IF;
2753
2754 RAISE;
2755 END start_date_standard;
2756
2757 -------------------------------------------------------------------------
2758 -- If order_type_lookup_code is not 'RATE' or 'FIXED PRICE', and item_id is not null,
2759 -- then bom_item_type cannot be 1 or 2.
2760 -------------------------------------------------------------------------
2761 PROCEDURE item_id_standard(
2762 p_id_tbl IN po_tbl_number,
2763 p_item_id_tbl IN po_tbl_number,
2764 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2765 p_inventory_org_id IN NUMBER,
2766 x_result_set_id IN OUT NOCOPY NUMBER,
2767 x_result_type OUT NOCOPY VARCHAR2)
2768 IS
2769 d_mod CONSTANT VARCHAR2(100) := d_item_id_standard;
2770 BEGIN
2771 IF x_result_set_id IS NULL THEN
2772 x_result_set_id := po_validations.next_result_set_id();
2773 END IF;
2774
2775 IF po_log.d_proc THEN
2776 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2777 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
2778 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2779 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
2780 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2781 END IF;
2782
2783 x_result_type := po_validations.c_result_type_success;
2784 FORALL i IN 1 .. p_id_tbl.COUNT
2785 INSERT INTO po_validation_results_gt
2786 (result_set_id,
2787 result_type,
2788 entity_type,
2789 entity_id,
2790 message_name,
2791 column_name,
2792 column_val,
2793 token1_name,
2794 token1_value)
2795 SELECT x_result_set_id,
2796 po_validations.c_result_type_failure,
2797 c_entity_type_line,
2798 p_id_tbl(i),
2799 'PO_ATO_ITEM_NA',
2800 'ITEM_ID',
2801 p_item_id_tbl(i),
2802 'ITEM_ID',
2803 p_item_id_tbl(i)
2804 FROM DUAL
2805 WHERE p_item_id_tbl(i) IS NOT NULL
2806 AND p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
2807 AND EXISTS(
2808 SELECT 1
2809 FROM mtl_system_items msi
2810 WHERE msi.inventory_item_id = p_item_id_tbl(i)
2811 AND msi.organization_id = p_inventory_org_id
2812 AND msi.bom_item_type IN(1, 2));
2813
2814 IF (SQL%ROWCOUNT > 0) THEN
2815 x_result_type := po_validations.c_result_type_failure;
2816 END IF;
2817
2818 IF po_log.d_proc THEN
2819 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2820 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2821 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2822 END IF;
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 IF po_log.d_exc THEN
2826 po_log.exc(d_mod, 0, NULL);
2827 END IF;
2828
2829 RAISE;
2830 END item_id_standard;
2831
2832 -------------------------------------------------------------------------
2833 -- Quantity cannot be zero for SPO
2834 -------------------------------------------------------------------------
2835 PROCEDURE quantity_standard(
2836 p_id_tbl IN po_tbl_number,
2837 p_quantity_tbl IN po_tbl_number,
2838 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2839 x_results IN OUT NOCOPY po_validation_results_type,
2840 x_result_type OUT NOCOPY VARCHAR2)
2841 IS
2842 d_mod CONSTANT VARCHAR2(100) := d_quantity_standard;
2843 BEGIN
2844 IF (x_results IS NULL) THEN
2845 x_results := po_validation_results_type.new_instance();
2846 END IF;
2847
2848 IF po_log.d_proc THEN
2849 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2850 po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
2851 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2852 END IF;
2853
2854 x_result_type := po_validations.c_result_type_success;
2855
2856 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2857 IF (p_quantity_tbl(i) IS NOT NULL AND p_quantity_tbl(i) = 0) THEN
2858 x_results.add_result(p_entity_type => c_entity_type_line,
2859 p_entity_id => p_id_tbl(i),
2860 p_column_name => 'QUANTITY',
2861 p_column_val => p_quantity_tbl(i),
2862 p_message_name => 'PO_PDOI_ZERO_QTY');
2863 x_result_type := po_validations.c_result_type_failure;
2864 ELSIF (p_quantity_tbl(i) IS NULL AND
2865 p_order_type_lookup_code_tbl(i) IN ('QUANTITY', 'AMOUNT')) THEN
2866 x_results.add_result(p_entity_type => c_entity_type_line,
2867 p_entity_id => p_id_tbl(i),
2868 p_column_name => 'QUANTITY',
2869 p_column_val => p_quantity_tbl(i),
2870 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL');
2871 x_result_type := po_validations.c_result_type_failure;
2872 END IF;
2873 END LOOP;
2874
2875 IF po_log.d_proc THEN
2876 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2877 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2878 END IF;
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 IF po_log.d_exc THEN
2882 po_log.exc(d_mod, 0, NULL);
2883 END IF;
2884
2885 RAISE;
2886 END quantity_standard;
2887
2888 -------------------------------------------------------------------------
2889 -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE', amount cannot be null;
2890 -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE' and amount is not
2891 -- empty, amount value must be greater than zero
2892 -------------------------------------------------------------------------
2893 PROCEDURE amount_standard(
2894 p_id_tbl IN po_tbl_number,
2895 p_amount_tbl IN po_tbl_number,
2896 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2897 x_results IN OUT NOCOPY po_validation_results_type,
2898 x_result_type OUT NOCOPY VARCHAR2)
2899 IS
2900 d_mod CONSTANT VARCHAR2(100) := d_amount_standard;
2901 BEGIN
2902 IF (x_results IS NULL) THEN
2903 x_results := po_validation_results_type.new_instance();
2904 END IF;
2905
2906 IF po_log.d_proc THEN
2907 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2908 po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
2909 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2910 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2911 END IF;
2912
2913 x_result_type := po_validations.c_result_type_success;
2914
2915 FOR i IN 1 .. p_id_tbl.COUNT LOOP
2916 IF (p_order_type_lookup_code_tbl(i) IN('FIXED PRICE', 'RATE') AND p_amount_tbl(i) IS NULL) THEN
2917 x_results.add_result(p_entity_type => c_entity_type_line,
2918 p_entity_id => p_id_tbl(i),
2919 p_column_name => 'AMOUNT',
2920 p_column_val => p_amount_tbl(i),
2921 p_message_name => 'PO_PDOI_SVC_MUST_AMT');
2922 x_result_type := po_validations.c_result_type_failure;
2923 ELSIF (p_order_type_lookup_code_tbl(i) IN ('FIXED PRICE', 'RATE')
2924 AND p_amount_tbl(i) IS NOT NULL
2925 AND p_amount_tbl(i) <= 0) THEN
2926 x_results.add_result(p_entity_type => c_entity_type_line,
2927 p_entity_id => p_id_tbl(i),
2928 p_column_name => 'AMOUNT',
2929 p_column_val => p_amount_tbl(i),
2930 p_message_name => 'PO_PDOI_LT_ZERO',
2931 p_token1_name => 'COLUMN_NAME',
2932 p_token1_value => 'AMOUNT',
2933 p_token2_name => 'VALUE',
2934 p_token2_value => p_amount_tbl(i),
2935 p_validation_id => PO_VAL_CONSTANTS.c_amount_gt_zero);
2936 x_result_type := po_validations.c_result_type_failure;
2937
2938 END IF;
2939 END LOOP;
2940
2941 IF po_log.d_proc THEN
2942 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2943 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2944 END IF;
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 IF po_log.d_exc THEN
2948 po_log.exc(d_mod, 0, NULL);
2949 END IF;
2950
2951 RAISE;
2952 END amount_standard;
2953
2954 -------------------------------------------------------------------------
2955 -- Price break lookup code should be valid
2956 -------------------------------------------------------------------------
2957 -- bug5016163 START
2958 PROCEDURE price_break_lookup_code(
2959 p_id_tbl IN po_tbl_number,
2960 p_price_break_lookup_code_tbl IN po_tbl_varchar30,
2961 p_global_agreement_flag_tbl IN po_tbl_varchar1,
2962 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
2963 p_purchase_basis_tbl IN po_tbl_varchar30,
2964 x_result_set_id IN OUT NOCOPY NUMBER,
2965 x_results IN OUT NOCOPY po_validation_results_type,
2966 x_result_type OUT NOCOPY VARCHAR2)
2967 IS
2968 d_mod CONSTANT VARCHAR2(100) := d_price_break_lookup_code;
2969 BEGIN
2970 IF x_result_set_id IS NULL THEN
2971 x_result_set_id := po_validations.next_result_set_id();
2972 END IF;
2973
2974 IF (x_results IS NULL) THEN
2975 x_results := po_validation_results_type.new_instance();
2976 END IF;
2977
2978 IF po_log.d_proc THEN
2979 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2980 po_log.proc_begin(d_mod, 'p_price_break_lookup_code_tbl', p_price_break_lookup_code_tbl);
2981 po_log.proc_begin(d_mod, 'p_global_agreement_flag_tbl', p_global_agreement_flag_tbl);
2982 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2983 END IF;
2984
2985 -- If price break lookup code is provided, it has to be a valid lookup
2986 -- code
2987 x_result_type := po_validations.c_result_type_success;
2988 FORALL i IN 1 .. p_id_tbl.COUNT
2989 INSERT INTO po_validation_results_gt
2990 (result_set_id,
2991 result_type,
2992 entity_type,
2993 entity_id,
2994 message_name,
2995 column_name,
2996 column_val,
2997 token1_name,
2998 token1_value)
2999 SELECT x_result_set_id,
3000 po_validations.c_result_type_failure,
3001 c_entity_type_line,
3002 p_id_tbl(i),
3003 'PO_PDOI_INVALID_PRICE_BREAK',
3004 'PRICE_BREAK_LOOKUP_CODE',
3005 p_price_break_lookup_code_tbl(i),
3006 'VALUE',
3007 p_price_break_lookup_code_tbl(i)
3008 FROM DUAL
3009 WHERE p_price_break_lookup_code_tbl(i) IS NOT NULL
3010 AND p_price_break_lookup_code_tbl(i) NOT IN
3011 ( SELECT lookup_code
3012 FROM po_lookup_codes PLC
3013 WHERE PLC.lookup_type = 'PRICE BREAK TYPE');
3014
3015 IF (SQL%ROWCOUNT > 0) THEN
3016 x_result_type := po_validations.c_result_type_failure;
3017 END IF;
3018
3019 -- Cumulative Pricing is not allowed for GA
3020 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3021 IF (p_price_break_lookup_code_tbl(i) = 'CUMULATIVE') THEN
3022 IF (p_global_agreement_flag_tbl(i) = 'Y') THEN
3023
3024 x_results.add_result(p_entity_type => c_entity_type_line,
3025 p_entity_id => p_id_tbl(i),
3026 p_column_name => 'PRICE_BREAK_LOOKUP_CODE',
3027 p_column_val => p_price_break_lookup_code_tbl(i),
3028 p_message_name => 'PO_PDOI_GA_PRICE_BREAK_NA'
3029 );
3030
3031 x_result_type := po_validations.c_result_type_failure;
3032 ELSIF ( p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND
3033 p_purchase_basis_tbl(i) = 'SERVICES') THEN
3034
3035 x_results.add_result(p_entity_type => c_entity_type_line,
3036 p_entity_id => p_id_tbl(i),
3037 p_column_name => 'PRICE_BREAK_LOOKUP_CODE',
3038 p_column_val => p_price_break_lookup_code_tbl(i),
3039 p_message_name => 'PO_PDOI_SVC_NO_CUMULATIVE_PB'
3040 );
3041
3042 x_result_type := po_validations.c_result_type_failure;
3043 END IF;
3044 END IF;
3045 END LOOP;
3046
3047 IF po_log.d_proc THEN
3048 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3049 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3050 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3051 END IF;
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 IF po_log.d_exc THEN
3055 po_log.exc(d_mod, 0, NULL);
3056 END IF;
3057
3058 RAISE;
3059 END price_break_lookup_code;
3060 -- bug5016163 END
3061
3062
3063 -------------------------------------------------------------------------
3064 -- If allow_price_override_flag is 'N', then not_to_exceed_price has to be null.
3065 -- If not_to_exceed_price is not null, then it cannot be less than unit_price.
3066 -------------------------------------------------------------------------
3067 PROCEDURE not_to_exceed_price(
3068 p_id_tbl IN po_tbl_number,
3069 p_not_to_exceed_price_tbl IN po_tbl_number,
3070 p_allow_price_override_tbl IN po_tbl_varchar1,
3071 p_unit_price_tbl IN po_tbl_number,
3072 x_results IN OUT NOCOPY po_validation_results_type,
3073 x_result_type OUT NOCOPY VARCHAR2)
3074 IS
3075 d_mod CONSTANT VARCHAR2(100) := d_not_to_exceed_price;
3076 BEGIN
3077 IF (x_results IS NULL) THEN
3078 x_results := po_validation_results_type.new_instance();
3079 END IF;
3080
3081 IF po_log.d_proc THEN
3082 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3083 po_log.proc_begin(d_mod, 'p_not_to_exceed_price_tbl', p_not_to_exceed_price_tbl);
3084 po_log.proc_begin(d_mod, 'p_allow_price_override_tbl', p_allow_price_override_tbl);
3085 po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3086 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3087 END IF;
3088
3089 x_result_type := po_validations.c_result_type_success;
3090
3091 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3092 IF (p_allow_price_override_tbl(i) = 'N' AND p_not_to_exceed_price_tbl(i) IS NOT NULL) THEN
3093 x_results.add_result(p_entity_type => c_entity_type_line,
3094 p_entity_id => p_id_tbl(i),
3095 p_column_name => 'NOT_TO_EXCEED_PRICE',
3096 p_column_val => p_not_to_exceed_price_tbl(i),
3097 p_message_name => 'PO_PDOI_EXCEED_PRICE_NULL',
3098 p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_null);
3099 x_result_type := po_validations.c_result_type_failure;
3100 ELSIF p_not_to_exceed_price_tbl(i) IS NOT NULL
3101 AND p_not_to_exceed_price_tbl(i) < p_unit_price_tbl(i) THEN
3102 -- If not_to_exceed_price is not null, then it cannot be less than
3103 -- unit_price
3104 x_results.add_result(p_entity_type => c_entity_type_line,
3105 p_entity_id => p_id_tbl(i),
3106 p_column_name => 'NOT_TO_EXCEED_PRICE',
3107 p_column_val => p_not_to_exceed_price_tbl(i),
3108 p_message_name => 'PO_PDOI_INVALID_PRICE',
3109 p_token1_name => 'VALUE',
3110 p_token1_value => p_not_to_exceed_price_tbl(i),
3111 p_token2_name => 'UNIT_PRICE',
3112 p_token2_value => p_unit_price_tbl(i),
3113 p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_valid);
3114 x_result_type := po_validations.c_result_type_failure;
3115 END IF;
3116 END LOOP;
3117
3118 IF po_log.d_proc THEN
3119 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3120 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3121 END IF;
3122 EXCEPTION
3123 WHEN OTHERS THEN
3124 IF po_log.d_exc THEN
3125 po_log.exc(d_mod, 0, NULL);
3126 END IF;
3127
3128 RAISE;
3129 END not_to_exceed_price;
3130
3131 -------------------------------------------------------------------------
3132 -- Validate ip_category_id is valid if not empty
3133 -------------------------------------------------------------------------
3134 PROCEDURE ip_category_id_update(
3135 p_id_tbl IN po_tbl_number,
3136 p_ip_category_id_tbl IN po_tbl_number,
3137 x_result_set_id IN OUT NOCOPY NUMBER,
3138 x_results IN OUT NOCOPY po_validation_results_type,
3139 x_result_type OUT NOCOPY VARCHAR2)
3140 IS
3141 d_mod CONSTANT VARCHAR2(100) := d_ip_category_id_update;
3142 BEGIN
3143 IF x_result_set_id IS NULL THEN
3144 x_result_set_id := po_validations.next_result_set_id();
3145 END IF;
3146
3147 IF (x_results IS NULL) THEN
3148 x_results := po_validation_results_type.new_instance();
3149 END IF;
3150
3151 IF po_log.d_proc THEN
3152 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3153 po_log.proc_begin(d_mod, 'p_ip_category_id_tbl', p_ip_category_id_tbl);
3154 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3155 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3156 END IF;
3157
3158 x_result_type := po_validations.c_result_type_success;
3159
3160 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3161 IF p_ip_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3162 x_results.add_result(p_entity_type => c_entity_type_line,
3163 p_entity_id => p_id_tbl(i),
3164 p_column_name => 'IP_CATEGORY_ID',
3165 p_column_val => p_ip_category_id_tbl(i),
3166 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3167 p_token1_name => 'COLUMN_NAME',
3168 p_token1_value => 'IP_CATEGORY_ID',
3169 p_validation_id => PO_VAL_CONSTANTS.c_ip_cat_id_update_not_null);
3170 x_result_type := po_validations.c_result_type_failure;
3171 END IF;
3172 END LOOP;
3173
3174 -- Validate if x_category_id is valid if not empty
3175 FORALL i IN 1 .. p_id_tbl.COUNT
3176 INSERT INTO po_validation_results_gt
3177 (result_set_id,
3178 result_type,
3179 entity_type,
3180 entity_id,
3181 message_name,
3182 column_name,
3183 column_val,
3184 token1_name,
3185 token1_value,
3186 validation_id)
3187 SELECT x_result_set_id,
3188 po_validations.c_result_type_failure,
3189 c_entity_type_line,
3190 p_id_tbl(i),
3191 'PO_PDOI_INVALID_IP_CATEGORY_ID',
3192 'IP_CATEGORY_ID',
3193 p_ip_category_id_tbl(i),
3194 'VALUE',
3195 p_ip_category_id_tbl(i),
3196 PO_VAL_CONSTANTS.c_ip_cat_id_update_valid
3197 FROM DUAL
3198 WHERE p_ip_category_id_tbl(i) IS NOT NULL
3199 AND p_ip_category_id_tbl(i) <> -2
3200 AND NOT EXISTS(
3201 SELECT 'Y'
3202 FROM icx_cat_categories_v
3203 WHERE rt_category_id = p_ip_category_id_tbl(i));
3204
3205 IF (SQL%ROWCOUNT > 0) THEN
3206 x_result_type := po_validations.c_result_type_failure;
3207 END IF;
3208
3209 IF po_log.d_proc THEN
3210 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3211 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3212 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3213 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3214 END IF;
3215 EXCEPTION
3216 WHEN OTHERS THEN
3217 IF po_log.d_exc THEN
3218 po_log.exc(d_mod, 0, NULL);
3219 END IF;
3220
3221 RAISE;
3222 END ip_category_id_update;
3223
3224 -----------------------------------------------------------------------------
3225 -- We need to validate UOM against po_lines_all and po_units_of_measure_val_v
3226 -----------------------------------------------------------------------------
3227 PROCEDURE uom_update(
3228 p_id_tbl IN po_tbl_number,
3229 p_unit_meas_lookup_code_tbl IN po_tbl_varchar30,
3230 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
3231 p_po_header_id_tbl IN po_tbl_number,
3232 p_po_line_id_tbl IN po_tbl_number,
3233 x_results IN OUT NOCOPY po_validation_results_type, x_result_set_id IN OUT NOCOPY NUMBER,
3234 x_result_type OUT NOCOPY VARCHAR2)
3235 IS
3236 d_mod CONSTANT VARCHAR2(100) := d_uom_update;
3237 BEGIN
3238 IF x_result_set_id IS NULL THEN
3239 x_result_set_id := po_validations.next_result_set_id();
3240 END IF;
3241
3242 IF po_log.d_proc THEN
3243 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3244 po_log.proc_begin(d_mod, 'p_unit_meas_lookup_code_tbl', p_unit_meas_lookup_code_tbl);
3245 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
3246 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
3247 po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3248 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3249 END IF;
3250
3251 x_result_type := po_validations.c_result_type_success;
3252 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3253 IF p_unit_meas_lookup_code_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3254 x_results.add_result(p_entity_type => c_entity_type_line,
3255 p_entity_id => p_id_tbl(i),
3256 p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
3257 p_column_val => p_unit_meas_lookup_code_tbl(i),
3258 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3259 p_token1_name => 'COLUMN_NAME',
3260 p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
3261 p_validation_id => PO_VAL_CONSTANTS.c_uom_update_not_null);
3262 x_result_type := po_validations.c_result_type_failure;
3263 END IF;
3264 END LOOP;
3265
3266 FORALL i IN 1 .. p_id_tbl.COUNT
3267 INSERT INTO po_validation_results_gt
3268 (result_set_id,
3269 result_type,
3270 entity_type,
3271 entity_id,
3272 message_name,
3273 column_name,
3274 column_val,
3275 token1_name,
3276 token1_value,
3277 validation_id)
3278 SELECT x_result_set_id,
3279 po_validations.c_result_type_failure,
3280 c_entity_type_line,
3281 p_id_tbl(i),
3282 'PO_PDOI_INVALID_UOM_CODE',
3283 'UNIT_MEAS_LOOKUP_CODE',
3284 p_unit_meas_lookup_code_tbl(i),
3285 'UNIT_MEAS_LOOKUP_CODE',
3286 p_unit_meas_lookup_code_tbl(i),
3287 PO_VAL_CONSTANTS.c_uom_update_valid
3288 FROM DUAL
3289 WHERE EXISTS(
3290 SELECT 1
3291 FROM po_lines_all pol,
3292 po_units_of_measure_val_v pumv
3293 WHERE po_header_id = p_po_header_id_tbl(i) AND
3294 po_line_id = p_po_line_id_tbl(i) AND
3295 p_unit_meas_lookup_code_tbl(i) IS NOT NULL AND
3296 p_unit_meas_lookup_code_tbl(i) <> NVL(pol.unit_meas_lookup_code,
3297 p_unit_meas_lookup_code_tbl(i)) AND
3298 pumv.unit_of_measure <> p_unit_meas_lookup_code_tbl(i))
3299 OR EXISTS(
3300 SELECT 1
3301 FROM po_lines_all pol,
3302 po_units_of_measure_val_v pumv
3303 WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE') AND
3304 po_header_id = p_po_header_id_tbl(i) AND
3305 po_line_id = p_po_line_id_tbl(i) AND
3306 pol.unit_meas_lookup_code IS NULL AND
3307 pumv.unit_of_measure <> p_unit_meas_lookup_code_tbl(i));
3308
3309 IF (SQL%ROWCOUNT > 0) THEN
3310 x_result_type := po_validations.c_result_type_failure;
3311 END IF;
3312
3313 IF po_log.d_proc THEN
3314 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3315 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3316 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3317 END IF;
3318 EXCEPTION
3319 WHEN OTHERS THEN
3320 IF po_log.d_exc THEN
3321 po_log.exc(d_mod, 0, NULL);
3322 END IF;
3323
3324 RAISE;
3325 END uom_update;
3326
3327 -------------------------------------------------------------------------
3328 -- Make sure that the item_description can be different from what is setup in the item master.
3329 -- Would not allow item_description update if item attribute allow_item_desc_update_flag is N.
3330 -- Also need to check the value in po_lines_all to make sure it is the same there, if necessary.
3331 -------------------------------------------------------------------------
3332 PROCEDURE item_desc_update(
3333 p_id_tbl IN po_tbl_number,
3334 p_item_description_tbl IN po_tbl_varchar2000,
3335 p_item_id_tbl IN po_tbl_number,
3336 p_inventory_org_id IN NUMBER,
3337 p_po_header_id_tbl IN po_tbl_number,
3338 p_po_line_id_tbl IN po_tbl_number,
3339 x_results IN OUT NOCOPY po_validation_results_type, x_result_set_id IN OUT NOCOPY NUMBER,
3340 x_result_type OUT NOCOPY VARCHAR2)
3341 IS
3342 d_mod CONSTANT VARCHAR2(100) := d_item_desc_update;
3343 BEGIN
3344 IF x_result_set_id IS NULL THEN
3345 x_result_set_id := po_validations.next_result_set_id();
3346 END IF;
3347
3348 IF po_log.d_proc THEN
3349 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3350 po_log.proc_begin(d_mod, 'p_item_description_tbl', p_item_description_tbl);
3351 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
3352 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
3353 po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
3354 po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3355 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3356 END IF;
3357
3358 x_result_type := po_validations.c_result_type_success;
3359 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3360 IF p_item_description_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3361 x_results.add_result(p_entity_type => c_entity_type_line,
3362 p_entity_id => p_id_tbl(i),
3363 p_column_name => 'ITEM_DESCRIPTION',
3364 p_column_val => p_item_description_tbl(i),
3365 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3366 p_token1_name => 'COLUMN_NAME',
3367 p_token1_value => 'ITEM_DESCRIPTION',
3368 p_validation_id => PO_VAL_CONSTANTS.c_item_desc_update_not_null);
3369 x_result_type := po_validations.c_result_type_failure;
3370 END IF;
3371 END LOOP;
3372
3373 /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
3374
3375
3376 FORALL i IN 1 .. p_id_tbl.COUNT
3377 INSERT INTO po_validation_results_gt
3378 (result_set_id,
3379 result_type,
3380 entity_type,
3381 entity_id,
3382 message_name,
3383 column_name,
3384 column_val,
3385 token1_name,
3386 token1_value,
3387 validation_id)
3388 SELECT x_result_set_id,
3389 po_validations.c_result_type_failure,
3390 c_entity_type_line,
3391 p_id_tbl(i),
3392 'PO_PDOI_DIFF_ITEM_DESC',
3393 'ITEM_DESCRIPTION',
3394 p_item_description_tbl(i),
3395 'ITEM_DESCRIPTION',
3396 p_item_description_tbl(i),
3397 PO_VAL_CONSTANTS.c_item_desc_update_unupdatable
3398 FROM DUAL
3399 WHERE EXISTS(
3400 SELECT 1
3401 FROM mtl_system_items msi,
3402 po_lines_all pol,
3403 mtl_system_items_tl mtl
3404 WHERE p_po_line_id_tbl(i) IS NOT NULL AND
3405 p_item_id_tbl(i) IS NOT NULL AND
3406 pol.po_header_id = nvl(p_po_header_id_tbl(i),pol.po_header_id) AND
3407 pol.po_line_id = p_po_line_id_tbl(i) AND
3408 msi.inventory_item_id = p_item_id_tbl(i) AND
3409 msi.inventory_item_id = mtl.inventory_item_id AND
3410 msi.organization_id = p_inventory_org_id AND
3411 msi.organization_id = mtl.organization_id AND
3412 msi.allow_item_desc_update_flag = 'N' AND
3413 mtl.language = USERENV('LANG') AND
3414 (p_item_description_tbl(i) <> mtl.description OR
3415 p_item_description_tbl(i) <> pol.item_description))
3416 OR EXISTS(
3417 SELECT 1
3418 FROM mtl_system_items msi,
3419 mtl_system_items_tl mtl
3420 WHERE p_po_line_id_tbl(i) IS NULL AND
3421 p_item_id_tbl(i) IS NOT NULL AND
3422 msi.inventory_item_id = p_item_id_tbl(i) AND
3423 mtl.inventory_item_id = msi.inventory_item_id AND
3424 msi.organization_id = p_inventory_org_id AND
3425 mtl.organization_id = msi.organization_id AND
3426 msi.allow_item_desc_update_flag = 'N' AND
3427 mtl.language = USERENV('LANG') AND
3428 p_item_description_tbl(i) <> mtl.description);
3429
3430 IF (SQL%ROWCOUNT > 0) THEN
3431 x_result_type := po_validations.c_result_type_failure;
3432 END IF;
3433
3434 IF po_log.d_proc THEN
3435 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3436 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3437 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3438 END IF;
3439 EXCEPTION
3440 WHEN OTHERS THEN
3441 IF po_log.d_exc THEN
3442 po_log.exc(d_mod, 0, NULL);
3443 END IF;
3444
3445 RAISE;
3446 END item_desc_update;
3447
3448 ----------------------------------------------------------------------------------------
3449 -- Called in create case for Blanket AND SPO, negotiated_by_preparer must be 'Y' or 'N'.
3450 ----------------------------------------------------------------------------------------
3451 PROCEDURE negotiated_by_preparer(
3452 p_id_tbl IN po_tbl_number,
3453 p_negotiated_by_preparer_tbl IN po_tbl_varchar1,
3454 x_results IN OUT NOCOPY po_validation_results_type,
3455 x_result_type OUT NOCOPY VARCHAR2)
3456 IS
3457 d_mod CONSTANT VARCHAR2(100) := d_negotiated_by_preparer;
3458 BEGIN
3459
3460 IF po_log.d_proc THEN
3461 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3462 po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3463 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3464 END IF;
3465
3466 x_result_type := po_validations.c_result_type_success;
3467 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3468 IF p_negotiated_by_preparer_tbl(i) NOT IN ('Y', 'N') THEN
3469 x_results.add_result(p_entity_type => c_entity_type_line,
3470 p_entity_id => p_id_tbl(i),
3471 p_column_name => 'NEGOTIATED_BY_PREPARER',
3472 p_column_val => p_negotiated_by_preparer_tbl(i),
3473 p_message_name => 'PO_PDOI_INVALID_FLAG_VALUE',
3474 p_token1_name => 'COLUMN_NAME',
3475 p_token1_value => 'NEGOTIATED_BY_PREPARER',
3476 p_token2_name => 'VALUE',
3477 p_token2_value => p_negotiated_by_preparer_tbl(i),
3478 p_validation_id => PO_VAL_CONSTANTS.c_negotiated_by_preparer);
3479 x_result_type := po_validations.c_result_type_failure;
3480 END IF;
3481 END LOOP;
3482
3483 IF po_log.d_proc THEN
3484 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3485 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3486 END IF;
3487
3488 EXCEPTION
3489 WHEN OTHERS THEN
3490 IF po_log.d_exc THEN
3491 po_log.exc(d_mod, 0, NULL);
3492 END IF;
3493
3494 RAISE;
3495 END negotiated_by_preparer;
3496
3497 --------------------------------------------------------------------------------------
3498 -- Called in update case for Blanket, negotiated_by_preparer must be NULL, 'Y' or 'N'.
3499 --------------------------------------------------------------------------------------
3500 PROCEDURE negotiated_by_prep_update(
3501 p_id_tbl IN po_tbl_number,
3502 p_negotiated_by_preparer_tbl IN po_tbl_varchar1,
3503 x_results IN OUT NOCOPY po_validation_results_type,
3504 x_result_type OUT NOCOPY VARCHAR2)
3505 IS
3506 d_mod CONSTANT VARCHAR2(100) := d_negotiated_by_prep_update;
3507 BEGIN
3508
3509 IF po_log.d_proc THEN
3510 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3511 po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3512 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3513 END IF;
3514
3515 x_result_type := po_validations.c_result_type_success;
3516 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3517 IF p_negotiated_by_preparer_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3518 x_results.add_result(p_entity_type => c_entity_type_line,
3519 p_entity_id => p_id_tbl(i),
3520 p_column_name => 'NEGOTIATED_BY_PREPARER',
3521 p_column_val => p_negotiated_by_preparer_tbl(i),
3522 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3523 p_token1_name => 'COLUMN_NAME',
3524 p_token1_value => 'NEGOTIATED_BY_PREPARER',
3525 p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_not_null);
3526 x_result_type := po_validations.c_result_type_failure;
3527 ELSIF p_negotiated_by_preparer_tbl(i) NOT IN (NULL, 'Y', 'N') THEN
3528 x_results.add_result(p_entity_type => c_entity_type_line,
3529 p_entity_id => p_id_tbl(i),
3530 p_column_name => 'NEGOTIATED_BY_PREPARER',
3531 p_column_val => p_negotiated_by_preparer_tbl(i),
3532 p_message_name => 'PO_PDOI_INVALID_FLAG_VALUE',
3533 p_token1_name => 'COLUMN_NAME',
3534 p_token1_value => 'NEGOTIATED_BY_PREPARER',
3535 p_token2_name => 'VALUE',
3536 p_token2_value => p_negotiated_by_preparer_tbl(i),
3537 p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_valid);
3538 x_result_type := po_validations.c_result_type_failure;
3539 END IF;
3540 END LOOP;
3541
3542 IF po_log.d_proc THEN
3543 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3544 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3545 END IF;
3546
3547 EXCEPTION
3548 WHEN OTHERS THEN
3549 IF po_log.d_exc THEN
3550 po_log.exc(d_mod, 0, NULL);
3551 END IF;
3552
3553 RAISE;
3554 END negotiated_by_prep_update;
3555
3556 -------------------------------------------------------------------------
3557 -- If either item_id or job_id are populated, then you are not allowed to change the po_category_id
3558 -- If change is allowed, the new category_id must be valid.
3559 -------------------------------------------------------------------------
3560 PROCEDURE category_id_update(
3561 p_id_tbl IN po_tbl_number,
3562 p_category_id_tbl IN po_tbl_number,
3563 p_po_line_id_tbl IN po_tbl_number,
3564 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
3565 p_item_id_tbl IN po_tbl_number,
3566 p_job_id_tbl IN po_tbl_number,
3567 p_inventory_org_id IN NUMBER,
3568 x_result_set_id IN OUT NOCOPY NUMBER,
3569 x_results IN OUT NOCOPY po_validation_results_type,
3570 x_result_type OUT NOCOPY VARCHAR2)
3571 IS
3572 d_mod CONSTANT VARCHAR2(100) := d_category_id_update;
3573 BEGIN
3574 IF x_result_set_id IS NULL THEN
3575 x_result_set_id := po_validations.next_result_set_id();
3576 END IF;
3577
3578 IF po_log.d_proc THEN
3579 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3580 po_log.proc_begin(d_mod, 'p_category_id_tbl', p_category_id_tbl);
3581 po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3582 po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
3583 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
3584 po_log.proc_begin(d_mod, 'p_job_id_tbl', p_job_id_tbl);
3585 po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
3586 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3587 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3588 END IF;
3589
3590 x_result_type := po_validations.c_result_type_success;
3591 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3592 IF p_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3593 x_results.add_result(p_entity_type => c_entity_type_line,
3594 p_entity_id => p_id_tbl(i),
3595 p_column_name => 'CATEGORY_ID',
3596 p_column_val => p_category_id_tbl(i),
3597 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3598 p_token1_name => 'COLUMN_NAME',
3599 p_token1_value => 'CATEGORY_ID',
3600 p_validation_id => PO_VAL_CONSTANTS.c_cat_id_update_not_null);
3601 x_result_type := po_validations.c_result_type_failure;
3602 END IF;
3603 END LOOP;
3604
3605 FORALL i IN 1 .. p_id_tbl.COUNT
3606 INSERT INTO po_validation_results_gt
3607 (result_set_id,
3608 result_type,
3609 entity_type,
3610 entity_id,
3611 message_name,
3612 column_name,
3613 column_val,
3614 validation_id)
3615 SELECT x_result_set_id,
3616 po_validations.c_result_type_failure,
3617 c_entity_type_line,
3618 p_id_tbl(i),
3619 'PO_PDOI_NO_PO_CAT_UPDATE',
3620 'CATEGORY_ID',
3621 p_category_id_tbl(i),
3622 PO_VAL_CONSTANTS.c_cat_id_update_not_updatable
3623 FROM DUAL
3624 WHERE p_category_id_tbl(i) IS NOT NULL
3625 AND (p_item_id_tbl(i) IS NOT NULL OR p_job_id_tbl(i) IS NOT NULL)
3626 AND (EXISTS(SELECT 1
3627 FROM po_lines_all pol
3628 WHERE p_po_line_id_tbl(i) = pol.po_line_id
3629 AND p_category_id_tbl(i) <> pol.category_id)
3630 OR EXISTS(SELECT 1
3631 FROM po_lines_draft_all pld
3632 WHERE p_po_line_id_tbl(i) = pld.po_line_id
3633 AND p_category_id_tbl(i) <> pld.category_id));
3634
3635 IF (SQL%ROWCOUNT > 0) THEN
3636 x_result_type := po_validations.c_result_type_failure;
3637 END IF;
3638
3639 PO_VAL_LINES2.category_id(p_id_tbl => p_id_tbl,
3640 p_category_id_tbl => p_category_id_tbl,
3641 p_order_type_lookup_code_tbl => p_order_type_lookup_code_tbl,
3642 p_item_id_tbl => p_item_id_tbl,
3643 p_inventory_org_id => p_inventory_org_id,
3644 x_result_set_id => x_result_set_id,
3645 x_results => x_results,
3646 x_result_type => x_result_type);
3647
3648 IF po_log.d_proc THEN
3649 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3650 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3651 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3652 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3653 END IF;
3654
3655 EXCEPTION
3656 WHEN OTHERS THEN
3657 IF po_log.d_exc THEN
3658 po_log.exc(d_mod, 0, NULL);
3659 END IF;
3660
3661 RAISE;
3662 END category_id_update;
3663
3664 -------------------------------------------------------------------------
3665 -- In the UPDATE case, unit_price cannot be negative. Also handle #DEL.
3666 -------------------------------------------------------------------------
3667 PROCEDURE unit_price_update
3668 ( p_id_tbl IN po_tbl_number,
3669 p_po_line_id_tbl IN po_tbl_number, -- bug5008206
3670 p_draft_id_tbl IN po_tbl_number,
3671 p_unit_price_tbl IN po_tbl_number,
3672 x_results IN OUT NOCOPY po_validation_results_type,
3673 x_result_set_id IN OUT NOCOPY NUMBER, -- bug5008206
3674 x_result_type OUT NOCOPY VARCHAR2
3675 )
3676 IS
3677 d_mod CONSTANT VARCHAR2(100) := d_unit_price_update;
3678 BEGIN
3679 IF (x_results IS NULL) THEN
3680 x_results := po_validation_results_type.new_instance();
3681 END IF;
3682
3683 IF x_result_set_id IS NULL THEN
3684 x_result_set_id := po_validations.next_result_set_id();
3685 END IF;
3686
3687 IF po_log.d_proc THEN
3688 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3689 po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3690 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3691 END IF;
3692
3693 x_result_type := po_validations.c_result_type_success;
3694
3695 FOR i IN 1 .. p_id_tbl.COUNT LOOP
3696
3697 IF p_unit_price_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3698 x_results.add_result(p_entity_type => c_entity_type_line,
3699 p_entity_id => p_id_tbl(i),
3700 p_column_name => 'UNIT_PRICE',
3701 p_column_val => p_unit_price_tbl(i),
3702 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3703 p_token1_name => 'COLUMN_NAME',
3704 p_token1_value => 'UNIT_PRICE',
3705 p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_not_null);
3706 x_result_type := po_validations.c_result_type_failure;
3707 ELSIF p_unit_price_tbl(i) IS NOT NULL AND p_unit_price_tbl(i) < 0 THEN
3708 x_results.add_result(p_entity_type => c_entity_type_line,
3709 p_entity_id => p_id_tbl(i),
3710 p_column_name => 'UNIT_PRICE',
3711 p_column_val => p_unit_price_tbl(i),
3712 p_message_name => 'PO_PDOI_LT_ZERO',
3713 p_token1_name => 'COLUMN_NAME',
3714 p_token1_value => 'UNIT_PRICE',
3715 p_token2_name => 'VALUE',
3716 p_token2_value => p_unit_price_tbl(i),
3717 p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_ge_zero);
3718 x_result_type := po_validations.c_result_type_failure;
3719
3720 END IF;
3721 END LOOP;
3722
3723 -- bug5258790
3724 -- For fixed price line update, unit price has to be NULL
3725 FORALL i IN 1..p_id_tbl.COUNT
3726 INSERT INTO po_validation_results_gt
3727 (result_set_id,
3728 result_type,
3729 entity_type,
3730 entity_id,
3731 message_name,
3732 column_name,
3733 column_val,
3734 validation_id)
3735 SELECT x_result_set_id,
3736 po_validations.c_result_type_failure,
3737 c_entity_type_line,
3738 p_id_tbl(i),
3739 'PO_PDOI_SVC_NO_PRICE',
3740 'UNIT_PRICE',
3741 p_unit_price_tbl(i),
3742 PO_VAL_CONSTANTS.c_unit_price_null
3743 FROM po_lines_all POL
3744 WHERE POL.po_line_id = p_po_line_id_tbl(i)
3745 AND POL.order_type_lookup_code = 'FIXED PRICE'
3746 AND p_unit_price_tbl(i) IS NOT NULL
3747 -- missin draft id
3748 UNION
3749 SELECT x_result_set_id,
3750 po_validations.c_result_type_failure,
3751 c_entity_type_line,
3752 p_id_tbl(i),
3753 'PO_PDOI_SVC_NO_PRICE',
3754 'UNIT_PRICE',
3755 p_unit_price_tbl(i),
3756 PO_VAL_CONSTANTS.c_unit_price_null
3757 FROM po_lines_draft_all POL
3758 WHERE POL.po_line_id = p_po_line_id_tbl(i)
3759 AND POL.draft_id = p_draft_id_tbl(i)
3760 AND POL.order_type_lookup_code = 'FIXED PRICE'
3761 AND p_unit_price_tbl(i) IS NOT NULL;
3762
3763 IF (SQL%ROWCOUNT > 0) THEN
3764 x_result_type := po_validations.c_result_type_failure;
3765 END IF;
3766
3767 -- bug5008026
3768 -- Make sure that the new price does not exceed price limit
3769 FORALL i IN 1 .. p_id_tbl.COUNT
3770 INSERT INTO po_validation_results_gt
3771 (result_set_id,
3772 result_type,
3773 entity_type,
3774 entity_id,
3775 message_name,
3776 token1_name,
3777 token1_value,
3778 token2_name,
3779 token2_value,
3780 column_name,
3781 column_val,
3782 validation_id)
3783 SELECT x_result_set_id,
3784 po_validations.c_result_type_failure,
3785 c_entity_type_line,
3786 p_id_tbl(i),
3787 'PO_PDOI_INVALID_PRICE',
3788 'VALUE',
3789 POL.not_to_exceed_price,
3790 'UNIT_PRICE',
3791 p_unit_price_tbl(i),
3792 'UNIT_PRICE',
3793 p_unit_price_tbl(i),
3794 PO_VAL_CONSTANTS.c_not_to_exceed_price_valid
3795 FROM po_lines_all POL
3796 WHERE POL.po_line_id = p_po_line_id_tbl(i)
3797 AND POL.not_to_exceed_price < p_unit_price_tbl(i);
3798
3799 IF (SQL%ROWCOUNT > 0) THEN
3800 x_result_type := po_validations.c_result_type_failure;
3801 END IF;
3802
3803 IF po_log.d_proc THEN
3804 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3805 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3806 END IF;
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 IF po_log.d_exc THEN
3810 po_log.exc(d_mod, 0, NULL);
3811 END IF;
3812
3813 RAISE;
3814 END unit_price_update;
3815
3816
3817 -- bug5258790 START
3818 -------------------------------------------------------------------------
3819 -- In the UPDATE case, amount should not be udpated if it's not 'FIXED PRICE'
3820 -------------------------------------------------------------------------
3821 PROCEDURE amount_update
3822 ( p_id_tbl IN po_tbl_number,
3823 p_po_line_id_tbl IN po_tbl_number, -- bug5008206
3824 p_draft_id_tbl IN po_tbl_number,
3825 p_amount_tbl IN po_tbl_number,
3826 x_results IN OUT NOCOPY po_validation_results_type,
3827 x_result_set_id IN OUT NOCOPY NUMBER, -- bug5008206
3828 x_result_type OUT NOCOPY VARCHAR2
3829 )
3830 IS
3831 d_mod CONSTANT VARCHAR2(100) := d_amount_update;
3832 BEGIN
3833 IF (x_results IS NULL) THEN
3834 x_results := po_validation_results_type.new_instance();
3835 END IF;
3836
3837 IF x_result_set_id IS NULL THEN
3838 x_result_set_id := po_validations.next_result_set_id();
3839 END IF;
3840
3841 IF po_log.d_proc THEN
3842 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3843 po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
3844 END IF;
3845
3846 x_result_type := po_validations.c_result_type_success;
3847
3848 -- bug5258790
3849 -- For fixed price line update, unit price has to be NULL
3850 FORALL i IN 1..p_id_tbl.COUNT
3851 INSERT INTO po_validation_results_gt
3852 (result_set_id,
3853 result_type,
3854 entity_type,
3855 entity_id,
3856 message_name,
3857 column_name,
3858 column_val,
3859 validation_id)
3860 SELECT x_result_set_id,
3861 po_validations.c_result_type_failure,
3862 c_entity_type_line,
3863 p_id_tbl(i),
3864 'PO_PDOI_SVC_BLKT_NO_AMT',
3865 'AMOUNT',
3866 p_amount_tbl(i),
3867 PO_VAL_CONSTANTS.c_amount_blanket
3868 FROM po_lines_all POL
3869 WHERE POL.po_line_id = p_po_line_id_tbl(i)
3870 AND POL.order_type_lookup_code <> 'FIXED PRICE'
3871 AND p_amount_tbl(i) IS NOT NULL
3872 -- missin draft id
3873 UNION
3874 SELECT x_result_set_id,
3875 po_validations.c_result_type_failure,
3876 c_entity_type_line,
3877 p_id_tbl(i),
3878 'PO_PDOI_SVC_BLKT_NO_AMT',
3879 'AMOUNT',
3880 p_amount_tbl(i),
3881 PO_VAL_CONSTANTS.c_amount_blanket
3882 FROM po_lines_draft_all POL
3883 WHERE POL.po_line_id = p_po_line_id_tbl(i)
3884 AND POL.draft_id = p_draft_id_tbl(i)
3885 AND POL.order_type_lookup_code <> 'FIXED PRICE'
3886 AND p_amount_tbl(i) IS NOT NULL;
3887
3888
3889 IF (SQL%ROWCOUNT > 0) THEN
3890 x_result_type := po_validations.c_result_type_failure;
3891 END IF;
3892
3893 IF po_log.d_proc THEN
3894 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3895 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3896 END IF;
3897 EXCEPTION
3898 WHEN OTHERS THEN
3899 IF po_log.d_exc THEN
3900 po_log.exc(d_mod, 0, NULL);
3901 END IF;
3902
3903 RAISE;
3904 END amount_update;
3905 -- bug5258790 END
3906
3907
3908 END PO_VAL_LINES2;