[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_HEADERS
Source
1 PACKAGE BODY po_val_headers AS
2 -- $Header: PO_VAL_HEADERS.plb 120.18.12020000.5 2013/04/04 04:42:01 amalick ship $
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 --Mod Project
22 c_MOD_EFFECTIVE_DATE CONSTANT VARCHAR2(30) := 'MOD_EFFECTIVE_DATE';
23 c_CONTROL_ACTION CONSTANT VARCHAR2(30) := 'CONTROL_ACTION';
24
25 c_BLANKET CONSTANT VARCHAR2(30) := 'BLANKET';
26 c_CONTRACT CONSTANT VARCHAR2(30) := 'CONTRACT';
27 c_FINALLY_CLOSED CONSTANT VARCHAR2(30) := 'FINALLY CLOSED';
28 c_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL';
29 c_NUMERIC CONSTANT VARCHAR2(30) := 'NUMERIC';
30 c_PLANNED CONSTANT VARCHAR2(30) := 'PLANNED';
31 c_PO CONSTANT VARCHAR2(30) := 'PO';
32 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
33 c_SUCCESS CONSTANT VARCHAR2(30) := 'SUCCESS';
34 c_User CONSTANT VARCHAR2(30) := 'User';
35 c_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
36
37 -- For CLM PO Fed Fields
38 c_CLM_MAX_ORD_AMT CONSTANT VARCHAR2(30) := 'CLM_MAX_ORDER_AMOUNT';
39 c_CLM_STANDARD_FORM VARCHAR2(30) := 'CLM_STANDARD_FORM';
40 c_CLM_DOCUMENT_FORMAT VARCHAR2(30) :='CLM_DOCUMENT_FORMAT';
41 c_MIN_GUAR_PERC VARCHAR2(30) := 'CLM_MIN_GUAR_AWARD_AMT_PERCENT';
42
43 --CLM PDOI Integration
44 c_CLM_SOURCE_DOCUMENT_ID VARCHAR2(30) := 'CLM_SOURCE_DOCUMENT_ID';
45 c_CLM_AWARD_ADMINISTRATOR VARCHAR2(30) := 'CLM_AWARD_ADMINISTRATOR';
46 c_CLM_CONTRACT_OFFICER VARCHAR2(30) := 'CLM_CONTRACT_OFFICER';
47 c_UMBRELLA_PROGRAM_ID VARCHAR2(30) := 'UMBRELLA_PROGRAM_ID';
48 c_CLM_AWARD_TYPE VARCHAR2(30) := 'CLM_AWARD_TYPE';
49 c_STYLE_ID VARCHAR2(30) := 'STYLE_ID';
50
51 --<Bug 16474640>
52 c_MODIFICATION_NUMBER VARCHAR2(30) := 'MODIFICATION_NUMBER';
53
54 -- The module base for this package.
55 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
56 PO_LOG.get_package_base('PO_VAL_HEADERS');
57
58 -- The module base for the subprogram.
59 D_price_update_tol_ge_zero CONSTANT VARCHAR2(100) :=
60 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_update_tol_ge_zero');
61
62 D_amount_limit_ge_zero CONSTANT VARCHAR2(100) :=
63 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_limit_ge_zero');
64
65 D_amt_limit_ge_amt_agreed CONSTANT VARCHAR2(100) :=
66 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amt_limit_ge_amt_agreed');
67
68 D_amount_agreed_ge_zero CONSTANT VARCHAR2(100) :=
69 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_agreed_ge_zero');
70
71 D_amount_agreed_not_null CONSTANT VARCHAR2(100) :=
72 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_agreed_not_null');
73
74 D_warn_supplier_on_hold CONSTANT VARCHAR2(100) :=
75 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'warn_supplier_on_hold');
76
77 D_rate_gt_zero CONSTANT VARCHAR2(100) :=
78 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'rate_gt_zero');
79
80 D_fax_email_address_valid CONSTANT VARCHAR2(100) :=
81 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'fax_email_address_valid');
82
83 D_rate_combination_valid CONSTANT VARCHAR2(100) :=
84 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'rate_combination_valid');
85
86 D_effective_le_expiration CONSTANT VARCHAR2(100) :=
87 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_le_expiration');
88
89 D_effective_from_le_order_date CONSTANT VARCHAR2(100) :=
90 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_from_le_order_date');
91
92 D_effective_to_ge_order_date CONSTANT VARCHAR2(100) :=
93 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'effective_to_ge_order_date');
94
95 D_contract_start_le_order_date CONSTANT VARCHAR2(100) :=
96 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'contract_start_le_order_date');
97
98 D_contract_end_ge_order_date CONSTANT VARCHAR2(100) :=
99 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'contract_end_ge_order_date');
100
101 D_doc_num_chars_valid CONSTANT VARCHAR2(100) :=
102 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'doc_num_chars_valid');
103
104 D_doc_num_unique CONSTANT VARCHAR2(100) :=
105 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'doc_num_unique');
106
107 D_check_agreement_dates CONSTANT VARCHAR2(100) :=
108 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_agreement_dates');
109
110 D_agent_id_not_null CONSTANT VARCHAR2(100) :=
111 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'agent_id_not_null');
112 D_ship_to_loc_not_null CONSTANT VARCHAR2(100) :=
113 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_to_loc_not_null');
114 D_vendor_id_not_null CONSTANT VARCHAR2(100) :=
115 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_id_not_null');
116 D_vendor_site_id_not_null CONSTANT VARCHAR2(100) :=
117 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_site_id_not_null');
118 D_segment1_not_null CONSTANT VARCHAR2(100) :=
119 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'segment1_not_null');
120
121 --Mod Project
122 D_mod_eff_dt_not_null CONSTANT VARCHAR2(100) :=
123 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'mod_eff_dt_not_null');
124 D_mod_eff_dt_lt_need_by_dt CONSTANT VARCHAR2(100) :=
125 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'mod_eff_dt_lt_need_by_dt');
126 D_mod_no_cancel_shp_complt CONSTANT VARCHAR2(100) :=
127 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'mod_no_cancel_shp_complt');
128
129
130 -- For CLM Project
131 D_clm_eff_from_le_order_date CONSTANT VARCHAR2(100) :=
132 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clm_eff_from_le_order_date');
133 D_max_ceil_awd_amt_gt_min CONSTANT VARCHAR2(100) :=
134 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'max_ceil_awd_amt_gt_min');
135 D_max_ceil_amt_gt_min_ord CONSTANT VARCHAR2(100) :=
136 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'max_ceil_amt_gt_min_ord');
137 D_max_ceil_amt_gt_max_ord CONSTANT VARCHAR2(100) :=
138 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'max_ceil_amt_gt_max_ord');
139 D_min_guar_perc_range_check CONSTANT VARCHAR2(100) :=
140 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'min_guar_perc_range_check');
141 D_max_ord_amt_gt_min CONSTANT VARCHAR2(100) :=
142 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'max_ord_amt_gt_min');
143 D_print_form_format_valid CONSTANT VARCHAR2(100) :=
144 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'print_form_format_valid');
145
146 --CLM PDOI Integration
147 D_validate_clm_standard_form CONSTANT VARCHAR2(100) :=
148 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'valid_clm_standard_form');
149 D_validate_clm_document_format CONSTANT VARCHAR2(100) :=
150 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'valid_clm_document_format');
151 D_validate_source_idv CONSTANT VARCHAR2(100) :=
152 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_source_idv');
153 D_validate_award_admin CONSTANT VARCHAR2(100) :=
154 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_award_admin');
155 D_validate_contract_officer CONSTANT VARCHAR2(100) :=
156 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_contract_officer');
157 D_validate_umbrella_program_id CONSTANT VARCHAR2(100) :=
158 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_umbrella_program_id');
159 D_validate_clm_award_type CONSTANT VARCHAR2(100) :=
160 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clm_award_type');
161 D_validate_style_id CONSTANT VARCHAR2(100) :=
162 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_style_id');
163 D_validate_idv_style_id CONSTANT VARCHAR2(100) :=
164 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_idv_style_id');
165 D_validate_po_idv_vendor_id VARCHAR2(100) :=
166 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_po_idv_vendor_id');
167 D_val_po_idv_vendor_site_id VARCHAR2(100) :=
168 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_po_idv_vendor_site_id');
169 D_validate_mod_number VARCHAR2(100) :=
170 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_mod_number');
171
172 ---------------------------------------------------------------------------
173 -- Checks that the Price Update Tolerance is greater than or equal to zero.
174 -- Agreements only.
175 ---------------------------------------------------------------------------
176 PROCEDURE price_update_tol_ge_zero(
177 p_header_id_tbl IN PO_TBL_NUMBER
178 , p_price_update_tol_tbl IN PO_TBL_NUMBER
179 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
180 , x_result_type OUT NOCOPY VARCHAR2
181 )
182 IS
183 BEGIN
184
185 PO_VALIDATION_HELPER.greater_or_equal_zero(
186 p_calling_module => D_price_update_tol_ge_zero
187 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
188 , p_value_tbl => p_price_update_tol_tbl
189 , p_entity_id_tbl => p_header_id_tbl
190 , p_entity_type => c_entity_type_HEADER
191 , p_column_name => c_PRICE_UPDATE_TOLERANCE
192 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
193 , x_results => x_results
194 , x_result_type => x_result_type
195 );
196
197 END price_update_tol_ge_zero;
198
199
200 ---------------------------------------------------------------------------
201 -- Checks that the Amount Limit is greater than or equal to zero.
202 -- Agreements only.
203 ---------------------------------------------------------------------------
204 PROCEDURE amount_limit_ge_zero(
205 p_header_id_tbl IN PO_TBL_NUMBER
206 , p_amount_limit_tbl IN PO_TBL_NUMBER
207 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
208 , x_result_type OUT NOCOPY VARCHAR2
209 )
210 IS
211 BEGIN
212
213 PO_VALIDATION_HELPER.greater_or_equal_zero(
214 p_calling_module => D_amount_limit_ge_zero
215 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
216 , p_value_tbl => p_amount_limit_tbl
217 , p_entity_id_tbl => p_header_id_tbl
218 , p_entity_type => c_entity_type_HEADER
219 , p_column_name => c_AMOUNT_LIMIT
220 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
221 , x_results => x_results
222 , x_result_type => x_result_type
223 );
224
225 END amount_limit_ge_zero;
226
227
228 ---------------------------------------------------------------------------
229 -- Checks that the Amount Limit is greater than or equal to the Amount Agreed.
230 -- Agreements only.
231 ---------------------------------------------------------------------------
232 PROCEDURE amt_limit_ge_amt_agreed(
233 p_header_id_tbl IN PO_TBL_NUMBER
234 , p_style_id_tbl IN PO_TBL_NUMBER --Bug 12660599
235 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
236 , p_amount_limit_tbl IN PO_TBL_NUMBER
237 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
238 , x_result_type OUT NOCOPY VARCHAR2
239 )
240 IS
241
242 l_is_clm_po VARCHAR2(5) := 'N';
243 l_message_name VARCHAR2(100);
244
245 BEGIN
246
247 --Bug 12660599
248 SELECT Nvl(clm_flag,'N')
249 INTO l_is_clm_po
250 FROM po_doc_style_headers
251 WHERE style_id = p_style_id_tbl(p_style_id_tbl.COUNT);
252
253
254 IF l_is_clm_po LIKE 'Y' THEN
255 l_message_name := PO_MESSAGE_S.PO_IDV_AMT_LIMIT_CK_FAILED;
256 ELSE
257 l_message_name := PO_MESSAGE_S.PO_PO_AMT_LIMIT_CK_FAILED;
258 END IF ;
259 --End Bug 12660599
260
261 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
262 p_calling_module => D_amt_limit_ge_amt_agreed
263 , p_num1_tbl => p_blanket_total_amount_tbl
264 , p_num2_tbl => p_amount_limit_tbl
265 , p_entity_id_tbl => p_header_id_tbl
266 , p_entity_type => c_entity_type_HEADER
267 , p_column_name => c_AMOUNT_LIMIT
268 , p_message_name => l_message_name --Bug 12660599
269 , x_results => x_results
270 , x_result_type => x_result_type
271 );
272
273 END amt_limit_ge_amt_agreed;
274
275
276 ---------------------------------------------------------------------------
277 -- Checks that the Amount Agreed is greater than or equal to zero.
278 -- Agreements only.
279 ---------------------------------------------------------------------------
280 PROCEDURE amount_agreed_ge_zero(
281 p_header_id_tbl IN PO_TBL_NUMBER
282 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
283 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
284 , x_result_type OUT NOCOPY VARCHAR2
285 )
286 IS
287 BEGIN
288
289 PO_VALIDATION_HELPER.greater_or_equal_zero(
290 p_calling_module => D_amount_agreed_ge_zero
291 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
292 , p_value_tbl => p_blanket_total_amount_tbl
293 , p_entity_id_tbl => p_header_id_tbl
294 , p_entity_type => c_entity_type_HEADER
295 , p_column_name => c_BLANKET_TOTAL_AMOUNT
296 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
297 , x_results => x_results
298 , x_result_type => x_result_type
299 );
300
301 END amount_agreed_ge_zero;
302
303
304 ---------------------------------------------------------------------------
305 -- Checks that the Amount Agreed is not null if Amount Limit is not null.
306 -- Agreements only.
307 ---------------------------------------------------------------------------
308 PROCEDURE amount_agreed_not_null(
309 p_header_id_tbl IN PO_TBL_NUMBER
310 , p_blanket_total_amount_tbl IN PO_TBL_NUMBER
311 , p_amount_limit_tbl IN PO_TBL_NUMBER
312 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
313 , x_result_type OUT NOCOPY VARCHAR2
314 )
315 IS
316 d_mod CONSTANT VARCHAR2(100) := D_amount_agreed_not_null;
317 l_results_count NUMBER;
318 BEGIN
319
320 IF PO_LOG.d_proc THEN
321 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
322 PO_LOG.proc_begin(d_mod,'p_blanket_total_amount_tbl',p_blanket_total_amount_tbl);
323 PO_LOG.proc_begin(d_mod,'p_amount_limit_tbl',p_amount_limit_tbl);
324 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
325 END IF;
326
327 IF (x_results IS NULL) THEN
328 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
329 END IF;
330
331 l_results_count := x_results.result_type.COUNT;
332
333 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
334 IF ( p_amount_limit_tbl(i) IS NOT NULL
335 AND p_blanket_total_amount_tbl(i) IS NULL
336 )
337 THEN
338 x_results.add_result(
339 p_entity_type => c_entity_type_HEADER
340 , p_entity_id => p_header_id_tbl(i)
341 , p_column_name => c_BLANKET_TOTAL_AMOUNT
342 , p_message_name => PO_MESSAGE_S.PO_AMT_LMT_NOT_NULL
343 );
344 END IF;
345 END LOOP;
346
347 IF (l_results_count < x_results.result_type.COUNT) THEN
348 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
349 ELSE
350 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
351 END IF;
352
353 IF PO_LOG.d_proc THEN
354 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
355 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
356 END IF;
357
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 RAISE;
364
365 END amount_agreed_not_null;
366
367
368 ---------------------------------------------------------------------------
369 -- Display a warning message if the supplier is on hold.
370 ---------------------------------------------------------------------------
371 PROCEDURE warn_supplier_on_hold(
372 p_header_id_tbl IN PO_TBL_NUMBER
373 , p_vendor_id_tbl IN PO_TBL_NUMBER
374 , x_result_set_id IN OUT NOCOPY NUMBER
375 , x_result_type OUT NOCOPY VARCHAR2
376 )
377 IS
378 d_mod CONSTANT VARCHAR2(100) := D_warn_supplier_on_hold;
379 BEGIN
380
381 IF PO_LOG.d_proc THEN
382 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
383 PO_LOG.proc_begin(d_mod,'p_vendor_id_tbl',p_vendor_id_tbl);
384 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
385 END IF;
386
387 IF (x_result_set_id IS NULL) THEN
388 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
389 END IF;
390
391 FORALL i IN 1 .. p_header_id_tbl.COUNT
392 INSERT INTO PO_VALIDATION_RESULTS_GT
393 ( result_set_id
394 , result_type
395 , entity_type
396 , entity_id
397 , column_name
398 , column_val
399 , message_name
400 )
401 SELECT
402 x_result_set_id
403 , PO_VALIDATIONS.c_result_type_WARNING
404 , c_entity_type_HEADER
405 , p_header_id_tbl(i)
406 , c_VENDOR_ID
407 , TO_CHAR(p_vendor_id_tbl(i))
408 , PO_MESSAGE_S.PO_PO_VENDOR_ON_HOLD
409 FROM
410 PO_VENDORS SUPPLIER
411 WHERE
412 SUPPLIER.vendor_id = p_vendor_id_tbl(i)
413 AND SUPPLIER.hold_flag = 'Y'
414 ;
415
416 IF(SQL%ROWCOUNT > 0) THEN
417 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
418 ELSE
419 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
420 END IF;
421
422 IF PO_LOG.d_proc THEN
423 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
424 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
425 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
426 END IF;
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 IF PO_LOG.d_exc THEN
431 PO_LOG.exc(d_mod,0,NULL);
432 END IF;
433 RAISE;
434
435 END warn_supplier_on_hold;
436
437
438 ---------------------------------------------------------------------------
439 -- Checks that the Rate is greater than zero.
440 -- For Rate Type of User, the Rate is also required (not null),
441 -- but that is handled elsewhere (in the UI).
442 ---------------------------------------------------------------------------
443 PROCEDURE rate_gt_zero(
444 p_header_id_tbl IN PO_TBL_NUMBER
445 , p_rate_tbl IN PO_TBL_NUMBER
446 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
447 , x_result_type OUT NOCOPY VARCHAR2
448 )
449 IS
450 BEGIN
451
452 PO_VALIDATION_HELPER.greater_than_zero(
453 p_calling_module => D_rate_gt_zero
454 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
455 , p_value_tbl => p_rate_tbl
456 , p_entity_id_tbl => p_header_id_tbl
457 , p_entity_type => c_entity_type_HEADER
458 , p_column_name => c_RATE
459 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
460 , x_results => x_results
461 , x_result_type => x_result_type
462 );
463
464 END rate_gt_zero;
465
466
467 ---------------------------------------------------------------------------
468 -- Checks the following are not null, if the currency is different from the
469 -- functional currency:
470 -- 1. Rate Type
471 -- 2. Rate Date
472 -- 3. Rate
473 ---------------------------------------------------------------------------
474 PROCEDURE rate_combination_valid(
475 p_header_id_tbl IN PO_TBL_NUMBER
476 , p_org_id_tbl IN PO_TBL_NUMBER
477 , p_currency_code_tbl IN PO_TBL_VARCHAR30
478 , p_rate_type_tbl IN PO_TBL_VARCHAR30
479 , p_rate_date_tbl IN PO_TBL_DATE
480 , p_rate_tbl IN PO_TBL_NUMBER
481 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
482 , x_result_type OUT NOCOPY VARCHAR2
483 )
484 IS
485 d_mod CONSTANT VARCHAR2(100) := D_rate_combination_valid;
486 l_results_count NUMBER;
487 l_func_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
488 BEGIN
489
490 IF PO_LOG.d_proc THEN
491 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
492 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
493 PO_LOG.proc_begin(d_mod,'p_currency_code_tbl',p_currency_code_tbl);
494 PO_LOG.proc_begin(d_mod,'p_rate_type_tbl',p_rate_type_tbl);
495 PO_LOG.proc_begin(d_mod,'p_rate_date_tbl',p_rate_date_tbl);
496 PO_LOG.proc_begin(d_mod,'p_rate_tbl',p_rate_tbl);
497 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
498 END IF;
499
500 IF (x_results IS NULL) THEN
501 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
502 END IF;
503
504 l_results_count := x_results.result_type.COUNT;
505
506 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
507
508 SELECT
509 BOOKS.currency_code
510 INTO
511 l_func_currency_code
512 FROM
513 FINANCIALS_SYSTEM_PARAMS_ALL FIN_PARAMS
514 , GL_SETS_OF_BOOKS BOOKS
515 WHERE
516 FIN_PARAMS.org_id = p_org_id_tbl(i)
517 AND BOOKS.set_of_books_id = FIN_PARAMS.set_of_books_id
518 ;
519
520 IF (p_currency_code_tbl(i) <> l_func_currency_code OR p_currency_code_tbl(i) IS NULL) THEN
521
522 IF (p_rate_type_tbl(i) IS NULL) THEN
523 x_results.add_result(
524 p_entity_type => c_entity_type_HEADER
525 , p_entity_id => p_header_id_tbl(i)
526 , p_column_name => c_RATE_TYPE
527 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
528 );
529 END IF;
530
531 IF (p_rate_date_tbl(i) IS NULL) THEN
532 x_results.add_result(
533 p_entity_type => c_entity_type_HEADER
534 , p_entity_id => p_header_id_tbl(i)
535 , p_column_name => c_RATE_DATE
536 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
537 );
538 END IF;
539
540 IF (p_rate_type_tbl(i) <> c_User AND p_rate_tbl(i) IS NULL) THEN
541 x_results.add_result(
542 p_entity_type => c_entity_type_HEADER
543 , p_entity_id => p_header_id_tbl(i)
544 , p_column_name => NULL
545 , p_message_name => PO_MESSAGE_S.PO_HTML_NO_RATE_DEFINED
546 );
547 ELSIF (p_rate_tbl(i) IS NULL) THEN
548 x_results.add_result(
549 p_entity_type => c_entity_type_HEADER
550 , p_entity_id => p_header_id_tbl(i)
551 , p_column_name => c_RATE
552 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
553 );
554 END IF;
555
556 END IF;
557
558 END LOOP;
559
560 IF (l_results_count < x_results.result_type.COUNT) THEN
561 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
562 ELSE
563 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
564 END IF;
565
566 IF PO_LOG.d_proc THEN
567 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
568 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
569 END IF;
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 IF PO_LOG.d_exc THEN
574 PO_LOG.exc(d_mod,0,NULL);
575 END IF;
576 RAISE;
577
578 END rate_combination_valid;
579
580
581 ---------------------------------------------------------------------------
582 -- Checks that email address is not null if supplier notification method
583 -- is email and that fax number is not null if supplier notification method
584 -- is fax.
585 ---------------------------------------------------------------------------
586 PROCEDURE fax_email_address_valid(
587 p_header_id_tbl IN PO_TBL_NUMBER
588 , p_supplier_notif_method_tbl IN PO_TBL_VARCHAR30
589 , p_fax_tbl IN PO_TBL_VARCHAR30
590 , p_email_address_tbl IN PO_TBL_VARCHAR2000
591 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
592 , x_result_type OUT NOCOPY VARCHAR2
593 )
594 IS
595 d_mod CONSTANT VARCHAR2(100) := D_fax_email_address_valid;
596 l_results_count NUMBER;
597 BEGIN
598
599 IF PO_LOG.d_proc THEN
600 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
601 PO_LOG.proc_begin(d_mod,'p_supplier_notif_method_tbl',p_supplier_notif_method_tbl);
602 PO_LOG.proc_begin(d_mod,'p_email_address_tbl',p_email_address_tbl);
603 PO_LOG.proc_begin(d_mod,'p_fax_tbl',p_fax_tbl);
604 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
605 END IF;
606
607 IF (x_results IS NULL) THEN
608 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
609 END IF;
610
611 l_results_count := x_results.result_type.COUNT;
612
613 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
614
615 IF ((p_supplier_notif_method_tbl(i) = c_EMAIL) AND
616 (p_email_address_tbl(i) IS NULL)) THEN
617 x_results.add_result(
618 p_entity_type => c_entity_type_HEADER
619 , p_entity_id => p_header_id_tbl(i)
620 , p_column_name => c_EMAIL_ADDRESS
621 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
622 );
623 ELSIF ((p_supplier_notif_method_tbl(i) = c_FAX) AND
624 (p_fax_tbl(i) IS NULL)) THEN
625 x_results.add_result(
626 p_entity_type => c_entity_type_HEADER
627 , p_entity_id => p_header_id_tbl(i)
628 , p_column_name => c_FAX
629 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
630 );
631 END IF;
632
633 END LOOP;
634
635 IF (l_results_count < x_results.result_type.COUNT) THEN
636 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
637 ELSE
638 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
639 END IF;
640
641 IF PO_LOG.d_proc THEN
642 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
643 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
644 END IF;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 IF PO_LOG.d_exc THEN
649 PO_LOG.exc(d_mod,0,NULL);
650 END IF;
651 RAISE;
652
653 END fax_email_address_valid;
654
655
656 ---------------------------------------------------------------------------
657 -- Checks that the Expiration Date is greater than or equal to
658 -- the Effective Date.
659 -- Agreements only.
660 ---------------------------------------------------------------------------
661 PROCEDURE effective_le_expiration(
662 p_header_id_tbl IN PO_TBL_NUMBER
663 , p_start_date_tbl IN PO_TBL_DATE
664 , p_end_date_tbl IN PO_TBL_DATE
665 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
666 , x_result_type OUT NOCOPY VARCHAR2
667 )
668 IS
669 l_message_name VARCHAR2(100);
670 BEGIN
671
672 IF PO_CLM_CLO_UTIL.isCLMInstalled THEN
673 l_message_name := PO_MESSAGE_S.PO_IDV_ORD_ST_DT_LT_END_DT_CLM;
674 ELSE
675 l_message_name := PO_MESSAGE_S.PO_ALL_DATE_BETWEEN_START_END;
676 END IF;
677
678 PO_VALIDATION_HELPER.start_date_le_end_date(
679 p_calling_module => D_effective_le_expiration
680 , p_start_date_tbl => p_start_date_tbl
681 , p_end_date_tbl => p_end_date_tbl
682 , p_entity_id_tbl => p_header_id_tbl
683 , p_entity_type => c_entity_type_HEADER
684 , p_column_name => c_END_DATE
685 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
686 , p_message_name => l_message_name
687 , x_results => x_results
688 , x_result_type => x_result_type
689 );
690
691 END effective_le_expiration;
692
693
694 ---------------------------------------------------------------------------
695 -- Checks that the Effective From Date is less than or equal to
696 -- the Creation Date of any Orders referencing the Agreement.
697 -- Agreements only.
698 ---------------------------------------------------------------------------
699 PROCEDURE effective_from_le_order_date(
700 p_header_id_tbl IN PO_TBL_NUMBER
701 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
702 , p_start_date_tbl IN PO_TBL_DATE
703 , x_result_set_id IN OUT NOCOPY NUMBER
704 , x_result_type OUT NOCOPY VARCHAR2
705 )
706 IS
707 d_mod CONSTANT VARCHAR2(100) := D_effective_from_le_order_date;
708 BEGIN
709
710 IF PO_LOG.d_proc THEN
711 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
712 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
713 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
714 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
715 END IF;
716
717 IF (x_result_set_id IS NULL) THEN
718 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
719 END IF;
720
721 FORALL i IN 1 .. p_header_id_tbl.COUNT
722 INSERT INTO PO_VALIDATION_RESULTS_GT
723 (
724 result_set_id
725 , entity_type
726 , entity_id
727 , column_name
728 , column_val
729 , message_name
730 )
731 SELECT
732 x_result_set_id
733 , c_entity_type_HEADER
734 , p_header_id_tbl(i)
735 , c_START_DATE
736 , TO_CHAR(p_start_date_tbl(i))
737 , PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR
738 FROM DUAL
739 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
740 AND EXISTS
741 (
742 SELECT NULL
743 FROM PO_LINES_ALL ORDER_LINE
744 WHERE ORDER_LINE.from_header_id = p_header_id_tbl(i)
745 AND p_start_date_tbl(i) > ORDER_LINE.creation_date
746 );
747
748 IF (SQL%ROWCOUNT > 0) THEN
749 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
750 ELSE
751 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
752 END IF;
753
754 IF PO_LOG.d_proc THEN
755 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
756 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
757 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
758 END IF;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762 IF PO_LOG.d_exc THEN
763 PO_LOG.exc(d_mod,0,NULL);
764 END IF;
765 RAISE;
766
767 END effective_from_le_order_date;
768
769
770 ---------------------------------------------------------------------------
771 -- Checks that the CLM Effective From Date is less than or equal to
772 -- the order start Date
773 ---------------------------------------------------------------------------
774 PROCEDURE clm_eff_from_le_order_date(
775 p_header_id_tbl IN PO_TBL_NUMBER
776 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
777 , p_start_date_tbl IN PO_TBL_DATE
778 , p_clm_effective_date_tbl IN PO_TBL_DATE
779 , x_result_set_id IN OUT NOCOPY NUMBER
780 , x_result_type OUT NOCOPY VARCHAR2
781 )
782 IS
783 d_mod CONSTANT VARCHAR2(100) := D_clm_eff_from_le_order_date;
784 BEGIN
785
786 IF PO_LOG.d_proc THEN
787 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
788 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
789 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
790 PO_LOG.proc_begin(d_mod,'p_clm_effective_date_tbl',p_clm_effective_date_tbl);
791 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
792 END IF;
793
794 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
795 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
796 RETURN;
797 END IF;
798
799
800 IF (x_result_set_id IS NULL) THEN
801 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
802 END IF;
803
804 FORALL i IN 1 .. p_header_id_tbl.COUNT
805 INSERT INTO PO_VALIDATION_RESULTS_GT
806 (
807 result_set_id
808 , entity_type
809 , entity_id
810 , column_name
811 , column_val
812 , message_name
813 )
814 SELECT
815 x_result_set_id
816 , c_entity_type_HEADER
817 , p_header_id_tbl(i)
818 , c_START_DATE
819 , TO_CHAR(p_start_date_tbl(i))
820 , PO_MESSAGE_S.PO_IDV_EFF_DT_LT_ORD_ST_DT_CLM
821 FROM DUAL
822 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
823 AND p_start_date_tbl(i) < p_clm_effective_date_tbl(i);
824
825 IF (SQL%ROWCOUNT > 0) THEN
826 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
827 ELSE
828 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
829 END IF;
830
831 IF PO_LOG.d_proc THEN
832 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
833 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
834 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
835 END IF;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 IF PO_LOG.d_exc THEN
840 PO_LOG.exc(d_mod,0,NULL);
841 END IF;
842 RAISE;
843 END clm_eff_from_le_order_date;
844
845 ---------------------------------------------------------------------------
846 -- Checks that the CLM amount limit is less than or equal to
847 -- the minimum guarantee award amount
848 ---------------------------------------------------------------------------
849 PROCEDURE max_ceil_awd_amt_gt_min(
850 p_header_id_tbl IN PO_TBL_NUMBER
851 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
852 , p_amount_limit_tbl IN PO_TBL_NUMBER
853 , p_min_grnt_awd_amt_tbl IN PO_TBL_NUMBER
854 , x_result_set_id IN OUT NOCOPY NUMBER
855 , x_result_type OUT NOCOPY VARCHAR2
856 )
857 IS
858 d_mod CONSTANT VARCHAR2(100) := D_max_ceil_awd_amt_gt_min;
859 BEGIN
860
861 IF PO_LOG.d_proc THEN
862 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
863 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
864 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_amount_limit_tbl);
865 PO_LOG.proc_begin(d_mod,'p_clm_effective_date_tbl',p_min_grnt_awd_amt_tbl);
866 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
867 END IF;
868
869 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
870 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
871 RETURN;
872 END IF;
873
874 IF (x_result_set_id IS NULL) THEN
875 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
876 END IF;
877
878 FORALL i IN 1 .. p_header_id_tbl.COUNT
879 INSERT INTO PO_VALIDATION_RESULTS_GT
880 (
881 result_set_id
882 , entity_type
883 , entity_id
884 , column_name
885 , column_val
886 , message_name
887 )
888 SELECT
889 x_result_set_id
890 , c_entity_type_HEADER
891 , p_header_id_tbl(i)
892 , c_AMOUNT_LIMIT
893 , TO_CHAR(p_amount_limit_tbl(i))
894 , PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_RLSD_CLM
895 FROM DUAL
896 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
897 AND p_amount_limit_tbl(i) < p_min_grnt_awd_amt_tbl(i);
898
899 IF (SQL%ROWCOUNT > 0) THEN
900 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
901 ELSE
902 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
903 END IF;
904
905 IF PO_LOG.d_proc THEN
906 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
907 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
908 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
909 END IF;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF PO_LOG.d_exc THEN
914 PO_LOG.exc(d_mod,0,NULL);
915 END IF;
916 RAISE;
917 END max_ceil_awd_amt_gt_min;
918
919
920 ---------------------------------------------------------------------------
921 -- Checks that Maximum Ceiling Award Amount >= Minimum Per Order Amount
922 ---------------------------------------------------------------------------
923 PROCEDURE max_ceil_amt_gt_min_ord (
924 p_header_id_tbl IN PO_TBL_NUMBER
925 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
926 , p_amount_limit_tbl IN PO_TBL_NUMBER
927 , p_min_order_amt_tbl IN PO_TBL_NUMBER
928 , x_result_set_id IN OUT NOCOPY NUMBER
929 , x_result_type OUT NOCOPY VARCHAR2
930 )
931 IS
932 d_mod CONSTANT VARCHAR2(100) := D_max_ceil_amt_gt_min_ord;
933 BEGIN
934
935 IF PO_LOG.d_proc THEN
936 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
937 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
938 PO_LOG.proc_begin(d_mod,'p_amount_limit_tbl',p_amount_limit_tbl);
939 PO_LOG.proc_begin(d_mod,'p_min_order_amt_tbl',p_min_order_amt_tbl);
940 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
941 END IF;
942
943 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
944 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
945 RETURN;
946 END IF;
947
948 IF (x_result_set_id IS NULL) THEN
949 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
950 END IF;
951
952 FORALL i IN 1 .. p_header_id_tbl.COUNT
953 INSERT INTO PO_VALIDATION_RESULTS_GT
954 (
955 result_set_id
956 , entity_type
957 , entity_id
958 , column_name
959 , column_val
960 , message_name
961 )
962 SELECT
963 x_result_set_id
964 , c_entity_type_HEADER
965 , p_header_id_tbl(i)
966 , c_AMOUNT_LIMIT
967 , TO_CHAR(p_amount_limit_tbl(i))
968 , PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_MNPM_CLM
969 FROM DUAL
970 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
971 AND p_amount_limit_tbl(i) < p_min_order_amt_tbl(i);
972
973 IF (SQL%ROWCOUNT > 0) THEN
974 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
975 ELSE
976 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
977 END IF;
978
979 IF PO_LOG.d_proc THEN
980 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
981 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
982 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 IF PO_LOG.d_exc THEN
988 PO_LOG.exc(d_mod,0,NULL);
989 END IF;
990 RAISE;
991 END max_ceil_amt_gt_min_ord;
992
993 ---------------------------------------------------------------------------
994 -- Checks that Maximum Ceiling Award Amount >= Maximum Per Order Amount
995 ---------------------------------------------------------------------------
996 PROCEDURE max_ceil_amt_gt_max_ord (
997 p_header_id_tbl IN PO_TBL_NUMBER
998 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
999 , p_amount_limit_tbl IN PO_TBL_NUMBER
1000 , p_max_order_amt_tbl IN PO_TBL_NUMBER
1001 , x_result_set_id IN OUT NOCOPY NUMBER
1002 , x_result_type OUT NOCOPY VARCHAR2
1003 )
1004 IS
1005 d_mod CONSTANT VARCHAR2(100) := D_max_ceil_amt_gt_max_ord;
1006 BEGIN
1007
1008 IF PO_LOG.d_proc THEN
1009 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1010 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1011 PO_LOG.proc_begin(d_mod,'p_amount_limit_tbl',p_amount_limit_tbl);
1012 PO_LOG.proc_begin(d_mod,'p_max_order_amt_tbl',p_max_order_amt_tbl);
1013 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1014 END IF;
1015
1016 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1017 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1018 RETURN;
1019 END IF;
1020
1021 IF (x_result_set_id IS NULL) THEN
1022 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1023 END IF;
1024
1025 FORALL i IN 1 .. p_header_id_tbl.COUNT
1026 INSERT INTO PO_VALIDATION_RESULTS_GT
1027 (
1028 result_set_id
1029 , entity_type
1030 , entity_id
1031 , column_name
1032 , column_val
1033 , message_name
1034 )
1035 SELECT
1036 x_result_set_id
1037 , c_entity_type_HEADER
1038 , p_header_id_tbl(i)
1039 , c_AMOUNT_LIMIT
1040 , TO_CHAR(p_amount_limit_tbl(i))
1041 , PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_MXPM_CLM
1042 FROM DUAL
1043 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
1044 AND p_amount_limit_tbl(i) < p_max_order_amt_tbl(i);
1045
1046 IF (SQL%ROWCOUNT > 0) THEN
1047 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1048 ELSE
1049 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1050 END IF;
1051
1052 IF PO_LOG.d_proc THEN
1053 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1054 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1055 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 IF PO_LOG.d_exc THEN
1061 PO_LOG.exc(d_mod,0,NULL);
1062 END IF;
1063 RAISE;
1064 END max_ceil_amt_gt_max_ord;
1065
1066
1067 ---------------------------------------------------------------------------
1068 -- Checks that the Minimum Guarantee Award Amount Percentage between 0 and 100
1069 ---------------------------------------------------------------------------
1070 PROCEDURE min_guar_perc_range_check(
1071 p_header_id_tbl IN PO_TBL_NUMBER
1072 , p_min_guar_perc_tbl IN PO_TBL_NUMBER
1073 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1074 , x_result_type OUT NOCOPY VARCHAR2
1075 )
1076 IS
1077 d_mod CONSTANT VARCHAR2(100) := D_min_guar_perc_range_check;
1078 BEGIN
1079
1080 IF PO_LOG.d_proc THEN
1081 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1082 PO_LOG.proc_begin(d_mod,'p_min_guar_perc_tbl',p_min_guar_perc_tbl);
1083 END IF;
1084
1085 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1086 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1087 RETURN;
1088 END IF;
1089
1090 PO_VALIDATION_HELPER.within_percentage_range(
1091 p_calling_module => D_min_guar_perc_range_check
1092 ,p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1093 ,p_value_tbl => p_min_guar_perc_tbl
1094 ,p_entity_id_tbl => p_header_id_tbl
1095 ,p_entity_type => c_ENTITY_TYPE_HEADER
1096 ,p_column_name => c_MIN_GUAR_PERC
1097 ,p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
1098 ,x_results => x_results
1099 ,x_result_type => x_result_type
1100 );
1101
1102 END min_guar_perc_range_check;
1103
1104
1105 ---------------------------------------------------------------------------
1106 -- Checks that the CLM minimum order amount should be less than maximum order amount
1107 ---------------------------------------------------------------------------
1108 PROCEDURE max_ord_amt_gt_min(
1109 p_header_id_tbl IN PO_TBL_NUMBER
1110 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1111 , p_min_order_amt_tbl IN PO_TBL_NUMBER
1112 , p_max_order_amt_tbl IN PO_TBL_NUMBER
1113 , x_result_set_id IN OUT NOCOPY NUMBER
1114 , x_result_type OUT NOCOPY VARCHAR2
1115 )
1116 IS
1117 d_mod CONSTANT VARCHAR2(100) := D_max_ord_amt_gt_min;
1118 BEGIN
1119
1120 IF PO_LOG.d_proc THEN
1121 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1122 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1123 PO_LOG.proc_begin(d_mod,'p_min_order_amt_tbl',p_min_order_amt_tbl);
1124 PO_LOG.proc_begin(d_mod,'p_max_order_amt_tbl',p_max_order_amt_tbl);
1125 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1126 END IF;
1127
1128 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1129 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1130 RETURN;
1131 END IF;
1132
1133 IF (x_result_set_id IS NULL) THEN
1134 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1135 END IF;
1136
1137 FORALL i IN 1 .. p_header_id_tbl.COUNT
1138 INSERT INTO PO_VALIDATION_RESULTS_GT
1139 (
1140 result_set_id
1141 , entity_type
1142 , entity_id
1143 , column_name
1144 , column_val
1145 , message_name
1146 )
1147 SELECT
1148 x_result_set_id
1149 , c_entity_type_HEADER
1150 , p_header_id_tbl(i)
1151 , c_CLM_MAX_ORD_AMT
1152 , TO_CHAR(p_max_order_amt_tbl(i))
1153 , PO_MESSAGE_S.PO_IDV_MX_OD_AMT_GT_MN_OD_CLM
1154 FROM DUAL
1155 WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
1156 AND p_max_order_amt_tbl(i) < p_min_order_amt_tbl(i);
1157
1158 IF (SQL%ROWCOUNT > 0) THEN
1159 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1160 ELSE
1161 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1162 END IF;
1163
1164 IF PO_LOG.d_proc THEN
1165 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1166 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1167 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 IF PO_LOG.d_exc THEN
1173 PO_LOG.exc(d_mod,0,NULL);
1174 END IF;
1175 RAISE;
1176 END max_ord_amt_gt_min;
1177
1178
1179 ---------------------------------------------------------------------------
1180 -- For CLM standard form and document format cannot be null
1181 ---------------------------------------------------------------------------
1182 PROCEDURE print_form_format_valid(
1183 p_header_id_tbl IN PO_TBL_NUMBER
1184 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1185 , p_style_id_tbl IN PO_TBL_NUMBER
1186 , p_draft_type_tbl IN PO_TBL_VARCHAR30
1187 , p_clm_standard_form_tbl IN PO_TBL_VARCHAR2000
1188 , p_document_format_tbl IN PO_TBL_VARCHAR2000
1189 , p_clm_src_doc_id_tbl IN PO_TBL_NUMBER
1190 , p_clm_ext_idv_tbl IN PO_TBL_VARCHAR2000
1191 , p_clm_award_type_tbl IN PO_TBL_VARCHAR30 -- bug 10389450
1192 , x_result_set_id IN OUT NOCOPY NUMBER
1193 , x_result_type OUT NOCOPY VARCHAR2
1194 )
1195 IS
1196 d_mod CONSTANT VARCHAR2(100) := D_print_form_format_valid;
1197 BEGIN
1198
1199 IF PO_LOG.d_proc THEN
1200 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1201 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1202 PO_LOG.proc_begin(d_mod,'p_clm_standard_form_tbl',p_clm_standard_form_tbl);
1203 PO_LOG.proc_begin(d_mod,'p_document_format_tbl',p_document_format_tbl);
1204 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1205 END IF;
1206
1207 IF (x_result_set_id IS NULL) THEN
1208 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1209 END IF;
1210
1211 FORALL i IN 1 .. p_header_id_tbl.COUNT
1212 INSERT INTO PO_VALIDATION_RESULTS_GT
1213 (
1214 result_set_id
1215 , entity_type
1216 , entity_id
1217 , column_name
1218 , column_val
1219 , message_name
1220 )
1221 SELECT
1222 x_result_set_id
1223 , c_entity_type_HEADER
1224 , p_header_id_tbl(i)
1225 , decode(nvl(p_document_format_tbl(i), '*'), '*', c_CLM_DOCUMENT_FORMAT, c_CLM_STANDARD_FORM)
1226 , null
1227 , PO_MESSAGE_S.PO_PRINT_FORM_FORMAT_INVALID
1228 FROM PO_DOC_STYLE_HEADERS PDSH
1229 WHERE PDSH.style_id = p_style_id_tbl(i)
1230 AND nvl(clm_flag, 'N') ='Y'
1231 AND NOT EXISTS (
1232 SELECT 1
1233 FROM po_print_form_formats ppff
1234 WHERE ppff.document_type = decode(nvl(p_draft_type_tbl(i), '*'),
1235 'MOD', 'PO_MOD_STD_FORM', -- bug 10389450 start
1236 decode(p_type_lookup_code_tbl(i), c_STANDARD,
1237 decode(p_clm_award_type_tbl(i),
1238 'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
1239 'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
1240 'PO_AWARD_STD_FORM'),
1241 'PO_IDV_STD_FORM')) -- bug 10389450 end
1242 AND ppff.standard_form = p_clm_standard_form_tbl(i)
1243 AND ppff.document_format = p_document_format_tbl(i)
1244 AND trunc(sysdate) <= trunc(nvl(ppff.inactive_date, sysdate +1))
1245 )
1246 AND Nvl(p_draft_type_tbl(i),'*') <> 'PAR'; --<PAR Project>
1247
1248 IF (SQL%ROWCOUNT > 0) THEN
1249 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1250 ELSE
1251 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1252 END IF;
1253
1254 IF PO_LOG.d_proc THEN
1255 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1256 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1257 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1258 END IF;
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 IF PO_LOG.d_exc THEN
1263 PO_LOG.exc(d_mod,0,NULL);
1264 END IF;
1265 RAISE;
1266 END print_form_format_valid;
1267
1268 ---------------------------------------------------------------------------
1269 -- Checks that the Effective To Date is greater than or equal to
1270 -- the Creation Date of any Orders referencing the Agreement.
1271 -- Agreements only.
1272 ---------------------------------------------------------------------------
1273 PROCEDURE effective_to_ge_order_date(
1274 p_header_id_tbl IN PO_TBL_NUMBER
1275 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1276 , p_end_date_tbl IN PO_TBL_DATE
1277 , x_result_set_id IN OUT NOCOPY NUMBER
1278 , x_result_type OUT NOCOPY VARCHAR2
1279 )
1280 IS
1281 d_mod CONSTANT VARCHAR2(100) := D_effective_to_ge_order_date;
1282 BEGIN
1283
1284 IF PO_LOG.d_proc THEN
1285 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1286 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1287 PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
1288 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1289 END IF;
1290
1291 IF (x_result_set_id IS NULL) THEN
1292 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1293 END IF;
1294
1295 FORALL i IN 1 .. p_header_id_tbl.COUNT
1296 INSERT INTO PO_VALIDATION_RESULTS_GT
1297 ( result_set_id
1298 , entity_type
1299 , entity_id
1300 , column_name
1301 , column_val
1302 , message_name
1303 )
1304 SELECT
1305 x_result_set_id
1306 , c_entity_type_HEADER
1307 , p_header_id_tbl(i)
1308 , c_END_DATE
1309 , TO_CHAR(p_end_date_tbl(i))
1310 , PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR
1311 FROM DUAL
1312 WHERE
1313 p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
1314 AND EXISTS
1315 ( SELECT NULL
1316 FROM
1317 PO_LINES_ALL ORDER_LINE
1318 , PO_HEADERS_ALL ORDER_HEADER
1319 WHERE
1320 ORDER_LINE.from_header_id = p_header_id_tbl(i)
1321 -- Bug # 13550798 Changed logic based on approved_date
1322 --AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
1323 AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
1324 AND ORDER_HEADER.approved_date IS NOT NULL
1325 AND TRUNC(p_end_date_tbl(i)+ nvl(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0)) < TRUNC(ORDER_HEADER.approved_date)
1326 AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
1327 AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
1328 )
1329 ;
1330
1331 IF (SQL%ROWCOUNT > 0) THEN
1332 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1333 ELSE
1334 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1335 END IF;
1336
1337 IF PO_LOG.d_proc THEN
1338 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1339 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1340 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1341 END IF;
1342
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 IF PO_LOG.d_exc THEN
1346 PO_LOG.exc(d_mod,0,NULL);
1347 END IF;
1348 RAISE;
1349
1350 END effective_to_ge_order_date;
1351
1352
1353 ---------------------------------------------------------------------------
1354 -- Checks that the Effective From Date is less than or equal to
1355 -- the Creation Date of any Orders referencing the Contract.
1356 ---------------------------------------------------------------------------
1357 PROCEDURE contract_start_le_order_date(
1358 p_header_id_tbl IN PO_TBL_NUMBER
1359 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1360 , p_start_date_tbl IN PO_TBL_DATE
1361 , x_result_set_id IN OUT NOCOPY NUMBER
1362 , x_result_type OUT NOCOPY VARCHAR2
1363 )
1364 IS
1365 d_mod CONSTANT VARCHAR2(100) := D_contract_start_le_order_date;
1366 BEGIN
1367
1368 IF PO_LOG.d_proc THEN
1369 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1370 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1371 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
1372 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1373 END IF;
1374
1375 IF (x_result_set_id IS NULL) THEN
1376 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1377 END IF;
1378
1379 FORALL i IN 1 .. p_header_id_tbl.COUNT
1380 INSERT INTO PO_VALIDATION_RESULTS_GT
1381 ( result_set_id
1382 , entity_type
1383 , entity_id
1384 , column_name
1385 , column_val
1386 , message_name
1387 )
1388 SELECT
1389 x_result_set_id
1390 , c_entity_type_HEADER
1391 , p_header_id_tbl(i)
1392 , c_START_DATE
1393 , TO_CHAR(p_start_date_tbl(i))
1394 , PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR
1395 FROM DUAL
1396 WHERE
1397 p_type_lookup_code_tbl(i) = c_CONTRACT
1398 AND EXISTS
1399 ( SELECT NULL
1400 FROM
1401 PO_LINES_ALL ORDER_LINE
1402 , PO_HEADERS_ALL ORDER_HEADER
1403 WHERE
1404 ORDER_LINE.contract_id = p_header_id_tbl(i)
1405 AND TRUNC(p_start_date_tbl(i)) > ORDER_LINE.creation_date
1406 AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
1407 AND ORDER_HEADER.approved_date IS NOT NULL
1408 AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
1409 AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
1410 )
1411 ;
1412
1413 -- TODO: check with PM about differences in Agreements / Contracts checks.
1414
1415 IF (SQL%ROWCOUNT > 0) THEN
1416 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1417 ELSE
1418 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1419 END IF;
1420
1421 IF PO_LOG.d_proc THEN
1422 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1423 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1424 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1425 END IF;
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 IF PO_LOG.d_exc THEN
1430 PO_LOG.exc(d_mod,0,NULL);
1431 END IF;
1432 RAISE;
1433
1434 END contract_start_le_order_date;
1435
1436
1437 ---------------------------------------------------------------------------
1438 -- Checks that the Effective To Date is greater than or equal to
1439 -- the Creation Date of any Orders referencing the Contract.
1440 ---------------------------------------------------------------------------
1441 PROCEDURE contract_end_ge_order_date(
1442 p_header_id_tbl IN PO_TBL_NUMBER
1443 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1444 , p_end_date_tbl IN PO_TBL_DATE
1445 , x_result_set_id IN OUT NOCOPY NUMBER
1446 , x_result_type OUT NOCOPY VARCHAR2
1447 )
1448 IS
1449 d_mod CONSTANT VARCHAR2(100) := D_contract_end_ge_order_date;
1450 BEGIN
1451
1452 IF PO_LOG.d_proc THEN
1453 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1454 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1455 PO_LOG.proc_begin(d_mod,'p_end_date_tbl',p_end_date_tbl);
1456 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1457 END IF;
1458
1459 IF (x_result_set_id IS NULL) THEN
1460 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1461 END IF;
1462
1463 FORALL i IN 1 .. p_header_id_tbl.COUNT
1464 INSERT INTO PO_VALIDATION_RESULTS_GT
1465 ( result_set_id
1466 , entity_type
1467 , entity_id
1468 , column_name
1469 , column_val
1470 , message_name
1471 )
1472 SELECT
1473 x_result_set_id
1474 , c_entity_type_HEADER
1475 , p_header_id_tbl(i)
1476 , c_END_DATE
1477 , TO_CHAR(p_end_date_tbl(i))
1478 , PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR
1479 FROM DUAL
1480 WHERE
1481 p_type_lookup_code_tbl(i) = c_CONTRACT
1482 AND EXISTS
1483 ( SELECT NULL
1484 FROM
1485 PO_LINES_ALL ORDER_LINE
1486 , PO_HEADERS_ALL ORDER_HEADER
1487 WHERE
1488 ORDER_LINE.contract_id = p_header_id_tbl(i)
1489 -- Bug # 13550798 Changed logic based on approved_date
1490 -- AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
1491 AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
1492 AND ORDER_HEADER.approved_date IS NOT NULL
1493 AND TRUNC(p_end_date_tbl(i)+ nvl(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0)) < TRUNC(ORDER_HEADER.approved_date)
1494 AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
1495 AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
1496 )
1497 ;
1498
1499 IF (SQL%ROWCOUNT > 0) THEN
1500 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1501 ELSE
1502 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1503 END IF;
1504
1505 IF PO_LOG.d_proc THEN
1506 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1507 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1508 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1509 END IF;
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 IF PO_LOG.d_exc THEN
1514 PO_LOG.exc(d_mod,0,NULL);
1515 END IF;
1516 RAISE;
1517
1518 END contract_end_ge_order_date;
1519
1520
1521 ---------------------------------------------------------------------------
1522 -- Checks that the Document Number is numeric, if required.
1523 ---------------------------------------------------------------------------
1524 PROCEDURE doc_num_chars_valid(
1525 p_header_id_tbl IN PO_TBL_NUMBER
1526 , p_org_id_tbl IN PO_TBL_NUMBER
1527 , p_style_id_tbl IN PO_TBL_NUMBER
1528 , p_segment1_tbl IN PO_TBL_VARCHAR30
1529 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1530 , x_result_type OUT NOCOPY VARCHAR2
1531 )
1532 IS
1533 d_mod CONSTANT VARCHAR2(100) := D_doc_num_chars_valid;
1534
1535 l_results_count NUMBER;
1536 l_data_key NUMBER;
1537 l_header_id_tbl PO_TBL_NUMBER;
1538 l_segment1_tbl PO_TBL_VARCHAR30;
1539 l_num_test NUMBER;
1540 L_TEMP_SEGMENT PO_HEADERS_ALL.SEGMENT1%TYPE;
1541 BEGIN
1542
1543 IF PO_LOG.d_proc THEN
1544 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1545 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1546 PO_LOG.proc_begin(d_mod,'p_segment1_tbl',p_segment1_tbl);
1547 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1548 END IF;
1549
1550 IF (x_results IS NULL) THEN
1551 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1552 END IF;
1553
1554 l_results_count := x_results.result_type.COUNT;
1555
1556 l_data_key := PO_CORE_S.get_session_gt_nextval();
1557
1558 FORALL i IN 1 .. p_header_id_tbl.COUNT
1559 INSERT INTO PO_SESSION_GT
1560 ( key
1561 , num1
1562 , num2
1563 , num3
1564 , char1
1565 )
1566 VALUES
1567 ( l_data_key
1568 , p_header_id_tbl(i)
1569 , p_org_id_tbl(i)
1570 , p_style_id_tbl(i)
1571 , p_segment1_tbl(i)
1572 )
1573 ;
1574
1575
1576 SELECT
1577 num1
1578 , char1
1579 BULK COLLECT INTO
1580 l_header_id_tbl
1581 , l_segment1_tbl
1582 FROM
1583 PO_SESSION_GT SES
1584 , PO_SYSTEM_PARAMETERS_ALL PARAMS
1585 , PO_DOC_STYLE_HEADERS PDSH
1586 WHERE
1587 SES.key = l_data_key
1588 AND SES.num2 = PARAMS.org_id
1589 AND PARAMS.manual_po_num_type = c_NUMERIC
1590 AND PDSH.style_id = SES.num3
1591 AND NVL(PDSH.clm_flag,'N') <> 'Y'
1592 ;
1593
1594
1595 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
1596 BEGIN
1597 /* Bug 10233230 Start
1598 Added below logic to ensure that the document numbering validation will
1599 be fired in case of new purchase orders only.
1600 We are selecting the segment1 from po_headers_all using po_header_id and segment1.
1601 If it finds any record then we need not do the document numbering validation as it
1602 is already created and we are updating now. If there is no such record in
1603 po_headers_all then l_temp_segment value will be null and validation will be fired.
1604 */
1605
1606 BEGIN
1607 SELECT SEGMENT1 INTO L_TEMP_SEGMENT
1608 FROM PO_HEADERS_ALL
1609 WHERE
1610 SEGMENT1=l_segment1_tbl(i)
1611 AND
1612 PO_HEADER_ID=l_header_id_tbl(i);
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 L_TEMP_SEGMENT:=NULL;
1616 END;
1617
1618 /* Bug 10233230 End */
1619 IF L_TEMP_SEGMENT IS NULL THEN
1620 l_num_test := TO_NUMBER(l_segment1_tbl(i));
1621 END IF;
1622 EXCEPTION
1623 WHEN VALUE_ERROR THEN
1624 x_results.add_result(
1625 p_entity_type => c_entity_type_HEADER
1626 , p_entity_id => l_header_id_tbl(i)
1627 , p_column_name => c_SEGMENT1
1628 , p_column_val => l_segment1_tbl(i)
1629 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_NUMERIC
1630 );
1631 END;
1632 END LOOP;
1633
1634 IF (l_results_count < x_results.result_type.COUNT) THEN
1635 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1636 ELSE
1637 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1638 END IF;
1639
1640 IF PO_LOG.d_proc THEN
1641 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1642 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1643 END IF;
1644
1645 EXCEPTION
1646 WHEN OTHERS THEN
1647 IF PO_LOG.d_exc THEN
1648 PO_LOG.exc(d_mod,0,NULL);
1649 END IF;
1650 RAISE;
1651
1652 END doc_num_chars_valid;
1653
1654
1655 ---------------------------------------------------------------------------
1656 -- Checks that the Document Number is unique within the Org.
1657 ---------------------------------------------------------------------------
1658 PROCEDURE doc_num_unique(
1659 p_header_id_tbl IN PO_TBL_NUMBER
1660 , p_org_id_tbl IN PO_TBL_NUMBER
1661 , p_segment1_tbl IN PO_TBL_VARCHAR30
1662 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1663 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1664 , x_result_type OUT NOCOPY VARCHAR2
1665 )
1666 IS
1667 d_mod CONSTANT VARCHAR2(100) := D_doc_num_unique;
1668 d_position NUMBER := 0;
1669
1670 l_results_count NUMBER;
1671
1672 l_data_key NUMBER;
1673 l_header_id_tbl PO_TBL_NUMBER;
1674 l_org_id_tbl PO_TBL_NUMBER;
1675 l_segment1_tbl PO_TBL_VARCHAR30;
1676 l_nonunique_tbl PO_TBL_VARCHAR2000;
1677
1678 l_check_sourcing_flag VARCHAR2(2000);
1679 l_pon_unique_status VARCHAR2(2000);
1680
1681 BEGIN
1682
1683 IF PO_LOG.d_proc THEN
1684 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
1685 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1686 PO_LOG.proc_begin(d_mod,'p_segment1_tbl',p_segment1_tbl);
1687 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1688 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1689 END IF;
1690
1691 d_position := 1;
1692
1693 IF (x_results IS NULL) THEN
1694 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1695 END IF;
1696
1697 l_results_count := x_results.result_type.COUNT;
1698
1699 ----------------------------------------------------------
1700 --
1701 -- The following mapping is used for PO_SESSION_GT in this
1702 -- procedure:
1703 --
1704 -- num1 - po_header_id
1705 -- num2 - org_id
1706 -- index_char1 - segment1
1707 -- char2 - type_lookup_code
1708 --
1709 -- index_char2 - used to identify failure rows
1710 --
1711 ----------------------------------------------------------
1712
1713 l_data_key := PO_CORE_S.get_session_gt_nextval();
1714
1715 -- Only check unsaved headers with manually entered document numbers.
1716
1717 FORALL i IN 1 .. p_header_id_tbl.COUNT
1718 INSERT INTO PO_SESSION_GT
1719 ( key
1720 , num1
1721 , num2
1722 , index_char1
1723 , char2
1724 )
1725 SELECT
1726 l_data_key
1727 , p_header_id_tbl(i)
1728 , p_org_id_tbl(i)
1729 , p_segment1_tbl(i)
1730 , p_type_lookup_code_tbl(i)
1731 FROM
1732 PO_SYSTEM_PARAMETERS_ALL PARAMS
1733 WHERE
1734 PARAMS.org_id = p_org_id_tbl(i)
1735 AND PARAMS.user_defined_po_num_code = c_MANUAL
1736 AND p_segment1_tbl(i) IS NOT NULL
1737 AND NOT EXISTS
1738 ( SELECT NULL
1739 FROM PO_HEADERS_ALL SAVED_HEADER
1740 WHERE SAVED_HEADER.po_header_id = p_header_id_tbl(i)
1741 )
1742 ;
1743
1744 d_position := 100;
1745
1746 IF (SQL%ROWCOUNT > 0) THEN
1747
1748 d_position := 110;
1749
1750 UPDATE PO_SESSION_GT SES
1751 SET index_char2 = 'X'
1752 WHERE
1753 SES.key = l_data_key
1754 AND
1755 (
1756 -- Check for currently existing documents.
1757 EXISTS
1758 ( SELECT NULL
1759 FROM PO_HEADERS_ALL HEADER
1760 WHERE
1761 HEADER.org_id = SES.num2
1762 AND HEADER.segment1 = SES.index_char1
1763 AND HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
1764 AND HEADER.po_header_id <> SES.num1
1765 )
1766 OR
1767 -- Check for previously purged documents.
1768 EXISTS
1769 ( SELECT NULL
1770 FROM PO_HISTORY_POS_ALL DELETED_HEADER
1771 WHERE
1772 DELETED_HEADER.org_id = SES.num2
1773 AND DELETED_HEADER.segment1 = SES.index_char1
1774 AND DELETED_HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
1775 )
1776 OR
1777 -- Check for other in-memory documents.
1778 EXISTS
1779 ( SELECT NULL
1780 FROM PO_SESSION_GT UNSAVED_DATA
1781 WHERE
1782 UNSAVED_DATA.key = l_data_key
1783 AND UNSAVED_DATA.num2 = SES.num2 -- org_id
1784 AND UNSAVED_DATA.index_char1 = SES.index_char1 -- segment1
1785 AND UNSAVED_DATA.num1 <> SES.num1 -- po_header_id
1786 )
1787 )
1788 ;
1789
1790 d_position := 200;
1791
1792 -- If Sourcing is enabled, we need to check for
1793 -- document number uniqueness across auctions as well.
1794 PO_SETUP_S1.get_sourcing_startup(l_check_sourcing_flag);
1795
1796 d_position := 210;
1797
1798 IF PO_LOG.d_stmt THEN
1799 PO_LOG.stmt(d_mod,d_position,'l_check_sourcing_flag',l_check_sourcing_flag);
1800 END IF;
1801
1802 SELECT
1803 SES.num1
1804 , SES.num2
1805 , SES.index_char1
1806 , SES.index_char2
1807 BULK COLLECT INTO
1808 l_header_id_tbl
1809 , l_org_id_tbl
1810 , l_segment1_tbl
1811 , l_nonunique_tbl
1812 FROM
1813 PO_SESSION_GT SES
1814 WHERE
1815 SES.key = l_data_key
1816 AND
1817 ( SES.index_char2 = 'X'
1818 OR
1819 (
1820 l_check_sourcing_flag = 'I'
1821 AND SES.char2 IN (c_STANDARD,c_BLANKET)
1822 )
1823 )
1824 ;
1825
1826 d_position := 300;
1827 IF PO_LOG.d_stmt THEN
1828 PO_LOG.stmt(d_mod,d_position,'l_header_id_tbl',l_header_id_tbl);
1829 PO_LOG.stmt(d_mod,d_position,'l_org_id_tbl',l_org_id_tbl);
1830 PO_LOG.stmt(d_mod,d_position,'l_segment1_tbl',l_segment1_tbl);
1831 PO_LOG.stmt(d_mod,d_position,'l_nonunique_tbl',l_nonunique_tbl);
1832 END IF;
1833
1834 IF (l_check_sourcing_flag = 'I') THEN
1835 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
1836 IF (l_nonunique_tbl(i) IS NULL) THEN
1837 PON_AUCTION_PO_PKG.check_unique(
1838 org_id => l_org_id_tbl(i)
1839 , po_number => l_segment1_tbl(i)
1840 , status => l_pon_unique_status
1841 );
1842 IF (NVL(l_pon_unique_status,'N') <> c_SUCCESS) THEN
1843 l_nonunique_tbl(i) := 'X';
1844 END IF;
1845 END IF;
1846 END LOOP;
1847 END IF;
1848
1849 d_position := 400;
1850
1851 FOR i IN 1 .. l_header_id_tbl.COUNT LOOP
1852 IF (l_nonunique_tbl(i) = 'X') THEN
1853 x_results.add_result(
1854 p_entity_type => c_entity_type_HEADER
1855 , p_entity_id => l_header_id_tbl(i)
1856 , p_column_name => c_SEGMENT1
1857 , p_column_val => l_segment1_tbl(i)
1858 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_UNIQUE_VAL
1859 );
1860 END IF;
1861 END LOOP;
1862
1863 END IF; -- any records need to be checked
1864
1865 d_position := 500;
1866
1867 IF (l_results_count < x_results.result_type.COUNT) THEN
1868 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1869 ELSE
1870 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1871 END IF;
1872
1873 IF PO_LOG.d_proc THEN
1874 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1875 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1876 END IF;
1877
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 IF PO_LOG.d_exc THEN
1881 PO_LOG.exc(d_mod,d_position,NULL);
1882 END IF;
1883 RAISE;
1884
1885 END doc_num_unique;
1886
1887
1888 -- TODO: move to PO_DOC_CHECK_HEADERS
1889
1890 ----------------------------------------------------------------------
1891 -- Validates the dates of an Agreement's lines and price breaks
1892 -- with respect to the dates on the header.
1893 --
1894 --Pre-reqs:
1895 -- The agreement data must be populated in the Submission Check
1896 -- temp tables before this procedure is called.
1897 -- Only the agreement data should be in the tables.
1898 ----------------------------------------------------------------------
1899 PROCEDURE check_agreement_dates(
1900 p_online_report_id IN NUMBER
1901 , p_login_id IN NUMBER
1902 , p_user_id IN NUMBER
1903 , x_sequence IN OUT NOCOPY NUMBER
1904 )
1905 IS
1906 d_mod CONSTANT VARCHAR2(100) := D_check_agreement_dates;
1907
1908 c_delim CONSTANT VARCHAR2(1) := ' ';
1909 l_linemsg VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_LINE'), 1,25);
1910
1911 l_message_name VARCHAR2(30);
1912 l_text VARCHAR2(4000);
1913 BEGIN
1914
1915 IF PO_LOG.d_proc THEN
1916 PO_LOG.proc_begin(d_mod,'p_online_report_id',p_online_report_id);
1917 PO_LOG.proc_begin(d_mod,'p_login_id',p_login_id);
1918 PO_LOG.proc_begin(d_mod,'p_user_id',p_user_id);
1919 PO_LOG.proc_begin(d_mod,'x_sequence',x_sequence);
1920 END IF;
1921
1922
1923 -- Check that the line's Expiration date is within the
1924 -- Effective From and To dates of the agreement.
1925
1926 l_message_name := PO_MESSAGE_S.POX_EXPIRATION_DATES;
1927
1928 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1929
1930 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1931 ( online_report_id
1932 , last_update_login
1933 , last_updated_by
1934 , last_update_date
1935 , created_by
1936 , creation_date
1937 , line_num
1938 , shipment_num
1939 , distribution_num
1940 , sequence
1941 , message_name
1942 , text_line
1943 )
1944 SELECT
1945 p_online_report_id
1946 , p_login_id
1947 , p_user_id
1948 , SYSDATE
1949 , p_user_id
1950 , SYSDATE
1951 , LINE.line_num
1952 , 0
1953 , 0
1954 , x_sequence + rownum
1955 , l_message_name
1956 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
1957 || l_text,1,240)
1958 FROM
1959 PO_LINES_GT LINE
1960 , PO_HEADERS_GT HEADER
1961 WHERE
1962 LINE.expiration_date < HEADER.start_date
1963 OR LINE.expiration_date > HEADER.end_date
1964 ;
1965
1966 x_sequence := x_sequence + SQL%ROWCOUNT;
1967
1968
1969 -- Check that the price break's Effective From date
1970 -- is before the Effective To date of the agreement.
1971
1972 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES4;
1973
1974 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
1975
1976 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
1977 ( online_report_id
1978 , last_update_login
1979 , last_updated_by
1980 , last_update_date
1981 , created_by
1982 , creation_date
1983 , line_num
1984 , shipment_num
1985 , distribution_num
1986 , sequence
1987 , message_name
1988 , text_line
1989 )
1990 SELECT
1991 p_online_report_id
1992 , p_login_id
1993 , p_user_id
1994 , SYSDATE
1995 , p_user_id
1996 , SYSDATE
1997 , LINE.line_num
1998 , PRICE_BREAK.shipment_num
1999 , 0
2000 , x_sequence + rownum
2001 , l_message_name
2002 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
2003 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
2004 || l_text,1,240)
2005 FROM
2006 PO_LINE_LOCATIONS_ALL PRICE_BREAK
2007 , PO_LINES_GT LINE
2008 , PO_HEADERS_GT HEADER
2009 WHERE
2010 PRICE_BREAK.po_line_id = LINE.po_line_id
2011 AND PRICE_BREAK.start_date > HEADER.end_date
2012 ;
2013
2014 x_sequence := x_sequence + SQL%ROWCOUNT;
2015
2016
2017 -- Check that the price break's Effective From date
2018 -- is after the Effective From date of the agreement.
2019
2020 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES1;
2021
2022 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
2023
2024 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
2025 ( online_report_id
2026 , last_update_login
2027 , last_updated_by
2028 , last_update_date
2029 , created_by
2030 , creation_date
2031 , line_num
2032 , shipment_num
2033 , distribution_num
2034 , sequence
2035 , message_name
2036 , text_line
2037 )
2038 SELECT
2039 p_online_report_id
2040 , p_login_id
2041 , p_user_id
2042 , SYSDATE
2043 , p_user_id
2044 , SYSDATE
2045 , LINE.line_num
2046 , PRICE_BREAK.shipment_num
2047 , 0
2048 , x_sequence + rownum
2049 , l_message_name
2050 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
2051 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
2052 || l_text,1,240)
2053 FROM
2054 PO_LINE_LOCATIONS_ALL PRICE_BREAK
2055 , PO_LINES_GT LINE
2056 , PO_HEADERS_GT HEADER
2057 WHERE
2058 PRICE_BREAK.po_line_id = LINE.po_line_id
2059 AND PRICE_BREAK.start_date < HEADER.start_date
2060 ;
2061
2062 x_sequence := x_sequence + SQL%ROWCOUNT;
2063
2064
2065 -- Check that the price break's Effective To date
2066 -- is before the Effective To date of the agreement.
2067
2068 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES;
2069
2070 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
2071
2072 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
2073 ( online_report_id
2074 , last_update_login
2075 , last_updated_by
2076 , last_update_date
2077 , created_by
2078 , creation_date
2079 , line_num
2080 , shipment_num
2081 , distribution_num
2082 , sequence
2083 , message_name
2084 , text_line
2085 )
2086 SELECT
2087 p_online_report_id
2088 , p_login_id
2089 , p_user_id
2090 , SYSDATE
2091 , p_user_id
2092 , SYSDATE
2093 , LINE.line_num
2094 , PRICE_BREAK.shipment_num
2095 , 0
2096 , x_sequence + rownum
2097 , l_message_name
2098 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
2099 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
2100 || l_text,1,240)
2101 FROM
2102 PO_LINE_LOCATIONS_ALL PRICE_BREAK
2103 , PO_LINES_GT LINE
2104 , PO_HEADERS_GT HEADER
2105 WHERE
2106 PRICE_BREAK.po_line_id = LINE.po_line_id
2107 AND PRICE_BREAK.end_date > HEADER.end_date
2108 ;
2109
2110 x_sequence := x_sequence + SQL%ROWCOUNT;
2111
2112
2113 -- Check that the price break's Effective To date
2114 -- is after the Effective From date of the agreement.
2115
2116 l_message_name := PO_MESSAGE_S.POX_EFFECTIVE_DATES5;
2117
2118 l_text := FND_MESSAGE.get_string(c_PO,l_message_name);
2119
2120 INSERT INTO PO_ONLINE_REPORT_TEXT_GT
2121 ( online_report_id
2122 , last_update_login
2123 , last_updated_by
2124 , last_update_date
2125 , created_by
2126 , creation_date
2127 , line_num
2128 , shipment_num
2129 , distribution_num
2130 , sequence
2131 , message_name
2132 , text_line
2133 )
2134 SELECT
2135 p_online_report_id
2136 , p_login_id
2137 , p_user_id
2138 , SYSDATE
2139 , p_user_id
2140 , SYSDATE
2141 , LINE.line_num
2142 , PRICE_BREAK.shipment_num
2143 , 0
2144 , x_sequence + rownum
2145 , l_message_name
2146 , SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
2147 || TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
2148 || l_text,1,240)
2149 FROM
2150 PO_LINE_LOCATIONS_ALL PRICE_BREAK
2151 , PO_LINES_GT LINE
2152 , PO_HEADERS_GT HEADER
2153 WHERE
2154 PRICE_BREAK.po_line_id = LINE.po_line_id
2155 AND PRICE_BREAK.end_date < HEADER.start_date
2156 ;
2157
2158 x_sequence := x_sequence + SQL%ROWCOUNT;
2159
2160 IF PO_LOG.d_proc THEN
2161 PO_LOG.proc_end(d_mod,'x_sequence',x_sequence);
2162 END IF;
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 IF PO_LOG.d_exc THEN
2167 PO_LOG.exc(d_mod,0,NULL);
2168 END IF;
2169 RAISE;
2170
2171 END check_agreement_dates;
2172
2173
2174 -------------------------------------------------------------------------------
2175 -- Ensures that the Buyer is not null.
2176 -------------------------------------------------------------------------------
2177 PROCEDURE agent_id_not_null(
2178 p_header_id_tbl IN PO_TBL_NUMBER
2179 , p_agent_id_tbl IN PO_TBL_NUMBER
2180 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2181 , x_result_type OUT NOCOPY VARCHAR2
2182 )
2183 IS
2184 BEGIN
2185
2186 PO_VALIDATION_HELPER.not_null(
2187 p_calling_module => D_agent_id_not_null
2188 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_agent_id_tbl)
2189 , p_entity_id_tbl => p_header_id_tbl
2190 , p_entity_type => c_entity_type_HEADER
2191 , p_column_name => c_AGENT_ID
2192 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2193 , x_results => x_results
2194 , x_result_type => x_result_type
2195 );
2196
2197 END agent_id_not_null;
2198
2199
2200 -------------------------------------------------------------------------------
2201 -- Ensures that the Default Ship-To / Work Location is not null.
2202 -------------------------------------------------------------------------------
2203 PROCEDURE ship_to_loc_not_null(
2204 p_header_id_tbl IN PO_TBL_NUMBER
2205 , p_ship_to_loc_id_tbl IN PO_TBL_NUMBER
2206 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2207 , x_result_type OUT NOCOPY VARCHAR2
2208 )
2209 IS
2210 BEGIN
2211
2212 PO_VALIDATION_HELPER.not_null(
2213 p_calling_module => D_ship_to_loc_not_null
2214 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ship_to_loc_id_tbl)
2215 , p_entity_id_tbl => p_header_id_tbl
2216 , p_entity_type => c_entity_type_HEADER
2217 , p_column_name => c_SHIP_TO_LOCATION_ID
2218 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2219 , x_results => x_results
2220 , x_result_type => x_result_type
2221 );
2222
2223 END ship_to_loc_not_null;
2224
2225
2226 -------------------------------------------------------------------------------
2227 -- Ensures that the Supplier is not null.
2228 -------------------------------------------------------------------------------
2229 PROCEDURE vendor_id_not_null(
2230 p_header_id_tbl IN PO_TBL_NUMBER
2231 , p_vendor_id_tbl IN PO_TBL_NUMBER
2232 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2233 , x_result_type OUT NOCOPY VARCHAR2
2234 )
2235 IS
2236 BEGIN
2237
2238 PO_VALIDATION_HELPER.not_null(
2239 p_calling_module => D_vendor_id_not_null
2240 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_vendor_id_tbl)
2241 , p_entity_id_tbl => p_header_id_tbl
2242 , p_entity_type => c_entity_type_HEADER
2243 , p_column_name => c_VENDOR_ID
2244 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2245 , x_results => x_results
2246 , x_result_type => x_result_type
2247 );
2248
2249 END vendor_id_not_null;
2250
2251
2252 -------------------------------------------------------------------------------
2253 -- Ensures that the Supplier Site is not null.
2254 -------------------------------------------------------------------------------
2255 PROCEDURE vendor_site_id_not_null(
2256 p_header_id_tbl IN PO_TBL_NUMBER
2257 , p_vendor_site_id_tbl IN PO_TBL_NUMBER
2258 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2259 , x_result_type OUT NOCOPY VARCHAR2
2260 )
2261 IS
2262 BEGIN
2263
2264 PO_VALIDATION_HELPER.not_null(
2265 p_calling_module => D_vendor_site_id_not_null
2266 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_vendor_site_id_tbl)
2267 , p_entity_id_tbl => p_header_id_tbl
2268 , p_entity_type => c_entity_type_HEADER
2269 , p_column_name => c_VENDOR_SITE_ID
2270 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2271 , x_results => x_results
2272 , x_result_type => x_result_type
2273 );
2274
2275 END vendor_site_id_not_null;
2276
2277 --<Begin Bug# 5372769> EXCEPTION WHEN SAVE PO WO/ NUMBER IF DOCUMENT NUMBERING IS SET TO MANUAL
2278 -------------------------------------------------------------------------------
2279 -- Ensures that the Segment1 is not null.
2280 -------------------------------------------------------------------------------
2281 PROCEDURE segment1_not_null(
2282 p_header_id_tbl IN PO_TBL_NUMBER
2283 , p_segment1_tbl IN PO_TBL_VARCHAR30
2284 , p_org_id_tbl IN PO_TBL_NUMBER
2285 , x_result_set_id IN OUT NOCOPY NUMBER
2286 , x_result_type OUT NOCOPY VARCHAR2
2287 )
2288 IS
2289 BEGIN
2290
2291 IF (x_result_set_id IS NULL) THEN
2292 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2293 END IF;
2294
2295 FORALL i IN 1 .. p_header_id_tbl.COUNT
2296 INSERT INTO PO_VALIDATION_RESULTS_GT
2297 ( result_set_id
2298 , entity_type
2299 , entity_id
2300 , message_name
2301 , column_name
2302 )
2303 SELECT
2304 x_result_set_id
2305 , c_entity_type_HEADER
2306 , p_header_id_tbl(i)
2307 , PO_MESSAGE_S.PO_ALL_NOT_NULL
2308 , c_SEGMENT1
2309 FROM
2310 PO_SYSTEM_PARAMETERS_ALL
2311 WHERE
2312 org_id = p_org_id_tbl(i)
2313 AND USER_DEFINED_PO_NUM_CODE = c_MANUAL
2314 AND p_segment1_tbl(i) IS NULL;
2315
2316 IF (SQL%ROWCOUNT > 0) THEN
2317 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2318 ELSE
2319 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2320 END IF;
2321
2322 END segment1_not_null;
2323 --<End 5372769>
2324 --<Start Bug 9213424> Error when the ship_via field has an invalid value.
2325 PROCEDURE ship_via_lookup_code_valid(
2326 p_header_id_tbl IN po_tbl_number,
2327 p_ship_via_lookup_code_tbl IN po_tbl_varchar30,
2328 --Bug 12409257 start.Bug 13771850-Revert 12409257 changes
2329 p_org_id_tbl IN PO_TBL_NUMBER,
2330 --p_ship_to_location_id_tbl IN PO_TBL_NUMBER,
2331 --Bug 12409257 end.Bug 13771850 end
2332 x_result_set_id IN OUT NOCOPY NUMBER,
2333 x_result_type OUT NOCOPY VARCHAR2)
2334 IS
2335 d_mod CONSTANT VARCHAR2(100) := 'SHIP_VIA_LOOKUP_CODE';
2336 x_temp VARCHAR2(100);
2337 BEGIN
2338 IF x_result_set_id IS NULL THEN
2339 x_result_set_id := po_validations.next_result_set_id();
2340 END IF;
2341
2342 IF po_log.d_proc THEN
2343 po_log.proc_begin(d_mod, 'p_header_id_tbl', p_header_id_tbl);
2344 po_log.proc_begin(d_mod, 'p_ship_via_lookup_code_tbl', p_ship_via_lookup_code_tbl);
2345 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2346
2347
2348 END IF;
2349
2350 x_result_type := po_validations.c_result_type_success;
2351
2352 FOR i IN 1 .. p_header_id_tbl.Count
2353 LOOP
2354 IF p_ship_via_lookup_code_tbl(i) IS NOT NULL THEN
2355
2356
2357 INSERT INTO po_validation_results_gt
2358 (result_set_id,
2359 result_type,
2360 entity_type,
2361 message_name,
2362 column_name,
2363 column_val,
2364 token1_name,
2365 token1_value,
2366 validation_id)
2367 SELECT x_result_set_id,
2368 po_validations.c_result_type_failure,
2369 c_entity_type_header,
2370 'PO_PDOI_INVALID_FREIGHT_CARR',
2371 'SHIP_VIA_LOOKUP_CODE',
2372 p_ship_via_lookup_code_tbl(i),
2373 'VALUE',
2374 p_ship_via_lookup_code_tbl(i),
2375 PO_VAL_CONSTANTS.c_ship_via_lookup_code
2376 FROM dual
2377 WHERE NOT EXISTS (SELECT 1 FROM org_freight ofr
2378 WHERE p_ship_via_lookup_code_tbl(i) = ofr.freight_code
2379 AND SYSDATE < NVL(ofr.disable_date, SYSDATE + 1)
2380 --Bug 12409257 start
2381 --AND p_org_id_tbl(i)=ofr.organization_id);
2382 --AND ofr.organization_id in (SELECT inventory_organization_id
2383 -- FROM hr_locations_v
2384 -- WHERE ship_to_location_id = --p_ship_to_location_id_tbl(i)
2385 -- AND ship_to_site_flag = 'Y'));
2386 --Bug 12409257 end
2387 --Bug 13771850 start
2388 AND ofr.organization_id in (SELECT inventory_organization_id
2389 FROM financials_system_params_all
2390 WHERE org_id = p_org_id_tbl(i) ));
2391 --Bug 13771850 end
2392 END IF ;
2393 END LOOP ;
2394
2395
2396 IF (SQL%ROWCOUNT > 0 ) THEN
2397 x_result_type := po_validations.c_result_type_FAILURE;
2398 END IF;
2399
2400 IF po_log.d_proc THEN
2401 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2402 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2403 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2404 END IF;
2405
2406
2407 EXCEPTION
2408 WHEN OTHERS THEN
2409 IF po_log.d_exc THEN
2410 po_log.exc(d_mod, 0, NULL);
2411 END IF;
2412 RAISE;
2413 END ship_via_lookup_code_valid;
2414 --<End Bug 9213424>
2415
2416 -- Mod Project
2417 -------------------------------------------------------------------------------
2418 -- Mod_effective date is less then earlist need_by_date
2419 -------------------------------------------------------------------------------
2420 PROCEDURE mod_eff_dt_lt_need_by_dt(
2421 p_header_id_tbl IN PO_TBL_NUMBER
2422 ,p_mod_eff_date_tbl IN PO_TBL_DATE
2423 ,x_result_set_id IN OUT NOCOPY NUMBER
2424 ,x_result_type OUT NOCOPY VARCHAR2
2425 )
2426 IS
2427 d_mod CONSTANT VARCHAR2(100) := D_mod_eff_dt_lt_need_by_dt;
2428 BEGIN
2429
2430 IF PO_LOG.d_proc THEN
2431 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
2432 PO_LOG.proc_begin(d_mod,'p_mod_eff_date_tbl',p_mod_eff_date_tbl);
2433 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2434 END IF;
2435 IF (x_result_set_id IS NULL) THEN
2436 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2437 END IF;
2438
2439 FORALL i IN 1..p_header_id_tbl.COUNT
2440 INSERT INTO po_validation_results_gt
2441 (result_set_id,
2442 entity_type,
2443 entity_id,
2444 message_name,
2445 column_name,
2446 column_val)
2447 SELECT x_result_set_id,
2448 c_entity_type_HEADER
2449 ,p_header_id_tbl(i)
2450 ,PO_MESSAGE_S.PO_MOD_EFFECT_DT_INVALID
2451 ,c_MOD_EFFECTIVE_DATE
2452 ,TO_CHAR(p_mod_eff_date_tbl(i))
2453 FROM DUAL
2454 WHERE p_mod_eff_date_tbl(i) is not NULL
2455 AND p_mod_eff_date_tbl(i) > (
2456 SELECT min(nvl(need_by_date, p_mod_eff_date_tbl(i)+1))
2457 FROM PO_LINE_LOCATIONS_ALL
2458 WHERE po_header_id = p_header_id_tbl(i));
2459
2460 IF (SQL%ROWCOUNT > 0) THEN
2461 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2462 ELSE
2463 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2464 END IF;
2465
2466
2467 IF PO_LOG.d_proc THEN
2468 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2469 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2470 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2471 END IF;
2472
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 IF PO_LOG.d_exc THEN
2476 PO_LOG.exc(d_mod,0,NULL);
2477 END IF;
2478 RAISE;
2479
2480 END mod_eff_dt_lt_need_by_dt;
2481
2482 -------------------------------------------------------------------------------
2483 -- Mod_effective date cannot be null for Modification
2484 -------------------------------------------------------------------------------
2485 PROCEDURE mod_eff_dt_not_null(
2486 p_header_id_tbl IN PO_TBL_NUMBER
2487 ,p_draft_type_tbl IN PO_TBL_VARCHAR30
2488 ,p_mod_eff_date_tbl IN PO_TBL_DATE
2489 ,x_result_set_id IN OUT NOCOPY NUMBER
2490 ,x_result_type OUT NOCOPY VARCHAR2
2491 )
2492 IS
2493 d_mod CONSTANT VARCHAR2(100) := D_mod_eff_dt_not_null;
2494 BEGIN
2495
2496 IF PO_LOG.d_proc THEN
2497 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
2498 PO_LOG.proc_begin(d_mod,'p_draft_type_tbl',p_draft_type_tbl);
2499 PO_LOG.proc_begin(d_mod,'p_mod_eff_date_tbl',p_mod_eff_date_tbl);
2500 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2501 END IF;
2502 IF (x_result_set_id IS NULL) THEN
2503 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2504 END IF;
2505
2506 FORALL i IN 1..p_header_id_tbl.COUNT
2507 INSERT INTO po_validation_results_gt
2508 (result_set_id,
2509 entity_type,
2510 entity_id,
2511 message_name,
2512 column_name)
2513 SELECT x_result_set_id,
2514 c_entity_type_HEADER
2515 ,p_header_id_tbl(i)
2516 ,PO_MESSAGE_S.PO_MOD_EFFECT_DT_NOTNULL
2517 ,c_MOD_EFFECTIVE_DATE
2518 FROM DUAL
2519 WHERE p_mod_eff_date_tbl(i) is NULL
2520 AND nvl(p_draft_type_tbl(i), '*') = 'MOD';
2521
2522 IF (SQL%ROWCOUNT > 0) THEN
2523 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2524 ELSE
2525 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2526 END IF;
2527
2528
2529 IF PO_LOG.d_proc THEN
2530 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2531 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2532 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2533 END IF;
2534
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 IF PO_LOG.d_exc THEN
2538 PO_LOG.exc(d_mod,0,NULL);
2539 END IF;
2540 RAISE;
2541
2542 END mod_eff_dt_not_null;
2543
2544 PROCEDURE mod_no_cancel_shp_complt(
2545 p_header_id_tbl IN PO_TBL_NUMBER
2546 ,p_control_action_tbl IN PO_TBL_VARCHAR30
2547 ,x_result_set_id IN OUT NOCOPY NUMBER
2548 ,x_result_type OUT NOCOPY VARCHAR2
2549 )
2550 IS
2551 d_mod CONSTANT VARCHAR2(100) := D_mod_no_cancel_shp_complt;
2552 BEGIN
2553
2554 IF PO_LOG.d_proc THEN
2555 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
2556 PO_LOG.proc_begin(d_mod,'p_control_action_tbl',p_control_action_tbl);
2557 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2558 END IF;
2559 IF (x_result_set_id IS NULL) THEN
2560 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2561 END IF;
2562
2563 FORALL i IN 1..p_header_id_tbl.COUNT
2564 INSERT INTO po_validation_results_gt
2565 (result_set_id,
2566 entity_type,
2567 entity_id,
2568 message_name,
2569 column_name,
2570 column_val)
2571 SELECT x_result_set_id,
2572 c_entity_type_HEADER
2573 ,p_header_id_tbl(i)
2574 ,PO_MESSAGE_S.PO_MOD_CANCEL_INVALID
2575 ,c_CONTROL_ACTION
2576 ,TO_CHAR(p_control_action_tbl(i))
2577 FROM DUAL
2578 WHERE nvl(p_control_action_tbl(i), '*') = 'CANCEL'
2579 AND not exists
2580 (SELECT 'Y'
2581 FROM PO_LINE_LOCATIONS_ALL
2582 WHERE po_header_id = p_header_id_tbl(i)
2583 AND quantity > quantity_received);
2584
2585 IF (SQL%ROWCOUNT > 0) THEN
2586 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2587 ELSE
2588 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2589 END IF;
2590
2591
2592 IF PO_LOG.d_proc THEN
2593 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2594 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2595 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2596 END IF;
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 IF PO_LOG.d_exc THEN
2601 PO_LOG.exc(d_mod,0,NULL);
2602 END IF;
2603 RAISE;
2604
2605 END mod_no_cancel_shp_complt;
2606
2607
2608 PROCEDURE validate_clm_standard_form (
2609
2610 p_intf_header_id IN PO_TBL_NUMBER,
2611 p_clm_standard_form_tbl IN PO_TBL_VARCHAR2000,
2612 p_style_id_tbl IN PO_TBL_NUMBER,
2613 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2614 x_result_type OUT NOCOPY VARCHAR2
2615 ) IS
2616
2617 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_standard_form;
2618 l_results_count NUMBER :=0;
2619 l_valid_standard_form VARCHAR2(1) := NULL;
2620
2621 BEGIN
2622 -- skip the validation for non-CLM documents
2623 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2624 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2625 RETURN;
2626 END IF;
2627
2628 IF PO_LOG.d_proc THEN
2629 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
2630 PO_LOG.proc_begin(d_mod,'p_clm_standard_form_tbl',p_clm_standard_form_tbl);
2631 END IF;
2632
2633 FOR i IN 1..p_intf_header_id.Count
2634 LOOP
2635 IF p_clm_standard_form_tbl(i) IS NOT NULL
2636 THEN
2637 BEGIN
2638 SELECT 'Y' INTO l_valid_standard_form
2639 FROM dual
2640 WHERE p_clm_standard_form_tbl(i) IN ( SELECT DISTINCT standard_form
2641 FROM PO_PRINT_FORM_FORMATS
2642 WHERE style_id = p_style_id_tbl(i)
2643 AND document_type = Decode (PO_PDOI_PARAMS.g_request.document_type,
2644 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,'PO_AWARD_STD_FORM')
2645 AND inactive_date IS NULL
2646 );
2647 EXCEPTION
2648 WHEN No_Data_Found THEN
2649 x_results.add_result(
2650 p_entity_type => c_entity_type_HEADER
2651 , p_entity_id => p_intf_header_id(i)
2652 , p_column_name => c_CLM_STANDARD_FORM
2653 , p_message_name => PO_MESSAGE_S.PO_PRINT_FORM_FORMAT_INVALID
2654 );
2655 l_results_count := l_results_count + 1;
2656 END;
2657 END IF;
2658 END LOOP;
2659 IF l_results_count > 0 THEN
2660 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2661 ELSE
2662 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2663 END IF;
2664
2665 IF PO_LOG.d_proc THEN
2666 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2667 END IF;
2668 EXCEPTION
2669 WHEN OTHERS THEN
2670 IF PO_LOG.d_exc THEN
2671 PO_LOG.exc(d_mod,0,NULL);
2672 END IF;
2673 RAISE;
2674
2675 END validate_clm_standard_form;
2676
2677
2678 PROCEDURE validate_clm_document_format(
2679 p_intf_header_id IN PO_TBL_NUMBER,
2680 p_clm_standard_form_tbl IN PO_TBL_VARCHAR2000,
2681 p_clm_document_format_tbl IN PO_TBL_VARCHAR2000,
2682 p_style_id_tbl IN PO_TBL_NUMBER,
2683 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2684 x_result_type OUT NOCOPY VARCHAR2
2685 ) IS
2686
2687 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_document_format;
2688 l_results_count NUMBER :=0;
2689 l_valid_document_format VARCHAR2(1):= NULL;
2690 BEGIN
2691 -- skip the validation for non-CLM documents
2692 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2693 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2694 RETURN;
2695 END IF;
2696
2697 IF PO_LOG.d_proc THEN
2698 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
2699 PO_LOG.proc_begin(d_mod,'p_clm_standard_form_tbl',p_clm_standard_form_tbl);
2700 END IF;
2701
2702 FOR i IN 1..p_intf_header_id.Count
2703 LOOP
2704 IF p_clm_document_format_tbl(i) IS NOT NULL
2705 THEN
2706 IF p_clm_document_format_tbl(i) = '-999'
2707 THEN
2708 x_results.add_result(
2709 p_entity_type => c_entity_type_HEADER
2710 , p_entity_id => p_intf_header_id(i)
2711 , p_column_name => c_CLM_DOCUMENT_FORMAT
2712 , p_message_name => PO_MESSAGE_S.PO_PRINT_FORM_FORMAT_INVALID
2713 );
2714 l_results_count := l_results_count + 1;
2715 ELSE
2716 BEGIN
2717 SELECT 'Y' INTO l_valid_document_format
2718 FROM dual
2719 WHERE p_clm_document_format_tbl(i) IN ( SELECT document_format
2720 FROM PO_PRINT_FORM_FORMATS
2721 WHERE style_id = p_style_id_tbl(i)
2722 AND standard_form = p_clm_standard_form_tbl(i)
2723 AND document_type = Decode (PO_PDOI_PARAMS.g_request.document_type,
2724 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,'PO_AWARD_STD_FORM')
2725 AND inactive_date IS NULL
2726 );
2727 EXCEPTION
2728 WHEN No_Data_Found THEN
2729 x_results.add_result(
2730 p_entity_type => c_entity_type_HEADER
2731 , p_entity_id => p_intf_header_id(i)
2732 , p_column_name => c_CLM_STANDARD_FORM
2733 , p_message_name => PO_MESSAGE_S.PO_PRINT_FORM_FORMAT_INVALID
2734 );
2735 l_results_count := l_results_count + 1;
2736 END;
2737 END IF;
2738 END IF;
2739 END LOOP;
2740 IF l_results_count > 0 THEN
2741 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2742 ELSE
2743 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2744 END IF;
2745
2746 IF PO_LOG.d_proc THEN
2747 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2748 END IF;
2749 EXCEPTION
2750 WHEN OTHERS THEN
2751 IF PO_LOG.d_exc THEN
2752 PO_LOG.exc(d_mod,0,NULL);
2753 END IF;
2754 RAISE;
2755
2756 END validate_clm_document_format;
2757
2758
2759 PROCEDURE validate_source_idv
2760 (
2761 p_intf_header_id in PO_TBL_NUMBER,
2762 p_src_doc_id_tbl IN PO_TBL_NUMBER,
2763 p_currency_code_tbl in PO_TBL_VARCHAR30,
2764 p_org_id_tbl in PO_TBL_NUMBER,
2765 x_results in OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2766 x_result_type OUT NOCOPY VARCHAR2
2767 ) IS
2768
2769 d_mod CONSTANT VARCHAR2(100) := D_validate_source_idv;
2770 l_results_count NUMBER :=0;
2771 is_valid_src_doc_id VARCHAR2(1):= NULL;
2772 BEGIN
2773 -- skip the validation for non-CLM documents
2774 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2775 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2776 RETURN;
2777 END IF;
2778
2779 IF PO_LOG.d_proc THEN
2780 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
2781 PO_LOG.proc_begin(d_mod,'p_src_doc_id',p_src_doc_id_tbl);
2782 END IF;
2783
2784 FOR i IN 1..p_intf_header_id.Count
2785 LOOP
2786 IF p_src_doc_id_tbl(i) IS NOT NULL
2787 THEN
2788 BEGIN
2789
2790 SELECT 'Y' INTO is_valid_src_doc_id
2791 FROM dual
2792 WHERE EXISTS (
2793 select poh.po_header_id
2794 FROM po_headers_all POH,
2795 hr_all_organization_units_tl HOUTL,
2796 po_doc_style_lines_vl PDSL,
2797 po_document_types_all_tl PDTL,
2798 po_doc_style_headers PDSH
2799 WHERE HOUTL.organization_id = POH.org_id
2800 AND HOUTL.language = USERENV('LANG')
2801 AND PDSL.document_subtype(+) = POH.type_lookup_code
2802 AND PDSL.style_id(+) = POH.style_id
2803 AND ((POH.type_lookup_code = 'QUOTATION'
2804 AND PDTL.document_type_code = POH.type_lookup_code
2805 AND PDTL.document_subtype = POH.quote_type_lookup_code
2806 )
2807 OR
2808 (PDTL.document_type_code IN ('PO','PA')
2809 AND PDTL.document_subtype = POH.type_lookup_code
2810 )
2811 )
2812 AND PDTL.org_id = POH.org_id
2813 AND PDTL.language = USERENV('LANG')
2814 AND ( POH.currency_code = p_currency_code_tbl(i)
2815 or (POH.type_lookup_code = 'CONTRACT' and POH.org_id = p_org_id_tbl(i))
2816 )
2817 AND ( (POH.type_lookup_code IN ('BLANKET', 'CONTRACT')
2818 AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
2819 AND NVL(POH.cancel_flag, 'N') = 'N'
2820 AND NVL(POH.frozen_flag,'N') = 'N'
2821 AND NVL(POH.user_hold_flag, 'N') = 'N'
2822 AND ((POH.type_lookup_code = 'CONTRACT'
2823 AND NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
2824 AND POH.approved_date is not null)
2825 OR
2826 POH.authorization_status = 'APPROVED'
2827 )
2828 ))
2829 AND POH.STYLE_ID = PDSH.STYLE_ID
2830 AND Nvl(PDSH.CLM_FLAG, 'N') = 'Y'
2831 AND POH.CLM_AWARD_TYPE IS NOT NULL
2832 and poh.po_header_id = p_src_doc_id_tbl(i)
2833 );
2834 EXCEPTION WHEN No_Data_Found
2835 THEN
2836 x_results.add_result(
2837 p_entity_type => c_entity_type_HEADER
2838 , p_entity_id => p_intf_header_id(i)
2839 , p_column_name => c_CLM_SOURCE_DOCUMENT_ID
2840 , p_message_name => 'PO_SOURCE_IDV_INVALID'
2841 );
2842 l_results_count := l_results_count + 1;
2843 END;
2844 END IF;
2845 END LOOP;
2846 IF l_results_count > 0 THEN
2847 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2848 ELSE
2849 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2850 END IF;
2851 IF PO_LOG.d_proc THEN
2852 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2853 END IF;
2854 EXCEPTION
2855 WHEN OTHERS THEN
2856 IF PO_LOG.d_exc THEN
2857 PO_LOG.exc(d_mod,0,NULL);
2858 END IF;
2859 RAISE;
2860
2861 END validate_source_idv;
2862
2863
2864
2865 PROCEDURE validate_award_admin
2866 (
2867 p_intf_header_id IN PO_TBL_NUMBER,
2868 p_award_admin_tbl IN PO_TBL_VARCHAR2000,
2869 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2870 x_result_type OUT NOCOPY VARCHAR2
2871 )IS
2872
2873 d_mod CONSTANT VARCHAR2(100) := D_validate_award_admin;
2874 l_results_count NUMBER :=0;
2875 is_valid_award_admin VARCHAR2(1):= NULL;
2876 BEGIN
2877 -- skip the validation for non-CLM documents
2878 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2879 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2880 RETURN;
2881 END IF;
2882
2883 IF PO_LOG.d_proc THEN
2884 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
2885 PO_LOG.proc_begin(d_mod,'p_award_admin_tbl',p_award_admin_tbl);
2886 END IF;
2887
2888 FOR i IN 1..p_intf_header_id.Count
2889 LOOP
2890 IF p_award_admin_tbl(i) IS NOT NULL
2891 THEN
2892 BEGIN
2893 SELECT 'Y' INTO is_valid_award_admin
2894 FROM dual
2895 WHERE EXISTS (
2896 SELECT FULL_NAME
2897 FROM
2898 PO_BUYERS_VAL_V
2899 WHERE('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
2900 AND employee_id = To_Number(p_award_admin_tbl(i))
2901 );
2902 EXCEPTION WHEN No_Data_Found
2903 THEN
2904 x_results.add_result(
2905 p_entity_type => c_entity_type_HEADER
2906 , p_entity_id => p_intf_header_id(i)
2907 , p_column_name => c_CLM_AWARD_ADMINISTRATOR
2908 , p_message_name => 'PO_AWARD_ADMIN_INVALID'
2909 );
2910 l_results_count := l_results_count + 1;
2911 END;
2912 END IF;
2913 END LOOP;
2914 IF l_results_count > 0 THEN
2915 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2916 ELSE
2917 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2918 END IF;
2919 IF PO_LOG.d_proc THEN
2920 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2921 END IF;
2922 EXCEPTION
2923 WHEN OTHERS THEN
2924 IF PO_LOG.d_exc THEN
2925 PO_LOG.exc(d_mod,0,NULL);
2926 END IF;
2927 RAISE;
2928
2929 END validate_award_admin;
2930
2931
2932
2933 PROCEDURE validate_contract_officer
2934 (
2935 p_intf_header_id IN PO_TBL_NUMBER,
2936 p_contract_officer_tbl IN PO_TBL_NUMBER,
2937 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2938 x_result_type OUT NOCOPY VARCHAR2
2939 )IS
2940
2941 d_mod CONSTANT VARCHAR2(100) := D_validate_award_admin;
2942 l_results_count NUMBER :=0;
2943 is_valid_contract_officer VARCHAR2(1):= NULL;
2944 BEGIN
2945 -- skip the validation for non-CLM documents
2946 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2947 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2948 RETURN;
2949 END IF;
2950
2951 IF PO_LOG.d_proc THEN
2952 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
2953 PO_LOG.proc_begin(d_mod,'p_contract_officer_tbl',p_contract_officer_tbl);
2954 END IF;
2955
2956 FOR i IN 1..p_intf_header_id.Count
2957 LOOP
2958 IF p_contract_officer_tbl(i) IS NOT NULL
2959 THEN
2960 BEGIN
2961 SELECT 'Y' INTO is_valid_contract_officer
2962 FROM dual
2963 WHERE EXISTS (
2964 SELECT FULL_NAME
2965 FROM
2966 PO_BUYERS_VAL_V
2967 WHERE('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
2968 AND employee_id = p_contract_officer_tbl(i)
2969 AND Nvl(contract_officer,'N') = 'Y'
2970 );
2971 EXCEPTION WHEN No_Data_Found
2972 THEN
2973 x_results.add_result(
2974 p_entity_type => c_entity_type_HEADER
2975 , p_entity_id => p_intf_header_id(i)
2976 , p_column_name => c_CLM_CONTRACT_OFFICER
2977 , p_message_name => 'PO_CONTRACT_OFFICER_INVALID'
2978 );
2979 l_results_count := l_results_count + 1;
2980 END;
2981 END IF;
2982 END LOOP;
2983 IF l_results_count > 0 THEN
2984 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2985 ELSE
2986 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2987 END IF;
2988 IF PO_LOG.d_proc THEN
2989 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2990 END IF;
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 IF PO_LOG.d_exc THEN
2994 PO_LOG.exc(d_mod,0,NULL);
2995 END IF;
2996 RAISE;
2997
2998 END validate_contract_officer;
2999
3000
3001 PROCEDURE validate_umbrella_program_id
3002 (
3003 p_intf_header_id IN PO_TBL_NUMBER,
3004 p_umbrella_program_id_tbl IN PO_TBL_NUMBER,
3005 p_src_doc_id_tbl IN PO_TBL_NUMBER,
3006 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3007 x_result_type OUT NOCOPY VARCHAR2
3008 ) IS
3009
3010 d_mod CONSTANT VARCHAR2(100) := D_validate_umbrella_program_id;
3011 l_results_count NUMBER :=0;
3012 is_valid_umbrella_prg_id VARCHAR2(1):= NULL;
3013 BEGIN
3014 -- skip the validation for non-CLM documents
3015 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
3016 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3017 RETURN;
3018 END IF;
3019
3020 IF PO_LOG.d_proc THEN
3021 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3022 PO_LOG.proc_begin(d_mod,'p_umbrella_program_id_tbl',p_umbrella_program_id_tbl);
3023 END IF;
3024
3025 FOR i IN 1..p_intf_header_id.Count
3026 LOOP
3027 IF (p_umbrella_program_id_tbl(i) IS NOT NULL)
3028 AND
3029 (p_src_doc_id_tbl(i) IS NOT NULL)
3030 THEN
3031 BEGIN
3032 SELECT 'Y' INTO is_valid_umbrella_prg_id
3033 FROM dual
3034 WHERE p_umbrella_program_id_tbl(i) = (
3035 SELECT Nvl(umbrella_program_id,-1)
3036 FROM po_headers_all
3037 WHERE po_header_id = p_src_doc_id_tbl(i)
3038 );
3039 EXCEPTION WHEN No_Data_Found
3040 THEN
3041 x_results.add_result(
3042 p_entity_type => c_entity_type_HEADER
3043 , p_entity_id => p_intf_header_id(i)
3044 , p_column_name => c_UMBRELLA_PROGRAM_ID
3045 , p_message_name => 'PO_UMBRELLA_PRG_ID_INVALID'
3046 );
3047 l_results_count := l_results_count + 1;
3048 END;
3049 END IF;
3050 END LOOP;
3051 IF l_results_count > 0 THEN
3052 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3053 ELSE
3054 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3055 END IF;
3056 IF PO_LOG.d_proc THEN
3057 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3058 END IF;
3059 EXCEPTION
3060 WHEN OTHERS THEN
3061 IF PO_LOG.d_exc THEN
3062 PO_LOG.exc(d_mod,0,NULL);
3063 END IF;
3064 RAISE;
3065
3066 END validate_umbrella_program_id;
3067
3068
3069
3070 PROCEDURE validate_clm_award_type
3071 (
3072 p_intf_header_id IN PO_TBL_NUMBER,
3073 p_clm_award_type_tbl IN PO_TBL_VARCHAR30,
3074 p_type_lookup_code_tbl IN PO_TBL_VARCHAR30,
3075 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3076 x_result_type OUT NOCOPY VARCHAR2
3077 ) IS
3078
3079 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_award_type;
3080 l_results_count NUMBER :=0;
3081 is_valid_awd_type VARCHAR2(1):= NULL;
3082 BEGIN
3083 -- skip the validation for non-CLM documents
3084 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
3085 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3086 RETURN;
3087 END IF;
3088
3089 IF PO_LOG.d_proc THEN
3090 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3091 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
3092 PO_LOG.proc_begin(d_mod,'p_clm_award_type_tbl',p_clm_award_type_tbl);
3093 END IF;
3094 IF (x_results IS NULL) THEN
3095 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3096 END IF;
3097
3098 FOR i IN 1..p_intf_header_id.Count
3099 LOOP
3100 IF p_clm_award_type_tbl(i) IS NOT NULL
3101 THEN
3102 IF p_type_lookup_code_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
3103 THEN
3104 BEGIN
3105 SELECT 'Y' INTO is_valid_awd_type
3106 FROM dual
3107 WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
3108 WHERE lookup_type IN ('PO_CLM_AWD_TYPE_SPO',
3109 'PO_CLM_AWD_TYPE_ORD_OFF_IDV'
3110 )
3111 AND lookup_code = p_clm_award_type_tbl(i)
3112 );
3113 EXCEPTION WHEN No_Data_Found
3114 THEN
3115 x_results.add_result(
3116 p_entity_type => c_entity_type_HEADER
3117 , p_entity_id => p_intf_header_id(i)
3118 , p_column_name => c_CLM_AWARD_TYPE
3119 , p_message_name => 'PO_AWARD_TYPE_INVALID'
3120 );
3121 l_results_count := l_results_count + 1;
3122 END;
3123 ELSIF p_type_lookup_code_tbl(i) IN (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
3124 PO_PDOI_CONSTANTS.g_DOC_TYPE_CONTRACT)
3125 THEN
3126 BEGIN
3127 SELECT 'Y' INTO is_valid_awd_type
3128 FROM dual
3129 WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
3130 WHERE lookup_type = Decode(p_type_lookup_code_tbl(i),
3131 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,'PO_CLM_AWD_TYPE_BPA',
3132 PO_PDOI_CONSTANTS.g_DOC_TYPE_CONTRACT,'PO_CLM_AWD_TYPE_CPA')
3133 AND lookup_code = p_clm_award_type_tbl(i)
3134 );
3135 EXCEPTION WHEN No_Data_Found
3136 THEN
3137 x_results.add_result(
3138 p_entity_type => c_entity_type_HEADER
3139 , p_entity_id => p_intf_header_id(i)
3140 , p_column_name => c_CLM_AWARD_TYPE
3141 , p_message_name => 'PO_AWARD_TYPE_INVALID'
3142 );
3143 l_results_count := l_results_count + 1;
3144 END;
3145 END IF;
3146 END IF;
3147 END LOOP;
3148
3149 IF l_results_count > 0 THEN
3150 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3151 ELSE
3152 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3153 END IF;
3154 IF PO_LOG.d_proc THEN
3155 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3156 END IF;
3157 EXCEPTION
3158 WHEN OTHERS THEN
3159 IF PO_LOG.d_exc THEN
3160 PO_LOG.exc(d_mod,0,NULL);
3161 END IF;
3162 RAISE;
3163
3164 END validate_clm_award_type;
3165
3166
3167
3168
3169 PROCEDURE validate_style_id
3170 (
3171 p_intf_header_id IN PO_TBL_NUMBER,
3172 p_style_id_tbl IN PO_TBL_NUMBER,
3173 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3174 x_result_type OUT NOCOPY VARCHAR2
3175 ) IS
3176
3177 d_mod CONSTANT VARCHAR2(100) := D_validate_style_id;
3178 l_results_count NUMBER :=0;
3179 l_clm_style_id_tbl PO_TBL_NUMBER;
3180 l_data_key NUMBER := NULL;
3181 l_intf_header_id_tbl PO_TBL_NUMBER;
3182 l_style_id_tbl PO_TBL_NUMBER;
3183 BEGIN
3184 -- skip the validation for non-CLM documents
3185 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
3186 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3187 RETURN;
3188 END IF;
3189
3190 IF PO_LOG.d_proc THEN
3191 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3192 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
3193 END IF;
3194 IF (x_results IS NULL) THEN
3195 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3196 END IF;
3197
3198 SELECT style_id
3199 BULK COLLECT INTO l_clm_style_id_tbl
3200 FROM po_doc_style_headers
3201 WHERE status = 'ACTIVE'
3202 AND Nvl(clm_flag,'N') = 'Y';
3203
3204 l_data_key := PO_CORE_S.get_session_gt_nextval();
3205
3206 FORALL i IN 1..p_intf_header_id.Count
3207 INSERT INTO po_session_gt(KEY,num1,num2)
3208 SELECT l_data_key,
3209 p_intf_header_id(i),
3210 p_style_id_tbl(i)
3211 FROM dual
3212 WHERE p_style_id_tbl(i) IS NULL
3213 OR p_style_id_tbl(i) NOT IN ( SELECT column_value FROM TABLE(l_clm_style_id_tbl) );
3214
3215 DELETE FROM po_session_gt
3216 WHERE key = l_data_key
3217 RETURNING num1,num2 BULK COLLECT INTO l_intf_header_id_tbl,l_style_id_tbl;
3218
3219 FOR i IN 1..l_intf_header_id_tbl.Count
3220 LOOP
3221 IF l_style_id_tbl(i) IS NULL
3222 THEN
3223 x_results.add_result(
3224 p_entity_type => c_ENTITY_TYPE_HEADER
3225 , p_entity_id => l_intf_header_id_tbl(i)
3226 , p_column_name => c_STYLE_ID
3227 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
3228 );
3229 ELSE
3230 x_results.add_result(
3231 p_entity_type => c_ENTITY_TYPE_HEADER
3232 , p_entity_id => l_intf_header_id_tbl(i)
3233 , p_column_name => c_STYLE_ID
3234 , p_column_val => To_Char(l_style_id_tbl(i))
3235 , p_message_name => 'PO_STYLE_ID_INVALID'
3236 );
3237 END IF;
3238 END LOOP;
3239
3240 IF l_intf_header_id_tbl.count > 0 THEN
3241 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3242 ELSE
3243 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3244 END IF;
3245 IF PO_LOG.d_proc THEN
3246 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3247 END IF;
3248 EXCEPTION
3249 WHEN OTHERS THEN
3250 IF PO_LOG.d_exc THEN
3251 PO_LOG.exc(d_mod,0,NULL);
3252 END IF;
3253 RAISE;
3254
3255 END validate_style_id;
3256
3257
3258
3259 /*Style id of the source idv should be same as that of the po*/
3260 PROCEDURE validate_idv_style_id
3261 (
3262 p_intf_header_id IN PO_TBL_NUMBER,
3263 p_style_id_tbl IN PO_TBL_NUMBER,
3264 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
3265 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3266 x_result_type OUT NOCOPY VARCHAR2
3267 ) IS
3268
3269 d_mod CONSTANT VARCHAR2(100) := D_validate_idv_style_id;
3270 l_data_key NUMBER := NULL;
3271 l_intf_header_id_tbl PO_TBL_NUMBER;
3272 l_style_id_tbl PO_TBL_NUMBER;
3273 BEGIN
3274
3275 IF PO_LOG.d_proc THEN
3276 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3277 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
3278 END IF;
3279 IF (x_results IS NULL) THEN
3280 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3281 END IF;
3282
3283 l_data_key := PO_CORE_S.get_session_gt_nextval();
3284
3285 FORALL i IN 1..p_intf_header_id.Count
3286 INSERT INTO po_session_gt(KEY,num1, num2)
3287 SELECT l_data_key,
3288 p_intf_header_id(i),
3289 p_style_id_tbl(i)
3290 FROM po_headers_all
3291 WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
3292 AND po_header_id = p_clm_source_document_id_tbl(i)
3293 AND Nvl(style_id,-1) <> Nvl(p_style_id_tbl(i),-1);
3294
3295 DELETE FROM po_session_gt
3296 WHERE key = l_data_key
3297 RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_style_id_tbl;
3298
3299 FOR i IN 1..l_intf_header_id_tbl.Count
3300 LOOP
3301 x_results.add_result(
3302 p_entity_type => c_ENTITY_TYPE_HEADER
3303 , p_entity_id => l_intf_header_id_tbl(i)
3304 , p_column_name => c_STYLE_ID
3305 , p_column_val => To_Char(l_style_id_tbl(i))
3306 , p_message_name => 'PO_IDV_STYLE_INVALID'
3307 );
3308 END LOOP;
3309
3310 IF l_intf_header_id_tbl.count > 0 THEN
3311 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3312 ELSE
3313 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3314 END IF;
3315
3316 IF PO_LOG.d_proc THEN
3317 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3318 END IF;
3319 EXCEPTION
3320 WHEN OTHERS THEN
3321 IF PO_LOG.d_exc THEN
3322 PO_LOG.exc(d_mod,0,NULL);
3323 END IF;
3324 RAISE;
3325
3326 END validate_idv_style_id;
3327
3328
3329 /*Vendor_id info should be same as that on the source idv*/
3330 PROCEDURE validate_po_idv_vendor_id
3331 (
3332 p_intf_header_id IN PO_TBL_NUMBER,
3333 p_vendor_id_tbl IN PO_TBL_NUMBER,
3334 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
3335 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3336 x_result_type OUT NOCOPY VARCHAR2
3337 ) IS
3338
3339 d_mod CONSTANT VARCHAR2(100) := D_validate_po_idv_vendor_id;
3340 l_data_key NUMBER := NULL;
3341 l_intf_header_id_tbl PO_TBL_NUMBER;
3342 l_vendor_id_tbl PO_TBL_NUMBER;
3343 BEGIN
3344
3345 IF PO_LOG.d_proc THEN
3346 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3347 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_vendor_id_tbl);
3348 END IF;
3349 IF (x_results IS NULL) THEN
3350 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3351 END IF;
3352
3353 l_data_key := PO_CORE_S.get_session_gt_nextval();
3354
3355 FORALL i IN 1..p_intf_header_id.Count
3356 INSERT INTO po_session_gt(KEY,num1, num2)
3357 SELECT l_data_key,
3358 p_intf_header_id(i),
3359 p_vendor_id_tbl(i)
3360 FROM po_headers_all
3361 WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
3362 AND p_vendor_id_tbl(i) IS NOT NULL
3363 AND po_header_id = p_clm_source_document_id_tbl(i)
3364 AND vendor_id <> p_vendor_id_tbl(i);
3365
3366 DELETE FROM po_session_gt
3367 WHERE key = l_data_key
3368 RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_vendor_id_tbl;
3369
3370 FOR i IN 1..l_intf_header_id_tbl.Count
3371 LOOP
3372 x_results.add_result(
3373 p_entity_type => c_ENTITY_TYPE_HEADER
3374 , p_entity_id => l_intf_header_id_tbl(i)
3375 , p_column_name => c_VENDOR_ID
3376 , p_column_val => To_Char(l_vendor_id_tbl(i))
3377 , p_message_name => 'PO_IDV_VENDOR_INVALID'
3378 );
3379 END LOOP;
3380
3381 IF l_intf_header_id_tbl.count > 0 THEN
3382 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3383 ELSE
3384 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3385 END IF;
3386
3387 IF PO_LOG.d_proc THEN
3388 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3389 END IF;
3390 EXCEPTION
3391 WHEN OTHERS THEN
3392 IF PO_LOG.d_exc THEN
3393 PO_LOG.exc(d_mod,0,NULL);
3394 END IF;
3395 RAISE;
3396
3397 END validate_po_idv_vendor_id;
3398
3399
3400
3401 /*Vendor_site_id info should be same as that on the source idv*/
3402 PROCEDURE validate_po_idv_vendor_site_id
3403 (
3404 p_intf_header_id IN PO_TBL_NUMBER,
3405 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
3406 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
3407 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3408 x_result_type OUT NOCOPY VARCHAR2
3409 ) IS
3410
3411 d_mod CONSTANT VARCHAR2(100) := D_val_po_idv_vendor_site_id;
3412 l_data_key NUMBER := NULL;
3413 l_intf_header_id_tbl PO_TBL_NUMBER;
3414 l_vendor_site_id_tbl PO_TBL_NUMBER;
3415 BEGIN
3416
3417 IF PO_LOG.d_proc THEN
3418 PO_LOG.proc_begin(d_mod,'p_intf_header_id',p_intf_header_id);
3419 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_vendor_site_id_tbl);
3420 END IF;
3421 IF (x_results IS NULL) THEN
3422 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3423 END IF;
3424
3425 l_data_key := PO_CORE_S.get_session_gt_nextval();
3426
3427 FORALL i IN 1..p_intf_header_id.Count
3428 INSERT INTO po_session_gt(KEY,num1, num2)
3429 SELECT l_data_key,
3430 p_intf_header_id(i),
3431 p_vendor_site_id_tbl(i)
3432 FROM po_headers_all
3433 WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
3434 AND p_vendor_site_id_tbl(i) IS NOT NULL
3435 AND po_header_id = p_clm_source_document_id_tbl(i)
3436 AND vendor_site_id <> p_vendor_site_id_tbl(i);
3437
3438 DELETE FROM po_session_gt
3439 WHERE key = l_data_key
3440 RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_vendor_site_id_tbl;
3441
3442 FOR i IN 1..l_intf_header_id_tbl.Count
3443 LOOP
3444 x_results.add_result(
3445 p_entity_type => c_ENTITY_TYPE_HEADER
3446 , p_entity_id => l_intf_header_id_tbl(i)
3447 , p_column_name => c_VENDOR_SITE_ID
3448 , p_column_val => To_Char(l_vendor_site_id_tbl(i))
3449 , p_message_name => 'PO_IDV_VENDOR_SITE_INVALID'
3450 );
3451 END LOOP;
3452
3453 IF l_intf_header_id_tbl.count > 0 THEN
3454 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3455 ELSE
3456 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3457 END IF;
3458
3459 IF PO_LOG.d_proc THEN
3460 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3461 END IF;
3462 EXCEPTION
3463 WHEN OTHERS THEN
3464 IF PO_LOG.d_exc THEN
3465 PO_LOG.exc(d_mod,0,NULL);
3466 END IF;
3467 RAISE;
3468
3469 END validate_po_idv_vendor_site_id;
3470
3471
3472 --<Bug 16474640> : This validation avoids null PAR/MOD document Number
3473 PROCEDURE validate_mod_number(
3474 p_header_id_tbl IN PO_TBL_NUMBER,
3475 p_draft_type_tbl IN PO_TBL_VARCHAR30,
3476 p_mod_number_tbl IN PO_TBL_VARCHAR100,
3477 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
3478 x_result_type OUT NOCOPY VARCHAR2
3479 )
3480 IS
3481 d_mod CONSTANT VARCHAR2(100) := D_validate_mod_number;
3482 l_results_count NUMBER;
3483 BEGIN
3484
3485 IF PO_LOG.d_proc THEN
3486 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
3487 PO_LOG.proc_begin(d_mod,'p_draft_type_tbl',p_draft_type_tbl);
3488 PO_LOG.proc_begin(d_mod,'p_mod_number_tbl',p_mod_number_tbl);
3489 END IF;
3490
3491 IF (x_results IS NULL) THEN
3492 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3493 END IF;
3494
3495 l_results_count := x_results.result_type.COUNT;
3496
3497 FOR i IN 1 .. p_header_id_tbl.COUNT LOOP
3498 IF (Nvl(p_draft_type_tbl(i),'*') IN ('MOD', 'PAR'))
3499 AND p_mod_number_tbl(i) IS NULL
3500 THEN
3501 x_results.add_result(
3502 p_entity_type => c_ENTITY_TYPE_HEADER
3503 , p_entity_id => p_header_id_tbl(i)
3504 , p_column_name => c_MODIFICATION_NUMBER
3505 , p_message_name => 'PO_INVALID_DOC_NUMBER'
3506 );
3507 END IF;
3508 END LOOP;
3509
3510 IF (l_results_count < x_results.result_type.COUNT) THEN
3511 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3512 ELSE
3513 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3514 END IF;
3515
3516 IF PO_LOG.d_proc THEN
3517 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3518 END IF;
3519 EXCEPTION
3520 WHEN OTHERS THEN
3521 IF PO_LOG.d_exc THEN
3522 PO_LOG.exc(d_mod,0,NULL);
3523 END IF;
3524 RAISE;
3525 END validate_mod_number;
3526
3527 END PO_VAL_HEADERS;