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