[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_HEADERS
Source
1 PACKAGE BODY PO_VAL_HEADERS AS
2 -- $Header: PO_VAL_HEADERS.plb 120.5 2006/09/22 22:29:46 masingh noship $
3
4 c_entity_type_HEADER CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_HEADER;
5
6 c_AGENT_ID CONSTANT VARCHAR2(30) := 'AGENT_ID';
7 c_AMOUNT_LIMIT CONSTANT VARCHAR2(30) := 'AMOUNT_LIMIT';
8 c_BLANKET_TOTAL_AMOUNT CONSTANT VARCHAR2(30) := 'BLANKET_TOTAL_AMOUNT';
9 c_END_DATE CONSTANT VARCHAR2(30) := 'END_DATE';
10 c_PRICE_UPDATE_TOLERANCE CONSTANT VARCHAR2(30) := 'PRICE_UPDATE_TOLERANCE';
11 c_RATE CONSTANT VARCHAR2(30) := 'RATE';
12 c_SEGMENT1 CONSTANT VARCHAR2(30) := 'SEGMENT1';
13 c_START_DATE CONSTANT VARCHAR2(30) := 'START_DATE';
14 c_VENDOR_ID CONSTANT VARCHAR2(30) := 'VENDOR_ID';
15 c_SHIP_TO_LOCATION_ID CONSTANT VARCHAR2(30) := 'SHIP_TO_LOCATION_ID';
16 c_VENDOR_SITE_ID CONSTANT VARCHAR2(30) := 'VENDOR_SITE_ID';
17 c_RATE_TYPE CONSTANT VARCHAR2(30) := 'RATE_TYPE';
18 c_RATE_DATE CONSTANT VARCHAR2(30) := 'RATE_DATE';
19 c_EMAIL_ADDRESS CONSTANT VARCHAR2(30) := 'EMAIL_ADDRESS';
20 c_FAX CONSTANT VARCHAR2(30) := 'FAX';
21
22 c_BLANKET CONSTANT VARCHAR2(30) := 'BLANKET';
23 c_CONTRACT CONSTANT VARCHAR2(30) := 'CONTRACT';
24 c_FINALLY_CLOSED CONSTANT VARCHAR2(30) := 'FINALLY CLOSED';
25 c_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL';
26 c_NUMERIC CONSTANT VARCHAR2(30) := 'NUMERIC';
27 c_PLANNED CONSTANT VARCHAR2(30) := 'PLANNED';
28 c_PO CONSTANT VARCHAR2(30) := 'PO';
29 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
30 c_SUCCESS CONSTANT VARCHAR2(30) := 'SUCCESS';
31 c_User CONSTANT VARCHAR2(30) := 'User';
32 c_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
33
34 -- The module base for this package.
35 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
36 PO_LOG.get_package_base('PO_VAL_HEADERS');
37
38 -- The module base for the subprogram.
39 D_price_update_tol_ge_zero CONSTANT VARCHAR2(100) :=
40 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_update_tol_ge_zero');
41
42 D_amount_limit_ge_zero CONSTANT VARCHAR2(100) :=
43 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_limit_ge_zero');
44
45 D_amt_limit_ge_amt_agreed CONSTANT VARCHAR2(100) :=
46 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amt_limit_ge_amt_agreed');
47
48 D_amount_agreed_ge_zero CONSTANT VARCHAR2(100) :=
49 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_agreed_ge_zero');
50
51 D_amount_agreed_not_null CONSTANT VARCHAR2(100) :=
52 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_agreed_not_null');
53
54 D_warn_supplier_on_hold CONSTANT VARCHAR2(100) :=
55 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'warn_supplier_on_hold');
56
57 D_rate_gt_zero CONSTANT VARCHAR2(100) :=
58 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'rate_gt_zero');
59
60 D_fax_email_address_valid CONSTANT VARCHAR2(100) :=
61 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'fax_email_address_valid');
62
63 D_rate_combination_valid CONSTANT VARCHAR2(100) :=
64 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'rate_combination_valid');
65
66 D_effective_le_expiration CONSTANT VARCHAR2(100) :=
67 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_le_expiration');
68
69 D_effective_from_le_order_date CONSTANT VARCHAR2(100) :=
70 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_from_le_order_date');
71
72 D_effective_to_ge_order_date CONSTANT VARCHAR2(100) :=
73 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_to_ge_order_date');
74
75 D_contract_start_le_order_date CONSTANT VARCHAR2(100) :=
76 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'contract_start_le_order_date');
77
78 D_contract_end_ge_order_date CONSTANT VARCHAR2(100) :=
79 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'contract_end_ge_order_date');
80
81 D_doc_num_chars_valid CONSTANT VARCHAR2(100) :=
82 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'doc_num_chars_valid');
83
84 D_doc_num_unique CONSTANT VARCHAR2(100) :=
85 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'doc_num_unique');
86
87 D_check_agreement_dates CONSTANT VARCHAR2(100) :=
88 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_agreement_dates');
89
90 D_agent_id_not_null CONSTANT VARCHAR2(100) :=
91 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'agent_id_not_null');
92 D_ship_to_loc_not_null CONSTANT VARCHAR2(100) :=
93 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_to_loc_not_null');
94 D_vendor_id_not_null CONSTANT VARCHAR2(100) :=
95 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_id_not_null');
96 D_vendor_site_id_not_null CONSTANT VARCHAR2(100) :=
97 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_site_id_not_null');
98 D_segment1_not_null CONSTANT VARCHAR2(100) :=
99 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'segment1_not_null');
100
101 ---------------------------------------------------------------------------
102 -- Checks that the Price Update Tolerance is greater than or equal to zero.
103 -- Agreements only.
104 ---------------------------------------------------------------------------
105 PROCEDURE price_update_tol_ge_zero(
106 p_header_id_tbl IN PO_TBL_NUMBER
107 , p_price_update_tol_tbl IN PO_TBL_NUMBER
108 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
109 , x_result_type OUT NOCOPY VARCHAR2
110 )
111 IS
112 BEGIN
113
114 PO_VALIDATION_HELPER.greater_or_equal_zero(
115 p_calling_module => D_price_update_tol_ge_zero
116 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
117 , p_value_tbl => p_price_update_tol_tbl
118 , p_entity_id_tbl => p_header_id_tbl
119 , p_entity_type => c_entity_type_HEADER
120 , p_column_name => c_PRICE_UPDATE_TOLERANCE
121 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
122 , x_results => x_results
123 , x_result_type => x_result_type
124 );
125
126 END price_update_tol_ge_zero;
127
128
129 ---------------------------------------------------------------------------
130 -- Checks that the Amount Limit is greater than or equal to zero.
131 -- Agreements only.
132 ---------------------------------------------------------------------------
133 PROCEDURE amount_limit_ge_zero(
134 p_header_id_tbl IN PO_TBL_NUMBER
135 , p_amount_limit_tbl IN PO_TBL_NUMBER
136 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
137 , x_result_type OUT NOCOPY VARCHAR2
138 )
139 IS
140 BEGIN
141
142 PO_VALIDATION_HELPER.greater_or_equal_zero(
143 p_calling_module => D_amount_limit_ge_zero
144 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
145 , p_value_tbl => p_amount_limit_tbl
146 , p_entity_id_tbl => p_header_id_tbl
147 , p_entity_type => c_entity_type_HEADER
148 , p_column_name => c_AMOUNT_LIMIT
149 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
150 , x_results => x_results
151 , x_result_type => x_result_type
152 );
153
154 END amount_limit_ge_zero;
155
156
157 ---------------------------------------------------------------------------
158 -- Checks that the Amount Limit is greater than or equal to the Amount Agreed.
159 -- Agreements only.
160 ---------------------------------------------------------------------------
161 PROCEDURE amt_limit_ge_amt_agreed(
162 p_header_id_tbl IN PO_TBL_NUMBER
163 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
164 , p_amount_limit_tbl IN PO_TBL_NUMBER
165 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
166 , x_result_type OUT NOCOPY VARCHAR2
167 )
168 IS
169 BEGIN
170
171 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
172 p_calling_module => D_amt_limit_ge_amt_agreed
173 , p_num1_tbl => p_blanket_total_amount_tbl
174 , p_num2_tbl => p_amount_limit_tbl
175 , p_entity_id_tbl => p_header_id_tbl
176 , p_entity_type => c_entity_type_HEADER
177 , p_column_name => c_AMOUNT_LIMIT
178 , p_message_name => PO_MESSAGE_S.PO_PO_AMT_LIMIT_CK_FAILED
179 , x_results => x_results
180 , x_result_type => x_result_type
181 );
182
183 END amt_limit_ge_amt_agreed;
184
185
186 ---------------------------------------------------------------------------
187 -- Checks that the Amount Agreed is greater than or equal to zero.
188 -- Agreements only.
189 ---------------------------------------------------------------------------
190 PROCEDURE amount_agreed_ge_zero(
191 p_header_id_tbl IN PO_TBL_NUMBER
192 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
193 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
194 , x_result_type OUT NOCOPY VARCHAR2
195 )
196 IS
197 BEGIN
198
199 PO_VALIDATION_HELPER.greater_or_equal_zero(
200 p_calling_module => D_amount_agreed_ge_zero
201 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
202 , p_value_tbl => p_blanket_total_amount_tbl
203 , p_entity_id_tbl => p_header_id_tbl
204 , p_entity_type => c_entity_type_HEADER
205 , p_column_name => c_BLANKET_TOTAL_AMOUNT
206 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
207 , x_results => x_results
208 , x_result_type => x_result_type
209 );
210
211 END amount_agreed_ge_zero;
212
213
214 ---------------------------------------------------------------------------
215 -- Checks that the Amount Agreed is not null if Amount Limit is not null.
216 -- Agreements only.
217 ---------------------------------------------------------------------------
218 PROCEDURE amount_agreed_not_null(
219 p_header_id_tbl IN PO_TBL_NUMBER
220 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
221 , p_amount_limit_tbl IN PO_TBL_NUMBER
222 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
223 , x_result_type OUT NOCOPY VARCHAR2
224 )
225 IS
226 d_mod CONSTANT VARCHAR2(100) := D_amount_agreed_not_null;
227 l_results_count NUMBER;
228 BEGIN
229
230 IF PO_LOG.d_proc THEN
231 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
232 PO_LOG.proc_begin(d_mod,'p_blanket_total_amount_tbl',p_blanket_total_amount_tbl);
233 PO_LOG.proc_begin(d_mod,'p_amount_limit_tbl',p_amount_limit_tbl);
234 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
235 END IF;
236
237 IF (x_results IS NULL) THEN
238 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
239 END IF;
240
241 l_results_count := x_results.result_type.COUNT;
242
243 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
244 IF ( p_amount_limit_tbl(i) IS NOT NULL
245 AND p_blanket_total_amount_tbl(i) IS NULL
246 )
247 THEN
248 x_results.add_result(
249 p_entity_type => c_entity_type_HEADER
250 , p_entity_id => p_header_id_tbl(i)
251 , p_column_name => c_BLANKET_TOTAL_AMOUNT
252 , p_message_name => PO_MESSAGE_S.PO_AMT_LMT_NOT_NULL
253 );
254 END IF;
255 END LOOP;
256
257 IF (l_results_count < x_results.result_type.COUNT) THEN
258 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
259 ELSE
260 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
261 END IF;
262
263 IF PO_LOG.d_proc THEN
264 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
265 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
266 END IF;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 IF PO_LOG.d_exc THEN
271 PO_LOG.exc(d_mod,0,NULL);
272 END IF;
273 RAISE;
274
275 END amount_agreed_not_null;
276
277
278 ---------------------------------------------------------------------------
279 -- Display a warning message if the supplier is on hold.
280 ---------------------------------------------------------------------------
281 PROCEDURE warn_supplier_on_hold(
282 p_header_id_tbl IN PO_TBL_NUMBER
283 , p_vendor_id_tbl IN PO_TBL_NUMBER
284 , x_result_set_id IN OUT NOCOPY NUMBER
285 , x_result_type OUT NOCOPY VARCHAR2
286 )
287 IS
288 d_mod CONSTANT VARCHAR2(100) := D_warn_supplier_on_hold;
289 BEGIN
290
291 IF PO_LOG.d_proc THEN
292 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
293 PO_LOG.proc_begin(d_mod,'p_vendor_id_tbl',p_vendor_id_tbl);
294 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
295 END IF;
296
297 IF (x_result_set_id IS NULL) THEN
298 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
299 END IF;
300
301 FORALL i IN 1 .. p_header_id_tbl.COUNT
302 INSERT INTO PO_VALIDATION_RESULTS_GT
303 ( result_set_id
304 , result_type
305 , entity_type
306 , entity_id
307 , column_name
308 , column_val
309 , message_name
310 )
311 SELECT
312 x_result_set_id
313 , PO_VALIDATIONS.c_result_type_WARNING
314 , c_entity_type_HEADER
315 , p_header_id_tbl(i)
316 , c_VENDOR_ID
317 , TO_CHAR(p_vendor_id_tbl(i))
318 , PO_MESSAGE_S.PO_PO_VENDOR_ON_HOLD
319 FROM
320 PO_VENDORS SUPPLIER
321 WHERE
322 SUPPLIER.vendor_id = p_vendor_id_tbl(i)
323 AND SUPPLIER.hold_flag = 'Y'
324 ;
325
326 IF(SQL%ROWCOUNT > 0) THEN
327 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
328 ELSE
329 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
330 END IF;
331
332 IF PO_LOG.d_proc THEN
333 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
334 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
335 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
336 END IF;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF PO_LOG.d_exc THEN
341 PO_LOG.exc(d_mod,0,NULL);
342 END IF;
343 RAISE;
344
345 END warn_supplier_on_hold;
346
347
348 ---------------------------------------------------------------------------
349 -- Checks that the Rate is greater than zero.
350 -- For Rate Type of User, the Rate is also required (not null),
351 -- but that is handled elsewhere (in the UI).
352 ---------------------------------------------------------------------------
353 PROCEDURE rate_gt_zero(
354 p_header_id_tbl IN PO_TBL_NUMBER
355 , p_rate_tbl IN PO_TBL_NUMBER
356 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
357 , x_result_type OUT NOCOPY VARCHAR2
358 )
359 IS
360 BEGIN
361
362 PO_VALIDATION_HELPER.greater_than_zero(
363 p_calling_module => D_rate_gt_zero
364 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
365 , p_value_tbl => p_rate_tbl
366 , p_entity_id_tbl => p_header_id_tbl
367 , p_entity_type => c_entity_type_HEADER
368 , p_column_name => c_RATE
369 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
370 , x_results => x_results
371 , x_result_type => x_result_type
372 );
373
374 END rate_gt_zero;
375
376
377 ---------------------------------------------------------------------------
378 -- Checks the following are not null, if the currency is different from the
379 -- functional currency:
380 -- 1. Rate Type
381 -- 2. Rate Date
382 -- 3. Rate
383 ---------------------------------------------------------------------------
384 PROCEDURE rate_combination_valid(
385 p_header_id_tbl IN PO_TBL_NUMBER
386 , p_org_id_tbl IN PO_TBL_NUMBER
387 , p_currency_code_tbl IN PO_TBL_VARCHAR30
388 , p_rate_type_tbl IN PO_TBL_VARCHAR30
389 , p_rate_date_tbl IN PO_TBL_DATE
390 , p_rate_tbl IN PO_TBL_NUMBER
391 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
392 , x_result_type OUT NOCOPY VARCHAR2
393 )
394 IS
395 d_mod CONSTANT VARCHAR2(100) := D_rate_combination_valid;
396 l_results_count NUMBER;
397 l_func_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
398 BEGIN
399
400 IF PO_LOG.d_proc THEN
401 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
402 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
403 PO_LOG.proc_begin(d_mod,'p_currency_code_tbl',p_currency_code_tbl);
404 PO_LOG.proc_begin(d_mod,'p_rate_type_tbl',p_rate_type_tbl);
405 PO_LOG.proc_begin(d_mod,'p_rate_date_tbl',p_rate_date_tbl);
406 PO_LOG.proc_begin(d_mod,'p_rate_tbl',p_rate_tbl);
407 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
408 END IF;
409
410 IF (x_results IS NULL) THEN
411 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
412 END IF;
413
414 l_results_count := x_results.result_type.COUNT;
415
416 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
417
418 SELECT
419 BOOKS.currency_code
420 INTO
421 l_func_currency_code
422 FROM
423 FINANCIALS_SYSTEM_PARAMS_ALL FIN_PARAMS
424 , GL_SETS_OF_BOOKS BOOKS
425 WHERE
426 FIN_PARAMS.org_id = p_org_id_tbl(i)
427 AND BOOKS.set_of_books_id = FIN_PARAMS.set_of_books_id
428 ;
429
430 IF (p_currency_code_tbl(i) <> l_func_currency_code OR p_currency_code_tbl(i) IS NULL) THEN
431
432 IF (p_rate_type_tbl(i) IS NULL) THEN
433 x_results.add_result(
434 p_entity_type => c_entity_type_HEADER
435 , p_entity_id => p_header_id_tbl(i)
436 , p_column_name => c_RATE_TYPE
437 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
438 );
439 END IF;
440
441 IF (p_rate_date_tbl(i) IS NULL) THEN
442 x_results.add_result(
443 p_entity_type => c_entity_type_HEADER
444 , p_entity_id => p_header_id_tbl(i)
445 , p_column_name => c_RATE_DATE
446 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
447 );
448 END IF;
449
450 IF (p_rate_type_tbl(i) <> c_User AND p_rate_tbl(i) IS NULL) THEN
451 x_results.add_result(
452 p_entity_type => c_entity_type_HEADER
453 , p_entity_id => p_header_id_tbl(i)
454 , p_column_name => NULL
455 , p_message_name => PO_MESSAGE_S.PO_HTML_NO_RATE_DEFINED
456 );
457 ELSIF (p_rate_tbl(i) IS NULL) THEN
458 x_results.add_result(
459 p_entity_type => c_entity_type_HEADER
460 , p_entity_id => p_header_id_tbl(i)
461 , p_column_name => c_RATE
462 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
463 );
464 END IF;
465
466 END IF;
467
468 END LOOP;
469
470 IF (l_results_count < x_results.result_type.COUNT) THEN
471 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
472 ELSE
473 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
474 END IF;
475
476 IF PO_LOG.d_proc THEN
477 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
478 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
479 END IF;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF PO_LOG.d_exc THEN
484 PO_LOG.exc(d_mod,0,NULL);
485 END IF;
486 RAISE;
487
488 END rate_combination_valid;
489
490
491 ---------------------------------------------------------------------------
492 -- Checks that email address is not null if supplier notification method
493 -- is email and that fax number is not null if supplier notification method
494 -- is fax.
495 ---------------------------------------------------------------------------
496 PROCEDURE fax_email_address_valid(
497 p_header_id_tbl IN PO_TBL_NUMBER
498 , p_supplier_notif_method_tbl IN PO_TBL_VARCHAR30
499 , p_fax_tbl IN PO_TBL_VARCHAR30
500 , p_email_address_tbl IN PO_TBL_VARCHAR2000
501 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
502 , x_result_type OUT NOCOPY VARCHAR2
503 )
504 IS
505 d_mod CONSTANT VARCHAR2(100) := D_fax_email_address_valid;
506 l_results_count NUMBER;
507 BEGIN
508
509 IF PO_LOG.d_proc THEN
510 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
511 PO_LOG.proc_begin(d_mod,'p_supplier_notif_method_tbl',p_supplier_notif_method_tbl);
512 PO_LOG.proc_begin(d_mod,'p_email_address_tbl',p_email_address_tbl);
513 PO_LOG.proc_begin(d_mod,'p_fax_tbl',p_fax_tbl);
514 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
515 END IF;
516
517 IF (x_results IS NULL) THEN
518 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
519 END IF;
520
521 l_results_count := x_results.result_type.COUNT;
522
523 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
524
525 IF ((p_supplier_notif_method_tbl(i) = c_EMAIL) AND
526 (p_email_address_tbl(i) IS NULL)) THEN
527 x_results.add_result(
528 p_entity_type => c_entity_type_HEADER
529 , p_entity_id => p_header_id_tbl(i)
530 , p_column_name => c_EMAIL_ADDRESS
531 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
532 );
533 ELSIF ((p_supplier_notif_method_tbl(i) = c_FAX) AND
534 (p_fax_tbl(i) IS NULL)) THEN
535 x_results.add_result(
536 p_entity_type => c_entity_type_HEADER
537 , p_entity_id => p_header_id_tbl(i)
538 , p_column_name => c_FAX
539 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
540 );
541 END IF;
542
543 END LOOP;
544
545 IF (l_results_count < x_results.result_type.COUNT) THEN
546 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
547 ELSE
548 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
549 END IF;
550
551 IF PO_LOG.d_proc THEN
552 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
553 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
554 END IF;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 IF PO_LOG.d_exc THEN
559 PO_LOG.exc(d_mod,0,NULL);
560 END IF;
561 RAISE;
562
563 END fax_email_address_valid;
564
565
566 ---------------------------------------------------------------------------
567 -- Checks that the Expiration Date is greater than or equal to
568 -- the Effective Date.
569 -- Agreements only.
570 ---------------------------------------------------------------------------
571 PROCEDURE effective_le_expiration(
572 p_header_id_tbl IN PO_TBL_NUMBER
573 , p_start_date_tbl IN PO_TBL_DATE
574 , p_end_date_tbl IN PO_TBL_DATE
575 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
576 , x_result_type OUT NOCOPY VARCHAR2
577 )
578 IS
579 BEGIN
580
581 PO_VALIDATION_HELPER.start_date_le_end_date(
582 p_calling_module => D_effective_le_expiration
583 , p_start_date_tbl => p_start_date_tbl
584 , p_end_date_tbl => p_end_date_tbl
585 , p_entity_id_tbl => p_header_id_tbl
586 , p_entity_type => c_entity_type_HEADER
587 , p_column_name => c_END_DATE
588 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
589 , p_message_name => PO_MESSAGE_S.PO_ALL_DATE_BETWEEN_START_END
590 , x_results => x_results
591 , x_result_type => x_result_type
592 );
593
594 END effective_le_expiration;
595
596
597 ---------------------------------------------------------------------------
598 -- Checks that the Effective From Date is less than or equal to
599 -- the Creation Date of any Orders referencing the Agreement.
600 -- Agreements only.
601 ---------------------------------------------------------------------------
602 PROCEDURE effective_from_le_order_date(
603 p_header_id_tbl IN PO_TBL_NUMBER
604 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
605 , p_start_date_tbl IN PO_TBL_DATE
606 , x_result_set_id IN OUT NOCOPY NUMBER
607 , x_result_type OUT NOCOPY VARCHAR2
608 )
609 IS
610 d_mod CONSTANT VARCHAR2(100) := D_effective_from_le_order_date;
611 BEGIN
612
613 IF PO_LOG.d_proc THEN
614 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
615 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
616 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
617 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
618 END IF;
619
620 IF (x_result_set_id IS NULL) THEN
621 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
622 END IF;
623
624 FORALL i IN 1 .. p_header_id_tbl.COUNT
625 INSERT INTO PO_VALIDATION_RESULTS_GT
626 ( result_set_id
627 , entity_type
628 , entity_id
629 , column_name
630 , column_val
631 , message_name
632 )
633 SELECT
634 x_result_set_id
635 , c_entity_type_HEADER
636 , p_header_id_tbl(i)
637 , c_START_DATE
638 , TO_CHAR(p_start_date_tbl(i))
639 , PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR --- Bug 5548899
640 FROM DUAL
641 WHERE
642 p_type_lookup_code_tbl(i) = c_BLANKET
643 AND EXISTS
644 ( SELECT NULL
645 FROM
646 PO_LINES_ALL ORDER_LINE
647 WHERE
648 ORDER_LINE.from_header_id = p_header_id_tbl(i)
649 AND p_start_date_tbl(i) > ORDER_LINE.creation_date
650 )
651 ;
652
653 IF (SQL%ROWCOUNT > 0) THEN
654 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
655 ELSE
656 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
657 END IF;
658
659 IF PO_LOG.d_proc THEN
660 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
661 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
662 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
663 END IF;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 IF PO_LOG.d_exc THEN
668 PO_LOG.exc(d_mod,0,NULL);
669 END IF;
670 RAISE;
671
672 END effective_from_le_order_date;
673
674
675 ---------------------------------------------------------------------------
676 -- Checks that the Effective To Date is greater than or equal to
677 -- the Creation Date of any Orders referencing the Agreement.
678 -- Agreements only.
679 ---------------------------------------------------------------------------
680 PROCEDURE effective_to_ge_order_date(
681 p_header_id_tbl IN PO_TBL_NUMBER
682 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
683 , p_end_date_tbl IN PO_TBL_DATE
684 , x_result_set_id IN OUT NOCOPY NUMBER
685 , x_result_type OUT NOCOPY VARCHAR2
686 )
687 IS
688 d_mod CONSTANT VARCHAR2(100) := D_effective_to_ge_order_date;
689 BEGIN
690
691 IF PO_LOG.d_proc THEN
692 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
693 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
694 PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
695 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
696 END IF;
697
698 IF (x_result_set_id IS NULL) THEN
699 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
700 END IF;
701
702 FORALL i IN 1 .. p_header_id_tbl.COUNT
703 INSERT INTO PO_VALIDATION_RESULTS_GT
704 ( result_set_id
705 , entity_type
706 , entity_id
707 , column_name
708 , column_val
709 , message_name
710 )
711 SELECT
712 x_result_set_id
713 , c_entity_type_HEADER
714 , p_header_id_tbl(i)
715 , c_END_DATE
716 , TO_CHAR(p_end_date_tbl(i))
717 , PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR --- Bug 5548899
718 FROM DUAL
719 WHERE
720 p_type_lookup_code_tbl(i) = c_BLANKET
721 AND EXISTS
722 ( SELECT NULL
723 FROM
724 PO_LINES_ALL ORDER_LINE
725 WHERE
726 ORDER_LINE.from_header_id = p_header_id_tbl(i)
727 AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
728 )
729 ;
730
731 IF (SQL%ROWCOUNT > 0) THEN
732 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
733 ELSE
734 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
735 END IF;
736
737 IF PO_LOG.d_proc THEN
738 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
739 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
740 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
741 END IF;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF PO_LOG.d_exc THEN
746 PO_LOG.exc(d_mod,0,NULL);
747 END IF;
748 RAISE;
749
750 END effective_to_ge_order_date;
751
752
753 ---------------------------------------------------------------------------
754 -- Checks that the Effective From Date is less than or equal to
755 -- the Creation Date of any Orders referencing the Contract.
756 ---------------------------------------------------------------------------
757 PROCEDURE contract_start_le_order_date(
758 p_header_id_tbl IN PO_TBL_NUMBER
759 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
760 , p_start_date_tbl IN PO_TBL_DATE
761 , x_result_set_id IN OUT NOCOPY NUMBER
762 , x_result_type OUT NOCOPY VARCHAR2
763 )
764 IS
765 d_mod CONSTANT VARCHAR2(100) := D_contract_start_le_order_date;
766 BEGIN
767
768 IF PO_LOG.d_proc THEN
769 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
770 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
771 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
772 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
773 END IF;
774
775 IF (x_result_set_id IS NULL) THEN
776 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
777 END IF;
778
779 FORALL i IN 1 .. p_header_id_tbl.COUNT
780 INSERT INTO PO_VALIDATION_RESULTS_GT
781 ( result_set_id
782 , entity_type
783 , entity_id
784 , column_name
785 , column_val
786 , message_name
787 )
788 SELECT
789 x_result_set_id
790 , c_entity_type_HEADER
791 , p_header_id_tbl(i)
792 , c_START_DATE
793 , TO_CHAR(p_start_date_tbl(i))
794 , PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR
795 FROM DUAL
796 WHERE
797 p_type_lookup_code_tbl(i) = c_CONTRACT
798 AND EXISTS
799 ( SELECT NULL
800 FROM
801 PO_LINES_ALL ORDER_LINE
802 , PO_HEADERS_ALL ORDER_HEADER
803 WHERE
804 ORDER_LINE.contract_id = p_header_id_tbl(i)
805 AND TRUNC(p_start_date_tbl(i)) > ORDER_LINE.creation_date
806 AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
807 AND ORDER_HEADER.approved_date IS NOT NULL
808 AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
809 AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
810 )
811 ;
812
813 -- TODO: check with PM about differences in Agreements / Contracts checks.
814
815 IF (SQL%ROWCOUNT > 0) THEN
816 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
817 ELSE
818 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
819 END IF;
820
821 IF PO_LOG.d_proc THEN
822 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
823 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
824 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
825 END IF;
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 IF PO_LOG.d_exc THEN
830 PO_LOG.exc(d_mod,0,NULL);
831 END IF;
832 RAISE;
833
834 END contract_start_le_order_date;
835
836
837 ---------------------------------------------------------------------------
838 -- Checks that the Effective To Date is greater than or equal to
839 -- the Creation Date of any Orders referencing the Contract.
840 ---------------------------------------------------------------------------
841 PROCEDURE contract_end_ge_order_date(
842 p_header_id_tbl IN PO_TBL_NUMBER
843 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
844 , p_end_date_tbl IN PO_TBL_DATE
845 , x_result_set_id IN OUT NOCOPY NUMBER
846 , x_result_type OUT NOCOPY VARCHAR2
847 )
848 IS
849 d_mod CONSTANT VARCHAR2(100) := D_contract_end_ge_order_date;
850 BEGIN
851
852 IF PO_LOG.d_proc THEN
853 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
854 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
855 PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
856 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
857 END IF;
858
859 IF (x_result_set_id IS NULL) THEN
860 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
861 END IF;
862
863 FORALL i IN 1 .. p_header_id_tbl.COUNT
864 INSERT INTO PO_VALIDATION_RESULTS_GT
865 ( result_set_id
866 , entity_type
867 , entity_id
868 , column_name
869 , column_val
870 , message_name
871 )
872 SELECT
873 x_result_set_id
874 , c_entity_type_HEADER
875 , p_header_id_tbl(i)
876 , c_END_DATE
877 , TO_CHAR(p_end_date_tbl(i))
878 , PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR
879 FROM DUAL
880 WHERE
881 p_type_lookup_code_tbl(i) = c_CONTRACT
882 AND EXISTS
883 ( SELECT NULL
884 FROM
885 PO_LINES_ALL ORDER_LINE
886 , PO_HEADERS_ALL ORDER_HEADER
887 WHERE
888 ORDER_LINE.contract_id = p_header_id_tbl(i)
889 AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
890 AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
891 AND ORDER_HEADER.approved_date IS NOT NULL
892 AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
893 AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
894 )
895 ;
896
897 IF (SQL%ROWCOUNT > 0) THEN
898 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
899 ELSE
900 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
901 END IF;
902
903 IF PO_LOG.d_proc THEN
904 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
905 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
906 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
907 END IF;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 IF PO_LOG.d_exc THEN
912 PO_LOG.exc(d_mod,0,NULL);
913 END IF;
914 RAISE;
915
916 END contract_end_ge_order_date;
917
918
919 ---------------------------------------------------------------------------
920 -- Checks that the Document Number is numeric, if required.
921 ---------------------------------------------------------------------------
922 PROCEDURE doc_num_chars_valid(
923 p_header_id_tbl IN PO_TBL_NUMBER
924 , p_org_id_tbl IN PO_TBL_NUMBER
925 , p_segment1_tbl IN PO_TBL_VARCHAR30
926 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
927 , x_result_type OUT NOCOPY VARCHAR2
928 )
929 IS
930 d_mod CONSTANT VARCHAR2(100) := D_doc_num_chars_valid;
931
932 l_results_count NUMBER;
933 l_data_key NUMBER;
934 l_header_id_tbl PO_TBL_NUMBER;
935 l_segment1_tbl PO_TBL_VARCHAR30;
936 l_num_test NUMBER;
937 BEGIN
938
939 IF PO_LOG.d_proc THEN
940 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
941 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
942 PO_LOG.proc_begin(d_mod,'p_segment1_tbl',p_segment1_tbl);
943 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
944 END IF;
945
946 IF (x_results IS NULL) THEN
947 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
948 END IF;
949
950 l_results_count := x_results.result_type.COUNT;
951
952 l_data_key := PO_CORE_S.get_session_gt_nextval();
953
954 FORALL i IN 1 .. p_header_id_tbl.COUNT
955 INSERT INTO PO_SESSION_GT
956 ( key
957 , num1
958 , num2
959 , char1
960 )
961 VALUES
962 ( l_data_key
963 , p_header_id_tbl(i)
964 , p_org_id_tbl(i)
965 , p_segment1_tbl(i)
966 )
967 ;
968
969
970 SELECT
971 num1
972 , char1
973 BULK COLLECT INTO
974 l_header_id_tbl
975 , l_segment1_tbl
976 FROM
977 PO_SESSION_GT SES
978 , PO_SYSTEM_PARAMETERS_ALL PARAMS
979 WHERE
980 SES.key = l_data_key
981 AND SES.num2 = PARAMS.org_id
982 AND PARAMS.manual_po_num_type = c_NUMERIC
983 ;
984
985
986 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
987 BEGIN
988 l_num_test := TO_NUMBER(l_segment1_tbl(i));
989 EXCEPTION
990 WHEN VALUE_ERROR THEN
991 x_results.add_result(
992 p_entity_type => c_entity_type_HEADER
993 , p_entity_id => l_header_id_tbl(i)
994 , p_column_name => c_SEGMENT1
995 , p_column_val => l_segment1_tbl(i)
996 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_NUMERIC
997 );
998 END;
999 END LOOP;
1000
1001 IF (l_results_count < x_results.result_type.COUNT) THEN
1002 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1003 ELSE
1004 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1005 END IF;
1006
1007 IF PO_LOG.d_proc THEN
1008 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1009 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 IF PO_LOG.d_exc THEN
1015 PO_LOG.exc(d_mod,0,NULL);
1016 END IF;
1017 RAISE;
1018
1019 END doc_num_chars_valid;
1020
1021
1022 ---------------------------------------------------------------------------
1023 -- Checks that the Document Number is unique within the Org.
1024 ---------------------------------------------------------------------------
1025 PROCEDURE doc_num_unique(
1026 p_header_id_tbl IN PO_TBL_NUMBER
1027 , p_org_id_tbl IN PO_TBL_NUMBER
1028 , p_segment1_tbl IN PO_TBL_VARCHAR30
1029 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1030 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1031 , x_result_type OUT NOCOPY VARCHAR2
1032 )
1033 IS
1034 d_mod CONSTANT VARCHAR2(100) := D_doc_num_unique;
1035 d_position NUMBER := 0;
1036
1037 l_results_count NUMBER;
1038
1039 l_data_key NUMBER;
1040 l_header_id_tbl PO_TBL_NUMBER;
1041 l_org_id_tbl PO_TBL_NUMBER;
1042 l_segment1_tbl PO_TBL_VARCHAR30;
1043 l_nonunique_tbl PO_TBL_VARCHAR2000;
1044
1045 l_check_sourcing_flag VARCHAR2(2000);
1046 l_pon_unique_status VARCHAR2(2000);
1047
1048 BEGIN
1049
1050 IF PO_LOG.d_proc THEN
1051 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1052 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1053 PO_LOG.proc_begin(d_mod,'p_segment1_tbl',p_segment1_tbl);
1054 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1055 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1056 END IF;
1057
1058 d_position := 1;
1059
1060 IF (x_results IS NULL) THEN
1061 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1062 END IF;
1063
1064 l_results_count := x_results.result_type.COUNT;
1065
1066 ----------------------------------------------------------
1067 --
1068 -- The following mapping is used for PO_SESSION_GT in this
1069 -- procedure:
1070 --
1071 -- num1 - po_header_id
1072 -- num2 - org_id
1073 -- index_char1 - segment1
1074 -- char2 - type_lookup_code
1075 --
1076 -- index_char2 - used to identify failure rows
1077 --
1078 ----------------------------------------------------------
1079
1080 l_data_key := PO_CORE_S.get_session_gt_nextval();
1081
1082 -- Only check unsaved headers with manually entered document numbers.
1083
1084 FORALL i IN 1 .. p_header_id_tbl.COUNT
1085 INSERT INTO PO_SESSION_GT
1086 ( key
1087 , num1
1088 , num2
1089 , index_char1
1090 , char2
1091 )
1092 SELECT
1093 l_data_key
1094 , p_header_id_tbl(i)
1095 , p_org_id_tbl(i)
1096 , p_segment1_tbl(i)
1097 , p_type_lookup_code_tbl(i)
1098 FROM
1099 PO_SYSTEM_PARAMETERS_ALL PARAMS
1100 WHERE
1101 PARAMS.org_id = p_org_id_tbl(i)
1102 AND PARAMS.user_defined_po_num_code = c_MANUAL
1103 AND p_segment1_tbl(i) IS NOT NULL
1104 AND NOT EXISTS
1105 ( SELECT NULL
1106 FROM PO_HEADERS_ALL SAVED_HEADER
1107 WHERE SAVED_HEADER.po_header_id = p_header_id_tbl(i)
1108 )
1109 ;
1110
1111 d_position := 100;
1112
1113 IF (SQL%ROWCOUNT > 0) THEN
1114
1115 d_position := 110;
1116
1117 UPDATE PO_SESSION_GT SES
1118 SET index_char2 = 'X'
1119 WHERE
1120 SES.key = l_data_key
1121 AND
1122 (
1123 -- Check for currently existing documents.
1124 EXISTS
1125 ( SELECT NULL
1126 FROM PO_HEADERS_ALL HEADER
1127 WHERE
1128 HEADER.org_id = SES.num2
1129 AND HEADER.segment1 = SES.index_char1
1130 AND HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
1131 AND HEADER.po_header_id <> SES.num1
1132 )
1133 OR
1134 -- Check for previously purged documents.
1135 EXISTS
1136 ( SELECT NULL
1137 FROM PO_HISTORY_POS_ALL DELETED_HEADER
1138 WHERE
1139 DELETED_HEADER.org_id = SES.num2
1140 AND DELETED_HEADER.segment1 = SES.index_char1
1141 AND DELETED_HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
1142 )
1143 OR
1144 -- Check for other in-memory documents.
1145 EXISTS
1146 ( SELECT NULL
1147 FROM PO_SESSION_GT UNSAVED_DATA
1148 WHERE
1149 UNSAVED_DATA.key = l_data_key
1150 AND UNSAVED_DATA.num2 = SES.num2 -- org_id
1151 AND UNSAVED_DATA.index_char1 = SES.index_char1 -- segment1
1152 AND UNSAVED_DATA.num1 <> SES.num1 -- po_header_id
1153 )
1154 )
1155 ;
1156
1157 d_position := 200;
1158
1159 -- If Sourcing is enabled, we need to check for
1160 -- document number uniqueness across auctions as well.
1161 PO_SETUP_S1.get_sourcing_startup(l_check_sourcing_flag);
1162
1163 d_position := 210;
1164
1165 IF PO_LOG.d_stmt THEN
1166 PO_LOG.stmt(d_mod,d_position,'l_check_sourcing_flag',l_check_sourcing_flag);
1167 END IF;
1168
1169 SELECT
1170 SES.num1
1171 , SES.num2
1172 , SES.index_char1
1173 , SES.index_char2
1174 BULK COLLECT INTO
1175 l_header_id_tbl
1176 , l_org_id_tbl
1177 , l_segment1_tbl
1178 , l_nonunique_tbl
1179 FROM
1180 PO_SESSION_GT SES
1181 WHERE
1182 SES.key = l_data_key
1183 AND
1184 ( SES.index_char2 = 'X'
1185 OR
1186 (
1187 l_check_sourcing_flag = 'I'
1188 AND SES.char2 IN (c_STANDARD,c_BLANKET)
1189 )
1190 )
1191 ;
1192
1193 d_position := 300;
1194 IF PO_LOG.d_stmt THEN
1195 PO_LOG.stmt(d_mod,d_position,'l_header_id_tbl',l_header_id_tbl);
1196 PO_LOG.stmt(d_mod,d_position,'l_org_id_tbl',l_org_id_tbl);
1197 PO_LOG.stmt(d_mod,d_position,'l_segment1_tbl',l_segment1_tbl);
1198 PO_LOG.stmt(d_mod,d_position,'l_nonunique_tbl',l_nonunique_tbl);
1199 END IF;
1200
1201 IF (l_check_sourcing_flag = 'I') THEN
1202 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
1203 IF (l_nonunique_tbl(i) IS NULL) THEN
1204 PON_AUCTION_PO_PKG.check_unique(
1205 org_id => l_org_id_tbl(i)
1206 , po_number => l_segment1_tbl(i)
1207 , status => l_pon_unique_status
1208 );
1209 IF (NVL(l_pon_unique_status,'N') <> c_SUCCESS) THEN
1210 l_nonunique_tbl(i) := 'X';
1211 END IF;
1212 END IF;
1213 END LOOP;
1214 END IF;
1215
1216 d_position := 400;
1217
1218 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
1219 IF (l_nonunique_tbl(i) = 'X') THEN
1220 x_results.add_result(
1221 p_entity_type => c_entity_type_HEADER
1222 , p_entity_id => l_header_id_tbl(i)
1223 , p_column_name => c_SEGMENT1
1224 , p_column_val => l_segment1_tbl(i)
1225 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_UNIQUE_VAL
1226 );
1227 END IF;
1228 END LOOP;
1229
1230 END IF; -- any records need to be checked
1231
1232 d_position := 500;
1233
1234 IF (l_results_count < x_results.result_type.COUNT) THEN
1235 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1236 ELSE
1237 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1238 END IF;
1239
1240 IF PO_LOG.d_proc THEN
1241 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1242 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1243 END IF;
1244
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 IF PO_LOG.d_exc THEN
1248 PO_LOG.exc(d_mod,d_position,NULL);
1249 END IF;
1250 RAISE;
1251
1252 END doc_num_unique;
1253
1254
1255 -- TODO: move to PO_DOC_CHECK_HEADERS
1256
1257 ----------------------------------------------------------------------
1258 -- Validates the dates of an Agreement's lines and price breaks
1259 -- with respect to the dates on the header.
1260 --
1261 --Pre-reqs:
1262 -- The agreement data must be populated in the Submission Check
1263 -- temp tables before this procedure is called.
1264 -- Only the agreement data should be in the tables.
1265 ----------------------------------------------------------------------
1266 PROCEDURE check_agreement_dates(
1267 p_online_report_id IN NUMBER
1268 , p_login_id IN NUMBER
1269 , p_user_id IN NUMBER
1270 , x_sequence IN OUT NOCOPY NUMBER
1271 )
1272 IS
1273 d_mod CONSTANT VARCHAR2(100) := D_check_agreement_dates;
1274
1275 c_delim CONSTANT VARCHAR2(1) := ' ';
1276 l_linemsg VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_LINE'), 1,25);
1277
1278 l_message_name VARCHAR2(30);
1279 l_text VARCHAR2(4000);
1280 BEGIN
1281
1282 IF PO_LOG.d_proc THEN
1283 PO_LOG.proc_begin(d_mod,'p_online_report_id',p_online_report_id);
1284 PO_LOG.proc_begin(d_mod,'p_login_id',p_login_id);
1285 PO_LOG.proc_begin(d_mod,'p_user_id',p_user_id);
1286 PO_LOG.proc_begin(d_mod,'x_sequence',x_sequence);
1287 END IF;
1288
1289
1290 -- Check that the line's Expiration date is within the
1291 -- Effective From and To dates of the agreement.
1292
1293 l_message_name := PO_MESSAGE_S.POX_EXPIRATION_DATES;
1294
1295 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1296
1297 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1298 ( online_report_id
1299 , last_update_login
1300 , last_updated_by
1301 , last_update_date
1302 , created_by
1303 , creation_date
1304 , line_num
1305 , shipment_num
1306 , distribution_num
1307 , sequence
1308 , message_name
1309 , text_line
1310 )
1311 SELECT
1312 p_online_report_id
1313 , p_login_id
1314 , p_user_id
1315 , SYSDATE
1316 , p_user_id
1317 , SYSDATE
1318 , LINE.line_num
1319 , 0
1320 , 0
1321 , x_sequence + rownum
1322 , l_message_name
1323 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1324 || l_text,1,240)
1325 FROM
1326 PO_LINES_GT LINE
1327 , PO_HEADERS_GT HEADER
1328 WHERE
1329 LINE.expiration_date < HEADER.start_date
1330 OR LINE.expiration_date > HEADER.end_date
1331 ;
1332
1333 x_sequence := x_sequence + SQL%ROWCOUNT;
1334
1335
1336 -- Check that the price break's Effective From date
1337 -- is before the Effective To date of the agreement.
1338
1339 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES4;
1340
1341 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1342
1343 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1344 ( online_report_id
1345 , last_update_login
1346 , last_updated_by
1347 , last_update_date
1348 , created_by
1349 , creation_date
1350 , line_num
1351 , shipment_num
1352 , distribution_num
1353 , sequence
1354 , message_name
1355 , text_line
1356 )
1357 SELECT
1358 p_online_report_id
1359 , p_login_id
1360 , p_user_id
1361 , SYSDATE
1362 , p_user_id
1363 , SYSDATE
1364 , LINE.line_num
1365 , PRICE_BREAK.shipment_num
1366 , 0
1367 , x_sequence + rownum
1368 , l_message_name
1369 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1370 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
1371 || l_text,1,240)
1372 FROM
1373 PO_LINE_LOCATIONS_ALL PRICE_BREAK
1374 , PO_LINES_GT LINE
1375 , PO_HEADERS_GT HEADER
1376 WHERE
1377 PRICE_BREAK.po_line_id = LINE.po_line_id
1378 AND PRICE_BREAK.start_date > HEADER.end_date
1379 ;
1380
1381 x_sequence := x_sequence + SQL%ROWCOUNT;
1382
1383
1384 -- Check that the price break's Effective From date
1385 -- is after the Effective From date of the agreement.
1386
1387 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES1;
1388
1389 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1390
1391 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1392 ( online_report_id
1393 , last_update_login
1394 , last_updated_by
1395 , last_update_date
1396 , created_by
1397 , creation_date
1398 , line_num
1399 , shipment_num
1400 , distribution_num
1401 , sequence
1402 , message_name
1403 , text_line
1404 )
1405 SELECT
1406 p_online_report_id
1407 , p_login_id
1408 , p_user_id
1409 , SYSDATE
1410 , p_user_id
1411 , SYSDATE
1412 , LINE.line_num
1413 , PRICE_BREAK.shipment_num
1414 , 0
1415 , x_sequence + rownum
1416 , l_message_name
1417 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1418 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
1419 || l_text,1,240)
1420 FROM
1421 PO_LINE_LOCATIONS_ALL PRICE_BREAK
1422 , PO_LINES_GT LINE
1423 , PO_HEADERS_GT HEADER
1424 WHERE
1425 PRICE_BREAK.po_line_id = LINE.po_line_id
1426 AND PRICE_BREAK.start_date < HEADER.start_date
1427 ;
1428
1429 x_sequence := x_sequence + SQL%ROWCOUNT;
1430
1431
1432 -- Check that the price break's Effective To date
1433 -- is before the Effective To date of the agreement.
1434
1435 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES;
1436
1437 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1438
1439 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1440 ( online_report_id
1441 , last_update_login
1442 , last_updated_by
1443 , last_update_date
1444 , created_by
1445 , creation_date
1446 , line_num
1447 , shipment_num
1448 , distribution_num
1449 , sequence
1450 , message_name
1451 , text_line
1452 )
1453 SELECT
1454 p_online_report_id
1455 , p_login_id
1456 , p_user_id
1457 , SYSDATE
1458 , p_user_id
1459 , SYSDATE
1460 , LINE.line_num
1461 , PRICE_BREAK.shipment_num
1462 , 0
1463 , x_sequence + rownum
1464 , l_message_name
1465 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1466 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
1467 || l_text,1,240)
1468 FROM
1469 PO_LINE_LOCATIONS_ALL PRICE_BREAK
1470 , PO_LINES_GT LINE
1471 , PO_HEADERS_GT HEADER
1472 WHERE
1473 PRICE_BREAK.po_line_id = LINE.po_line_id
1474 AND PRICE_BREAK.end_date > HEADER.end_date
1475 ;
1476
1477 x_sequence := x_sequence + SQL%ROWCOUNT;
1478
1479
1480 -- Check that the price break's Effective To date
1481 -- is after the Effective From date of the agreement.
1482
1483 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES5;
1484
1485 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1486
1487 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1488 ( online_report_id
1489 , last_update_login
1490 , last_updated_by
1491 , last_update_date
1492 , created_by
1493 , creation_date
1494 , line_num
1495 , shipment_num
1496 , distribution_num
1497 , sequence
1498 , message_name
1499 , text_line
1500 )
1501 SELECT
1502 p_online_report_id
1503 , p_login_id
1504 , p_user_id
1505 , SYSDATE
1506 , p_user_id
1507 , SYSDATE
1508 , LINE.line_num
1509 , PRICE_BREAK.shipment_num
1510 , 0
1511 , x_sequence + rownum
1512 , l_message_name
1513 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1514 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
1515 || l_text,1,240)
1516 FROM
1517 PO_LINE_LOCATIONS_ALL PRICE_BREAK
1518 , PO_LINES_GT LINE
1519 , PO_HEADERS_GT HEADER
1520 WHERE
1521 PRICE_BREAK.po_line_id = LINE.po_line_id
1522 AND PRICE_BREAK.end_date < HEADER.start_date
1523 ;
1524
1525 x_sequence := x_sequence + SQL%ROWCOUNT;
1526
1527 IF PO_LOG.d_proc THEN
1528 PO_LOG.proc_end(d_mod,'x_sequence',x_sequence);
1529 END IF;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 IF PO_LOG.d_exc THEN
1534 PO_LOG.exc(d_mod,0,NULL);
1535 END IF;
1536 RAISE;
1537
1538 END check_agreement_dates;
1539
1540
1541 -------------------------------------------------------------------------------
1542 -- Ensures that the Buyer is not null.
1543 -------------------------------------------------------------------------------
1544 PROCEDURE agent_id_not_null(
1545 p_header_id_tbl IN PO_TBL_NUMBER
1546 , p_agent_id_tbl IN PO_TBL_NUMBER
1547 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1548 , x_result_type OUT NOCOPY VARCHAR2
1549 )
1550 IS
1551 BEGIN
1552
1553 PO_VALIDATION_HELPER.not_null(
1554 p_calling_module => D_agent_id_not_null
1555 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_agent_id_tbl)
1556 , p_entity_id_tbl => p_header_id_tbl
1557 , p_entity_type => c_entity_type_HEADER
1558 , p_column_name => c_AGENT_ID
1559 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1560 , x_results => x_results
1561 , x_result_type => x_result_type
1562 );
1563
1564 END agent_id_not_null;
1565
1566
1567 -------------------------------------------------------------------------------
1568 -- Ensures that the Default Ship-To / Work Location is not null.
1569 -------------------------------------------------------------------------------
1570 PROCEDURE ship_to_loc_not_null(
1571 p_header_id_tbl IN PO_TBL_NUMBER
1572 , p_ship_to_loc_id_tbl IN PO_TBL_NUMBER
1573 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1574 , x_result_type OUT NOCOPY VARCHAR2
1575 )
1576 IS
1577 BEGIN
1578
1579 PO_VALIDATION_HELPER.not_null(
1580 p_calling_module => D_ship_to_loc_not_null
1581 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ship_to_loc_id_tbl)
1582 , p_entity_id_tbl => p_header_id_tbl
1583 , p_entity_type => c_entity_type_HEADER
1584 , p_column_name => c_SHIP_TO_LOCATION_ID
1585 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1586 , x_results => x_results
1587 , x_result_type => x_result_type
1588 );
1589
1590 END ship_to_loc_not_null;
1591
1592
1593 -------------------------------------------------------------------------------
1594 -- Ensures that the Supplier is not null.
1595 -------------------------------------------------------------------------------
1596 PROCEDURE vendor_id_not_null(
1597 p_header_id_tbl IN PO_TBL_NUMBER
1598 , p_vendor_id_tbl IN PO_TBL_NUMBER
1599 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1600 , x_result_type OUT NOCOPY VARCHAR2
1601 )
1602 IS
1603 BEGIN
1604
1605 PO_VALIDATION_HELPER.not_null(
1606 p_calling_module => D_vendor_id_not_null
1607 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_vendor_id_tbl)
1608 , p_entity_id_tbl => p_header_id_tbl
1609 , p_entity_type => c_entity_type_HEADER
1610 , p_column_name => c_VENDOR_ID
1611 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1612 , x_results => x_results
1613 , x_result_type => x_result_type
1614 );
1615
1616 END vendor_id_not_null;
1617
1618
1619 -------------------------------------------------------------------------------
1620 -- Ensures that the Supplier Site is not null.
1621 -------------------------------------------------------------------------------
1622 PROCEDURE vendor_site_id_not_null(
1623 p_header_id_tbl IN PO_TBL_NUMBER
1624 , p_vendor_site_id_tbl IN PO_TBL_NUMBER
1625 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1626 , x_result_type OUT NOCOPY VARCHAR2
1627 )
1628 IS
1629 BEGIN
1630
1631 PO_VALIDATION_HELPER.not_null(
1632 p_calling_module => D_vendor_site_id_not_null
1633 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_vendor_site_id_tbl)
1634 , p_entity_id_tbl => p_header_id_tbl
1635 , p_entity_type => c_entity_type_HEADER
1636 , p_column_name => c_VENDOR_SITE_ID
1637 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1638 , x_results => x_results
1639 , x_result_type => x_result_type
1640 );
1641
1642 END vendor_site_id_not_null;
1643
1644 --<Begin Bug# 5372769> EXCEPTION WHEN SAVE PO WO/ NUMBER IF DOCUMENT NUMBERING IS SET TO MANUAL
1645 -------------------------------------------------------------------------------
1646 -- Ensures that the Segment1 is not null.
1647 -------------------------------------------------------------------------------
1648 PROCEDURE segment1_not_null(
1649 p_header_id_tbl IN PO_TBL_NUMBER
1650 , p_segment1_tbl IN PO_TBL_VARCHAR30
1651 , p_org_id_tbl IN PO_TBL_NUMBER
1652 , x_result_set_id IN OUT NOCOPY NUMBER
1653 , x_result_type OUT NOCOPY VARCHAR2
1654 )
1655 IS
1656 BEGIN
1657
1658 IF (x_result_set_id IS NULL) THEN
1659 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1660 END IF;
1661
1662 FORALL i IN 1 .. p_header_id_tbl.COUNT
1663 INSERT INTO PO_VALIDATION_RESULTS_GT
1664 ( result_set_id
1665 , entity_type
1666 , entity_id
1667 , message_name
1668 , column_name
1669 )
1670 SELECT
1671 x_result_set_id
1672 , c_entity_type_HEADER
1673 , p_header_id_tbl(i)
1674 , PO_MESSAGE_S.PO_ALL_NOT_NULL
1675 , c_SEGMENT1
1676 FROM
1677 PO_SYSTEM_PARAMETERS_ALL
1678 WHERE
1679 org_id = p_org_id_tbl(i)
1680 AND USER_DEFINED_PO_NUM_CODE = c_MANUAL
1681 AND p_segment1_tbl(i) IS NULL;
1682
1683 IF (SQL%ROWCOUNT > 0) THEN
1684 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1685 ELSE
1686 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1687 END IF;
1688
1689 END segment1_not_null;
1690 --<End 5372769>
1691
1692 END PO_VAL_HEADERS;