[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.13.12010000.2 2008/08/03 03:44:57 sarunach 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,
42 pon_auc_doctypes doc
43 where pah.auction_header_id = l_auction_header_id
44 and pah.doctype_id = doc.doctype_id;
45 EXCEPTION
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
226 )
227 VALUES
228 (
229 selected_batch_id,
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
416 AND pape.auction_header_id = ip.auction_header_id
417 AND pape.interface_line_id = ip.interface_line_id;
418
419 -- perform duplicate checks (using price element type id)
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.
577 when
578 (
579 selected_auction_line_number = -1
580 and selected_ATTR_MAX_SCORE is not null
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
731
732 -- populate GROUP_CODE. Note that the comparison is case sensitive.
733
734 update pon_auc_attributes_interface paai
735 set paai.GROUP_CODE = (
736 select lookup_code
737 from fnd_lookup_values attrGrpFlv
738 where lookup_type = l_group_lookup_type
739 --where lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
740 and meaning = paai.GROUP_NAME
741 and attrGrpFlv.LANGUAGE = userenv('LANG')
742 and attrGrpFlv.view_application_id = 0
743 and attrGrpFlv.security_group_id = 0
744 and attrGrpFlv.enabled_flag = 'Y'
745 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
746 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
747 )
748 where paai.batch_id = p_batch_id
749 and paai.GROUP_NAME is not null;
750
751 ELSE
752 -- For Header Case, if the Group Name is null, default General.
753 update pon_auc_attributes_interface paai
754 set paai.GROUP_NAME = (
755 select meaning
756 from fnd_lookup_values attrGrpFlv
757 where attrGrpFlv.lookup_type = 'PON_HEADER_ATTRIBUTE_GROUPS'
758 and attrGrpFlv.lookup_code = 'GENERAL'
759 and attrGrpFlv.LANGUAGE = userenv('LANG')
760 and attrGrpFlv.view_application_id = 0
761 and attrGrpFlv.security_group_id = 0
762 and attrGrpFlv.enabled_flag = 'Y'
763 and nvl(attrGrpFlv.start_date_active,SYSDATE) <= SYSDATE
764 and nvl(attrGrpFlv.end_date_active,SYSDATE) > SYSDATE-1
765 )
766 where paai.batch_id = p_batch_id
767 and paai.GROUP_NAME is null;
768
769 END IF;
770
771 ---------------- Validations start here ------------
772
773 INSERT ALL
774
775 -- Datatype
776 WHEN
777 (
778 selected_datatype is null
779 )
780 THEN into pon_interface_errors
781 (
782 BATCH_ID,
783 INTERFACE_LINE_ID,
784 TABLE_NAME,
785 COLUMN_NAME,
786 ERROR_MESSAGE_NAME,
787 ERROR_VALUE
788 )
789 VALUES
790 (
791 selected_batch_id,
792 selected_interface_line_id,
793 'PON_AUC_ATTRIBUTES_INTERFACE',
794 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
795 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
796 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
797 selected_datatype
798 )
799
800 -- Response Type
801
802 WHEN
803 (
804 selected_response_type is null
805 )
806 THEN into pon_interface_errors
807 (
808 BATCH_ID,
809 INTERFACE_LINE_ID,
810 TABLE_NAME,
811 COLUMN_NAME,
812 ERROR_MESSAGE_NAME,
813 ERROR_VALUE
814 )
815 VALUES
816 (
817 selected_batch_id,
818 selected_interface_line_id,
819 'PON_AUC_ATTRIBUTES_INTERFACE',
820 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')),
821 'PON_CAT_INVALID_VALUE',
822 selected_response_type_name
823 )
824
825 -- Display Target Flag
826
827 WHEN
828 (
829 nvl(selected_display_target_flag,'N') not in ('Y','N')
830 )
831
832 THEN into pon_interface_errors
833 (
834 BATCH_ID,
835 INTERFACE_LINE_ID,
836 TABLE_NAME,
837 COLUMN_NAME,
838 ERROR_MESSAGE_NAME,
839 ERROR_VALUE
840 )
841 values
842 (
843 selected_batch_id,
844 selected_interface_line_id,
845 'PON_AUC_ATTRIBUTES_INTERFACE',
846 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
847 'PON_AUCTS_INV_PR_QT_VAL',
848 selected_display_target_flag
849 )
850
851 -- validate datatype [lookup_type = PON_AUCTION_ATTRIBUTE_TYPE]
852
853 when
854 (
855 selected_datatype not in (
856 select lookup_code
857 from fnd_lookups
858 where lookup_type = 'PON_AUCTION_ATTRIBUTE_TYPE'
859 )
860 )
861 then into pon_interface_errors
862 (
863 BATCH_ID,
864 INTERFACE_LINE_ID,
865 TABLE_NAME,
866 COLUMN_NAME,
867 ERROR_MESSAGE_NAME,
868 ERROR_VALUE
869 )
870 values
871 (
872 selected_batch_id,
873 selected_interface_line_id,
874 'PON_AUC_ATTRIBUTES_INTERFACE',
875 fnd_message.get_string('PON','PON_AUCTS_ATTR_DATATYPE'),
876 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
877 'PON_AUCTS_ATTR_INVALID_TYPE', 'PON_AUCTS_REQ_INVALID_TYPE'),
878 selected_datatype
879 )
880
881 -- validate display target flag is Y/N
882 when
883 (
884 nvl(selected_display_target_flag,'N') not in ('Y','N')
885 )
886 then into pon_interface_errors
887 (
888 BATCH_ID,
889 INTERFACE_LINE_ID,
890 TABLE_NAME,
891 COLUMN_NAME,
892 ERROR_MESSAGE_NAME,
893 ERROR_VALUE
894 )
895 values
896 (
897 selected_batch_id,
898 selected_interface_line_id,
899 'PON_AUC_ATTRIBUTES_INTERFACE',
900 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
901 'PON_AUCTS_INV_PR_QT_VAL',
902 selected_display_target_flag
903 )
904
905 when
906 (
907 selected_display_target_flag = 'Y'
908 and selected_value is null
909 )
910 then into pon_interface_errors
911 (
912 BATCH_ID,
913 INTERFACE_LINE_ID,
914 TABLE_NAME,
915 COLUMN_NAME,
916 ERROR_MESSAGE_NAME,
917 ERROR_VALUE
918 )
919 values
920 (
921 selected_batch_id,
922 selected_interface_line_id,
923 'PON_AUC_ATTRIBUTES_INTERFACE',
924 fnd_message.get_string('PON','PON_AUCTS_TARGET_VALUE'),
925 'PON_AUCTS_ATTR_SHOW_TARGET',
926 selected_value
927 )
928
929 -- validate value given if display_only = Y
930 when
931 (
932 selected_display_only_flag = 'Y'
933 and selected_value is null
934 -- Bug 6957765
935 and nvl(selected_display_target_flag,'N') = 'Y'
936 )
937 then into pon_interface_errors
938 (
939 BATCH_ID,
940 INTERFACE_LINE_ID,
941 TABLE_NAME,
942 COLUMN_NAME,
943 ERROR_MESSAGE_NAME,
944 ERROR_VALUE
945 )
946 values
947 (
948 selected_batch_id,
949 selected_interface_line_id,
950 'PON_AUC_ATTRIBUTES_INTERFACE',
951 pon_auction_pkg.getMessage('PON_AUCTS_ATTR_TARGET',l_message_suffix),
952 decode(l_group_pref_name, 'LINE_ATTR_DEFAULT_GROUP',
953 'PON_AUCTS_ATTR_DISPLAY_TARGET', 'PON_AUCTS_REQ_DISPLAY_TARGET'),
954 selected_value
955 )
956
957 -- Attribute Name
958
959 when
960 (
961 selected_attribute_name is null
962 )
963 then into pon_interface_errors
964 (
965 BATCH_ID,
966 INTERFACE_LINE_ID,
967 TABLE_NAME,
968 COLUMN_NAME,
969 ERROR_MESSAGE_NAME,
970 ERROR_VALUE
971 )
972 values
973 (
974 selected_batch_id,
975 selected_interface_line_id,
976 'PON_AUC_ATTRIBUTES_INTERFACE',
977 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')),
978 'PON_FIELD_MUST_BE_ENTERED',
979 selected_attribute_name
980 )
981
982 -- required field checks
983 -- SEQUENCE_NUMBER -- should never be displayed to user
984 -- Do not do this check for Header attributes. We will populate the sequence
985 -- when we copy them over to the AuctionAttributesVO
986 when
987 (
988 l_attr_type_header = 'N' AND
989 selected_sequence_number is null
990 )
991 then into pon_interface_errors
992 (
993 BATCH_ID,
994 INTERFACE_LINE_ID,
995 TABLE_NAME,
996 COLUMN_NAME,
997 ERROR_MESSAGE_NAME,
998 ERROR_VALUE
999 )
1000 values
1001 (
1002 selected_batch_id,
1003 selected_interface_line_id,
1004 'PON_AUC_ATTRIBUTES_INTERFACE',
1005 'SEQUENCE_NUMBER',
1006 'PON_CAT_DISP_SEQ_M',
1007 selected_sequence_number
1008 )
1009
1010 -- To insert errors for the group which are invalid.
1011 when
1012 (
1013 selected_group_code is null
1014 AND l_attr_type_header = 'N'
1015 )
1016 then into pon_interface_errors
1017 (
1018 BATCH_ID,
1019 INTERFACE_LINE_ID,
1020 TABLE_NAME,
1021 COLUMN_NAME,
1022 ERROR_MESSAGE_NAME,
1023 ERROR_VALUE
1024 )
1025 values
1026 (
1027 selected_batch_id,
1028 selected_interface_line_id,
1029 'PON_AUC_ATTRIBUTES_INTERFACE',
1030 fnd_message.get_string('PON','PON_AUCTS_GROUP'),
1031 'PON_AUCTS_INVALID_GROUP',
1032 selected_group_name
1033 )
1034
1035 -- validate display target == N if attribute type is Internal
1036 when
1037 (
1038 nvl(selected_display_target_flag,'N') = 'Y'
1039 and selected_internal_attr_flag = 'Y'
1040 and l_attr_type_header = 'Y'
1041 )
1042 then into pon_interface_errors
1043 (
1044 BATCH_ID,
1045 INTERFACE_LINE_ID,
1046 TABLE_NAME,
1047 COLUMN_NAME,
1048 ERROR_MESSAGE_NAME,
1049 ERROR_VALUE
1050 )
1051 values
1052 (
1053 selected_batch_id,
1054 selected_interface_line_id,
1055 'PON_AUC_ATTRIBUTES_INTERFACE',
1056 fnd_message.get_string('PON','PON_AUCTS_ATTR_D_TARGET'),
1057 'PON_AUC_INTERNAL_ATT_ERROR',
1058 fnd_message.get_string('PON','PON_CORE_NO')
1059 )
1060
1061 SELECT
1062 pai.batch_id selected_batch_id,
1063 pai.interface_line_id selected_interface_line_id,
1064 pai.datatype selected_datatype,
1065 pai.value selected_value,
1066 pai.display_target_flag selected_display_target_flag,
1067 pai.display_only_flag selected_display_only_flag,
1068 pai.response_type_name selected_response_type_name,
1069 pai.response_type selected_response_type,
1070 pai.attribute_name selected_attribute_name,
1071 pai.sequence_number selected_sequence_number,
1072 pai.group_name selected_group_name,
1073 pai.group_code selected_group_code,
1074 pai.internal_attr_flag selected_internal_attr_flag
1075
1076 from
1077 pon_auc_attributes_interface pai
1078 where
1079 pai.batch_id = p_batch_id;
1080
1081 -- perform duplicate checks
1082 IF ( p_attr_type_header ) THEN
1083 insert into pon_interface_errors (
1084 BATCH_ID,
1085 INTERFACE_LINE_ID,
1086 TABLE_NAME,
1087 COLUMN_NAME,
1088 ERROR_MESSAGE_NAME,
1089 ERROR_VALUE
1090 )
1091 select
1092 pai1.batch_id,
1093 pai1.interface_line_id,
1094 'PON_AUC_ATTRIBUTES_INTERFACE',
1095 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
1096 'PON_REQUIREMENT_DUPLICATE_ATT',
1097 pai1.attribute_name
1098 from pon_auc_attributes_interface pai1,
1099 pon_auc_attributes_interface pai2
1100 where pai1.batch_id = p_batch_id
1101 and pai1.batch_id = pai2.batch_id
1102 and pai1.auction_line_number = pai2.auction_line_number
1103 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
1104 and pai1.interface_line_id <> pai2.interface_line_id;
1105 ELSE
1106 insert into pon_interface_errors (
1107 BATCH_ID,
1108 INTERFACE_LINE_ID,
1109 TABLE_NAME,
1110 COLUMN_NAME,
1111 ERROR_MESSAGE_NAME,
1112 ERROR_VALUE
1113 )
1114 select
1115 pai1.batch_id,
1116 pai1.interface_line_id,
1117 'PON_AUC_ATTRIBUTES_INTERFACE',
1118 fnd_message.get_string('PON','PON_AUCTS_ATTR'),
1119 'PON_DUPLICATE_WARNING_ATTR',
1120 pai1.attribute_name
1121 from pon_auc_attributes_interface pai1,
1122 pon_auc_attributes_interface pai2
1123 where pai1.batch_id = p_batch_id
1124 and pai1.batch_id = pai2.batch_id
1125 and pai1.interface_line_id = pai2.interface_line_id
1126 and upper(pai1.attribute_name) = upper(pai2.attribute_name)
1127 and pai1.sequence_number <> pai2.sequence_number;
1128 END IF;
1129
1130 -- duplicate check against the allready saved header attributes.
1131 IF ( p_attr_type_header ) THEN
1132 insert into pon_interface_errors (
1133 BATCH_ID,
1134 INTERFACE_LINE_ID,
1135 TABLE_NAME,
1136 COLUMN_NAME,
1137 ERROR_MESSAGE_NAME,
1138 ERROR_VALUE
1139 )
1140 select
1141 pai1.batch_id,
1142 pai1.interface_line_id,
1143 'PON_AUC_ATTRIBUTES_INTERFACE',
1144 fnd_message.get_string('PON','PON_AUC_REQUIREMENT'),
1145 'PON_REQUIREMENT_DUPLICATE_ATT',
1146 pai1.attribute_name
1147 from pon_auc_attributes_interface pai1,
1148 pon_auction_attributes paa
1149 where pai1.batch_id = p_batch_id
1150 and paa.auction_header_id = pai1.auction_header_id
1151 and pai1.auction_line_number = -1
1152 and paa.line_number = pai1.auction_line_number
1153 and upper(pai1.attribute_name) = upper(paa.attribute_name);
1154 END IF;
1155
1156 END validate_attributes;
1157
1158
1159 PROCEDURE add_template_price_elements(
1160 p_batch_id NUMBER,
1161 p_auction_template_id NUMBER,
1162 p_auction_header_id NUMBER
1163 ) AS
1164 l_sequence_start NUMBER;
1165 l_auction_pf_type_allowed VARCHAR2(30);
1166 BEGIN
1167 -- select max sequence number (across all lines)
1168 select nvl(max(sequence_number), 0)
1169 into l_sequence_start
1170 from pon_auc_price_elements_int
1171 where batch_id = p_batch_id
1172 and sequence_number >= 0;
1173
1174 l_sequence_start := l_sequence_start + 10;
1175
1176 -- determine which price factor types are allowed by the negotiation
1177 SELECT pf_type_allowed
1178 INTO l_auction_pf_type_allowed
1179 FROM pon_auction_headers_all
1180 WHERE auction_header_id = p_auction_header_id;
1181
1182 -- insert template price elements for all lines
1183 insert into pon_auc_price_elements_int (
1184 BATCH_ID,
1185 INTERFACE_LINE_ID,
1186 AUCTION_HEADER_ID,
1187 AUCTION_LINE_NUMBER,
1188 SEQUENCE_NUMBER,
1189 PRICE_ELEMENT_TYPE_NAME,
1190 PRICE_ELEMENT_TYPE_ID,
1191 DESCRIPTION,
1192 PRICING_BASIS_NAME,
1193 PRICING_BASIS,
1194 VALUE,
1195 PRECISION,
1196 DISPLAY_TARGET_FLAG,
1197 PF_TYPE,
1198 DISPLAY_TO_SUPPLIERS_FLAG
1199 )
1200 select
1201 ip.batch_id,
1202 ip.interface_line_id,
1203 ip.auction_header_id,
1204 ip.auction_line_number,
1205 l_sequence_start + pe.sequence_number,
1206 petl.name,
1207 pe.price_element_type_id,
1208 petl.description,
1209 fl.meaning,
1210 pe.pricing_basis,
1211 pe.value,
1212 -1,
1213 pe.display_target_flag,
1214 pe.pf_type,
1215 pe.display_to_suppliers_flag
1216 from
1217 pon_price_elements pe,
1218 pon_price_element_types pet,
1219 pon_price_element_types_tl petl,
1220 fnd_lookups fl,
1221 pon_item_prices_interface ip
1222 where
1223 ip.batch_id = p_batch_id
1224 and pe.auction_header_id = p_auction_template_id
1225 and pe.price_element_type_id = pet.price_element_type_id
1226 and pe.price_element_type_id = petl.price_element_type_id
1227 and pe.price_element_type_id <> -10 -- why copy Item Price?
1228 and pet.enabled_flag = 'Y'
1229 and petl.language = userenv('LANG')
1230 and fl.lookup_type = 'PON_PRICING_BASIS'
1231 and fl.lookup_code = pe.pricing_basis
1232 and ip.group_type <> 'GROUP'
1233 and pe.pf_type = DECODE(l_auction_pf_type_allowed,
1234 'BOTH', pe.pf_type,
1235 'BUYER', 'BUYER',
1236 'SUPPLIER', 'SUPPLIER',
1237 'NONE');
1238
1239 -- resolve duplicate price elements by removing the Template price element
1240 delete from pon_auc_price_elements_int pape1
1241 where rowid in (
1242 select pape1.rowid
1243 from pon_auc_price_elements_int pape2
1244 where pape1.batch_id = p_batch_id
1245 and pape1.batch_id = pape2.batch_id
1246 and pape1.interface_line_id = pape2.interface_line_id
1247 and pape1.price_element_type_id = pape2.price_element_type_id
1248 and pape1.precision = -1
1249 and (pape2.precision is null or pape2.precision <> -1)
1250 );
1251 END add_template_price_elements;
1252
1253
1254 PROCEDURE add_template_attributes(
1255 p_batch_id NUMBER,
1256 p_auction_template_id NUMBER
1257 ) AS
1258 l_sequence_start NUMBER;
1259 BEGIN
1260 -- select max sequence number (across all lines)
1261 BEGIN
1262 select nvl(max(sequence_number),0)
1263 into l_sequence_start
1264 from pon_auc_attributes_interface
1265 where batch_id = p_batch_id
1266 and sequence_number >= 0;
1267 l_sequence_start := l_sequence_start + 10;
1268 EXCEPTION
1269 WHEN no_data_found THEN
1270 l_sequence_start := 0;
1271 END;
1272
1273 -- insert template attributes for all lines
1274 insert into pon_auc_attributes_interface (
1275 BATCH_ID,
1276 INTERFACE_LINE_ID,
1277 AUCTION_LINE_NUMBER,
1278 AUCTION_HEADER_ID,
1279 SEQUENCE_NUMBER,
1280 ATTRIBUTE_NAME,
1281 GROUP_CODE,
1282 DATATYPE,
1283 VALUE,
1284 RESPONSE_TYPE_NAME,
1285 RESPONSE_TYPE,
1286 MANDATORY_FLAG,
1287 DISPLAY_ONLY_FLAG,
1288 DISPLAY_TARGET_FLAG,
1289 SCORING_TYPE,
1290 ATTR_GROUP_SEQ_NUMBER,
1291 ATTR_DISP_SEQ_NUMBER
1292 )
1293 select
1294 ip.batch_id,
1295 ip.interface_line_id,
1296 ip.auction_line_number,
1297 ip.auction_header_id,
1298 l_sequence_start + att.sequence_number,
1299 att.attribute_name,
1300 att.ATTR_GROUP,
1301 att.datatype,
1302 att.value,
1303 'PON_FROM_TEMPLATE',
1304 decode(att.mandatory_flag,'Y','REQUIRED',
1305 decode(att.display_only_flag,'Y','DISPLAY_ONLY','OPTIONAL') ),
1306 att.mandatory_flag,
1307 att.display_only_flag,
1308 att.display_target_flag,
1309 att.scoring_type,
1310 att.attr_group_seq_number,
1311 att.attr_disp_seq_number
1312 from
1313 pon_auction_attributes att,
1314 pon_item_prices_interface ip
1315 where
1316 ip.batch_id = p_batch_id
1317 and att.line_number <> -1
1318 and ip.group_type <> 'GROUP'
1319 and att.auction_header_id = p_auction_template_id;
1320
1321 -- resolve duplicate attributes by removing the Template attribute
1322 delete from pon_auc_attributes_interface pai1
1323 where rowid in (
1324 select pai1.rowid
1325 from pon_auc_attributes_interface pai2
1326 where pai1.batch_id = p_batch_id
1327 and pai1.batch_id = pai2.batch_id
1328 and pai1.interface_line_id = pai2.interface_line_id
1329 and pai1.attribute_name = pai2.attribute_name
1330 and pai1.response_type_name = 'PON_FROM_TEMPLATE'
1331 and pai2.response_type_name <> 'PON_FROM_TEMPLATE'
1332 );
1333 END add_template_attributes;
1334
1335
1336 PROCEDURE validate_price_differentials(
1337 p_source VARCHAR2,
1338 p_batch_id NUMBER
1339 )AS
1340
1341 l_auction_header_id pon_auc_price_differ_int.auction_header_id%TYPE;
1342 l_message_suffix VARCHAR2(2);
1343 l_contract_type pon_auction_headers_all.contract_type%TYPE;
1344 l_global_agreement pon_auction_headers_all.global_agreement_flag%TYPE;
1345
1346 BEGIN
1347
1348 -- init vars
1349 BEGIN
1350
1351
1352 SELECT max(papd.auction_header_id)
1353 INTO l_auction_header_id
1354 FROM pon_auc_price_differ_int papd
1355 WHERE papd.batch_id = p_batch_id;
1356
1357 SELECT pon_auction_pkg.get_message_suffix(doc.internal_name)
1358 INTO l_message_suffix
1359 FROM pon_auction_headers_all pah,pon_auc_doctypes doc
1360 WHERE pah.auction_header_id = l_auction_header_id
1361 AND pah.doctype_id = doc.doctype_id;
1362
1363 --Don't do any validation if its not a global blanket agreement
1364 -- And if there are any rows in the interface table delete them
1365 SELECT pah.contract_type, pah.global_agreement_flag
1366 INTO l_contract_type, l_global_agreement
1367 FROM pon_auction_headers_all pah
1368 WHERE pah.auction_header_id = l_auction_header_id;
1369
1370 IF ((l_contract_type <> 'BLANKET' AND l_contract_type <> 'CONTRACT') OR l_global_agreement <> 'Y') THEN
1371 DELETE FROM pon_auc_price_differ_int
1372 WHERE batch_id = p_batch_id;
1373 RETURN;
1374 END IF ;
1375
1376 EXCEPTION
1377
1378 WHEN no_data_found THEN
1379 l_message_suffix := NULL;
1380 l_auction_header_id := NULL;
1381 END;
1382
1383 -- update the interface table with the price_type values
1384 -- from the po_price_diff_lookups_v values
1385 UPDATE pon_auc_price_differ_int papdi
1386 SET price_type = (SELECT Nvl(MAX(ppdl.price_differential_type),'PRICE_TYPE_INVALID')
1387 FROM po_price_diff_lookups_v ppdl
1388 WHERE papdi.price_type_name = ppdl.price_differential_dsp(+)),
1389
1390 price_type_desc = (SELECT Nvl(MAX(ppdl.price_differential_desc),'PRICE_DESC_INVALID')
1391 FROM po_price_diff_lookups_v ppdl
1392 WHERE papdi.price_type_name = ppdl.price_differential_dsp(+))
1393 WHERE batch_id = p_batch_id
1394 AND price_type_name <> 'EMPTY_PRICE_TYPE_NAME';
1395
1396 --------------- Validations start here --------------
1397 INSERT ALL
1398
1399 -- Price Type errors will go into the interface table
1400 -- Check for Price type being null for those
1401
1402 WHEN
1403 (
1404 selected_multiplier <> -9999
1405 AND selected_price_type_name = 'EMPTY_PRICE_TYPE_NAME'
1406 )
1407 THEN INTO pon_interface_errors
1408 (
1409 BATCH_ID,
1410 INTERFACE_LINE_ID,
1411 TABLE_NAME,
1412 COLUMN_NAME,
1413 ERROR_MESSAGE_NAME,
1414 ERROR_VALUE
1415 )
1416 VALUES
1417 (
1418 selected_batch_id,
1419 selected_interface_line_id,
1420 'PON_AUC_PRICE_DIFFER_INT',
1421 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1422 'PON_FIELD_MUST_BE_ENTERED',
1423 null
1424 )
1425
1426 -- Price Type errors will go into the interface table
1427 -- Check for invalid price type values
1428
1429 WHEN
1430 (
1431 selected_price_type_name = 'PRICE_TYPE_INVALID'
1432 AND selected_price_type_name <> 'EMPTY_PRICE_TYPE_NAME'
1433 )
1434 THEN INTO pon_interface_errors
1435 (
1436 BATCH_ID,
1437 INTERFACE_LINE_ID,
1438 TABLE_NAME,
1439 COLUMN_NAME,
1440 ERROR_MESSAGE_NAME,
1441 ERROR_VALUE
1442 )
1443 values
1444 (
1445 selected_batch_id,
1446 selected_interface_line_id,
1447 'PON_AUC_PRICE_DIFFER_INT',
1448 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1449 'PON_TYPE_VALUE_INVALID',
1450 selected_price_type_name
1451 )
1452
1453 SELECT
1454 papd.batch_id selected_batch_id,
1455 papd.interface_line_id selected_interface_line_id,
1456 papd.multiplier selected_multiplier,
1457 papd.price_type_name selected_price_type_name
1458 FROM
1459 pon_auc_price_differ_int papd
1460 WHERE
1461 papd.batch_id = p_batch_id;
1462
1463 -- Price Type errors will go into the interface table
1464 -- Check if there are any duplicate values
1465 INSERT INTO
1466 pon_interface_errors (
1467 BATCH_ID,
1468 INTERFACE_LINE_ID,
1469 TABLE_NAME,
1470 COLUMN_NAME,
1471 ERROR_MESSAGE_NAME,
1472 ERROR_VALUE
1473 )
1474
1475 SELECT
1476 papd.batch_id,
1477 papd.interface_line_id,
1478 'PON_AUC_PRICE_DIFFER_INT',
1479 fnd_message.get_string('PON','PON_AUCTS_PRICE_TYPE'),
1480 'PON_DUPLICATE_WARN_PRICE_TYPE',
1481 papd.price_type_name
1482 FROM pon_auc_price_differ_int papd,
1483 pon_auc_price_differ_int papd2
1484 WHERE papd.batch_id = p_batch_id
1485 AND papd.batch_id = papd2.batch_id
1486 AND papd.interface_line_id = papd2.interface_line_id
1487 AND papd.price_type = papd2.price_type
1488 AND papd.sequence_number <> papd2.sequence_number;
1489
1490 END validate_price_differentials;
1491
1492 PROCEDURE add_ip_descriptors(p_batch_id IN NUMBER,
1493 p_auction_header_id IN NUMBER) IS
1494
1495 l_tp_id NUMBER;
1496 l_auction_round_number NUMBER;
1497 l_amendment_number NUMBER;
1498 l_default_attr_group pon_auction_attributes.attr_group%TYPE;
1499 l_ip_attr_default_option VARCHAR2(10);
1500
1501 BEGIN
1502
1503 select trading_partner_id, nvl(auction_round_number, 1), nvl(amendment_number, 0)
1504 into l_tp_id, l_auction_round_number, l_amendment_number
1505 from pon_auction_headers_all
1506 where auction_header_id = p_auction_header_id;
1507
1508 select nvl(ppp.preference_value,'GENERAL')
1509 into l_default_attr_group
1510 from pon_party_preferences ppp
1511 where ppp.app_short_name = 'PON' and
1512 ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
1513 ppp.party_id = l_tp_id;
1514
1515
1516 l_ip_attr_default_option := fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION');
1517 IF (nvl(l_ip_attr_default_option, 'NONE') = 'NONE') THEN
1518 RETURN;
1519 END IF;
1520
1521 add_ip_descs_on_new_lines(p_batch_id, l_default_attr_group, l_ip_attr_default_option);
1522
1523 IF (l_auction_round_number > 1 or l_amendment_number > 0) THEN
1524 add_ip_descs_on_updated_lines(p_batch_id, l_default_attr_group, l_ip_attr_default_option);
1525 END IF;
1526
1527 END add_ip_descriptors;
1528
1529 PROCEDURE add_ip_descs_on_new_lines(p_batch_id IN VARCHAR2,
1530 p_default_attr_group IN VARCHAR2,
1531 p_ip_attr_default_option IN VARCHAR2) IS
1532
1533
1534 l_max_attr_seq_num NUMBER;
1535 l_def_attr_group_seq_num NUMBER;
1536 l_def_attr_max_disp_seq_num NUMBER;
1537 l_max_ip_seq_num NUMBER;
1538
1539 CURSOR lines IS
1540 SELECT interface_line_id, auction_header_id, auction_line_number, ip_category_id
1541 FROM pon_item_prices_interface
1542 WHERE batch_id = p_batch_id and
1543 nvl(action, '+') = '+';
1544
1545
1546 BEGIN
1547
1548 l_max_ip_seq_num := 9999999999999;
1549
1550 FOR line in lines
1551 LOOP
1552
1553 l_max_attr_seq_num := get_max_attr_seq_num(p_batch_id, line.interface_line_id);
1554
1555 l_def_attr_group_seq_num := get_attr_group_seq_num(p_batch_id, line.interface_line_id,
1556 p_default_attr_group);
1557
1558 l_def_attr_max_disp_seq_num := get_attr_max_disp_seq_num(p_batch_id, line.interface_line_id,
1559 p_default_attr_group);
1560
1561 -- bring over ip descriptors (base and catalog) for new lines
1562
1563 INSERT INTO PON_AUC_ATTRIBUTES_INTERFACE (
1564 BATCH_ID,
1565 INTERFACE_LINE_ID,
1566 AUCTION_HEADER_ID,
1567 AUCTION_LINE_NUMBER,
1568 SEQUENCE_NUMBER,
1569 ATTRIBUTE_NAME,
1570 GROUP_CODE,
1571 DATATYPE,
1572 RESPONSE_TYPE,
1573 MANDATORY_FLAG,
1574 DISPLAY_ONLY_FLAG,
1575 INTERNAL_ATTR_FLAG,
1576 DISPLAY_TARGET_FLAG,
1577 VALUE,
1578 SCORING_TYPE,
1579 ATTR_GROUP_SEQ_NUMBER,
1580 ATTR_DISP_SEQ_NUMBER,
1581 IP_CATEGORY_ID,
1582 IP_DESCRIPTOR_ID
1583 )
1584 SELECT
1585
1586 p_batch_id, -- BATCH_ID
1587 line.interface_line_id, -- INTERFACE_LINE_ID
1588 line.auction_header_id, -- AUCTION_HEADER_ID
1589 line.auction_line_number, -- AUCTION_LINE_NUMBER
1590 l_max_attr_seq_num + (rownum*10), -- SEQUENCE_NUMBER
1591 attribute_name, -- ATTRIBUTE_NAME
1592 p_default_attr_group, -- GROUP_CODE
1593 datatype, -- DATATYPE
1594 'OPTIONAL', -- RESPONSE_TYPE
1595 'N', -- MANDATORY_FLAG
1596 'N', -- DISPLAY_ONLY_FLAG
1597 'N', -- INTERNAL_ATTR_FLAG
1598 'N', -- DISPLAY_TARGET_FLAG
1599 null, -- VALUE
1600 'NONE', -- SCORING_TYPE
1601 l_def_attr_group_seq_num, -- ATTR_GROUP_SEQ_NUMBER
1602 l_def_attr_max_disp_seq_num + (rownum * 10), -- ATTR_DISP_SEQ_NUMBER
1603 ip_category_id, -- IP_CATEGORY_ID
1604 ip_descriptor_id -- IP_DESCRIPTOR_ID
1605 FROM
1606 (SELECT attribute_name, decode(type, 1, 'NUM', 'TXT') datatype,
1607 rt_category_id ip_category_id, attribute_id ip_descriptor_id
1608 FROM icx_cat_agreement_attrs_v
1609 WHERE ((rt_category_id = 0 and p_ip_attr_default_option in ('ALL', 'BASE')) or
1610 (rt_category_id = line.ip_category_id and p_ip_attr_default_option in ('ALL', 'CATEGORY'))) and language = userenv('LANG') and
1611 upper(attribute_name) not in (select upper(attribute_name)
1612 from pon_auc_attributes_interface
1613 where batch_id = p_batch_id and
1614 interface_line_id = line.interface_line_id)
1615 ORDER BY nvl(sequence, l_max_ip_seq_num) asc);
1616
1617 END LOOP;
1618
1619 END add_ip_descs_on_new_lines;
1620
1621 PROCEDURE add_ip_descs_on_updated_lines(p_batch_id IN VARCHAR2,
1622 p_default_attr_group IN VARCHAR2,
1623 p_ip_attr_default_option IN VARCHAR2) IS
1624
1625 l_max_attr_seq_num NUMBER;
1626 l_def_attr_group_seq_num NUMBER;
1627 l_def_attr_max_disp_seq_num NUMBER;
1628 l_max_ip_seq_num NUMBER;
1629 l_ip_attr_default_option VARCHAR2(10);
1630
1631 -- updated lines where the ip category has changed and is not null;
1632
1633 CURSOR lines IS
1634 SELECT interface_line_id, auction_header_id, auction_line_number, ip_category_id
1635 FROM pon_item_prices_interface
1636 WHERE batch_id = p_batch_id and
1637 nvl(action, '+') = '#' and
1638 auction_line_number in (select paip.line_number
1639 from pon_item_prices_interface p1,
1640 pon_auction_item_prices_all paip
1641 where p1.batch_id = p_batch_id and
1642 nvl(p1.action, '+') = '#' and
1643 p1.auction_header_id = paip.auction_header_id and
1644 p1.auction_line_number = paip.line_number and
1645 p1.ip_category_id is not null and
1646 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1));
1647
1648
1649 BEGIN
1650
1651 delete from
1652 (select *
1653 from pon_auc_attributes_interface
1654 where auction_line_number in (select paip.line_number
1655 from pon_item_prices_interface p1,
1656 pon_auction_item_prices_all paip
1657 where p1.batch_id = p_batch_id and
1658 nvl(p1.action, '+') = '#' and
1659 p1.auction_header_id = paip.auction_header_id and
1660 p1.auction_line_number = paip.line_number and
1661 paip.ip_category_id is not null and
1662 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1))) paai
1663 where batch_id = p_batch_id and
1664 exists (select null
1665 from pon_auction_attributes paa
1666 where paa.auction_header_id = paai.auction_header_id and
1667 paa.line_number = paai.auction_line_number and
1668 upper(paa.attribute_name) = upper(paai.attribute_name) and
1669 paa.ip_category_id is not null and
1670 paa.ip_category_id <> 0);
1671
1672 l_ip_attr_default_option := nvl(fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION'), 'NONE');
1673
1674 IF (l_ip_attr_default_option in ('NONE', 'BASE')) THEN
1675 RETURN;
1676 END IF;
1677
1678 l_max_ip_seq_num := 9999999999999;
1679
1680 FOR line in lines
1681 LOOP
1682
1683 l_max_attr_seq_num := get_max_attr_seq_num(p_batch_id, line.interface_line_id);
1684
1685 l_def_attr_group_seq_num := get_attr_group_seq_num(p_batch_id, line.interface_line_id,
1686 p_default_attr_group);
1687
1688 l_def_attr_max_disp_seq_num := get_attr_max_disp_seq_num(p_batch_id, line.interface_line_id,
1689 p_default_attr_group);
1690
1691 -- bring over ip catalog descriptors for updated lines
1692
1693 INSERT INTO PON_AUC_ATTRIBUTES_INTERFACE (
1694 BATCH_ID,
1695 INTERFACE_LINE_ID,
1696 AUCTION_HEADER_ID,
1697 AUCTION_LINE_NUMBER,
1698 SEQUENCE_NUMBER,
1699 ATTRIBUTE_NAME,
1700 GROUP_CODE,
1701 DATATYPE,
1702 RESPONSE_TYPE,
1703 MANDATORY_FLAG,
1704 DISPLAY_ONLY_FLAG,
1705 INTERNAL_ATTR_FLAG,
1706 DISPLAY_TARGET_FLAG,
1707 VALUE,
1708 SCORING_TYPE,
1709 ATTR_GROUP_SEQ_NUMBER,
1710 ATTR_DISP_SEQ_NUMBER,
1711 IP_CATEGORY_ID,
1712 IP_DESCRIPTOR_ID
1713 )
1714 SELECT
1715
1716 p_batch_id, -- BATCH_ID
1717 line.interface_line_id, -- INTERFACE_LINE_ID
1718 line.auction_header_id, -- AUCTION_HEADER_ID
1719 line.auction_line_number, -- AUCTION_LINE_NUMBER
1720 l_max_attr_seq_num + (rownum*10), -- SEQUENCE_NUMBER
1721 attribute_name, -- ATTRIBUTE_NAME
1722 p_default_attr_group, -- GROUP_CODE
1723 datatype, -- DATATYPE
1724 'OPTIONAL', -- RESPONSE_TYPE
1725 'N', -- MANDATORY_FLAG
1726 'N', -- DISPLAY_ONLY_FLAG
1727 'N', -- INTERNAL_ATTR_FLAG
1728 'N', -- DISPLAY_TARGET_FLAG
1729 null, -- VALUE
1730 'NONE', -- SCORING_TYPE
1731 l_def_attr_group_seq_num, -- ATTR_GROUP_SEQ_NUMBER
1732 l_def_attr_max_disp_seq_num + (rownum * 10), -- ATTR_DISP_SEQ_NUMBER
1733 ip_category_id, -- IP_CATEGORY_ID
1734 ip_descriptor_id -- IP_DESCRIPTOR_ID
1735 FROM
1736 (SELECT attribute_name, decode(type, 1, 'NUM', 'TXT') datatype,
1737 rt_category_id ip_category_id, attribute_id ip_descriptor_id
1738 FROM icx_cat_agreement_attrs_v
1739 WHERE rt_category_id = line.ip_category_id and
1740 language = userenv('LANG') and
1741 upper(attribute_name) not in (select upper(attribute_name)
1742 from pon_auc_attributes_interface
1743 where batch_id = p_batch_id and
1744 interface_line_id = line.interface_line_id)
1745 ORDER BY nvl(sequence, l_max_ip_seq_num) asc);
1746
1747 END LOOP;
1748
1749 END add_ip_descs_on_updated_lines;
1750
1751 FUNCTION get_max_attr_seq_num(p_batch_id IN NUMBER,
1752 p_interface_line_id IN NUMBER) RETURN NUMBER
1753
1754 IS
1755
1756 l_max_attr_seq_num NUMBER;
1757
1758 BEGIN
1759
1760 select nvl(max(sequence_number), 0)
1761 into l_max_attr_seq_num
1762 from pon_auc_attributes_interface
1763 where batch_id = p_batch_id and
1764 interface_line_id = p_interface_line_id;
1765
1766 RETURN l_max_attr_seq_num;
1767
1768 END get_max_attr_seq_num;
1769
1770 FUNCTION get_attr_group_seq_num(p_batch_id IN NUMBER,
1771 p_interface_line_id IN NUMBER,
1772 p_attr_group IN VARCHAR2) RETURN NUMBER
1773 IS
1774
1775 l_attr_group_seq_num NUMBER;
1776
1777 BEGIN
1778
1779 select attr_group_seq_number
1780 into l_attr_group_seq_num
1781 from pon_auc_attributes_interface
1782 where batch_id = p_batch_id and
1783 interface_line_id = p_interface_line_id and
1784 group_code = p_attr_group and
1785 rownum = 1;
1786
1787 RETURN l_attr_group_seq_num;
1788
1789 EXCEPTION
1790
1791 when others then
1792
1793 -- since group is not on the line yet,
1794 -- find max group seq number and add 10
1795
1796 select nvl(max(attr_group_seq_number), 0) + 10
1797 into l_attr_group_seq_num
1798 from pon_auc_attributes_interface
1799 where batch_id = p_batch_id and
1800 interface_line_id = p_interface_line_id;
1801
1802 RETURN l_attr_group_seq_num;
1803
1804 END get_attr_group_seq_num;
1805
1806 FUNCTION get_attr_max_disp_seq_num(p_batch_id IN NUMBER,
1807 p_interface_line_id IN NUMBER,
1808 p_attr_group IN VARCHAR2) RETURN NUMBER
1809 IS
1810
1811 l_attr_max_disp_seq_num NUMBER;
1812
1813 BEGIN
1814
1815 select nvl(max(attr_disp_seq_number), 0)
1816 into l_attr_max_disp_seq_num
1817 from pon_auc_attributes_interface
1818 where batch_id = p_batch_id and
1819 interface_line_id = p_interface_line_id and
1820 group_code = p_attr_group;
1821
1822 RETURN l_attr_max_disp_seq_num;
1823
1824 END get_attr_max_disp_seq_num;
1825
1826
1827 END pon_auc_interface_table_pkg;