[Home] [Help]
PACKAGE BODY: APPS.PON_AUC_INTERFACE_TABLE_PKG
Source
1 PACKAGE BODY pon_auc_interface_table_pkg as
2 /* $Header: PONAITB.pls 120.14.12020000.4 2013/05/22 05:32:06 rakearun ship $ */
3
4 PROCEDURE add_ip_descs_on_new_lines(p_batch_id IN VARCHAR2,
5 p_default_attr_group IN VARCHAR2,
6 p_ip_attr_default_option IN VARCHAR2);
7
8 PROCEDURE add_ip_descs_on_updated_lines(p_batch_id IN VARCHAR2,
9 p_default_attr_group IN VARCHAR2,
10 p_ip_attr_default_option IN VARCHAR2);
11
12 FUNCTION get_max_attr_seq_num(p_batch_id IN NUMBER,
13 p_interface_line_id IN NUMBER) RETURN NUMBER;
14
15 FUNCTION get_attr_group_seq_num(p_batch_id IN NUMBER,
16 p_interface_line_id IN NUMBER,
17 p_attr_group IN VARCHAR2) RETURN NUMBER;
18
19 FUNCTION get_attr_max_disp_seq_num(p_batch_id IN NUMBER,
20 p_interface_line_id IN NUMBER,
21 p_attr_group IN VARCHAR2) RETURN NUMBER;
22
23 PROCEDURE validate_price_elements(
24 p_source VARCHAR2,
25 p_batch_id NUMBER,
26 p_fnd_currency_precision NUMBER,
27 p_num_price_decimals NUMBER
28 ) AS
29 l_auction_header_id NUMBER;
30 l_message_suffix VARCHAR2(2);
31 BEGIN
32 -- init vars
33 BEGIN
34 select max(pape.auction_header_id)
35 into l_auction_header_id
36 from pon_auc_price_elements_int pape
37 where pape.batch_id = p_batch_id;
38
39 select pon_auction_pkg.get_message_suffix(doc.internal_name)
40 into l_message_suffix
41 from pon_auction_headers_all pah,
45 EXCEPTION
42 pon_auc_doctypes doc
43 where pah.auction_header_id = l_auction_header_id
44 and pah.doctype_id = doc.doctype_id;
46 WHEN no_data_found THEN
47 l_message_suffix := NULL;
48 l_auction_header_id := NULL;
49 END;
50
51 -- Perform data population
52 -- populate PRICE_ELEMENT_TYPE_ID and DESCRIPTION
53 update pon_auc_price_elements_int pape
54 set (pape.price_element_type_id, pape.description) = (
55 select pet.price_element_type_id,
56 pet.description
57 from pon_price_element_types_tl pet
58 where pet.name = pape.price_element_type_name
59 and pet.language = userenv('LANG')
60 )
61 where pape.batch_id = p_batch_id
62 and pape.price_element_type_name is not null;
63
64 -- populate PRICING_BASIS
65 update pon_auc_price_elements_int pape
66 set pape.pricing_basis = (
67 select lookup_code
68 from fnd_lookups
69 where lookup_type = 'PON_PRICING_BASIS'
70 and meaning = pape.pricing_basis_name
71 )
72 where pape.batch_id = p_batch_id
73 and pape.pricing_basis_name is not null;
74
75 -- if null, default from price_element_type
76 update pon_auc_price_elements_int pape
77 set pape.pricing_basis = (
78 select pricing_basis
79 from pon_price_element_types pet
80 where pet.price_element_type_id = pape.price_element_type_id
81 )
82 where pape.batch_id = p_batch_id
83 and pape.pricing_basis_name is null;
84
85 -------------- Validations start here ------------------------
86
87 INSERT ALL
88
89 -- validate that Line Price cannot be added as a price factor
90
91 WHEN
92 (
93 selected_price_element_type_id = -10
94 )
95
96 THEN INTO pon_interface_errors
97 (
98 BATCH_ID,
99 INTERFACE_LINE_ID,
100 TABLE_NAME,
101 COLUMN_NAME,
102 ERROR_MESSAGE_NAME,
103 ERROR_VALUE
104 )
105
106 VALUES
107 (
108 selected_batch_id,
109 selected_interface_line_id,
110 'PON_AUC_PRICE_ELEMENTS_INT',
111 fnd_message.get_string('PON', 'PON_AUC_PRICE_ELEMENT_NAME'),
112 'PON_AUC_CANNOT_UPLOAD_LP_PF',
113 sel_price_element_type_name
114 )
115
116 -- PRICE_ELEMENT_TYPE_ID
117
118 WHEN
119 (
120 selected_price_element_type_id is null
121 )
122 THEN into pon_interface_errors
123 (
124 BATCH_ID,
125 INTERFACE_LINE_ID,
126 TABLE_NAME,
127 COLUMN_NAME,
128 ERROR_MESSAGE_NAME,
129 ERROR_VALUE
130 )
131 VALUES
132 (
133 selected_batch_id,
134 selected_interface_line_id,
135 'PON_AUC_PRICE_ELEMENTS_INT',
136 fnd_message.get_string('PON','PON_AUC_PRICE_ELEMENT_NAME'),
137 'PON_AUC_PE_INVALID_VALUE',
138 sel_price_element_type_name
139 )
140
141 -- PRICING_BASIS
142
143 WHEN
144 (
145 selected_pricing_basis is null
146 )
147 THEN into pon_interface_errors
148 (
149 BATCH_ID,
150 INTERFACE_LINE_ID,
151 TABLE_NAME,
152 COLUMN_NAME,
153 ERROR_MESSAGE_NAME,
154 ERROR_VALUE
155 )
156 VALUES
157 (
158 selected_batch_id,
159 selected_interface_line_id,
160 'PON_AUC_PRICE_ELEMENTS_INT',
161 fnd_message.get_string('PON','PON_AUC_PRICING_BASIS'),
162 'PON_AUC_BASIS_INVAID_VALUE',
163 selected_pricing_basis_name
164 )
165
166 SELECT
167
168 pape.batch_id selected_batch_id,
169 pape.interface_line_id selected_interface_line_id,
170 pape.price_element_type_name sel_price_element_type_name,
171 pape.price_element_type_id selected_price_element_type_id,
172 pape.pricing_basis selected_pricing_basis,
173 pape.pricing_basis_name selected_pricing_basis_name
174
175 FROM
176 pon_auc_price_elements_int pape
177
178 WHERE
179 pape.batch_id = p_batch_id;
180
181
182 INSERT ALL
183
184 -- Consider moving isRequired checks here... [doctype reqd?]
185 -- SEQUENCE_NUMBER -- should never be displayed to user
186
187 WHEN
188 (
189 selected_sequence_number is null
190 )
191 THEN into pon_interface_errors
192 (
193 BATCH_ID,
194 INTERFACE_LINE_ID,
195 TABLE_NAME,
196 COLUMN_NAME,
197 ERROR_MESSAGE_NAME,
198 ERROR_VALUE
199 )
200 VALUES
201 (
202 selected_batch_id,
203 selected_interface_line_id,
204 'PON_AUC_PRICE_ELEMENTS_INT',
205 'SEQUENCE_NUMBER',
206 'PON_CAT_DISP_SEQ_M',
207 selected_sequence_number
208 )
209
210 -- validate precision (if PRICING_BASIS not null)
211 -- use p_fnd_currency_precision = if PRICING_BASIS is FIXED_AMOUNT
212
213 WHEN
214 (
215 selected_pricing_basis = 'FIXED_AMOUNT'
216 and selected_precision > p_fnd_currency_precision
217 )
218 THEN into pon_interface_errors
219 (
220 BATCH_ID,
221 INTERFACE_LINE_ID,
222 TABLE_NAME,
223 COLUMN_NAME,
224 ERROR_MESSAGE_NAME,
225 ERROR_VALUE
229 selected_batch_id,
226 )
227 VALUES
228 (
230 selected_interface_line_id,
231 'PON_AUC_PRICE_ELEMENTS_INT',
232 fnd_message.get_string('PON','PON_AUC_TARGET_VALUE'),
233 'PON_AUC_INVALID_PRECISION',
234 selected_value
235 )
236
237 -- use p_num_price_decimals = if PRICING_BASIS is PER_UNIT
238
239 WHEN
240 (
241 selected_pricing_basis = 'PER_UNIT'
242 and selected_precision > p_num_price_decimals
243 )
244 THEN into pon_interface_errors
245 (
246 BATCH_ID,
247 INTERFACE_LINE_ID,
248 TABLE_NAME,
249 COLUMN_NAME,
250 ERROR_MESSAGE_NAME,
251 ERROR_VALUE
252 )
253 VALUES
254 (
255 selected_batch_id,
256 selected_interface_line_id,
257 'PON_AUC_PRICE_ELEMENTS_INT',
258 fnd_message.get_string('PON','PON_AUC_TARGET_VALUE'),
259 'PON_AUC_INVALID_PRECISION_AU'||l_message_suffix,
260 selected_value
261 )
262
263 -- validate value is positive (if given)
264
265 WHEN
266 (
267 selected_value < 0
268 )
269 THEN into pon_interface_errors
270 (
271 BATCH_ID,
272 INTERFACE_LINE_ID,
273 TABLE_NAME,
274 COLUMN_NAME,
275 ERROR_MESSAGE_NAME,
276 ERROR_VALUE
277 )
278 VALUES
279 (
280 selected_batch_id,
281 selected_interface_line_id,
282 'PON_AUC_PRICE_ELEMENTS_INT',
283 fnd_message.get_string('PON','PON_AUC_TARGET_VALUE'),
284 'PON_AUC_POSITIVE_OR_ZERO',
285 selected_value
286 )
287
288 -- validate display target flag is Y/N
289
290 WHEN
291 (
292 nvl(selected_display_target_flag,'N') not in ('Y','N')
293 )
294 THEN into pon_interface_errors
295 (
296 BATCH_ID,
297 INTERFACE_LINE_ID,
298 TABLE_NAME,
299 COLUMN_NAME,
300 ERROR_MESSAGE_NAME,
301 ERROR_VALUE
302 )
303 VALUES
304 (
305 selected_batch_id,
306 selected_interface_line_id,
307 'PON_AUC_PRICE_ELEMENTS_INT',
308 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
309 'PON_AUCTS_INV_PR_QT_VAL',
310 selected_display_target_flag
311 )
312
313 -- value must be given if display target flag = Y
314
315 WHEN
316 (
317 selected_value is null and
318 selected_display_target_flag = 'Y'
319 )
320
321 THEN into pon_interface_errors
322 (
323 BATCH_ID,
324 INTERFACE_LINE_ID,
325 TABLE_NAME,
326 COLUMN_NAME,
327 ERROR_MESSAGE_NAME,
328 ERROR_VALUE
329 )
330 VALUES
331 (
332 selected_batch_id,
333 selected_interface_line_id,
334 'PON_AUC_PRICE_ELEMENTS_INT',
335 fnd_message.get_string('PON','PON_AUC_TARGET_VALUE'),
336 'PON_AUC_POSITIVE_OR_ZERO',
337 selected_value
338 )
339
340 -- the only allowed pricing bases are FIXED_AMOUNT and PERCENTAGE
341 -- if the line type of the line is fixed price
342
343 WHEN
344 (
345 selected_pricing_basis <> 'FIXED_AMOUNT'
346 and selected_pricing_basis <> 'PERCENTAGE'
347 and sel_order_type_lookup_code = 'FIXED PRICE'
348 )
349 THEN into pon_interface_errors
350 (
351 BATCH_ID,
352 INTERFACE_LINE_ID,
353 TABLE_NAME,
354 COLUMN_NAME,
355 ERROR_MESSAGE_NAME,
356 ERROR_VALUE
357 )
358 VALUES
359 (
360 selected_batch_id,
361 selected_interface_line_id,
362 'PON_AUC_PRICE_ELEMENTS_INT',
363 fnd_message.get_string('PON','PON_AUC_PRICING_BASIS'),
364 'PON_AUC_CANNOT_UPLOAD_PF_2',
365 selected_pricing_basis_name
366 )
367
368 -- validate price element type is active
369
370 WHEN
371 (
372 selected_enabled_flag = 'N'
373 )
374 THEN into pon_interface_errors
375 (
376 BATCH_ID,
377 INTERFACE_LINE_ID,
378 TABLE_NAME,
379 COLUMN_NAME,
380 ERROR_MESSAGE_NAME,
381 ERROR_VALUE
382 )
383 VALUES
384 (
385 selected_batch_id,
386 selected_interface_line_id,
387 'PON_AUC_PRICE_ELEMENTS_INT',
388 fnd_message.get_string('PON','PON_AUC_PRICE_ELEMENT_NAME'),
389 'PON_AUC_AUCTION_INA_PES_SP',
390 sel_price_element_type_name
391 )
392
393 SELECT
394
395 pape.batch_id selected_batch_id,
396 pape.interface_line_id selected_interface_line_id,
397 pape.price_element_type_name sel_price_element_type_name,
398 pape.value selected_value,
399 pape.display_target_flag selected_display_target_flag,
400 pape.precision selected_precision,
401 pape.pricing_basis selected_pricing_basis,
402 pape.pricing_basis_name selected_pricing_basis_name,
403 pape.sequence_number selected_sequence_number,
404 ip.order_type_lookup_code sel_order_type_lookup_code,
405 pet.enabled_flag selected_enabled_flag
406
407 FROM
408 pon_auc_price_elements_int pape,
409 pon_item_prices_interface ip,
410 pon_price_element_types pet
411
412 WHERE
413 pape.batch_id = p_batch_id
414 and pape.price_element_type_id = pet.price_element_type_id
415 and pape.batch_id = ip.batch_id
419 -- perform duplicate checks (using price element type id)
416 AND pape.auction_header_id = ip.auction_header_id
417 AND pape.interface_line_id = ip.interface_line_id;
418
420 insert into pon_interface_errors (
421 BATCH_ID,
422 INTERFACE_LINE_ID,
423 TABLE_NAME,
424 COLUMN_NAME,
425 ERROR_MESSAGE_NAME,
426 ERROR_VALUE
427 )
428 select
429 pape1.batch_id,
430 pape1.interface_line_id,
431 'PON_AUC_PRICE_ELEMENTS_INT',
432 fnd_message.get_string('PON','PON_AUC_PRICE_ELEMENT_NAME'),
433 'PON_DUPLICATE_WARNING_PRICE',
434 pape1.price_element_type_name
435 from pon_auc_price_elements_int pape1,
436 pon_auc_price_elements_int pape2
437 where pape1.batch_id = p_batch_id
438 and pape1.batch_id = pape2.batch_id
439 and pape1.interface_line_id = pape2.interface_line_id
440 and pape1.price_element_type_id = pape2.price_element_type_id
441 and pape1.sequence_number <> pape2.sequence_number;
442
443 -- validate that in the amendment creation flow or new negotiation round creation flow
444 -- an uploaded supplier price factor cannot replace an existing buyer price factor with the same name
445 -- if the buyer price factor already has supplier values defined
446 insert into pon_interface_errors (
447 BATCH_ID,
448 INTERFACE_LINE_ID,
449 TABLE_NAME,
450 COLUMN_NAME,
451 ERROR_MESSAGE_NAME,
452 ERROR_VALUE
453 )
454 select
455 int_pe.batch_id,
456 int_pe.interface_line_id,
457 'PON_AUC_PRICE_ELEMENTS_INT',
458 fnd_message.get_string('PON','PON_AUC_PRICE_ELEMENT_NAME'),
459 'PON_AUC_CANNOT_UPLOAD_PF_1',
460 int_pe.price_element_type_name
461 from
462 pon_price_elements auction_pe,
463 pon_auc_price_elements_int int_pe
464 where
465 int_pe.batch_id = p_batch_id
466 and auction_pe.auction_header_id = int_pe.auction_header_id
467 and auction_pe.line_number = int_pe.auction_line_number
468 and auction_pe.price_element_type_id = int_pe.price_element_type_id
469 and auction_pe.pf_type = 'BUYER'
470 and int_pe.pf_type = 'SUPPLIER'
471 and exists (select 1
472 from pon_pf_supplier_values pf_values
473 where
474 pf_values.auction_header_id = auction_pe.auction_header_id
475 and pf_values.line_number = auction_pe.line_number
476 and pf_values.pf_seq_number = auction_pe.sequence_number);
477
478 RETURN ;
479 END;
480
481
482 PROCEDURE validate_header_attributes(
483 p_source VARCHAR2,
484 p_batch_id NUMBER,
485 p_party_id NUMBER
486 ) AS
487 l_auction_header_id pon_auc_attributes_interface.auction_header_id%TYPE;
488 l_message_suffix VARCHAR2(2);
489 l_hdr_attr_enable_weights VARCHAR2(1);
490 BEGIN
491 -- init vars
492 BEGIN
493 select max(pai.auction_header_id)
494 into l_auction_header_id
495 from pon_auc_attributes_interface pai
496 where pai.batch_id = p_batch_id;
497
498 select hdr_attr_enable_weights
499 into l_hdr_attr_enable_weights
500 from pon_auction_headers_all pah
501 where pah.auction_header_id = l_auction_header_id;
502
503 select pon_auction_pkg.get_message_suffix(doc.internal_name)
504 into l_message_suffix
505 from pon_auction_headers_all pah,
506 pon_auc_doctypes doc
507 where pah.auction_header_id = l_auction_header_id
508 and pah.doctype_id = doc.doctype_id;
509 EXCEPTION
510 WHEN no_data_found THEN
511 l_message_suffix := NULL;
512 l_auction_header_id := NULL;
513 l_hdr_attr_enable_weights := NULL;
514 END;
515
516 -- This is a call to common validate Attributes.last var indicates header attr.
517 validate_attributes(p_source,p_batch_id,p_party_id,true);
518
519 INSERT ALL
520
521 -- validate that the weight is between 0 and 100.
522 when
523 (
524 l_hdr_attr_enable_weights = 'Y'
525 and selected_auction_line_number = -1
526 and selected_weight is not null
527 and (selected_weight > 100
528 or selected_weight < 0 )
529 )
530 then into pon_interface_errors
531 (
532 BATCH_ID,
533 INTERFACE_LINE_ID,
534 TABLE_NAME,
535 COLUMN_NAME,
536 ERROR_MESSAGE_NAME,
537 ERROR_VALUE
538 )
539 values
540 (
541 selected_batch_id,
542 selected_interface_line_id,
543 'PON_AUC_ATTRIBUTES_INTERFACE',
544 fnd_message.get_string('PON','PON_AUCTS_ATTR_WEIGHT'),
545 'PON_AUC_WEIGHT_RANGE',
546 selected_weight
547 )
548
549 --validate that the score is not entered for Display only attributes.
550
551 when
552 (
553 selected_auction_line_number = -1
554 and (selected_aTTR_MAX_SCORE is not null and selected_ATTR_MAX_SCORE <> 0)
555 and selected_DISPLAY_ONLY_FLAG = 'Y'
556 )
557 then into pon_interface_errors
558 (
559 BATCH_ID,
560 INTERFACE_LINE_ID,
561 TABLE_NAME,
562 COLUMN_NAME,
563 ERROR_MESSAGE_NAME,
564 ERROR_VALUE
565 )
566 values
567 (
568 selected_batch_id,
569 selected_interface_line_id,
570 'PON_AUC_ATTRIBUTES_INTERFACE',
571 fnd_message.get_string('PON','PON_AUC_SCORE'),
572 'PON_AUCTS_DISP_ATTR_NO_SCORES',
573 selected_ATTR_MAX_SCORE
574 )
575
576 -- validate that the score is greater than zero.
580 and selected_ATTR_MAX_SCORE is not null
577 when
578 (
579 selected_auction_line_number = -1
581 and selected_ATTR_MAX_SCORE < 0
582 )
583 then into pon_interface_errors
584 (
585 BATCH_ID,
586 INTERFACE_LINE_ID,
587 TABLE_NAME,
588 COLUMN_NAME,
589 ERROR_MESSAGE_NAME,
590 ERROR_VALUE
591 )
592 values
593 (
594 selected_batch_id,
595 selected_interface_line_id,
596 'PON_AUC_ATTRIBUTES_INTERFACE',
597 fnd_message.get_string('PON','PON_AUC_SCORE'),
598 'PON_AUC_INVALID_MAXSCORE_RANGE',
599 selected_ATTR_MAX_SCORE
600 )
601
602 SELECT
603 pai.batch_id selected_batch_id,
604 pai.interface_line_id selected_interface_line_id,
605 pai.weight selected_weight,
606 pai.auction_line_number selected_auction_line_number,
607 pai.attr_max_score selected_attr_max_score,
608 pai.display_only_flag selected_display_only_flag
609
610 from
611 pon_auc_attributes_interface pai
612
613 where
614 pai.batch_id = p_batch_id;
615
616
617 END validate_header_attributes;
618
619 -- for line attributes.
620 PROCEDURE validate_attributes(
621 p_source VARCHAR2,
622 p_batch_id NUMBER,
623 p_party_id NUMBER
624 ) AS
625 BEGIN
626 -- This is a call to common validate Attributes.last var indicates header attr.
627 validate_attributes(p_source,p_batch_id,p_party_id,false);
628 END validate_attributes;
629
630 PROCEDURE validate_attributes(
631 p_source VARCHAR2,
632 p_batch_id NUMBER,
633 p_party_id NUMBER,
634 p_attr_type_header BOOLEAN
635 ) AS
636 l_auction_header_id pon_auc_attributes_interface.auction_header_id%TYPE;
637 l_message_suffix VARCHAR2(2);
638 l_group_pref_name VARCHAR2(40);
639 l_group_lookup_type VARCHAR2(40);
640 l_attr_type_header VARCHAR2(1);
641 BEGIN
642
643 IF (p_attr_type_header) THEN
644
645 l_attr_type_header := 'Y';
646 ELSE
647
648 l_attr_type_header := 'N';
649 END IF;
650
651 -- init vars
652
653 BEGIN
654 select max(pai.auction_header_id)
655 into l_auction_header_id
656 from pon_auc_attributes_interface pai
657 where pai.batch_id = p_batch_id;
658
659 select pon_auction_pkg.get_message_suffix(doc.internal_name)
660 into l_message_suffix
661 from pon_auction_headers_all pah,
662 pon_auc_doctypes doc
663 where pah.auction_header_id = l_auction_header_id
664 and pah.doctype_id = doc.doctype_id;
665 EXCEPTION
666 WHEN no_data_found THEN
667 l_message_suffix := NULL;
668 l_auction_header_id := NULL;
669 l_group_pref_name := NULL;
670 l_group_lookup_type := NULL;
671 END;
672
673 -- GROUP Check Starts.
674 -- default GROUP_CODE as General for lines with group_name as null.
675 IF ( not p_attr_type_header ) THEN
676 l_group_pref_name := 'LINE_ATTR_DEFAULT_GROUP';
677 l_group_lookup_type := 'PON_LINE_ATTRIBUTE_GROUPS';
678 END IF;
679
680
681 -- Populate data
682 -- RESPONSE_TYPE
683 -- bug 3373002
684 -- need to compare apple with apple, response type values are from fnd messages
685 -- so also validate against messages, not lookups.
686 IF ( p_attr_type_header ) THEN
687 update pon_auc_attributes_interface pai
688 set response_type = decode(response_type_name,
689 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'), 'REQUIRED',
690 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'), 'OPTIONAL',
691 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'), 'DISPLAY_ONLY',
692 fnd_message.get_string('PON','PON_AUCTS_INTERNAL'), 'INTERNAL',
693 null)
694 where pai.batch_id = p_batch_id
695 and pai.response_type_name is not null;
696 ELSE
697 update pon_auc_attributes_interface pai
698 set response_type = decode(response_type_name,
699 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'), 'REQUIRED',
700 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'), 'OPTIONAL',
701 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'), 'DISPLAY_ONLY',
702 null)
703 where pai.batch_id = p_batch_id
704 and pai.response_type_name is not null;
705 END IF;
706
707 -- MANDATORY_FLAG
708 -- DISPLAY_ONLY_FLAG
709 -- INTERNAL_ATTR_FLAG
710 update
711 pon_auc_attributes_interface
712 set
713 mandatory_flag = decode(response_type,'REQUIRED','Y','N'),
714 display_only_flag = decode(response_type,'DISPLAY_ONLY','Y','N'),
715 internal_attr_flag = decode(response_type,'INTERNAL','Y','N')
716 where
717 batch_id = p_batch_id;
718
719
720 -- Not to be Done for Header Section.
721 IF ( not p_attr_type_header ) THEN
722 update pon_auc_attributes_interface paai
723 set paai.GROUP_CODE = (select nvl(ppp.preference_value,'GENERAL')
724 from pon_party_preferences ppp
725 where ppp.app_short_name = 'PON'
726 and ppp.preference_name = l_group_pref_name
727 and ppp.party_id = p_party_id)
728 where paai.batch_id = p_batch_id
729 and paai.GROUP_NAME is NULL
730 AND paai.auction_line_number <> -1;
731
732
733 -- populate GROUP_CODE. Note that the comparison is case sensitive.
734
738 from fnd_lookup_values attrGrpFlv
735 update pon_auc_attributes_interface paai
736 set paai.GROUP_CODE = (
737 select lookup_code
739 where lookup_type = l_group_lookup_type
740 --where lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
741 and meaning = paai.GROUP_NAME
742 and attrGrpFlv.LANGUAGE = userenv('LANG')
743 and attrGrpFlv.view_application_id = 0
744 and attrGrpFlv.security_group_id = 0
745 and attrGrpFlv.enabled_flag = 'Y'
746 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
747 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
748 )
749 where paai.batch_id = p_batch_id
750 and paai.GROUP_NAME is not NULL
751 AND paai.auction_line_number <> -1;
752
753 ELSE
754 -- For Header Case, if the Group Name is null, default General.
755 update pon_auc_attributes_interface paai
756 set paai.GROUP_NAME = (
757 select meaning
758 from fnd_lookup_values attrGrpFlv
759 where attrGrpFlv.lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
760 and attrGrpFlv.lookup_code = 'GENERAL'
761 and attrGrpFlv.LANGUAGE = userenv('LANG')
762 and attrGrpFlv.view_application_id = 0
763 and attrGrpFlv.security_group_id = 0
764 and attrGrpFlv.enabled_flag = 'Y'
765 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
766 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
767 )
768 where paai.batch_id = p_batch_id
769 and paai.GROUP_NAME is NULL
770 AND paai.auction_line_number = -1;
771
772 END IF;
773
774 ---------------- Validations start here ------------
775
776 INSERT ALL
777
778 -- Datatype
779 WHEN
780 (
781 selected_datatype is null
782 )
783 THEN into pon_interface_errors
784 (
785 BATCH_ID,
786 INTERFACE_LINE_ID,
787 TABLE_NAME,
788 COLUMN_NAME,
789 ERROR_MESSAGE_NAME,
790 ERROR_VALUE
791 )
792 VALUES
793 (
794 selected_batch_id,
795 selected_interface_line_id,
796 'PON_AUC_ATTRIBUTES_INTERFACE',
797 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
798 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
799 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
800 selected_datatype
801 )
802
803 -- Response Type
804
805 WHEN
806 (
807 selected_response_type is null
808 )
809 THEN into pon_interface_errors
810 (
811 BATCH_ID,
812 INTERFACE_LINE_ID,
813 TABLE_NAME,
814 COLUMN_NAME,
815 ERROR_MESSAGE_NAME,
816 ERROR_VALUE
817 )
818 VALUES
819 (
820 selected_batch_id,
821 selected_interface_line_id,
822 'PON_AUC_ATTRIBUTES_INTERFACE',
823 decode(l_group_pref_name,'LINE_ATTR_DEFAULT_GROUP',pon_auction_pkg.getMessage('PON_AUCTS_BID_RESPONSE',l_message_suffix),pon_auction_pkg.getMessage('PON_AUCTS_TYPE')),
824 'PON_CAT_INVALID_VALUE',
825 selected_response_type_name
826 )
827
828 -- Display Target Flag
829
830 WHEN
831 (
832 nvl(selected_display_target_flag,'N') not in ('Y','N')
833 )
834
835 THEN into pon_interface_errors
836 (
837 BATCH_ID,
838 INTERFACE_LINE_ID,
839 TABLE_NAME,
840 COLUMN_NAME,
841 ERROR_MESSAGE_NAME,
842 ERROR_VALUE
843 )
844 values
845 (
846 selected_batch_id,
847 selected_interface_line_id,
848 'PON_AUC_ATTRIBUTES_INTERFACE',
849 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
850 'PON_AUCTS_INV_PR_QT_VAL',
851 selected_display_target_flag
852 )
853
854 -- validate datatype [lookup_type = PON_AUCTION_ATTRIBUTE_TYPE]
855
856 when
857 (
858 selected_datatype not in (
859 select lookup_code
860 from fnd_lookups
861 where lookup_type = 'PON_AUCTION_ATTRIBUTE_TYPE'
862 )
863 )
864 then into pon_interface_errors
865 (
866 BATCH_ID,
867 INTERFACE_LINE_ID,
868 TABLE_NAME,
869 COLUMN_NAME,
870 ERROR_MESSAGE_NAME,
871 ERROR_VALUE
872 )
873 values
874 (
875 selected_batch_id,
876 selected_interface_line_id,
877 'PON_AUC_ATTRIBUTES_INTERFACE',
878 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
879 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
880 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
881 selected_datatype
882 )
883
884 -- validate display target flag is Y/N
885 when
886 (
887 nvl(selected_display_target_flag,'N') not in ('Y','N')
888 )
889 then into pon_interface_errors
890 (
891 BATCH_ID,
892 INTERFACE_LINE_ID,
893 TABLE_NAME,
894 COLUMN_NAME,
895 ERROR_MESSAGE_NAME,
896 ERROR_VALUE
897 )
898 values
899 (
900 selected_batch_id,
901 selected_interface_line_id,
902 'PON_AUC_ATTRIBUTES_INTERFACE',
903 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
904 'PON_AUCTS_INV_PR_QT_VAL',
905 selected_display_target_flag
906 )
907
908 when
909 (
910 selected_display_target_flag = 'Y'
911 and selected_value is null
912 )
913 then into pon_interface_errors
914 (
915 BATCH_ID,
919 ERROR_MESSAGE_NAME,
916 INTERFACE_LINE_ID,
917 TABLE_NAME,
918 COLUMN_NAME,
920 ERROR_VALUE
921 )
922 values
923 (
924 selected_batch_id,
925 selected_interface_line_id,
926 'PON_AUC_ATTRIBUTES_INTERFACE',
927 fnd_message.get_string('PON','PON_AUCTS_TARGET_VALUE'),
928 'PON_AUCTS_ATTR_SHOW_TARGET',
929 selected_value
930 )
931
932 -- validate value given if display_only = Y
933 when
934 (
935 selected_display_only_flag = 'Y'
936 and selected_value is null
937 -- Bug 6957765
938 and nvl(selected_display_target_flag,'N') = 'Y'
939 )
940 then into pon_interface_errors
941 (
942 BATCH_ID,
943 INTERFACE_LINE_ID,
944 TABLE_NAME,
945 COLUMN_NAME,
946 ERROR_MESSAGE_NAME,
947 ERROR_VALUE
948 )
949 values
950 (
951 selected_batch_id,
952 selected_interface_line_id,
953 'PON_AUC_ATTRIBUTES_INTERFACE',
954 pon_auction_pkg.getMessage('PON_AUCTS_ATTR_TARGET',l_message_suffix),
955 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
956 'PON_AUCTS_ATTR_DISPLAY_TARGET', 'PON_AUCTS_REQ_DISPLAY_TARGET'),
957 selected_value
958 )
959
960 -- Attribute Name
961
962 when
963 (
964 selected_attribute_name is null
965 )
966 then into pon_interface_errors
967 (
968 BATCH_ID,
969 INTERFACE_LINE_ID,
970 TABLE_NAME,
971 COLUMN_NAME,
972 ERROR_MESSAGE_NAME,
973 ERROR_VALUE
974 )
975 values
976 (
977 selected_batch_id,
978 selected_interface_line_id,
979 'PON_AUC_ATTRIBUTES_INTERFACE',
980 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP', fnd_message.get_string('PON','PON_AUCTS_ATTR'), fnd_message.get_string('PON', 'PON_AUC_REQUIREMENT')),
981 'PON_FIELD_MUST_BE_ENTERED',
982 selected_attribute_name
983 )
984
985 -- required field checks
986 -- SEQUENCE_NUMBER -- should never be displayed to user
987 -- Do not do this check for Header attributes. We will populate the sequence
988 -- when we copy them over to the AuctionAttributesVO
989 when
990 (
991 l_attr_type_header = 'N' AND
992 selected_sequence_number is null
993 )
994 then into pon_interface_errors
995 (
996 BATCH_ID,
997 INTERFACE_LINE_ID,
998 TABLE_NAME,
999 COLUMN_NAME,
1000 ERROR_MESSAGE_NAME,
1001 ERROR_VALUE
1002 )
1003 values
1004 (
1005 selected_batch_id,
1006 selected_interface_line_id,
1007 'PON_AUC_ATTRIBUTES_INTERFACE',
1008 'SEQUENCE_NUMBER',
1009 'PON_CAT_DISP_SEQ_M',
1010 selected_sequence_number
1011 )
1012
1013 -- To insert errors for the group which are invalid.
1014 when
1015 (
1016 selected_group_code is null
1017 AND l_attr_type_header = 'N'
1018 AND selected_auction_line_num <> -1 -- bug 16801086
1019 )
1020 then into pon_interface_errors
1021 (
1022 BATCH_ID,
1023 INTERFACE_LINE_ID,
1024 TABLE_NAME,
1025 COLUMN_NAME,
1026 ERROR_MESSAGE_NAME,
1027 ERROR_VALUE
1028 )
1029 values
1030 (
1031 selected_batch_id,
1032 selected_interface_line_id,
1033 'PON_AUC_ATTRIBUTES_INTERFACE',
1034 fnd_message.get_string('PON','PON_AUCTS_GROUP'),
1035 'PON_AUCTS_INVALID_GROUP',
1036 selected_group_name
1037 )
1038
1039 -- validate display target == N if attribute type is Internal
1040 when
1041 (
1042 nvl(selected_display_target_flag,'N') = 'Y'
1043 and selected_internal_attr_flag = 'Y'
1044 and l_attr_type_header = 'Y'
1045 )
1046 then into pon_interface_errors
1047 (
1048 BATCH_ID,
1049 INTERFACE_LINE_ID,
1050 TABLE_NAME,
1051 COLUMN_NAME,
1052 ERROR_MESSAGE_NAME,
1053 ERROR_VALUE
1054 )
1055 values
1056 (
1057 selected_batch_id,
1058 selected_interface_line_id,
1059 'PON_AUC_ATTRIBUTES_INTERFACE',
1060 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
1061 'PON_AUC_INTERNAL_ATT_ERROR',
1062 fnd_message.get_string('PON','PON_CORE_NO')
1063 )
1064
1065 SELECT
1066 pai.batch_id selected_batch_id,
1067 pai.interface_line_id selected_interface_line_id,
1068 pai.datatype selected_datatype,
1069 pai.value selected_value,
1070 pai.display_target_flag selected_display_target_flag,
1071 pai.display_only_flag selected_display_only_flag,
1072 pai.response_type_name selected_response_type_name,
1073 pai.response_type selected_response_type,
1074 pai.attribute_name selected_attribute_name,
1075 pai.sequence_number selected_sequence_number,
1076 pai.group_name selected_group_name,
1077 pai.group_code selected_group_code,
1078 pai.internal_attr_flag selected_internal_attr_flag,
1079 pai.auction_line_number selected_auction_line_num
1080
1081 from
1082 pon_auc_attributes_interface pai
1083 where
1084 pai.batch_id = p_batch_id;
1085
1086 -- perform duplicate checks
1087 IF ( p_attr_type_header ) THEN
1088 insert into pon_interface_errors (
1089 BATCH_ID,
1090 INTERFACE_LINE_ID,
1091 TABLE_NAME,
1092 COLUMN_NAME,
1093 ERROR_MESSAGE_NAME,
1094 ERROR_VALUE
1095 )
1096 select
1097 pai1.batch_id,
1098 pai1.interface_line_id,
1102 pai1.attribute_name
1099 'PON_AUC_ATTRIBUTES_INTERFACE',
1100 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
1101 'PON_REQUIREMENT_DUPLICATE_ATT',
1103 from pon_auc_attributes_interface pai1,
1104 pon_auc_attributes_interface pai2
1105 where pai1.batch_id = p_batch_id
1106 and pai1.batch_id = pai2.batch_id
1107 and pai1.auction_line_number = pai2.auction_line_number
1108 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
1109 and pai1.interface_line_id <> pai2.interface_line_id;
1110 ELSE
1111 insert into pon_interface_errors (
1112 BATCH_ID,
1113 INTERFACE_LINE_ID,
1114 TABLE_NAME,
1115 COLUMN_NAME,
1116 ERROR_MESSAGE_NAME,
1117 ERROR_VALUE
1118 )
1119 select
1120 pai1.batch_id,
1121 pai1.interface_line_id,
1122 'PON_AUC_ATTRIBUTES_INTERFACE',
1123 fnd_message.get_string('PON','PON_AUCTS_ATTR'),
1124 'PON_DUPLICATE_WARNING_ATTR',
1125 pai1.attribute_name
1126 from pon_auc_attributes_interface pai1,
1127 pon_auc_attributes_interface pai2
1128 where pai1.batch_id = p_batch_id
1129 and pai1.batch_id = pai2.batch_id
1130 and pai1.interface_line_id = pai2.interface_line_id
1131 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
1132 and pai1.sequence_number <> pai2.sequence_number;
1133 END IF;
1134
1135 -- duplicate check against the allready saved header attributes.
1136 IF ( p_attr_type_header ) THEN
1137 insert into pon_interface_errors (
1138 BATCH_ID,
1139 INTERFACE_LINE_ID,
1140 TABLE_NAME,
1141 COLUMN_NAME,
1142 ERROR_MESSAGE_NAME,
1143 ERROR_VALUE
1144 )
1145 select
1146 pai1.batch_id,
1147 pai1.interface_line_id,
1148 'PON_AUC_ATTRIBUTES_INTERFACE',
1149 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
1150 'PON_REQUIREMENT_DUPLICATE_ATT',
1151 pai1.attribute_name
1152 from pon_auc_attributes_interface pai1,
1153 pon_auction_attributes paa
1154 where pai1.batch_id = p_batch_id
1155 and paa.auction_header_id = pai1.auction_header_id
1156 and pai1.auction_line_number = -1
1157 and paa.line_number = pai1.auction_line_number
1158 and upper(pai1.attribute_name) = upper(paa.attribute_name);
1159 END IF;
1160
1161 END validate_attributes;
1162
1163
1164 PROCEDURE add_template_price_elements(
1165 p_batch_id NUMBER,
1166 p_auction_template_id NUMBER,
1167 p_auction_header_id NUMBER
1168 ) AS
1169 l_sequence_start NUMBER;
1170 l_auction_pf_type_allowed VARCHAR2(30);
1171 BEGIN
1172 -- select max sequence number (across all lines)
1173 select nvl(max(sequence_number), 0)
1174 into l_sequence_start
1175 from pon_auc_price_elements_int
1176 where batch_id = p_batch_id
1177 and sequence_number >= 0;
1178
1179 l_sequence_start := l_sequence_start + 10;
1180
1181 -- determine which price factor types are allowed by the negotiation
1182 SELECT pf_type_allowed
1183 INTO l_auction_pf_type_allowed
1184 FROM pon_auction_headers_all
1185 WHERE auction_header_id = p_auction_header_id;
1186
1187 -- insert template price elements for all lines
1188 insert into pon_auc_price_elements_int (
1189 BATCH_ID,
1190 INTERFACE_LINE_ID,
1191 AUCTION_HEADER_ID,
1192 AUCTION_LINE_NUMBER,
1193 SEQUENCE_NUMBER,
1194 PRICE_ELEMENT_TYPE_NAME,
1195 PRICE_ELEMENT_TYPE_ID,
1196 DESCRIPTION,
1197 PRICING_BASIS_NAME,
1198 PRICING_BASIS,
1199 VALUE,
1200 PRECISION,
1201 DISPLAY_TARGET_FLAG,
1202 PF_TYPE,
1203 DISPLAY_TO_SUPPLIERS_FLAG
1204 )
1205 select
1206 ip.batch_id,
1207 ip.interface_line_id,
1208 ip.auction_header_id,
1209 ip.auction_line_number,
1210 l_sequence_start + pe.sequence_number,
1211 petl.name,
1212 pe.price_element_type_id,
1213 petl.description,
1214 fl.meaning,
1215 pe.pricing_basis,
1216 pe.value,
1217 -1,
1218 pe.display_target_flag,
1219 pe.pf_type,
1220 pe.display_to_suppliers_flag
1221 from
1222 pon_price_elements pe,
1223 pon_price_element_types pet,
1224 pon_price_element_types_tl petl,
1225 fnd_lookups fl,
1226 pon_item_prices_interface ip
1227 where
1228 ip.batch_id = p_batch_id
1229 and pe.auction_header_id = p_auction_template_id
1230 and pe.price_element_type_id = pet.price_element_type_id
1231 and pe.price_element_type_id = petl.price_element_type_id
1232 and pe.price_element_type_id <> -10 -- why copy Item Price?
1233 and pet.enabled_flag = 'Y'
1234 and petl.language = userenv('LANG')
1235 and fl.lookup_type = 'PON_PRICING_BASIS'
1236 and fl.lookup_code = pe.pricing_basis
1237 and ip.group_type <> 'GROUP'
1238 and pe.pf_type = DECODE(l_auction_pf_type_allowed,
1239 'BOTH', pe.pf_type,
1240 'BUYER', 'BUYER',
1241 'SUPPLIER', 'SUPPLIER',
1242 'NONE');
1243
1244 -- resolve duplicate price elements by removing the Template price element
1245 delete from pon_auc_price_elements_int pape1
1246 where rowid in (
1247 select pape1.rowid
1248 from pon_auc_price_elements_int pape2
1249 where pape1.batch_id = p_batch_id
1250 and pape1.batch_id = pape2.batch_id
1251 and pape1.interface_line_id = pape2.interface_line_id
1252 and pape1.price_element_type_id = pape2.price_element_type_id
1253 and pape1.precision = -1
1254 and (pape2.precision is null or pape2.precision <> -1)
1255 );
1256 END add_template_price_elements;
1257
1258
1262 ) AS
1259 PROCEDURE add_template_attributes(
1260 p_batch_id NUMBER,
1261 p_auction_template_id NUMBER
1263 l_sequence_start NUMBER;
1264 BEGIN
1265 -- select max sequence number (across all lines)
1266 BEGIN
1267 select nvl(max(sequence_number),0)
1268 into l_sequence_start
1269 from pon_auc_attributes_interface
1270 where batch_id = p_batch_id
1271 and sequence_number >= 0;
1272 l_sequence_start := l_sequence_start + 10;
1273 EXCEPTION
1274 WHEN no_data_found THEN
1275 l_sequence_start := 0;
1276 END;
1277
1278 -- insert template attributes for all lines
1279 insert into pon_auc_attributes_interface (
1280 BATCH_ID,
1281 INTERFACE_LINE_ID,
1282 AUCTION_LINE_NUMBER,
1283 AUCTION_HEADER_ID,
1284 SEQUENCE_NUMBER,
1285 ATTRIBUTE_NAME,
1286 GROUP_CODE,
1287 DATATYPE,
1288 VALUE,
1289 RESPONSE_TYPE_NAME,
1290 RESPONSE_TYPE,
1291 MANDATORY_FLAG,
1292 DISPLAY_ONLY_FLAG,
1293 DISPLAY_TARGET_FLAG,
1294 SCORING_TYPE,
1295 ATTR_GROUP_SEQ_NUMBER,
1296 ATTR_DISP_SEQ_NUMBER
1297 )
1298 select
1299 ip.batch_id,
1300 ip.interface_line_id,
1301 ip.auction_line_number,
1302 ip.auction_header_id,
1303 l_sequence_start + att.sequence_number,
1304 att.attribute_name,
1305 att.ATTR_GROUP,
1306 att.datatype,
1307 att.value,
1308 'PON_FROM_TEMPLATE',
1309 decode(att.mandatory_flag,'Y','REQUIRED',
1310 decode(att.display_only_flag,'Y','DISPLAY_ONLY','OPTIONAL') ),
1311 att.mandatory_flag,
1312 att.display_only_flag,
1313 att.display_target_flag,
1314 att.scoring_type,
1315 att.attr_group_seq_number,
1316 att.attr_disp_seq_number
1317 from
1318 pon_auction_attributes att,
1319 pon_item_prices_interface ip
1320 where
1321 ip.batch_id = p_batch_id
1322 and att.line_number <> -1
1323 and ip.group_type <> 'GROUP'
1324 and att.auction_header_id = p_auction_template_id;
1325
1326 -- resolve duplicate attributes by removing the Template attribute
1327 delete from pon_auc_attributes_interface pai1
1328 where rowid in (
1329 select pai1.rowid
1330 from pon_auc_attributes_interface pai2
1331 where pai1.batch_id = p_batch_id
1332 and pai1.batch_id = pai2.batch_id
1333 and pai1.interface_line_id = pai2.interface_line_id
1334 and pai1.attribute_name = pai2.attribute_name
1335 and pai1.response_type_name = 'PON_FROM_TEMPLATE'
1336 and pai2.response_type_name <> 'PON_FROM_TEMPLATE'
1337 );
1338 END add_template_attributes;
1339
1340
1341 PROCEDURE validate_price_differentials(
1342 p_source VARCHAR2,
1343 p_batch_id NUMBER
1344 )AS
1345
1346 l_auction_header_id pon_auc_price_differ_int.auction_header_id%TYPE;
1347 l_message_suffix VARCHAR2(2);
1348 l_contract_type pon_auction_headers_all.contract_type%TYPE;
1349 l_global_agreement pon_auction_headers_all.global_agreement_flag%TYPE;
1350
1351 BEGIN
1352
1353 -- init vars
1354 BEGIN
1355
1356
1357 SELECT max(papd.auction_header_id)
1358 INTO l_auction_header_id
1359 FROM pon_auc_price_differ_int papd
1360 WHERE papd.batch_id = p_batch_id;
1361
1362 SELECT pon_auction_pkg.get_message_suffix(doc.internal_name)
1363 INTO l_message_suffix
1364 FROM pon_auction_headers_all pah,pon_auc_doctypes doc
1365 WHERE pah.auction_header_id = l_auction_header_id
1366 AND pah.doctype_id = doc.doctype_id;
1367
1368 --Don't do any validation if its not a global blanket agreement
1369 -- And if there are any rows in the interface table delete them
1370 SELECT pah.contract_type, pah.global_agreement_flag
1371 INTO l_contract_type, l_global_agreement
1372 FROM pon_auction_headers_all pah
1373 WHERE pah.auction_header_id = l_auction_header_id;
1374
1375 IF ((l_contract_type <> 'BLANKET' AND l_contract_type <> 'CONTRACT') OR l_global_agreement <> 'Y') THEN
1376 DELETE FROM pon_auc_price_differ_int
1377 WHERE batch_id = p_batch_id;
1378 RETURN;
1379 END IF ;
1380
1381 EXCEPTION
1382
1383 WHEN no_data_found THEN
1384 l_message_suffix := NULL;
1385 l_auction_header_id := NULL;
1386 END;
1387
1388 -- update the interface table with the price_type values
1389 -- from the po_price_diff_lookups_v values
1390 UPDATE pon_auc_price_differ_int papdi
1391 SET price_type = (SELECT Nvl(MAX(ppdl.price_differential_type),'PRICE_TYPE_INVALID')
1392 FROM po_price_diff_lookups_v ppdl
1393 WHERE papdi.price_type_name = ppdl.price_differential_dsp(+)),
1394
1395 price_type_desc = (SELECT Nvl(MAX(ppdl.price_differential_desc),'PRICE_DESC_INVALID')
1396 FROM po_price_diff_lookups_v ppdl
1397 WHERE papdi.price_type_name = ppdl.price_differential_dsp(+))
1398 WHERE batch_id = p_batch_id
1399 AND price_type_name <> 'EMPTY_PRICE_TYPE_NAME';
1400
1401 --------------- Validations start here --------------
1402 INSERT ALL
1403
1404 -- Price Type errors will go into the interface table
1405 -- Check for Price type being null for those
1406
1407 WHEN
1408 (
1409 selected_multiplier <> -9999
1410 AND selected_price_type_name = 'EMPTY_PRICE_TYPE_NAME'
1411 )
1412 THEN INTO pon_interface_errors
1413 (
1414 BATCH_ID,
1415 INTERFACE_LINE_ID,
1416 TABLE_NAME,
1417 COLUMN_NAME,
1418 ERROR_MESSAGE_NAME,
1419 ERROR_VALUE
1423 selected_batch_id,
1420 )
1421 VALUES
1422 (
1424 selected_interface_line_id,
1425 'PON_AUC_PRICE_DIFFER_INT',
1426 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1427 'PON_FIELD_MUST_BE_ENTERED',
1428 null
1429 )
1430
1431 -- Price Type errors will go into the interface table
1432 -- Check for invalid price type values
1433
1434 WHEN
1435 (
1436 selected_price_type_name = 'PRICE_TYPE_INVALID'
1437 AND selected_price_type_name <> 'EMPTY_PRICE_TYPE_NAME'
1438 )
1439 THEN INTO pon_interface_errors
1440 (
1441 BATCH_ID,
1442 INTERFACE_LINE_ID,
1443 TABLE_NAME,
1444 COLUMN_NAME,
1445 ERROR_MESSAGE_NAME,
1446 ERROR_VALUE
1447 )
1448 values
1449 (
1450 selected_batch_id,
1451 selected_interface_line_id,
1452 'PON_AUC_PRICE_DIFFER_INT',
1453 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1454 'PON_TYPE_VALUE_INVALID',
1455 selected_price_type_name
1456 )
1457
1458 SELECT
1459 papd.batch_id selected_batch_id,
1460 papd.interface_line_id selected_interface_line_id,
1461 papd.multiplier selected_multiplier,
1462 papd.price_type_name selected_price_type_name
1463 FROM
1464 pon_auc_price_differ_int papd
1465 WHERE
1466 papd.batch_id = p_batch_id;
1467
1468 -- Price Type errors will go into the interface table
1469 -- Check if there are any duplicate values
1470 INSERT INTO
1471 pon_interface_errors (
1472 BATCH_ID,
1473 INTERFACE_LINE_ID,
1474 TABLE_NAME,
1475 COLUMN_NAME,
1476 ERROR_MESSAGE_NAME,
1477 ERROR_VALUE
1478 )
1479
1480 SELECT
1481 papd.batch_id,
1482 papd.interface_line_id,
1483 'PON_AUC_PRICE_DIFFER_INT',
1484 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1485 'PON_DUPLICATE_WARN_PRICE_TYPE',
1486 papd.price_type_name
1487 FROM pon_auc_price_differ_int papd,
1488 pon_auc_price_differ_int papd2
1489 WHERE papd.batch_id = p_batch_id
1490 AND papd.batch_id = papd2.batch_id
1491 AND papd.interface_line_id = papd2.interface_line_id
1492 AND papd.price_type = papd2.price_type
1493 AND papd.sequence_number <> papd2.sequence_number;
1494
1495 END validate_price_differentials;
1496
1497 PROCEDURE add_ip_descriptors(p_batch_id IN NUMBER,
1498 p_auction_header_id IN NUMBER) IS
1499
1500 l_tp_id NUMBER;
1501 l_auction_round_number NUMBER;
1502 l_amendment_number NUMBER;
1503 l_default_attr_group pon_auction_attributes.attr_group%TYPE;
1504 l_ip_attr_default_option VARCHAR2(10);
1505
1506 BEGIN
1507
1508 select trading_partner_id, nvl(auction_round_number, 1), nvl(amendment_number, 0)
1509 into l_tp_id, l_auction_round_number, l_amendment_number
1510 from pon_auction_headers_all
1511 where auction_header_id = p_auction_header_id;
1512
1513 select nvl(ppp.preference_value,'GENERAL')
1514 into l_default_attr_group
1515 from pon_party_preferences ppp
1516 where ppp.app_short_name = 'PON' and
1517 ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
1518 ppp.party_id = l_tp_id;
1519
1520
1521 l_ip_attr_default_option := fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION');
1522 IF (nvl(l_ip_attr_default_option, 'NONE') = 'NONE') THEN
1523 RETURN;
1524 END IF;
1525
1526 add_ip_descs_on_new_lines(p_batch_id, l_default_attr_group, l_ip_attr_default_option);
1527
1528 IF (l_auction_round_number > 1 or l_amendment_number > 0) THEN
1529 add_ip_descs_on_updated_lines(p_batch_id, l_default_attr_group, l_ip_attr_default_option);
1530 END IF;
1531
1532 END add_ip_descriptors;
1533
1534 PROCEDURE add_ip_descs_on_new_lines(p_batch_id IN VARCHAR2,
1535 p_default_attr_group IN VARCHAR2,
1536 p_ip_attr_default_option IN VARCHAR2) IS
1537
1538
1539 l_max_attr_seq_num NUMBER;
1540 l_def_attr_group_seq_num NUMBER;
1541 l_def_attr_max_disp_seq_num NUMBER;
1542 l_max_ip_seq_num NUMBER;
1543
1544 CURSOR lines IS
1545 SELECT interface_line_id, auction_header_id, auction_line_number, ip_category_id
1546 FROM pon_item_prices_interface
1547 WHERE batch_id = p_batch_id and
1548 nvl(action, '+') = '+';
1549
1550
1551 BEGIN
1552
1553 l_max_ip_seq_num := 9999999999999;
1554
1555 FOR line in lines
1556 LOOP
1557
1558 l_max_attr_seq_num := get_max_attr_seq_num(p_batch_id, line.interface_line_id);
1559
1560 l_def_attr_group_seq_num := get_attr_group_seq_num(p_batch_id, line.interface_line_id,
1561 p_default_attr_group);
1562
1563 l_def_attr_max_disp_seq_num := get_attr_max_disp_seq_num(p_batch_id, line.interface_line_id,
1564 p_default_attr_group);
1565
1566 -- bring over ip descriptors (base and catalog) for new lines
1567
1568 INSERT INTO PON_AUC_ATTRIBUTES_INTERFACE (
1569 BATCH_ID,
1570 INTERFACE_LINE_ID,
1571 AUCTION_HEADER_ID,
1572 AUCTION_LINE_NUMBER,
1573 SEQUENCE_NUMBER,
1574 ATTRIBUTE_NAME,
1575 GROUP_CODE,
1576 DATATYPE,
1577 RESPONSE_TYPE,
1578 MANDATORY_FLAG,
1579 DISPLAY_ONLY_FLAG,
1580 INTERNAL_ATTR_FLAG,
1581 DISPLAY_TARGET_FLAG,
1585 ATTR_DISP_SEQ_NUMBER,
1582 VALUE,
1583 SCORING_TYPE,
1584 ATTR_GROUP_SEQ_NUMBER,
1586 IP_CATEGORY_ID,
1587 IP_DESCRIPTOR_ID
1588 )
1589 SELECT
1590
1591 p_batch_id, -- BATCH_ID
1592 line.interface_line_id, -- INTERFACE_LINE_ID
1593 line.auction_header_id, -- AUCTION_HEADER_ID
1594 line.auction_line_number, -- AUCTION_LINE_NUMBER
1595 l_max_attr_seq_num + (rownum*10), -- SEQUENCE_NUMBER
1596 attribute_name, -- ATTRIBUTE_NAME
1597 p_default_attr_group, -- GROUP_CODE
1598 datatype, -- DATATYPE
1599 'OPTIONAL', -- RESPONSE_TYPE
1600 'N', -- MANDATORY_FLAG
1601 'N', -- DISPLAY_ONLY_FLAG
1602 'N', -- INTERNAL_ATTR_FLAG
1603 'N', -- DISPLAY_TARGET_FLAG
1604 null, -- VALUE
1605 'NONE', -- SCORING_TYPE
1606 l_def_attr_group_seq_num, -- ATTR_GROUP_SEQ_NUMBER
1607 l_def_attr_max_disp_seq_num + (rownum * 10), -- ATTR_DISP_SEQ_NUMBER
1608 ip_category_id, -- IP_CATEGORY_ID
1609 ip_descriptor_id -- IP_DESCRIPTOR_ID
1610 FROM
1611 (SELECT attribute_name, decode(type, 1, 'NUM', 'TXT') datatype,
1612 rt_category_id ip_category_id, attribute_id ip_descriptor_id
1613 FROM icx_cat_agreement_attrs_v
1614 WHERE ((rt_category_id = 0 and p_ip_attr_default_option in ('ALL', 'BASE')) or
1615 (rt_category_id = line.ip_category_id and p_ip_attr_default_option in ('ALL', 'CATEGORY'))) and language = userenv('LANG') and
1616 upper(attribute_name) not in (select upper(attribute_name)
1617 from pon_auc_attributes_interface
1618 where batch_id = p_batch_id and
1619 interface_line_id = line.interface_line_id)
1620 ORDER BY nvl(sequence, l_max_ip_seq_num) asc);
1621
1622 END LOOP;
1623
1624 END add_ip_descs_on_new_lines;
1625
1626 PROCEDURE add_ip_descs_on_updated_lines(p_batch_id IN VARCHAR2,
1627 p_default_attr_group IN VARCHAR2,
1628 p_ip_attr_default_option IN VARCHAR2) IS
1629
1630 l_max_attr_seq_num NUMBER;
1631 l_def_attr_group_seq_num NUMBER;
1632 l_def_attr_max_disp_seq_num NUMBER;
1633 l_max_ip_seq_num NUMBER;
1634 l_ip_attr_default_option VARCHAR2(10);
1635
1636 -- updated lines where the ip category has changed and is not null;
1637
1638 CURSOR lines IS
1639 SELECT interface_line_id, auction_header_id, auction_line_number, ip_category_id
1640 FROM pon_item_prices_interface
1641 WHERE batch_id = p_batch_id and
1642 nvl(action, '+') = '#' and
1643 auction_line_number in (select paip.line_number
1644 from pon_item_prices_interface p1,
1645 pon_auction_item_prices_all paip
1646 where p1.batch_id = p_batch_id and
1647 nvl(p1.action, '+') = '#' and
1648 p1.auction_header_id = paip.auction_header_id and
1649 p1.auction_line_number = paip.line_number and
1650 p1.ip_category_id is not null and
1651 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1));
1652
1653
1654 BEGIN
1655
1656 delete from
1657 (select *
1658 from pon_auc_attributes_interface
1659 where auction_line_number in (select paip.line_number
1660 from pon_item_prices_interface p1,
1661 pon_auction_item_prices_all paip
1662 where p1.batch_id = p_batch_id and
1663 nvl(p1.action, '+') = '#' and
1664 p1.auction_header_id = paip.auction_header_id and
1665 p1.auction_line_number = paip.line_number and
1666 paip.ip_category_id is not null and
1667 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1))) paai
1668 where batch_id = p_batch_id and
1669 exists (select null
1670 from pon_auction_attributes paa
1671 where paa.auction_header_id = paai.auction_header_id and
1672 paa.line_number = paai.auction_line_number and
1673 upper(paa.attribute_name) = upper(paai.attribute_name) and
1674 paa.ip_category_id is not null and
1675 paa.ip_category_id <> 0);
1676
1677 l_ip_attr_default_option := nvl(fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION'), 'NONE');
1678
1679 IF (l_ip_attr_default_option in ('NONE', 'BASE')) THEN
1680 RETURN;
1681 END IF;
1682
1683 l_max_ip_seq_num := 9999999999999;
1684
1685 FOR line in lines
1686 LOOP
1687
1688 l_max_attr_seq_num := get_max_attr_seq_num(p_batch_id, line.interface_line_id);
1689
1690 l_def_attr_group_seq_num := get_attr_group_seq_num(p_batch_id, line.interface_line_id,
1694 p_default_attr_group);
1691 p_default_attr_group);
1692
1693 l_def_attr_max_disp_seq_num := get_attr_max_disp_seq_num(p_batch_id, line.interface_line_id,
1695
1696 -- bring over ip catalog descriptors for updated lines
1697
1698 INSERT INTO PON_AUC_ATTRIBUTES_INTERFACE (
1699 BATCH_ID,
1700 INTERFACE_LINE_ID,
1701 AUCTION_HEADER_ID,
1702 AUCTION_LINE_NUMBER,
1703 SEQUENCE_NUMBER,
1704 ATTRIBUTE_NAME,
1705 GROUP_CODE,
1706 DATATYPE,
1707 RESPONSE_TYPE,
1708 MANDATORY_FLAG,
1709 DISPLAY_ONLY_FLAG,
1710 INTERNAL_ATTR_FLAG,
1711 DISPLAY_TARGET_FLAG,
1712 VALUE,
1713 SCORING_TYPE,
1714 ATTR_GROUP_SEQ_NUMBER,
1715 ATTR_DISP_SEQ_NUMBER,
1716 IP_CATEGORY_ID,
1717 IP_DESCRIPTOR_ID
1718 )
1719 SELECT
1720
1721 p_batch_id, -- BATCH_ID
1722 line.interface_line_id, -- INTERFACE_LINE_ID
1723 line.auction_header_id, -- AUCTION_HEADER_ID
1724 line.auction_line_number, -- AUCTION_LINE_NUMBER
1725 l_max_attr_seq_num + (rownum*10), -- SEQUENCE_NUMBER
1726 attribute_name, -- ATTRIBUTE_NAME
1727 p_default_attr_group, -- GROUP_CODE
1728 datatype, -- DATATYPE
1729 'OPTIONAL', -- RESPONSE_TYPE
1730 'N', -- MANDATORY_FLAG
1731 'N', -- DISPLAY_ONLY_FLAG
1732 'N', -- INTERNAL_ATTR_FLAG
1733 'N', -- DISPLAY_TARGET_FLAG
1734 null, -- VALUE
1735 'NONE', -- SCORING_TYPE
1736 l_def_attr_group_seq_num, -- ATTR_GROUP_SEQ_NUMBER
1737 l_def_attr_max_disp_seq_num + (rownum * 10), -- ATTR_DISP_SEQ_NUMBER
1738 ip_category_id, -- IP_CATEGORY_ID
1739 ip_descriptor_id -- IP_DESCRIPTOR_ID
1740 FROM
1741 (SELECT attribute_name, decode(type, 1, 'NUM', 'TXT') datatype,
1742 rt_category_id ip_category_id, attribute_id ip_descriptor_id
1743 FROM icx_cat_agreement_attrs_v
1744 WHERE rt_category_id = line.ip_category_id and
1745 language = userenv('LANG') and
1746 upper(attribute_name) not in (select upper(attribute_name)
1747 from pon_auc_attributes_interface
1748 where batch_id = p_batch_id and
1749 interface_line_id = line.interface_line_id)
1750 ORDER BY nvl(sequence, l_max_ip_seq_num) asc);
1751
1752 END LOOP;
1753
1754 END add_ip_descs_on_updated_lines;
1755
1756 FUNCTION get_max_attr_seq_num(p_batch_id IN NUMBER,
1757 p_interface_line_id IN NUMBER) RETURN NUMBER
1758
1759 IS
1760
1761 l_max_attr_seq_num NUMBER;
1762
1763 BEGIN
1764
1765 select nvl(max(sequence_number), 0)
1766 into l_max_attr_seq_num
1767 from pon_auc_attributes_interface
1768 where batch_id = p_batch_id and
1769 interface_line_id = p_interface_line_id;
1770
1771 RETURN l_max_attr_seq_num;
1772
1773 END get_max_attr_seq_num;
1774
1775 FUNCTION get_attr_group_seq_num(p_batch_id IN NUMBER,
1776 p_interface_line_id IN NUMBER,
1777 p_attr_group IN VARCHAR2) RETURN NUMBER
1778 IS
1779
1780 l_attr_group_seq_num NUMBER;
1781
1782 BEGIN
1783
1784 select attr_group_seq_number
1785 into l_attr_group_seq_num
1786 from pon_auc_attributes_interface
1787 where batch_id = p_batch_id and
1788 interface_line_id = p_interface_line_id and
1789 group_code = p_attr_group and
1790 rownum = 1;
1791
1792 RETURN l_attr_group_seq_num;
1793
1794 EXCEPTION
1795
1796 when others then
1797
1798 -- since group is not on the line yet,
1799 -- find max group seq number and add 10
1800
1801 select nvl(max(attr_group_seq_number), 0) + 10
1802 into l_attr_group_seq_num
1803 from pon_auc_attributes_interface
1804 where batch_id = p_batch_id and
1805 interface_line_id = p_interface_line_id;
1806
1807 RETURN l_attr_group_seq_num;
1808
1809 END get_attr_group_seq_num;
1810
1811 FUNCTION get_attr_max_disp_seq_num(p_batch_id IN NUMBER,
1812 p_interface_line_id IN NUMBER,
1813 p_attr_group IN VARCHAR2) RETURN NUMBER
1814 IS
1815
1816 l_attr_max_disp_seq_num NUMBER;
1817
1818 BEGIN
1819
1820 select nvl(max(attr_disp_seq_number), 0)
1821 into l_attr_max_disp_seq_num
1822 from pon_auc_attributes_interface
1823 where batch_id = p_batch_id and
1824 interface_line_id = p_interface_line_id and
1825 group_code = p_attr_group;
1826
1827 RETURN l_attr_max_disp_seq_num;
1828
1829 END get_attr_max_disp_seq_num;
1830
1831 PROCEDURE validate_header_attributes_api(
1832 p_source VARCHAR2,
1833 p_batch_id NUMBER,
1837 l_message_suffix VARCHAR2(2);
1834 p_party_id NUMBER
1835 ) AS
1836 l_auction_header_id pon_auc_attributes_interface.auction_header_id%TYPE;
1838 l_hdr_attr_enable_weights VARCHAR2(1);
1839 BEGIN
1840 -- init vars
1841 BEGIN
1842 select max(pai.auction_header_id)
1843 into l_auction_header_id
1844 from pon_auc_attributes_interface pai
1845 where pai.batch_id = p_batch_id;
1846
1847 select hdr_attr_enable_weights
1848 into l_hdr_attr_enable_weights
1849 from pon_auction_headers_all pah
1850 where pah.auction_header_id = l_auction_header_id;
1851
1852 select pon_auction_pkg.get_message_suffix(doc.internal_name)
1853 into l_message_suffix
1854 from pon_auction_headers_all pah,
1855 pon_auc_doctypes doc
1856 where pah.auction_header_id = l_auction_header_id
1857 and pah.doctype_id = doc.doctype_id;
1858 EXCEPTION
1859 WHEN no_data_found THEN
1860 l_message_suffix := NULL;
1861 l_auction_header_id := NULL;
1862 l_hdr_attr_enable_weights := NULL;
1863 END;
1864
1865 -- This is a call to common validate Attributes.last var indicates header attr.
1866 validate_attributes_api(p_source,p_batch_id,p_party_id,true);
1867
1868 INSERT ALL
1869
1870 -- validate that the weight is between 0 and 100.
1871 when
1872 (
1873 l_hdr_attr_enable_weights = 'Y'
1874 and selected_auction_line_number = -1
1875 and selected_weight is not null
1876 and (selected_weight > 100
1877 or selected_weight < 0 )
1878 AND Nvl(selected_action,'INSERT') = 'INSERT'
1879 )
1880 then into pon_interface_errors
1881 (
1882 BATCH_ID,
1883 INTERFACE_LINE_ID,
1884 TABLE_NAME,
1885 COLUMN_NAME,
1886 ERROR_MESSAGE_NAME,
1887 ERROR_VALUE
1888 )
1889 values
1890 (
1891 selected_batch_id,
1892 selected_interface_line_id,
1893 'PON_AUC_ATTRIBUTES_INTERFACE',
1894 fnd_message.get_string('PON','PON_AUCTS_ATTR_WEIGHT'),
1895 'PON_AUC_WEIGHT_RANGE',
1896 selected_weight
1897 )
1898
1899 --validate that the score is not entered for Display only attributes.
1900
1901 when
1902 (
1903 selected_auction_line_number = -1
1904 and (selected_aTTR_MAX_SCORE is not null and selected_ATTR_MAX_SCORE <> 0)
1905 and selected_DISPLAY_ONLY_FLAG = 'Y'
1906 AND Nvl(selected_action,'INSERT') = 'INSERT'
1907 )
1908 then into pon_interface_errors
1909 (
1910 BATCH_ID,
1911 INTERFACE_LINE_ID,
1912 TABLE_NAME,
1913 COLUMN_NAME,
1914 ERROR_MESSAGE_NAME,
1915 ERROR_VALUE
1916 )
1917 values
1918 (
1919 selected_batch_id,
1920 selected_interface_line_id,
1921 'PON_AUC_ATTRIBUTES_INTERFACE',
1922 fnd_message.get_string('PON','PON_AUC_SCORE'),
1923 'PON_AUCTS_DISP_ATTR_NO_SCORES',
1924 selected_ATTR_MAX_SCORE
1925 )
1926
1927 -- validate that the score is greater than zero.
1928 when
1929 (
1930 selected_auction_line_number = -1
1931 and selected_ATTR_MAX_SCORE is not null
1932 and selected_ATTR_MAX_SCORE < 0
1933 AND Nvl(selected_action,'INSERT') = 'INSERT'
1934 )
1935 then into pon_interface_errors
1936 (
1937 BATCH_ID,
1938 INTERFACE_LINE_ID,
1939 TABLE_NAME,
1940 COLUMN_NAME,
1941 ERROR_MESSAGE_NAME,
1942 ERROR_VALUE
1943 )
1944 values
1945 (
1946 selected_batch_id,
1947 selected_interface_line_id,
1948 'PON_AUC_ATTRIBUTES_INTERFACE',
1949 fnd_message.get_string('PON','PON_AUC_SCORE'),
1950 'PON_AUC_INVALID_MAXSCORE_RANGE',
1951 selected_ATTR_MAX_SCORE
1952 )
1953
1954 SELECT
1955 pai.batch_id selected_batch_id,
1956 pai.interface_line_id selected_interface_line_id,
1957 pai.weight selected_weight,
1958 pai.auction_line_number selected_auction_line_number,
1959 pai.attr_max_score selected_attr_max_score,
1960 pai.display_only_flag selected_display_only_flag,
1961 pai.action selected_action
1962
1963 from
1964 pon_auc_attributes_interface pai
1965
1966 where
1967 pai.batch_id = p_batch_id;
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 NULL;
1972
1973 END validate_header_attributes_api;
1974
1975 PROCEDURE validate_attributes_api(
1976 p_source VARCHAR2,
1977 p_batch_id NUMBER,
1978 p_party_id NUMBER,
1979 p_attr_type_header BOOLEAN
1980 ) AS
1981 l_auction_header_id pon_auc_attributes_interface.auction_header_id%TYPE;
1982 l_message_suffix VARCHAR2(2);
1983 l_group_pref_name VARCHAR2(40);
1984 l_group_lookup_type VARCHAR2(40);
1985 l_attr_type_header VARCHAR2(1);
1986 BEGIN
1987
1988 IF (p_attr_type_header) THEN
1989
1990 l_attr_type_header := 'Y';
1991 ELSE
1992
1993 l_attr_type_header := 'N';
1994 END IF;
1995
1996 -- init vars
1997
1998 BEGIN
1999 select max(pai.auction_header_id)
2000 into l_auction_header_id
2001 from pon_auc_attributes_interface pai
2002 where pai.batch_id = p_batch_id;
2003
2004 select pon_auction_pkg.get_message_suffix(doc.internal_name)
2005 into l_message_suffix
2006 from pon_auction_headers_all pah,
2007 pon_auc_doctypes doc
2011 WHEN no_data_found THEN
2008 where pah.auction_header_id = l_auction_header_id
2009 and pah.doctype_id = doc.doctype_id;
2010 EXCEPTION
2012 l_message_suffix := NULL;
2013 l_auction_header_id := NULL;
2014 l_group_pref_name := NULL;
2015 l_group_lookup_type := NULL;
2016 END;
2017
2018 -- GROUP Check Starts.
2019 -- default GROUP_CODE as General for lines with group_name as null.
2020 IF ( not p_attr_type_header ) THEN
2021 l_group_pref_name := 'LINE_ATTR_DEFAULT_GROUP';
2022 l_group_lookup_type := 'PON_LINE_ATTRIBUTE_GROUPS';
2023 END IF;
2024
2025
2026 -- Populate data
2027 -- RESPONSE_TYPE
2028 -- bug 3373002
2029 -- need to compare apple with apple, response type values are from fnd messages
2030 -- so also validate against messages, not lookups.
2031 IF ( p_attr_type_header ) THEN
2032 update pon_auc_attributes_interface pai
2033 set response_type = decode(response_type_name,
2034 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'), 'REQUIRED',
2035 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'), 'OPTIONAL',
2036 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'), 'DISPLAY_ONLY',
2037 fnd_message.get_string('PON','PON_AUCTS_INTERNAL'), 'INTERNAL',
2038 null)
2039 where pai.batch_id = p_batch_id
2040 and pai.response_type_name is not null;
2041 ELSE
2042 update pon_auc_attributes_interface pai
2043 set response_type = decode(response_type_name,
2044 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'), 'REQUIRED',
2045 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'), 'OPTIONAL',
2046 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'), 'DISPLAY_ONLY',
2047 null)
2048 where pai.batch_id = p_batch_id
2049 and pai.response_type_name is not null;
2050 END IF;
2051
2052 -- MANDATORY_FLAG
2053 -- DISPLAY_ONLY_FLAG
2054 -- INTERNAL_ATTR_FLAG
2055 update
2056 pon_auc_attributes_interface
2057 set
2058 mandatory_flag = decode(response_type,'REQUIRED','Y','N'),
2059 display_only_flag = decode(response_type,'DISPLAY_ONLY','Y','N'),
2060 internal_attr_flag = decode(response_type,'INTERNAL','Y','N')
2061 where
2062 batch_id = p_batch_id;
2063
2064
2065 -- Not to be Done for Header Section.
2066 IF ( not p_attr_type_header ) THEN
2067 update pon_auc_attributes_interface paai
2068 set paai.GROUP_CODE = (select nvl(ppp.preference_value,'GENERAL')
2069 from pon_party_preferences ppp
2070 where ppp.app_short_name = 'PON'
2071 and ppp.preference_name = l_group_pref_name
2072 and ppp.party_id = p_party_id)
2073 where paai.batch_id = p_batch_id
2074 and paai.GROUP_NAME is NULL
2075 AND paai.auction_line_number <> -1;
2076
2077
2078 -- populate GROUP_CODE. Note that the comparison is case sensitive.
2079
2080 update pon_auc_attributes_interface paai
2081 set paai.GROUP_CODE = (
2082 select lookup_code
2083 from fnd_lookup_values attrGrpFlv
2084 where lookup_type = l_group_lookup_type
2085 --where lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
2086 and meaning = paai.GROUP_NAME
2087 and attrGrpFlv.LANGUAGE = userenv('LANG')
2088 and attrGrpFlv.view_application_id = 0
2089 and attrGrpFlv.security_group_id = 0
2090 and attrGrpFlv.enabled_flag = 'Y'
2091 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
2092 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
2093 )
2094 where paai.batch_id = p_batch_id
2095 and paai.GROUP_NAME is not NULL
2096 AND paai.auction_line_number <> -1;
2097
2098 ELSE
2099 -- For Header Case, if the Group Name is null, default General.
2100 update pon_auc_attributes_interface paai
2101 set paai.GROUP_NAME = (
2102 select meaning
2103 from fnd_lookup_values attrGrpFlv
2104 where attrGrpFlv.lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
2105 and attrGrpFlv.lookup_code = 'GENERAL'
2106 and attrGrpFlv.LANGUAGE = userenv('LANG')
2107 and attrGrpFlv.view_application_id = 0
2108 and attrGrpFlv.security_group_id = 0
2109 and attrGrpFlv.enabled_flag = 'Y'
2110 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
2111 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
2112 )
2113 where paai.batch_id = p_batch_id
2114 and paai.GROUP_NAME is NULL
2115 AND paai.auction_line_number = -1;
2116
2117 END IF;
2118
2119 ---------------- Validations start here ------------
2120
2121 INSERT ALL
2122
2123 -- Datatype
2124 WHEN
2125 (
2126 selected_datatype is NULL
2127 AND Nvl(selected_action,'INSERT') = 'INSERT'
2128 )
2129 THEN into pon_interface_errors
2130 (
2131 BATCH_ID,
2132 INTERFACE_LINE_ID,
2133 TABLE_NAME,
2134 COLUMN_NAME,
2135 ERROR_MESSAGE_NAME,
2136 ERROR_VALUE
2137 )
2138 VALUES
2139 (
2140 selected_batch_id,
2141 selected_interface_line_id,
2142 'PON_AUC_ATTRIBUTES_INTERFACE',
2143 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
2144 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
2145 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
2146 selected_datatype
2147 )
2148
2149 -- Response Type
2150
2151 WHEN
2152 (
2153 selected_response_type is NULL
2154 AND Nvl(selected_action,'INSERT')='INSERT'
2155 )
2159 INTERFACE_LINE_ID,
2156 THEN into pon_interface_errors
2157 (
2158 BATCH_ID,
2160 TABLE_NAME,
2161 COLUMN_NAME,
2162 ERROR_MESSAGE_NAME,
2163 ERROR_VALUE
2164 )
2165 VALUES
2166 (
2167 selected_batch_id,
2168 selected_interface_line_id,
2169 'PON_AUC_ATTRIBUTES_INTERFACE',
2170 decode(l_group_pref_name,'LINE_ATTR_DEFAULT_GROUP',pon_auction_pkg.getMessage('PON_AUCTS_BID_RESPONSE',l_message_suffix),pon_auction_pkg.getMessage('PON_AUCTS_TYPE')),
2171 'PON_CAT_INVALID_VALUE',
2172 selected_response_type_name
2173 )
2174
2175 -- Display Target Flag
2176
2177 WHEN
2178 (
2179 nvl(selected_display_target_flag,'N') not in ('Y','N')
2180 AND Nvl(selected_action,'INSERT') = 'INSERT'
2181 )
2182
2183 THEN into pon_interface_errors
2184 (
2185 BATCH_ID,
2186 INTERFACE_LINE_ID,
2187 TABLE_NAME,
2188 COLUMN_NAME,
2189 ERROR_MESSAGE_NAME,
2190 ERROR_VALUE
2191 )
2192 values
2193 (
2194 selected_batch_id,
2195 selected_interface_line_id,
2196 'PON_AUC_ATTRIBUTES_INTERFACE',
2197 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
2198 'PON_AUCTS_INV_PR_QT_VAL',
2199 selected_display_target_flag
2200 )
2201
2202 -- validate datatype [lookup_type = PON_AUCTION_ATTRIBUTE_TYPE]
2203
2204 when
2205 (
2206 selected_datatype not in (
2207 select lookup_code
2208 from fnd_lookups
2209 where lookup_type = 'PON_AUCTION_ATTRIBUTE_TYPE'
2210 )
2211 AND Nvl(selected_action,'INSERT') = 'INSERT'
2212 )
2213 then into pon_interface_errors
2214 (
2215 BATCH_ID,
2216 INTERFACE_LINE_ID,
2217 TABLE_NAME,
2218 COLUMN_NAME,
2219 ERROR_MESSAGE_NAME,
2220 ERROR_VALUE
2221 )
2222 values
2223 (
2224 selected_batch_id,
2225 selected_interface_line_id,
2226 'PON_AUC_ATTRIBUTES_INTERFACE',
2227 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
2228 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
2229 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
2230 selected_datatype
2231 )
2232
2233 -- validate display target flag is Y/N
2234 when
2235 (
2236 nvl(selected_display_target_flag,'N') not in ('Y','N')
2237 AND Nvl(selected_action,'INSERT')='INSERT'
2238 )
2239 then into pon_interface_errors
2240 (
2241 BATCH_ID,
2242 INTERFACE_LINE_ID,
2243 TABLE_NAME,
2244 COLUMN_NAME,
2245 ERROR_MESSAGE_NAME,
2246 ERROR_VALUE
2247 )
2248 values
2249 (
2250 selected_batch_id,
2251 selected_interface_line_id,
2252 'PON_AUC_ATTRIBUTES_INTERFACE',
2253 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
2254 'PON_AUCTS_INV_PR_QT_VAL',
2255 selected_display_target_flag
2256 )
2257
2258 when
2259 (
2260 selected_display_target_flag = 'Y'
2261 and selected_value is NULL
2262 AND Nvl(selected_action,'INSERT') = 'INSERT'
2263 )
2264 then into pon_interface_errors
2265 (
2266 BATCH_ID,
2267 INTERFACE_LINE_ID,
2268 TABLE_NAME,
2269 COLUMN_NAME,
2270 ERROR_MESSAGE_NAME,
2271 ERROR_VALUE
2272 )
2273 values
2274 (
2275 selected_batch_id,
2276 selected_interface_line_id,
2277 'PON_AUC_ATTRIBUTES_INTERFACE',
2278 fnd_message.get_string('PON','PON_AUCTS_TARGET_VALUE'),
2279 'PON_AUCTS_ATTR_SHOW_TARGET',
2280 selected_value
2281 )
2282
2283 -- validate value given if display_only = Y
2284 when
2285 (
2286 selected_display_only_flag = 'Y'
2287 and selected_value is null
2288 -- Bug 6957765
2289 and nvl(selected_display_target_flag,'N') = 'Y'
2290 AND Nvl(selected_action,'INSERT') = 'INSERT'
2291 )
2292 then into pon_interface_errors
2293 (
2294 BATCH_ID,
2295 INTERFACE_LINE_ID,
2296 TABLE_NAME,
2297 COLUMN_NAME,
2298 ERROR_MESSAGE_NAME,
2299 ERROR_VALUE
2300 )
2301 values
2302 (
2303 selected_batch_id,
2304 selected_interface_line_id,
2305 'PON_AUC_ATTRIBUTES_INTERFACE',
2306 pon_auction_pkg.getMessage('PON_AUCTS_ATTR_TARGET',l_message_suffix),
2307 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
2308 'PON_AUCTS_ATTR_DISPLAY_TARGET', 'PON_AUCTS_REQ_DISPLAY_TARGET'),
2309 selected_value
2310 )
2311
2312 -- Attribute Name
2313
2314 when
2315 (
2316 selected_attribute_name is NULL
2317 AND Nvl(selected_action,'INSERT') = 'INSERT'
2318 )
2319 then into pon_interface_errors
2320 (
2321 BATCH_ID,
2322 INTERFACE_LINE_ID,
2323 TABLE_NAME,
2324 COLUMN_NAME,
2325 ERROR_MESSAGE_NAME,
2326 ERROR_VALUE
2327 )
2328 values
2329 (
2330 selected_batch_id,
2331 selected_interface_line_id,
2332 'PON_AUC_ATTRIBUTES_INTERFACE',
2333 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP', fnd_message.get_string('PON','PON_AUCTS_ATTR'), fnd_message.get_string('PON', 'PON_AUC_REQUIREMENT')),
2334 'PON_FIELD_MUST_BE_ENTERED',
2335 selected_attribute_name
2336 )
2337
2338 -- required field checks
2339 -- SEQUENCE_NUMBER -- should never be displayed to user
2340 -- Do not do this check for Header attributes. We will populate the sequence
2341 -- when we copy them over to the AuctionAttributesVO
2345 selected_sequence_number is NULL
2342 when
2343 (
2344 l_attr_type_header = 'N' AND
2346 AND Nvl(selected_action,'INSERT') = 'INSERT'
2347 )
2348 then into pon_interface_errors
2349 (
2350 BATCH_ID,
2351 INTERFACE_LINE_ID,
2352 TABLE_NAME,
2353 COLUMN_NAME,
2354 ERROR_MESSAGE_NAME,
2355 ERROR_VALUE
2356 )
2357 values
2358 (
2359 selected_batch_id,
2360 selected_interface_line_id,
2361 'PON_AUC_ATTRIBUTES_INTERFACE',
2362 'SEQUENCE_NUMBER',
2363 'PON_CAT_DISP_SEQ_M',
2364 selected_sequence_number
2365 )
2366
2367 -- To insert errors for the group which are invalid.
2368 when
2369 (
2370 selected_group_code is null
2371 AND l_attr_type_header = 'N'
2372 AND Nvl(selected_action,'INSERT') = 'INSERT'
2373 AND selected_auction_line_num <> -1 -- bug 16801086
2374 )
2375 then into pon_interface_errors
2376 (
2377 BATCH_ID,
2378 INTERFACE_LINE_ID,
2379 TABLE_NAME,
2380 COLUMN_NAME,
2381 ERROR_MESSAGE_NAME,
2382 ERROR_VALUE
2383 )
2384 values
2385 (
2386 selected_batch_id,
2387 selected_interface_line_id,
2388 'PON_AUC_ATTRIBUTES_INTERFACE',
2389 fnd_message.get_string('PON','PON_AUCTS_GROUP'),
2390 'PON_AUCTS_INVALID_GROUP',
2391 selected_group_name
2392 )
2393
2394 -- validate display target == N if attribute type is Internal
2395 when
2396 (
2397 nvl(selected_display_target_flag,'N') = 'Y'
2398 and selected_internal_attr_flag = 'Y'
2399 and l_attr_type_header = 'Y'
2400 AND Nvl(selected_action,'INSERT') = 'INSERT'
2401 )
2402 then into pon_interface_errors
2403 (
2404 BATCH_ID,
2405 INTERFACE_LINE_ID,
2406 TABLE_NAME,
2407 COLUMN_NAME,
2408 ERROR_MESSAGE_NAME,
2409 ERROR_VALUE
2410 )
2411 values
2412 (
2413 selected_batch_id,
2414 selected_interface_line_id,
2415 'PON_AUC_ATTRIBUTES_INTERFACE',
2416 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
2417 'PON_AUC_INTERNAL_ATT_ERROR',
2418 fnd_message.get_string('PON','PON_CORE_NO')
2419 )
2420 -- Bug 16801089
2421 -- Adding New Validation for Weight
2422 when
2423 (
2424 selected_scoring_type='NONE'
2425 AND ((selected_interface_line_id>0 AND Nvl(selected_weight,0)>0) OR (selected_interface_line_id=-1 AND selected_weight IS NOT null))
2426 AND Nvl(selected_action,'INSERT') = 'INSERT'
2427 )
2428 then into pon_interface_errors
2429 (
2430 BATCH_ID,
2431 INTERFACE_LINE_ID,
2432 TABLE_NAME,
2433 COLUMN_NAME,
2434 ERROR_MESSAGE_NAME,
2435 ERROR_VALUE
2436 )
2437 values
2438 (
2439 selected_batch_id,
2440 selected_interface_line_id,
2441 'PON_AUC_ATTRIBUTES_INTERFACE',
2442 fnd_message.get_string('PON','PON_AUCTS_ATTR_WEIGHT'),
2443 'PON_AUC_INVALID_WEIGHT',
2444 fnd_message.get_string('PON','PON_CORE_NO')
2445 )
2446
2447 SELECT
2448 pai.batch_id selected_batch_id,
2449 pai.interface_line_id selected_interface_line_id,
2450 pai.datatype selected_datatype,
2451 pai.value selected_value,
2452 pai.display_target_flag selected_display_target_flag,
2453 pai.display_only_flag selected_display_only_flag,
2454 pai.response_type_name selected_response_type_name,
2455 pai.response_type selected_response_type,
2456 pai.attribute_name selected_attribute_name,
2457 pai.sequence_number selected_sequence_number,
2458 pai.group_name selected_group_name,
2459 pai.scoring_type selected_scoring_type,
2460 pai.weight selected_weight,
2461 pai.group_code selected_group_code,
2462 pai.internal_attr_flag selected_internal_attr_flag,
2463 pai.action selected_action,
2464 pai.auction_line_number selected_auction_line_num
2465
2466 from
2467 pon_auc_attributes_interface pai
2468 where
2469 pai.batch_id = p_batch_id;
2470
2471 -- perform duplicate checks
2472 IF ( p_attr_type_header ) THEN
2473 insert into pon_interface_errors (
2474 BATCH_ID,
2475 INTERFACE_LINE_ID,
2476 TABLE_NAME,
2477 COLUMN_NAME,
2478 ERROR_MESSAGE_NAME,
2479 ERROR_VALUE
2480 )
2481 select
2482 pai1.batch_id,
2483 pai1.interface_line_id,
2484 'PON_AUC_ATTRIBUTES_INTERFACE',
2485 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
2486 'PON_REQUIREMENT_DUPLICATE_ATT',
2487 pai1.attribute_name
2488 from pon_auc_attributes_interface pai1,
2489 pon_auc_attributes_interface pai2
2490 where pai1.batch_id = p_batch_id
2491 and pai1.batch_id = pai2.batch_id
2492 and pai1.auction_line_number = pai2.auction_line_number
2493 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
2494 and pai1.interface_line_id <> pai2.interface_line_id
2495 AND Nvl(pai1.action,'INSERT')='INSERT';
2496 ELSE
2497 insert into pon_interface_errors (
2498 BATCH_ID,
2499 INTERFACE_LINE_ID,
2500 TABLE_NAME,
2501 COLUMN_NAME,
2502 ERROR_MESSAGE_NAME,
2503 ERROR_VALUE
2504 )
2505 select
2506 pai1.batch_id,
2507 pai1.interface_line_id,
2508 'PON_AUC_ATTRIBUTES_INTERFACE',
2509 fnd_message.get_string('PON','PON_AUCTS_ATTR'),
2510 'PON_DUPLICATE_WARNING_ATTR',
2514 where pai1.batch_id = p_batch_id
2511 pai1.attribute_name
2512 from pon_auc_attributes_interface pai1,
2513 pon_auc_attributes_interface pai2
2515 and pai1.batch_id = pai2.batch_id
2516 and pai1.interface_line_id = pai2.interface_line_id
2517 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
2518 and pai1.sequence_number <> pai2.sequence_number
2519 AND Nvl(pai1.action,'INSERT')='INSERT';
2520 END IF;
2521
2522 -- duplicate check against the allready saved header attributes.
2523 IF ( p_attr_type_header ) THEN
2524 insert into pon_interface_errors (
2525 BATCH_ID,
2526 INTERFACE_LINE_ID,
2527 TABLE_NAME,
2528 COLUMN_NAME,
2529 ERROR_MESSAGE_NAME,
2530 ERROR_VALUE
2531 )
2532 select
2533 pai1.batch_id,
2534 pai1.interface_line_id,
2535 'PON_AUC_ATTRIBUTES_INTERFACE',
2536 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
2537 'PON_REQUIREMENT_DUPLICATE_ATT',
2538 pai1.attribute_name
2539 from pon_auc_attributes_interface pai1,
2540 pon_auction_attributes paa
2541 where pai1.batch_id = p_batch_id
2542 and paa.auction_header_id = pai1.auction_header_id
2543 and pai1.auction_line_number = -1
2544 and paa.line_number = pai1.auction_line_number
2545 and upper(pai1.attribute_name) = upper(paa.attribute_name)
2546 AND Nvl(pai1.action,'INSERT')='INSERT';
2547 END IF;
2548
2549 END validate_attributes_api;
2550 -----------------------------------------------------------------------
2551 --Start of Comments
2552 --Name: insert_error_interface
2553 --Description : insert a record in pon_interface_errors table for validation failures
2554 --Parameters:
2555 --IN:
2556 -- l_BATCH_ID
2557 -- l_INTERFACE_LINE_ID
2558 -- l_TABLE_NAME
2559 -- l_COLUMN_NAME
2560 -- l_ERROR_MESSAGE_NAME
2561 -- l_ERROR_VALUE
2562 --OUT:
2563 --Returns:
2564 --Notes:
2565 --Testing:
2566 --End of Comments
2567 ------------------------------------------------------------------------
2568 PROCEDURE INSERT_ERROR_INTERFACE
2569 (
2570 l_BATCH_ID IN PON_INTERFACE_ERRORS.BATCH_ID%TYPE,
2571 l_INTERFACE_LINE_ID IN PON_INTERFACE_ERRORS.INTERFACE_LINE_ID%TYPE,
2572 l_TABLE_NAME IN PON_INTERFACE_ERRORS.TABLE_NAME%TYPE,
2573 l_COLUMN_NAME IN PON_INTERFACE_ERRORS.COLUMN_NAME%TYPE,
2574 l_ERROR_MESSAGE_NAME IN PON_INTERFACE_ERRORS.ERROR_MESSAGE_NAME%TYPE,
2575 l_ERROR_VALUE IN PON_INTERFACE_ERRORS.ERROR_VALUE%TYPE)
2576 IS
2577 l_status NUMBER;
2578 BEGIN
2579 INSERT
2580 INTO pon_interface_errors
2581 (
2582 BATCH_ID ,
2583 INTERFACE_LINE_ID ,
2584 TABLE_NAME ,
2585 COLUMN_NAME ,
2586 ERROR_MESSAGE_NAME,
2587 ERROR_VALUE
2588 )
2589 VALUES
2590 (
2591 l_BATCH_ID ,
2592 l_INTERFACE_LINE_ID ,
2593 l_TABLE_NAME ,
2594 l_COLUMN_NAME ,
2595 l_ERROR_MESSAGE_NAME,
2596 l_ERROR_VALUE
2597 ) ;
2598 END INSERT_ERROR_INTERFACE;
2599 -----------------------------------------------------------------------
2600 --Start of Comments
2601 --Name: create_emd_receipt_and_apply
2602 --Description : Validate the requirement before inserting
2603 --Parameters:
2604 --IN:
2605 -- p_source
2606 -- p_batch_id
2607 -- p_commit
2608 -- p_party_id
2609 -- p_in_rec
2610 -- p_in_rec.l_SCORING_METHOD
2611 --OUT:
2612 -- x_return_status Return status SUCCESS /ERROR
2613 --Returns:
2614 --Notes:
2615 --Testing:
2616 --End of Comments
2617 ------------------------------------------------------------------------
2618 PROCEDURE VALIDATE_REQUIREMENT
2619 (
2620 p_source VARCHAR2,
2621 p_batch_id NUMBER,
2622 p_party_id NUMBER,
2623 p_in_rec IN ATTRIBUTES_VALUES_VALIDATION,
2624 --p_in_rec.l_SCORING_METHOD PON_AUCTION_ATTRIBUTES.SCORING_METHOD%TYPE,
2625 x_return_status OUT NOCOPY VARCHAR
2626 )
2627 IS
2628 l_TABLE_NAME PON_INTERFACE_ERRORS.TABLE_NAME%TYPE :='PON_AUC_ATTRIBUTES_INTERFACE';
2629 --p_in_rec.l_KNOCKOUT_SCORE PON_AUCTION_ATTRIBUTES.KNOCKOUT_SCORE%TYPE:=NULL;
2630 l_status NUMBER;
2631 BEGIN
2632 x_return_status := 'Y';
2633 /* If response type is optional we can allow only Manual or None scoring methods */
2634 IF(p_in_rec.l_RESPONSE_TYPE = 'OPTIONAL')THEN
2635 IF(p_in_rec.l_SCORING_METHOD ='AUTOMATIC')THEN
2636 insert_error_interface
2637 (
2638 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_15',p_in_rec.l_RESPONSE_TYPE
2639 );
2640 x_return_status := 'N';
2641 END IF;
2642 END IF;
2643 /* If scoring method is null then the weight, knockout score, max score must be null*/
2644 IF(p_in_rec.l_SCORING_METHOD IS NULL)THEN
2645 IF(p_in_rec.l_WEIGHT IS NOT NULL) THEN
2646 insert_error_interface
2647 (
2648 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_2',p_in_rec.l_SCORING_METHOD
2649 );
2650 x_return_status := 'N';
2651 END IF;
2652 IF(p_in_rec.l_KNOCKOUT_SCORE IS NOT NULL) THEN
2653 insert_error_interface
2654 (
2655 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_3',p_in_rec.l_SCORING_METHOD
2656 );
2657 x_return_status := 'N';
2658 END IF;
2659 IF(p_in_rec.l_ATTR_MAX_SCORE IS NOT NULL) THEN
2660 insert_error_interface
2661 (
2662 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_4',p_in_rec.l_SCORING_METHOD
2663 );
2664 x_return_status := 'N';
2665 END IF;
2666 END IF;
2667 /* If response type is internal then scoring method cann't be automatic*/
2668 IF(p_in_rec.l_RESPONSE_TYPE = 'INTERNAL') THEN
2669 IF(p_in_rec.l_SCORING_METHOD ='AUTOMATIC') THEN
2670 insert_error_interface
2671 (
2672 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_5',p_in_rec.l_RESPONSE_TYPE
2673 );
2674 x_return_status := 'N';
2675 END IF;
2676 END IF;
2677 /* If response type is diaplay only or internal then scoring type cann't be LOV/RANGE */
2678 IF(p_in_rec.l_RESPONSE_TYPE = 'DISPLAY_ONLY' OR p_in_rec.l_RESPONSE_TYPE = 'INTERNAL') THEN
2679 IF(p_in_rec.l_SCORING_TYPE='LOV' OR p_in_rec.l_SCORING_TYPE='RANGE') THEN
2680 insert_error_interface
2681 (
2682 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_6',p_in_rec.l_RESPONSE_TYPE
2683 );
2684 x_return_status := 'N';
2685 END IF;
2686 END IF;
2687 /* If DATATYPE is URL then scoring type cannot be LOV/RANGE and also scoring method cannot be automatic */
2688 IF(p_in_rec.l_DATATYPE = 'URL') THEN
2689 IF(p_in_rec.l_SCORING_TYPE='LOV' OR p_in_rec.l_SCORING_TYPE='RANGE') THEN
2690 insert_error_interface
2691 (
2692 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),'PON_REQUIREMENT_ERR_7',p_in_rec.l_DATATYPE
2693 );
2694 x_return_status := 'N';
2695 END IF;
2696 IF(p_in_rec.l_SCORING_METHOD='AUTOMATIC') THEN
2697 insert_error_interface
2698 (
2699 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),'PON_REQUIREMENT_ERR_8',p_in_rec.l_DATATYPE
2700 );
2701 x_return_status := 'N';
2702 END IF;
2703 END IF;
2704 /* Automatic scoring will only have for Required type */
2705 IF(p_in_rec.l_RESPONSE_TYPE <> 'REQUIRED' AND p_in_rec.l_SCORING_METHOD='AUTOMATIC') THEN
2706 insert_error_interface
2707 (
2708 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_9',p_in_rec.l_RESPONSE_TYPE
2709 );
2710 x_return_status := 'N';
2711 END IF;
2712 /* Manual scoring method cannot have scoring type LOV/RANGE and ATTR_MAX_SCORE is must*/
2713 IF(p_in_rec.l_SCORING_METHOD ='MANUAL') THEN
2714 IF(p_in_rec.l_SCORING_TYPE='LOV' OR p_in_rec.l_SCORING_TYPE='RANGE') THEN
2715 insert_error_interface
2716 (
2717 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,pon_auction_pkg.getMessage('PON_AUCTS_TYPE'),'PON_REQUIREMENT_ERR_10',p_in_rec.l_SCORING_METHOD
2718 );
2719 x_return_status := 'N';
2720 END IF;
2721 IF(p_in_rec.l_ATTR_MAX_SCORE = NULL OR p_in_rec.l_ATTR_MAX_SCORE = 0) THEN
2722 insert_error_interface
2723 (
2724 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUC_SCORE'),'PON_REQUIREMENT_ERR_14',p_in_rec.l_SCORING_METHOD
2725 );
2726 x_return_status := 'N';
2727 END IF;
2728 END IF;
2729 /* If scoring method is automatic then the score need to be exsist*/
2730 IF(p_in_rec.l_SCORING_METHOD='AUTOMATIC') THEN
2731 BEGIN
2732 SELECT DISTINCT 'Y' INTO x_return_status FROM pon_attribute_scores WHERE auction_header_id=p_in_rec.l_auction_header_id AND attribute_sequence_number=p_in_rec.l_sequence_number;
2733 EXCEPTION
2734 WHEN No_Data_Found THEN
2735 insert_error_interface
2736 (
2737 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUC_SCORE'),'PON_REQUIREMENT_ERR_11',p_in_rec.l_SCORING_METHOD
2738 );
2739 x_return_status := 'N';
2740 END;
2741 /* Text datatype cannot have range scoring type and date/num datatype cannot have LOV scoring type */
2742 IF((p_in_rec.l_SCORING_TYPE <>'LOV' AND p_in_rec.l_DATATYPE = 'TXT') OR (p_in_rec.l_SCORING_TYPE<>'RANGE' AND (p_in_rec.l_DATATYPE = 'NUM' OR p_in_rec.l_DATATYPE = 'DAT'))) THEN
2743 insert_error_interface
2744 (
2745 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUC_SCORE'),'PON_DATA_SCORE_MISMATCH',p_in_rec.l_SCORING_TYPE
2746 );
2747 x_return_status := 'N';
2748 END IF;
2749 END IF;
2750
2751 /* If knockout score is not null then max score cannot be null and knockout score cannot be greater than max score and cannot be less than 0*/
2752 IF(p_in_rec.l_KNOCKOUT_SCORE IS NOT NULL) THEN
2753 IF(p_in_rec.l_ATTR_MAX_SCORE IS NULL OR p_in_rec.l_KNOCKOUT_SCORE > p_in_rec.l_ATTR_MAX_SCORE OR p_in_rec.l_KNOCKOUT_SCORE< 0) THEN
2754 insert_error_interface
2755 (
2756 p_in_rec.l_BATCH_ID,p_in_rec.l_INTERFACE_LINE_ID,l_TABLE_NAME,fnd_message.get_string('PON','PON_AUC_SCORE'),'PON_REQUIREMENT_ERR_12',p_in_rec.l_KNOCKOUT_SCORE
2757 );
2758 x_return_status := 'N';
2759 END IF;
2760 END IF;
2761
2762 END VALIDATE_REQUIREMENT;
2763
2764 END pon_auc_interface_table_pkg;