[Home] [Help]
PACKAGE BODY: APPS.PON_CP_INTRFAC_TO_TRANSACTION
Source
1 PACKAGE BODY PON_CP_INTRFAC_TO_TRANSACTION as
2 /* $Header: PONCPITB.pls 120.26 2007/09/10 23:57:50 sssahai ship $ */
3
4
5 /** =============Start declaration of global variables =========*/
6 g_update_action CONSTANT VARCHAR2(1) := '#';
7 g_add_action CONSTANT VARCHAR2(1) :='+';
8 g_delete_action CONSTANT VARCHAR2(1) := '-';
9
10 -- batch id for which the copy needs to take place
11 g_batch_id NUMBER;
12 -- auction heder id corresponding to the batch id for which the copy needs to take place
13 g_auction_header_id NUMBER;
14 --User id of the person who uploaded the spreadsheet.
15 g_user_id NUMBER;
16
17 --the following are used in the function GET_NEXT_PE_SEQUENCE_NUMBER
18 g_price_element_line_number NUMBER;
19 g_price_element_seq_number NUMBER;
20 g_price_element_seq_increment CONSTANT NUMBER := 10;
21
22 -- These will be used by the procedure INITIALIZE_LINE_ATTR_GROUP
23 g_default_attribute_group VARCHAR2(30);
24 g_default_section_name VARCHAR2(240);
25 g_default_appl_attribute_group CONSTANT VARCHAR2(7) := 'GENERAL';
26 g_default_appl_section_name CONSTANT VARCHAR2(7) := 'General';
27
28 -- These determine if attribute, price differentials or price elements data is to be captured.
29 g_line_attribute_enabled VARCHAR2(1);
30 g_price_differentials_flag VARCHAR2(1);
31 g_price_element_enabled_flag VARCHAR2(1);
32 g_attribute_score_enabled_flag VARCHAR2(1);
33
34 --This will be used by GET_SEQUENCE_NUMBER
35 g_cur_internal_line_num NUMBER;
36 g_max_attribute_seq_num NUMBER;
37
38 --Global varibales that are computed once and is required in UPDATE_PRICE_FACTORS
39 g_is_amendment VARCHAR2(1);
40 -- This is the max previous round line plus one.
41 g_max_prev_line_num_plus_one number;
42
43 -- These will be used for debugging the code
44 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
45 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_CP_INTRFAC_TO_TRANSACTION';
46 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
47 /** ============= End declaration of global variables =========*/
48
49
50
51 /** =============Start declaration of private functions and procedures =========*/
52 PROCEDURE print_debug_log(p_module IN VARCHAR2,
53 p_message IN VARCHAR2);
54
55 PROCEDURE print_error_log(p_module IN VARCHAR2,
56 p_message IN VARCHAR2);
57
58
59 PROCEDURE INITIALIZE_LINE_ATTR_GROUP(p_party_id IN NUMBER);
60
61
62 -- Procedures for deleteing lines and their children that have been
63 -- marked as deleted by the spread
64 PROCEDURE DELETE_LINES_WITH_CHILDREN;
65
66 -- Procedures for inserting data for lines that have been added.
67 PROCEDURE ADD_LINES;
68 PROCEDURE ADD_PRICE_FACTORS;
69 PROCEDURE ADD_PRICE_DIFFERENTIALS;
70 PROCEDURE ADD_ATTRIBUTES;
71 PROCEDURE ADD_ATTRIBUTE_SCORES;
72 PROCEDURE ADD_NEW_LINE_WITH_CHILDREN;
73
74
75 -- Procedures for updating/inserting data for lines that have been updated.
76 PROCEDURE UPDATE_LINES;
77 PROCEDURE UPDATE_PRICE_DIFFERNTIALS;
78 PROCEDURE UPDATE_PRICE_FACTORS;
79 PROCEDURE UPDATE_LINE_ATTRIBUTES;
80 PROCEDURE UPDATE_LINES_WITH_CHILDREN;
81 /** =============End declaration of private functions and procedures =========*/
82
83
84
85 /*======================================================================
86 PROCEDURE: DEFAULT_PREV_ROUND_AMEND_LINES PUBLIC
87 PARAMETERS:
88 IN : p_auction_header_id NUMBER auction header id
89 IN : p_batch_id NUMBER batch id for which the defaulting will be done.
90
91 COMMENT : This procedure will default various field in pon_item_prices_interface,
92 pon_auc_attributes_interface and pon_auc_price_elements_int
93 for lines being updated based on various conditions.
94 ======================================================================*/
95 procedure DEFAULT_PREV_ROUND_AMEND_LINES(
96 p_auction_header_id IN NUMBER,
97 p_batch_id IN NUMBER) IS
98
99 l_prev_max_line_number number;
100 l_contract_type pon_auction_headers_all.contract_type%type;
101 l_is_blanket_agreement VARCHAR2(1);
102 l_is_amendment VARCHAR2(1);
103
104 l_module CONSTANT VARCHAR2(30) := 'DEFAULT_PREV_ROUND_AMEND_LINES';
105
106 begin
107 --{
108 ----IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
109 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
110 print_debug_log(l_module,'DEFAULT_PREV_ROUND_AMEND_LINES START p_auction_header_id = '||p_auction_header_id||
111 ' p_batch_id = '||p_batch_id);
112 END IF;
113
114 select
115 max_line_number,
116 contract_type,
117 decode(nvl(amendment_number,0),0,'N','Y')
118 into
119 l_prev_max_line_number,
120 l_contract_type,
121 l_is_amendment
122 from
123 pon_auction_headers_all
124 where auction_header_id = p_auction_header_id;
125
126 IF (l_contract_type = 'BLANKET' or l_contract_type = 'CONTRACT') THEN
127 l_is_blanket_agreement := 'Y' ;
128 ELSE
129 l_is_blanket_agreement := 'N' ;
130 END IF;
131
132 -- Update all lines that have line number less than or
133 -- equal to the max line number and have action as update.
134 update pon_item_prices_interface p1
135 set p1.price_and_quantity_apply = 'Y'
136 where
137 p1.batch_id = p_batch_id
138 and p1.action = g_update_action
139 and exists
140 (select 'x'
141 from
142 pon_auction_item_prices_all prev_round_item
143 where
144 prev_round_item.line_number <= l_prev_max_line_number
145 and prev_round_item.auction_header_id = p_auction_header_id
146 and nvl(prev_round_item.quantity_disabled_flag,'N') = 'Y'
147 and nvl(prev_round_item.price_disabled_flag,'N') = 'Y');
148
149
150 -- For blanket lines or items with description null
151 -- the description will be set from the Item as it is
152 -- stored in the database.
153 -- TBD CONSIDER THIS FOR UPDATE STATEMENT.
154 -- TBD ANY VALIDATIONS
155 update pon_item_prices_interface p1
156 set item_description =
157 (select item_description
158 from pon_auction_item_prices_all pal
159 where p1.batch_id = p_batch_id
160 and p1.action = g_update_action
161 and p1.auction_header_id = pal.auction_header_id
162 and p1.auction_line_number = pal.line_number)
163 where
164 p1.batch_id = p_batch_id
165 and p1.action = g_update_action
166 and exists
167 (select 'x'
168 from pon_auction_item_prices_all pal1
169 where
170 p1.auction_header_id = pal1.auction_header_id
171 and p1.auction_line_number = pal1.line_number
172 and (pal1.line_origination_code ='BLANKET' or p1.item_id is not null));
173
174
175 -- Update the line type and the line number of existing
176 -- lines if they are from blanket or requisition.
177 update pon_item_prices_interface p1
178 set line_type = (select tl.line_type
179 from po_line_types_tl tl,
180 pon_auction_item_prices_all pal
181 where p1.batch_id = p_batch_id
182 and p1.action = g_update_action
183 and pal.auction_header_id = p1.auction_header_id
184 and p1.auction_line_number = pal.line_number
185 and pal.line_type_id = tl.line_type_id),
186 item_number = (select pal.item_number
187 from
188 pon_auction_item_prices_all pal
189 where p1.batch_id = p_batch_id
190 and p1.action = g_update_action
191 and p1.auction_header_id = pal.auction_header_id
192 and p1.auction_line_number = pal.line_number)
193 where
194 p1.batch_id = p_batch_id
195 and p1.action = g_update_action
196 and exists
197 (select 'x'
198 from pon_auction_item_prices_all pal1
199 where
200 p1.auction_header_id = pal1.auction_header_id
201 and p1.auction_line_number = pal1.line_number
202 and pal1.line_origination_code in ('BLANKET','REQUISITION'));
203
204 /*
205 -- Update the Item revision and Unit Of Measure if the
206 -- line is coming from a requisition from
207 update pon_item_prices_interface p1
208 set (item_revision,unit_of_measure) =
209 (select pal.item_revision,pal.unit_of_measure
210 from
211 pon_auction_item_prices_all pal
212 where p1.batch_id = p_batch_id
213 and p1.action=g_update_action
214 and p1.auction_header_id = pal.auction_header_id
215 and p1.auction_line_number = pal.line_number)
216 where
217 p1.batch_id = p_batch_id
218 and p1.action = g_update_action
219 and exists
220 (select 'x'
221 from pon_auction_item_prices_all pal1
222 where
223 p1.auction_header_id = pal1.auction_header_id
224 and p1.auction_line_number = pal1.line_number
225 and pal1.line_origination_code = 'REQUISITION');
226
227 -- If the line order type look up code = 'AMOUNT' and the
228 -- line is from a backing requisition from the then update the quantity
229 update pon_item_prices_interface p1
230 set quantity = (select pal.quantity
231 from
232 pon_auction_item_prices_all pal
233 where p1.batch_id = p_batch_id
234 and p1.action = g_update_action
235 and pal.auction_header_id = p1.auction_header_id
236 and pal.line_number = p1.auction_line_number)
237 where
238 p1.batch_id = p_batch_id
239 and p1.action = g_update_action
240 and exists
241 (select 'x'
242 from pon_auction_item_prices_all pal1
243 where
244 p1.auction_header_id = pal1.auction_header_id
245 and p1.auction_line_number = pal1.line_number
246 and pal1.order_type_lookup_code = 'AMOUNT'
247 and pal1.line_origination_code ='REQUISITION');
248
249 TBD verify Combine above 2 statements as shown below */
250
251
252 -- Update the Item revision and Unit Of Measure if the
253 -- line is coming from a requisition from
254 -- If the line order type look up code = 'AMOUNT' and the
255 -- line is from a backing requisition from the then update the quantity
256 update pon_item_prices_interface p1
257 set (item_revision,
258 unit_of_measure,
259 quantity) =
260 (select pal.item_revision,
261 pal.unit_of_measure,
262 decode(pal.order_type_lookup_code,'AMOUNT',pal.quantity,p1.quantity)
263 from
264 pon_auction_item_prices_all pal
265 where p1.batch_id = p_batch_id
266 and p1.action=g_update_action
267 and p1.auction_header_id = pal.auction_header_id
268 and p1.auction_line_number = pal.line_number)
269 where
270 p1.batch_id = p_batch_id
271 and p1.action = g_update_action
272 and exists
273 (select 'x'
274 from pon_auction_item_prices_all pal1
275 where
276 p1.auction_header_id = pal1.auction_header_id
277 and p1.auction_line_number = pal1.line_number
278 and pal1.line_origination_code ='REQUISITION');
279
280 -- TBD : Lets se if we can ignore this while copying. VERY DIRTY
281 -- Update Ship to location id from the if the negotiation
282 -- outcome is BPA or CPA.
283 if l_is_blanket_agreement <> 'Y' then
284
285 update pon_item_prices_interface p1
286 set ship_to_location = (select st.location_code
287 from po_ship_to_loc_org_v st,
288 financials_system_params_all fsp,
289 pon_auction_item_prices_all pal
290 where p1.batch_id = p_batch_id
291 and p1.action = g_update_action
292 and p1.auction_header_id = pal.auction_header_id
293 and p1.auction_line_number = pal.line_number
294 and (st.SET_OF_BOOKS_ID IS NULL
295 OR st.SET_OF_BOOKS_ID = fsp.set_of_books_id)
296 and nvl(fsp.org_id,-9999) = nvl(pal.org_id,-9999))
297 where
298 p1.batch_id = p_batch_id
299 and p1.action = g_update_action
300 and exists
301 (select 'x'
302 from pon_auction_item_prices_all pal1
303 where
304 p1.auction_header_id = pal1.auction_header_id
305 and p1.auction_line_number = pal1.line_number
306 and pal1.line_origination_code in ('BLANKET','REQUISITION'));
307
308 end if;
309
310 -- If this is an amendment then the display target flag and display target
311 -- unit flag will be updated from the last amendment.
312 if l_is_amendment = 'Y' then
313 update pon_item_prices_interface p1
314 set (display_target_flag,unit_display_target_flag) = (select
315 pal.display_target_price_flag,
316 pal.unit_display_target_flag
317 from
318 pon_auction_item_prices_all pal
319 where p1.batch_id = p_batch_id
320 and p1.action = g_update_action
321 and p1.auction_line_number = pal.line_number
322 and pal.auction_header_id = p1.auction_header_id)
323 where
324 p1.batch_id = p_batch_id
325 and p1.action = g_update_action;
326 END if;
327
328 -- Update display_target_flag in pon_auc_attributes_interface for existing attributes
329 -- in lines being updated for attributes that have sequence number -10 and -20
330 update pon_auc_attributes_interface interface_attribute
331 set display_target_flag =
332 (select display_target_flag
333 from
334 pon_auction_attributes auction_attributes
335 where
336 auction_attributes.auction_header_id = interface_attribute.auction_header_id
337 and auction_attributes.line_number = interface_attribute.auction_line_number
338 and auction_attributes.attribute_name = interface_attribute.attribute_name
339 and auction_attributes.sequence_number in (-10,-20))
340 where
341 interface_attribute.batch_id = p_batch_id
342 and exists
343 (select 'x'
344 from
345 pon_item_prices_interface item_interface,
346 pon_auction_attributes auction_attributes
347 where
348 item_interface.batch_id = p_batch_id
349 and item_interface.action = g_update_action
350 and item_interface.auction_line_number = interface_attribute.auction_line_number
351 and auction_attributes.auction_header_id = interface_attribute.auction_header_id
352 and auction_attributes.line_number = interface_attribute.auction_line_number
353 and auction_attributes.attribute_name = interface_attribute.attribute_name
354 and auction_attributes.sequence_number in (-10,-20));
355
356 -- Update display_target_flag in pon_auc_price_elements_int for existing price elements
357 -- for lines that were present in the previous amendment.
358 if l_is_amendment = 'Y' then
359
360 update pon_auc_price_elements_int pe_int
361 set pe_int.DISPLAY_TARGET_FLAG = (select pe1.display_target_flag
362 from pon_price_elements pe1
363 where
364 pe_int.auction_line_number = pe1.line_number
365 and pe_int.auction_header_id = pe1.auction_header_id
366 and pe1.PRICE_ELEMENT_TYPE_ID = pe_int.PRICE_ELEMENT_TYPE_ID)
367 where pe_int.batch_id = p_batch_id
368 and exists
369 (select
370 'x'
371 from
372 pon_price_elements pe,
373 pon_item_prices_interface paip_int,
374 pon_auction_headers_all pah
375 where
376 paip_int.batch_id = p_batch_id
377 and pe_int.auction_line_number = pe.line_number
378 and pe_int.auction_header_id = pe.auction_header_id
379 and pah.auction_header_id = pe.auction_header_id
380 and paip_int.batch_id = pe_int.batch_id
381 and paip_int.auction_line_number = pe_int.auction_line_number
382 and pe.PRICE_ELEMENT_TYPE_ID = pe_int.PRICE_ELEMENT_TYPE_ID
383 and pah.max_internal_line_num >= paip_int.auction_line_number
384 and paip_int.action = g_update_action);
385
386 END IF;
387 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
388 print_debug_log(l_module,'DEFAULT_PREV_ROUND_AMEND_LINES END p_auction_header_id = '||p_auction_header_id||
389 ' p_batch_id = '||p_batch_id);
390 END IF;
391
392 --}
393 END DEFAULT_PREV_ROUND_AMEND_LINES;
394
395
396 /*======================================================================
397 PROCEDURE: GET_NEXT_PE_SEQUENCE_NUMBER PUBLIC
398 PARAMETERS:
399 IN : p_auction_header NUMBER auction header id
400 IN : p_line_number NUMBER line number
401
402 COMMENT : This function will return the next line Price element sequence number
403 for the new price elements being inserted for existing and new lines.
404 ======================================================================*/
405 FUNCTION GET_NEXT_PE_SEQUENCE_NUMBER(p_auction_header IN NUMBER,
406 p_line_number IN NUMBER)
407 RETURN NUMBER IS
408
409 l_module CONSTANT VARCHAR2(27) := 'GET_NEXT_PE_SEQUENCE_NUMBER';
410 l_next_sequence_number number;
411
412 BEGIN
413 --{
414 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
415 print_debug_log(l_module,'GET_NEXT_PE_SEQUENCE_NUMBER START');
416 END IF;
417
418 l_next_sequence_number := g_price_element_seq_number + 10;
419 /*
420 --If the g_price_element_line_number = p_line_number then simple increment
421 --g_price_element_seq_number by 10 (g_price_element_seq_increment) else
422 --get the max price element sequence_number for the line and increment it by
423 --10
424
425 if p_line_number <> nvl(g_price_element_line_number,-1) then
426 --{
427 select nvl(max(sequence_number),0) + g_price_element_seq_increment
428 into
429 l_next_sequence_number
430 from
431 pon_price_elements
432 where
433 auction_header_id = p_auction_header
434 and line_number = p_line_number;
435
436 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
437 print_debug_log(l_module,'GET_NEXT_PE_SEQUENCE_NUMBER LINE Number is not same '||
438 ' p_line_number = '||p_line_number||
439 ' g_price_element_line_number = '||g_price_element_line_number);
440 END IF;
441
442 g_price_element_line_number := p_line_number;
443
444 --}
445 else
446 --{
447 l_next_sequence_number := g_price_element_seq_number + g_price_element_seq_increment;
448 --}
449 end if;
450 */
451 g_price_element_seq_number := l_next_sequence_number;
452
453 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
454 print_debug_log(l_module,'GET_NEXT_PE_SEQUENCE_NUMBER END l_next_sequence_number = '||l_next_sequence_number);
455 END IF;
456
457 return l_next_sequence_number;
458 --}
459 END GET_NEXT_PE_SEQUENCE_NUMBER;
460
461
462
463 /*======================================================================
464 PROCEDURE: INITIALIZE_LINE_ATTR_GROUP PRIVATE
465 PARAMETERS:
466 IN : p_party_id NUMBER party id for which the defualt attribute
467 group is to be set.
468
469 COMMENT : This function will set the default line attribute group
470 in the global variable g_default_attribute_group for the
471 party id passed as a parameter. The global variable
472 g_default_attribute_group will be set to 'GENERAL' if the
473 party prefrence LINE_ATTR_DEFAULT_GROUP does not exist.
474 ======================================================================*/
475 PROCEDURE INITIALIZE_LINE_ATTR_GROUP(p_party_id IN NUMBER) is
476
477 l_module CONSTANT VARCHAR2(26) := 'INITIALIZE_LINE_ATTR_GROUP';
478 l_default_pary_group VARCHAR2(30);
479 l_pref_value VARCHAR2(30);
480 l_pref_meaning VARCHAR2(80);
481 l_status VARCHAR2(1);
482 l_exception_msg VARCHAR2(100);
483
484 BEGIN
485 --{
486 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
487 print_debug_log(l_module,'INITIALIZE_LINE_ATTR_GROUP START p_party_id = '||p_party_id);
488 END IF;
489
490 pon_profile_util_pkg.retrieve_party_pref_cover(
491 p_party_id => p_party_id,
492 p_app_short_name => 'PON',
493 p_pref_name => 'LINE_ATTR_DEFAULT_GROUP',
494 x_pref_value => l_default_pary_group,
495 x_pref_meaning => l_pref_meaning,
496 x_status => l_status,
497 x_exception_msg => l_exception_msg
498 );
499
500 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
501 print_debug_log(l_module,'INITIALIZE_LINE_ATTR_GROUP END p_party_id = '||p_party_id||
502 'l_default_pary_group = '||l_default_pary_group||
503 'l_pref_meaning = '||l_pref_meaning||
504 'l_status = '||l_status||
505 'l_exception_msg = '||l_exception_msg);
506 END IF;
507
508 IF (l_status = 'S' and l_default_pary_group is not null and l_default_pary_group <>'') THEN
509 g_default_attribute_group := l_default_pary_group;
510 g_default_section_name := l_default_pary_group;
511 ELSE
512 g_default_attribute_group := g_default_appl_attribute_group;
513 g_default_section_name := g_default_appl_section_name;
514 END IF;
515
516 SELECT nvl(max(sequence_number),10)
517 INTO g_price_element_seq_number
518 FROM pon_price_elements
519 WHERE auction_header_id = g_auction_header_id;
520
521 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
522 print_debug_log(l_module,'INITIALIZE_LINE_ATTR_GROUP END p_party_id = '||p_party_id||
523 'g_default_attribute_group = '|| g_default_attribute_group ||
524 'g_price_element_seq_number = '|| g_price_element_seq_number);
525 END IF;
526
527 --}
528 END INITIALIZE_LINE_ATTR_GROUP;
529
530
531 /*======================================================================
532 FUNCTION: GET_SEQUENCE_NUMBER PRIVATE
533 PARAMETERS:
534 IN : p_batch_id NUMBER batch id
535 IN : p_interface_line_id NUMBER interface_line_id for the line
536 whose attribute is being copied.
537 IN : p_template_sequence_number NUMBER Sequence number of the attribute
538 in the template
539
540 COMMENT : This procedure determines the sequence number of the attributes
541 being copied. We need this special handling for templates.
542 ======================================================================*/
543 FUNCTION GET_SEQUENCE_NUMBER(p_batch_id IN NUMBER,
544 p_interface_line_id IN NUMBER,
545 p_template_sequence_number IN NUMBER) RETURN NUMBER
546 IS
547
548 l_sequence_number NUMBER;
549
550 BEGIN
551 --{
552 if g_cur_internal_line_num = p_interface_line_id then
553 --{
554 l_sequence_number := g_max_attribute_seq_num + p_template_sequence_number;
555 --}
556 else
557 --{
558 g_cur_internal_line_num := p_interface_line_id;
559
560 select nvl(max(SEQUENCE_NUMBER), 0) + p_template_sequence_number
561 into l_sequence_number
562 from pon_auc_attributes_interface
563 where batch_id = p_batch_id
564 and interface_line_id = p_interface_line_id
565 and response_type_name <> 'PON_FROM_TEMPLATE';
566 --}
567 end if;
568
569 g_max_attribute_seq_num := l_sequence_number;
570
571 RETURN l_sequence_number;
572 --}
573 END get_sequence_number;
574
575
576 /*======================================================================
577 FUNCTION: GET_ATTR_GROUP_SEQ_NUMBER PRIVATE
578 PARAMETERS:
579 IN : p_batch_id NUMBER batch id
580 IN : p_interface_line_id NUMBER interface_line_id for the line
581 whose attribute is being copied.
582 IN : p_attr_group NUMBER attribute group being copied.
583 IN : p_template_sequence_number NUMBER Sequence number of the attribute
584 in the template
585
586 COMMENT : Determines the grooup sequence number of the attributes being
587 copied from templates.
588 ======================================================================*/
589 FUNCTION get_attr_group_seq_number(p_batch_id IN NUMBER,
590 p_interface_line_id IN NUMBER,
591 p_attr_group IN VARCHAR2,
592 p_template_group_seq_number IN NUMBER) RETURN NUMBER
593
594 IS
595
596 l_attr_group_seq_number NUMBER;
597
598 BEGIN
599
600 select ATTR_GROUP_SEQ_NUMBER
601 into l_attr_group_seq_number
602 from pon_auc_attributes_interface
603 where batch_id = p_batch_id
604 and interface_line_id = p_interface_line_id
605 and group_code = p_attr_group
606 and response_type_name <> 'PON_FROM_TEMPLATE'
607 and rownum = 1;
608
609 RETURN l_attr_group_seq_number;
610
611 EXCEPTION
612
613 WHEN NO_DATA_FOUND THEN
614 -- If the attribute group code does not exist in the attributes interface table
615 -- then the attribute group sequence number will be the max attribute group sequence
616 -- number for the attributes in the line + the attribute group sequence number of
617 -- the attribute in the template
618 select nvl(max(ATTR_GROUP_SEQ_NUMBER), 0) + p_template_group_seq_number
619 into l_attr_group_seq_number
620 from pon_auc_attributes_interface
621 where batch_id = p_batch_id
622 and interface_line_id = p_interface_line_id
623 and response_type_name <> 'PON_FROM_TEMPLATE';
624
625 RETURN l_attr_group_seq_number;
626
627 END get_attr_group_seq_number;
628
629
630 /*======================================================================
631 FUNCTION: GET_ATTR_DISP_SEQ_NUMBER PRIVATE
632 PARAMETERS:
633 IN : p_batch_id NUMBER batch id
634 IN : p_interface_line_id NUMBER interface_line_id for the line
635 whose attribute is being copied.
636 IN : p_attr_group NUMBER attribute group being copied.
637 IN : p_template_sequence_number NUMBER Sequence number of the attribute
638 in the template
639
640 COMMENT : Determines the attribute display sequence for the attribute
641 based on the line number and the position of the attribute
642 within the template.
643 * If attributes do not exist for the line then the display
644 sequence number of the attribute is same as the display
645 sequence number of the attribute within the template.
646 * If attributes exist for the line then the display sequence
647 number of the attribute is max attribute sequence number +
648 the display sequence number of the attribute within the
649 template
650 ======================================================================*/
651 FUNCTION get_attr_disp_seq_number(p_batch_id IN NUMBER,
652 p_interface_line_id IN NUMBER,
653 p_attr_group IN VARCHAR2,
654 p_template_disp_seq_number IN NUMBER) RETURN NUMBER
655
656 IS
657
658 l_attr_disp_seq_number NUMBER;
659
660 BEGIN
661
662 select nvl(max(ATTR_DISP_SEQ_NUMBER), 0) + p_template_disp_seq_number
663 into l_attr_disp_seq_number
664 from pon_auc_attributes_interface
665 where batch_id = p_batch_id and
666 interface_line_id = p_interface_line_id
667 and response_type_name <> 'PON_FROM_TEMPLATE'
668 and group_code = p_attr_group;
669
670 RETURN l_attr_disp_seq_number;
671
672 END get_attr_disp_seq_number;
673
674
675
676
677 /*======================================================================
678 PROCEDURE: DELETE_LINES_WITH_CHILDREN PRIVATE
679
680 PARAMETERS: NONE
681
682 COMMENT : The procedure delete_lines_with_children will delete those lines
683 that were marked as deleted in the spreadsheet. The records will
684 be deleted from the tables in the following order. For LOTS and
685 GROUP marked as deleted the corresponding children are also deleted.
686 PON_ATTRIBUTE_SCORES
687 PON_AUCTION_ATTRIBUTES
688 PON_PF_SUPPLIER_VALUES
689 PON_PRICE_ELEMENTS
690 PON_PRICE_DIFFERENTIALS
691 PON_AUCTION_SHIPMENTS_ALL
692 PON_PARTY_LINE_EXCLUSIONS
693 PON_AUC_PAYMENTS_SHIPMENTS
694 Attachments
695 Update backing requisitions for lines being deleted.
696 PON_AUCTION_ITEM_PRICES_ALL
697 ======================================================================*/
698 PROCEDURE DELETE_LINES_WITH_CHILDREN is
699
700 l_module CONSTANT VARCHAR2(26) := 'DELETE_LINES_WITH_CHILDREN';
701 l_error_code VARCHAR2(100);
702
703 CURSOR delete_line_cursor IS
704 SELECT
705 auction_item.line_number,
706 auction_item.line_origination_code,
707 auction_item.org_id
708 FROM pon_item_prices_interface interface_line,
709 pon_auction_item_prices_all auction_item
710 WHERE interface_line.BATCH_ID = g_batch_id
711 and interface_line.action = g_delete_action
712 and interface_line.auction_header_id = auction_item.auction_header_id
713 and interface_line.auction_line_number = auction_item.line_number
714 and (auction_item.line_number = interface_line.auction_line_number or
715 (auction_item.parent_line_number = interface_line.auction_line_number
716 and auction_item.group_type in ('LOT_LINE','GROUP_LINE')));
717
718 --Added for deleting payments attachments
719 CURSOR delete_pymt_attachments_cursor IS
720 SELECT paps.payment_id,
721 paps.auction_header_id,
722 paps.line_number
723 FROM pon_item_prices_interface p1,
724 pon_auction_item_prices paip,
725 pon_auc_payments_shipments paps,
726 FND_ATTACHED_DOCUMENTS fnd
727 WHERE p1.batch_id = g_batch_id
728 AND p1.action = g_delete_action
729 AND paip.auction_header_id = p1.auction_header_id
730 AND ((paip.line_number = p1.auction_line_number
731 AND paip.group_type in ('LINE','LOT')
732 AND paps.line_number = paip.line_number)
733 OR
734 (paip.parent_line_number = p1.auction_line_number
735 AND paip.group_type = 'GROUP_LINE'
736 AND paps.line_number = paip.line_number))
737 AND paps.auction_header_id = paip.auction_header_id
738 AND fnd.pk1_value = paps.auction_header_id
739 AND fnd.pk2_value = paps.line_number
740 AND fnd.pk3_value = paps.payment_id
741 AND fnd.entity_name = 'PON_AUC_PAYMENTS_SHIPMENTS'
742 AND paip.group_type <> 'LOT_LINE';
743
744
745 BEGIN
746 --{
747 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
748 print_debug_log(l_module,'DELETE_LINES_WITH_CHILDREN START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
749 END IF;
750
751 --delete from PON_AUCTION_ATTRIBUTES
752 if(g_line_attribute_enabled = 'Y') then
753
754 --delete from PON_ATTRIBUTE_SCORES
755 if(g_attribute_score_enabled_flag = 'Y') then
756 delete from pon_attribute_scores auction_scores
757 where
758 auction_header_id = g_auction_header_id
759 and exists
760 (select
761 'x'
762 from
763 pon_item_prices_interface p1,
764 pon_auction_item_prices paip
765 where
766 p1.batch_id = g_batch_id
767 and p1.action = g_delete_action
768 and paip.auction_header_id = p1.auction_header_id
769 and (paip.line_number = p1.auction_line_number or
770 (paip.parent_line_number = p1.auction_line_number
771 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
772 and auction_scores.line_number = p1.auction_line_number);
773 END IF;
774
775
776 --delete from PON_AUCTION_ATTRIBUTES
777 delete from pon_auction_attributes auction_attributes
778 where
779 auction_header_id = g_auction_header_id
780 and exists
781 (select
782 'x'
783 from
784 pon_item_prices_interface p1,
785 pon_auction_item_prices paip
786 where
787 p1.batch_id = g_batch_id
788 and p1.action = g_delete_action
789 and paip.auction_header_id = p1.auction_header_id
790 and (paip.line_number = p1.auction_line_number or
791 (paip.parent_line_number = p1.auction_line_number
792 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
793 and auction_attributes.line_number = p1.auction_line_number);
794 END IF;
795
796 --delete from PON_PF_SUPPLIER_VALUES
797 if(g_price_element_enabled_flag = 'Y') then
798 delete from pon_pf_supplier_values auction_pf_values
799 where
800 auction_pf_values.auction_header_id = g_auction_header_id
801 and exists
802 (select
803 'x'
804 from
805 pon_item_prices_interface p1,
806 pon_auction_item_prices paip
807 where
808 p1.batch_id = g_batch_id
809 and p1.action = g_delete_action
810 and paip.auction_header_id = p1.auction_header_id
811 and (paip.line_number = p1.auction_line_number or
812 (paip.parent_line_number = p1.auction_line_number
813 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
814 and auction_pf_values.line_number = p1.auction_line_number);
815
816
817 --delete from PON_PRICE_ELEMENTS
818 delete from pon_price_elements price_elements
819 where
820 auction_header_id = g_auction_header_id
821 and exists
822 (select
823 'x'
824 from
825 pon_item_prices_interface p1,
826 pon_auction_item_prices paip
827 where
828 p1.batch_id = g_batch_id
829 and p1.action = g_delete_action
830 and paip.auction_header_id = p1.auction_header_id
831 and (paip.line_number = p1.auction_line_number or
832 (paip.parent_line_number = p1.auction_line_number
833 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
834 and price_elements.line_number = p1.auction_line_number);
835 END IF;
836
837
838 --delete from PON_PRICE_DIFFERENTIALS
839 if(g_price_differentials_flag = 'Y') then
840 delete from pon_price_differentials price_differentials
841 where
842 auction_header_id = g_auction_header_id
843 and exists
844 (select
845 'x'
846 from
847 pon_item_prices_interface p1,
848 pon_auction_item_prices paip
849 where
850 p1.batch_id = g_batch_id
851 and p1.action = g_delete_action
852 and paip.auction_header_id = p1.auction_header_id
853 and (paip.line_number = p1.auction_line_number or
854 (paip.parent_line_number = p1.auction_line_number
855 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
856 and price_differentials.line_number = p1.auction_line_number);
857 END IF;
858
859 --delete from PON_AUCTION_SHIPMENTS_ALL
860 delete from pon_auction_shipments_all auction_shipments
861 where
862 auction_header_id = g_auction_header_id
863 and exists
864 (select
865 'x'
866 from
867 pon_item_prices_interface p1,
868 pon_auction_item_prices paip
869 where
870 p1.batch_id = g_batch_id
871 and p1.action = g_delete_action
872 and paip.auction_header_id = p1.auction_header_id
873 and (paip.line_number = p1.auction_line_number or
874 (paip.parent_line_number = p1.auction_line_number
875 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
876 and auction_shipments.line_number = p1.auction_line_number);
877
878 --delete from PON_PARTY_LINE_EXCLUSIONS
879 delete from pon_party_line_exclusions supplier_line_exclusions
880 where
881 auction_header_id = g_auction_header_id
882 and exists
883 (select
884 'x'
885 from
886 pon_item_prices_interface p1,
887 pon_auction_item_prices paip
888 where
889 p1.batch_id = g_batch_id
890 and p1.action = g_delete_action
891 and paip.auction_header_id = p1.auction_header_id
892 and (paip.line_number = p1.auction_line_number or
893 (paip.parent_line_number = p1.auction_line_number
894 and paip.group_type in ('LOT_LINE','GROUP_LINE')))
895 and supplier_line_exclusions.line_number = p1.auction_line_number);
896
897
898 -- To delete attachments of pon_auc_payments_shipments
899 FOR delete_pymt_attachments_record IN delete_pymt_attachments_cursor LOOP
900 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
901 (x_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
902 x_pk1_value => delete_pymt_attachments_record.auction_header_id,
903 x_pk2_value => delete_pymt_attachments_record.line_number,
904 x_pk3_value => delete_pymt_attachments_record.payment_id);
905 END LOOP;
906
907 --delete from PON_AUC_PAYMENTS_SHIPMENTS
908 delete from pon_auc_payments_shipments auc_payments
909 where
910 auction_header_id = g_auction_header_id
911 and line_number IN
912 (select
913 paip.line_number
914 from
915 pon_item_prices_interface p1,
916 pon_auction_item_prices paip
917 where
918 p1.batch_id = g_batch_id
919 and p1.action = g_delete_action
920 and paip.auction_header_id = p1.auction_header_id
921 and ((paip.line_number = p1.auction_line_number) or
922 (paip.parent_line_number = p1.auction_line_number
923 and paip.group_type = 'GROUP_LINE')));
924
925
926 --Delete all attachments and update backing requisitions if they exist.
927 FOR delete_line_record IN delete_line_cursor LOOP
928
929 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
930 (x_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
931 x_pk1_value => g_auction_header_id,
932 x_pk2_value => delete_line_record.line_number);
933
934 if delete_line_record.line_origination_code is not null then
935 PON_AUCTION_PKG.DELETE_NEGOTIATION_LINE_REF(x_negotiation_id => g_auction_header_id,
936 x_negotiation_line_num => delete_line_record.line_number,
937 x_org_id => delete_line_record.org_id,
938 x_error_code => l_error_code);
939
940 end if;
941
942 end loop;
943
944
945 delete from pon_auction_item_prices_all item_prices
946 where
947 auction_header_id = g_auction_header_id
948 and exists
949 (select
950 'x'
951 from
952 pon_item_prices_interface p1
953 where
954 p1.batch_id = g_batch_id
955 and p1.action = g_delete_action
956 and item_prices.parent_line_number = p1.auction_line_number
957 and item_prices.group_type in ('LOT_LINE','GROUP_LINE'));
958
959
960 delete from pon_auction_item_prices_all item_prices
961 where
962 auction_header_id = g_auction_header_id
963 and exists
964 (select
965 'x'
966 from
967 pon_item_prices_interface p1
968 where
969 p1.batch_id = g_batch_id
970 and p1.action = g_delete_action
971 and item_prices.line_number = p1.auction_line_number);
972
973
974 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
975 print_debug_log(l_module,'DELETE_LINES_WITH_CHILDREN END g_batch_id = '|| g_batch_id ||' g_auction_header_id '||g_auction_header_id);
976 END IF;
977
978 --}
979 END DELETE_LINES_WITH_CHILDREN;
980
981
982 /*======================================================================
983 PROCEDURE: ADD_LINES PRIVATE
984
985 PARAMETERS: NONE
986
987 COMMENT : This procedure will add the new lines from the pon_item_prices_interface
988 interface table to the pon_auction_item_prices_all transaction table.
989 The following sql will be used for the same. This procedure will
990 contain the logic of copying the lines as is present in the
991 copyItemData method of NegItemSpreadsheetAMImpl.
992 ======================================================================*/
993 PROCEDURE ADD_LINES is
994
995 l_module CONSTANT VARCHAR2(9) := 'ADD_LINES';
996
997 l_price_break_type pon_auction_item_prices_all.price_break_type%type;
998 l_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
999
1000 BEGIN
1001 --{
1002 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1003 print_debug_log(l_module,'ADD_LINES START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1004 END IF;
1005
1006 -- price break line setting
1007 PON_AUCTION_PKG.get_default_pb_settings (g_auction_header_id,
1008 l_price_break_type,
1009 l_price_break_neg_flag);
1010
1011
1012
1013 insert into pon_auction_item_prices_all
1014 fields
1015 (AUCTION_HEADER_ID,
1016 LINE_NUMBER,
1017 SUB_LINE_SEQUENCE_NUMBER,
1018 DOCUMENT_DISP_LINE_NUMBER,
1019 DISP_LINE_NUMBER,
1020 PARENT_LINE_NUMBER,
1021 GROUP_TYPE,
1022 ITEM_DESCRIPTION,
1023 CATEGORY_ID,
1024 CATEGORY_NAME,
1025 IP_CATEGORY_ID,
1026 QUANTITY,
1027 UOM_CODE,
1028 UNIT_OF_MEASURE,
1029 NEED_BY_START_DATE,
1030 NEED_BY_DATE,
1031 TARGET_PRICE,
1032 BID_START_PRICE,
1033 NOTE_TO_BIDDERS,
1034 SHIP_TO_LOCATION_ID,
1035 CURRENT_PRICE,
1036 RESERVE_PRICE,
1037 DISPLAY_TARGET_PRICE_FLAG,
1038 PO_MIN_REL_AMOUNT,
1039 LINE_TYPE_ID,
1040 ORDER_TYPE_LOOKUP_CODE,
1041 ITEM_ID,
1042 ITEM_NUMBER,
1043 ITEM_REVISION,
1044 JOB_ID,
1045 ADDITIONAL_JOB_DETAILS,
1046 PO_AGREED_AMOUNT,
1047 UNIT_TARGET_PRICE,
1048 UNIT_DISPLAY_TARGET_FLAG,
1049 DIFFERENTIAL_RESPONSE_TYPE,
1050 PURCHASE_BASIS,
1051 PRICE_DISABLED_FLAG,
1052 QUANTITY_DISABLED_FLAG,
1053 LAST_AMENDMENT_UPDATE,
1054 MODIFIED_DATE,
1055 ORG_ID,
1056 PRICE_BREAK_TYPE,
1057 PRICE_BREAK_NEG_FLAG,
1058 PRICE_DIFF_SHIPMENT_NUMBER,
1059 --R12 - Complex work
1060 ADVANCE_AMOUNT,
1061 RECOUPMENT_RATE_PERCENT,
1062 PROGRESS_PYMT_RATE_PERCENT,
1063 RETAINAGE_RATE_PERCENT,
1064 MAX_RETAINAGE_AMOUNT,
1065 PROJECT_ID,
1066 PROJECT_TASK_ID,
1067 PROJECT_AWARD_ID,
1068 PROJECT_EXPENDITURE_TYPE,
1069 PROJECT_EXP_ORGANIZATION_ID,
1070 PROJECT_EXPENDITURE_ITEM_DATE,
1071 WORK_APPROVER_USER_ID,
1072 CREATION_DATE,
1073 CREATED_BY,
1074 LAST_UPDATE_DATE,
1075 LAST_UPDATED_BY,
1076 LAST_UPDATE_LOGIN)
1077 select
1078 p1.AUCTION_HEADER_ID,
1079 p1.AUCTION_LINE_NUMBER,
1080 p1.SUB_LINE_SEQUENCE_NUMBER,
1081 p1.DOCUMENT_DISP_LINE_NUMBER,
1082 p1.DISP_LINE_NUMBER,
1083 p1.PARENT_LINE_NUMBER,
1084 p1.GROUP_TYPE,
1085 p1.ITEM_DESCRIPTION,
1086 p1.CATEGORY_ID,
1087 p1.CATEGORY_NAME,
1088 p1.IP_CATEGORY_ID,
1089 p1.QUANTITY,
1090 decode(nvl(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','',UOM_CODE),
1091 decode(nvl(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','',UNIT_OF_MEASURE),
1092 p1.NEED_BY_START_DATE,
1093 p1.NEED_BY_DATE,
1094 p1.TARGET_PRICE,
1095 p1.BID_START_PRICE,
1096 p1.NOTE_TO_BIDDERS,
1097 p1.SHIP_TO_LOCATION_ID,
1098 p1.CURRENT_PRICE,
1099 p1.RESERVE_PRICE,
1100 p1.DISPLAY_TARGET_PRICE_FLAG,
1101 p1.PO_MIN_REL_AMOUNT,
1102 p1.LINE_TYPE_ID,
1103 p1.ORDER_TYPE_LOOKUP_CODE,
1104 p1.ITEM_ID,
1105 p1.ITEM_NUMBER,
1106 p1.ITEM_REVISION,
1107 p1.JOB_ID,
1108 p1.ADDITIONAL_JOB_DETAILS,
1109 p1.PO_AGREED_AMOUNT,
1110 p1.UNIT_TARGET_PRICE,
1111 p1.UNIT_DISPLAY_TARGET_FLAG,
1112 decode(p1.DIFFERENTIAL_RESPONSE_TYPE,
1113 PON_AUCTION_PKG.getMessage('PON_AUCTS_REQUIRED'),'REQUIRED',
1114 PON_AUCTION_PKG.getMessage('PON_AUCTS_OPTIONAL'),'OPTIONAL',
1115 PON_AUCTION_PKG.getMessage('PON_AUCTS_DISPLAY_ONLY'),'DISPLAY_ONLY',
1116 null),
1117 p1.PURCHASE_BASIS,
1118 decode(NVL(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','Y','N'),
1119 decode(NVL(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','Y','N'),
1120 pah.AMENDMENT_NUMBER,
1121 sysdate,
1122 pah.ORG_ID,
1123 decode(p1.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', 'NONE', 'FIXED PRICE', 'NONE', l_price_break_type),
1124 l_price_break_neg_flag,
1125 -1,
1126 --R12 - Complex work
1127 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.ADVANCE_AMOUNT),
1128 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.RECOUPMENT_RATE_PERCENT),
1129 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROGRESS_PYMT_RATE_PERCENT),
1130 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.RETAINAGE_RATE_PERCENT),
1131 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.MAX_RETAINAGE_AMOUNT),
1132 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_ID),
1133 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_TASK_ID),
1134 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_AWARD_ID),
1135 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXPENDITURE_TYPE),
1136 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXP_ORGANIZATION_ID),
1137 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXPENDITURE_ITEM_DATE),
1138 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.WORK_APPROVER_USER_ID),
1139 sysdate,
1140 g_user_id,
1141 sysdate,
1142 g_user_id,
1143 fnd_global.login_id
1144 from
1145 pon_item_prices_interface p1,
1146 pon_auction_headers_all pah
1147 where
1148 p1.batch_id = g_batch_id
1149 and p1.auction_header_id = pah.auction_header_id
1150 and nvl(p1.action,'+') = g_add_action;
1151
1152 /*
1153 The following column will be updated for all the records modified
1154 HAS_ATTRIBUTES_FLAG,
1155 HAS_SHIPMENTS_FLAG
1156 HAS_PRICE_ELEMENTS_FLAG
1157 HAS_BUYER_PFS_FLAG
1158 HAS_PRICE_DIFFERENTIALS_FLAG
1159 HAS_QUANTITY_TIERS
1160
1161 The procedure PON_NEGOTIATION_PUBLISH_PVT.SET_ITEM_HAS_CHILDREN_FLAGS
1162 will be used for this. This would be called after lined have been updated
1163 added or deleted.
1164 */
1165
1166
1167 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1168 print_debug_log(l_module,'ADD_LINES END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1169 END IF;
1170
1171 --}
1172 END ADD_LINES;
1173
1174
1175 /*======================================================================
1176 PROCEDURE: ADD_PRICE_FACTORS PRIVATE
1177
1178 PARAMETERS: NONE
1179
1180 COMMENT : This procedure will add the supplier price factors in
1181 PON_AUC_PRICE_ELEMENTS_INT corresponding to the new lines
1182 from the PON_ITEM_PRICES_INTERFACE interface table to the
1183 PON_PRICE_ELEMENTS transaction tables. The following sql
1184 will be used for the same. The logic for this is present
1185 in copyPriceElement method in NegItemSpreadsheetAMImpl.
1186 ======================================================================*/
1187 PROCEDURE ADD_PRICE_FACTORS is
1188
1189 l_module CONSTANT VARCHAR2(17) := 'ADD_PRICE_FACTORS';
1190
1191 BEGIN
1192 --{
1193 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1194 print_debug_log(l_module,'ADD_PRICE_FACTORS START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1195 END IF;
1196
1197 insert into PON_PRICE_ELEMENTS
1198 fields
1199 (AUCTION_HEADER_ID,
1200 LINE_NUMBER,
1201 SEQUENCE_NUMBER,
1202 PRICE_ELEMENT_TYPE_ID,
1203 PRICING_BASIS,
1204 VALUE,
1205 DISPLAY_TARGET_FLAG,
1206 PF_TYPE,
1207 DISPLAY_TO_SUPPLIERS_FLAG,
1208 LIST_ID,
1209 CREATION_DATE,
1210 CREATED_BY,
1211 LAST_UPDATE_DATE,
1212 LAST_UPDATED_BY)
1213 select
1214 pe_int.AUCTION_HEADER_ID,
1215 pe_int.AUCTION_LINE_NUMBER,
1216 sequence_number+10,
1217 pe_int.PRICE_ELEMENT_TYPE_ID,
1218 pe_int.pricing_basis,
1219 pe_int.VALUE,
1220 pe_int.DISPLAY_TARGET_FLAG,
1221 pe_int.PF_TYPE,
1222 pe_int.DISPLAY_TO_SUPPLIERS_FLAG,
1223 -1,
1224 sysdate,
1225 g_user_id,
1226 sysdate,
1227 g_user_id
1228 from
1229 pon_auc_price_elements_int pe_int,
1230 pon_item_prices_interface p1
1231 where
1232 pe_int.batch_id = g_batch_id
1233 and p1.batch_id = pe_int.batch_id
1234 and p1.auction_line_number = pe_int.auction_line_number
1235 and nvl(p1.action,g_add_action) = g_add_action
1236 order by sequence_number;
1237
1238 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1239 print_debug_log(l_module,'ADD_PRICE_FACTORS END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1240 END IF;
1241
1242 --}
1243 END ADD_PRICE_FACTORS ;
1244
1245
1246 /*======================================================================
1247 PROCEDURE: ADD_PRICE_DIFFERENTIALS PRIVATE
1248
1249 PARAMETERS: NONE
1250
1251 COMMENT : This procedure will add the price differentials corresponding
1252 to the new lines from the PON_AUC_PRICE_DIFFER_INT interface table
1253 to the PON_PRICE_DIFFERENTIALS transaction tables.
1254 ======================================================================*/
1255 PROCEDURE ADD_PRICE_DIFFERENTIALS is
1256
1257 l_module CONSTANT VARCHAR2(23) := 'ADD_PRICE_DIFFERENTIALS';
1258
1259 BEGIN
1260 --{
1261 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1262 print_debug_log(l_module,'ADD_PRICE_DIFFERENTIALS START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1263 END IF;
1264
1265 insert into PON_PRICE_DIFFERENTIALS
1266 fields
1267 (AUCTION_HEADER_ID,
1268 LINE_NUMBER,
1269 SHIPMENT_NUMBER,
1270 PRICE_DIFFERENTIAL_NUMBER,
1271 PRICE_TYPE,
1272 MULTIPLIER,
1273 CREATION_DATE,
1274 CREATED_BY,
1275 LAST_UPDATE_DATE,
1276 LAST_UPDATED_BY,
1277 LAST_UPDATE_LOGIN)
1278 select
1279 pdf_int.AUCTION_HEADER_ID,
1280 pdf_int.AUCTION_LINE_NUMBER,
1281 -1,
1282 pdf_int.SEQUENCE_NUMBER,
1283 pdf_int.PRICE_TYPE,
1284 pdf_int.MULTIPLIER,
1285 sysdate,
1286 g_user_id,
1287 sysdate,
1288 g_user_id,
1289 fnd_global.login_id
1290 from
1291 pon_auc_price_differ_int pdf_int,
1292 pon_item_prices_interface p1
1293 where
1294 pdf_int.batch_id = g_batch_id
1295 and p1.batch_id = pdf_int.batch_id
1296 and p1.auction_line_number = pdf_int.auction_line_number
1297 and nvl(p1.action,'+') = g_add_action;
1298
1299
1300 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1301 print_debug_log(l_module,'ADD_PRICE_DIFFERENTIALS END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1302 END IF;
1303
1304 --}
1305 END ADD_PRICE_DIFFERENTIALS ;
1306
1307
1308 /*======================================================================
1309 PROCEDURE: ADD_ATTRIBUTES PRIVATE
1310
1311 PARAMETERS: NONE
1312
1313 COMMENT : This procedure will add the attributes corresponding to the new
1314 lines from the PON_AUC_ATTRIBUTES_INTERFACE interface table to the
1315 PON_AUCTION_ATTRIBUTES transaction tables. The following sql will
1316 be used for the same. The logic for this is present in copyAttributes
1317 method in NegItemSpreadsheetAMImpl.
1318 ======================================================================*/
1319 PROCEDURE ADD_ATTRIBUTES is
1320
1321 l_module CONSTANT VARCHAR2(14) := 'ADD_ATTRIBUTES';
1322
1323 BEGIN
1324 --{
1325 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1326 print_debug_log(l_module,'ADD_ATTRIBUTES START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1327 END IF;
1328
1329 insert into pon_auction_attributes
1330 fields
1331 (AUCTION_HEADER_ID,
1332 LINE_NUMBER,
1333 SEQUENCE_NUMBER,
1334 ATTR_GROUP,
1335 MANDATORY_FLAG,
1336 DISPLAY_ONLY_FLAG,
1337 INTERNAL_ATTR_FLAG,
1338 ATTRIBUTE_NAME,
1339 DATATYPE,
1340 DISPLAY_TARGET_FLAG,
1341 VALUE,
1342 SCORING_TYPE,
1343 ATTR_LEVEL,
1344 ATTR_GROUP_SEQ_NUMBER,
1345 ATTR_DISP_SEQ_NUMBER,
1346 ATTRIBUTE_LIST_ID,
1347 WEIGHT,
1348 ATTR_MAX_SCORE,
1349 IP_CATEGORY_ID,
1350 IP_DESCRIPTOR_ID,
1351 CREATION_DATE,
1352 CREATED_BY,
1353 LAST_UPDATE_DATE,
1354 LAST_UPDATED_BY,
1355 SECTION_NAME)
1356 select
1357 paa_int.AUCTION_HEADER_ID,
1358 paa_int.AUCTION_LINE_NUMBER,
1359 paa_int.SEQUENCE_NUMBER,
1360 nvl(paa_int.GROUP_CODE, g_default_attribute_group),
1361 decode(paa_int.RESPONSE_TYPE,'REQUIRED','Y','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'N'),
1362 decode(paa_int.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'Y','OPTIONAL', 'N','INTERNAL', 'N'),
1363 decode(paa_int.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'Y'),
1364 paa_int.ATTRIBUTE_NAME,
1365 paa_int.DATATYPE,
1366 paa_int.DISPLAY_TARGET_FLAG,
1367 paa_int.VALUE,
1368 paa_int.SCORING_TYPE,
1369 'LINE',
1370 decode(nvl(paa_int.response_type_name,''),'PON_FROM_TEMPLATE',
1371 PON_CP_INTRFAC_TO_TRANSACTION.get_attr_group_seq_number(g_batch_id,paa_int.interface_line_id,
1372 nvl(paa_int.GROUP_CODE, g_default_attribute_group),
1373 paa_int.ATTR_GROUP_SEQ_NUMBER),
1374 paa_int.ATTR_GROUP_SEQ_NUMBER),
1375 decode(nvl(paa_int.response_type_name,''),'PON_FROM_TEMPLATE',
1376 PON_CP_INTRFAC_TO_TRANSACTION.get_attr_disp_seq_number(g_batch_id,paa_int.interface_line_id,
1377 nvl(paa_int.GROUP_CODE, g_default_attribute_group),
1378 paa_int.attr_disp_seq_number),
1379 paa_int.ATTR_DISP_SEQ_NUMBER),
1380 -1,
1381 0,
1382 0,
1383 paa_int.IP_CATEGORY_ID,
1384 paa_int.IP_DESCRIPTOR_ID,
1385 sysdate,
1386 g_user_id,
1387 sysdate,
1388 g_user_id,
1389 nvl(paa_int.group_name,g_default_section_name)
1390 from
1391 pon_auc_attributes_interface paa_int,
1392 pon_item_prices_interface p1
1393 where
1394 paa_int.batch_id= g_batch_id
1395 and p1.batch_id = paa_int.batch_id
1396 and p1.auction_line_number = paa_int.auction_line_number
1397 and nvl(p1.action,'+') = g_add_action;
1398
1399 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1400 print_debug_log(l_module,'ADD_ATTRIBUTES END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1401 END IF;
1402
1403 --}
1404 END ADD_ATTRIBUTES ;
1405
1406
1407 /*======================================================================
1408 PROCEDURE: ADD_ATTRIBUTE_SCORES PRIVATE
1409
1410 PARAMETERS: NONE
1411
1412 COMMENT : This procedure will add the attributes scores for the new
1413 attributes added incase the negotiation was created from
1414 a template. The following logic is used
1415
1416 i. Get all the attributes and the corresponding scores related to
1417 the template. The following where condition takes care of this
1418
1419 ii. Get the lines that have the attributes present in the
1420 template. We only require those attributes that have not been copied
1421 as part of the spreadsheet. These attributes can be identified using
1422 the field response_type_name in pon_auc_attributes_interface. The
1423 value of this field for all the attributes added will be 'PON_FROM_TEMPLATE"
1424 ======================================================================*/
1425 PROCEDURE ADD_ATTRIBUTE_SCORES is
1426
1427 l_module CONSTANT VARCHAR2(20) := 'ADD_ATTRIBUTE_SCORES';
1428
1429 BEGIN
1430 --{
1431 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1432 print_debug_log(l_module,'ADD_ATTRIBUTE_SCORES START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1433 END IF;
1434
1435 insert into pon_attribute_scores
1436 fields
1437 (AUCTION_HEADER_ID,
1438 LINE_NUMBER,
1439 ATTRIBUTE_SEQUENCE_NUMBER,
1440 ATTRIBUTE_LIST_ID,
1441 FROM_RANGE,
1442 TO_RANGE,
1443 SCORE,
1444 VALUE,
1445 SEQUENCE_NUMBER,
1446 CREATION_DATE,
1447 CREATED_BY,
1448 LAST_UPDATE_DATE,
1449 LAST_UPDATED_BY
1450 )
1451 SELECT
1452 auction_attributes.auction_header_id,
1453 auction_attributes.line_number,
1454 auction_attributes.sequence_number,
1455 -1,
1456 template_attribute_score.from_range,
1457 template_attribute_score.to_range,
1458 template_attribute_score.score,
1459 template_attribute_score.value,
1460 template_attribute_score.sequence_number,
1461 sysdate,
1462 g_user_id,
1463 sysdate,
1464 g_user_id
1465 FROM
1466 pon_auction_headers_all pah1,
1467 pon_auction_headers_all template,
1468 pon_attribute_scores template_attribute_score,
1469 pon_auction_attributes template_attribute,
1470 pon_auction_attributes auction_attributes,
1471 pon_auc_attributes_interface interface_attributes
1472 WHERE
1473 pah1.auction_header_id = g_auction_header_id
1474 and template.auction_header_id = pah1.template_id
1475 and template_attribute.auction_header_id = template.auction_header_id
1476 and template_attribute_score.attribute_sequence_number = template_attribute.sequence_number
1477 and template_attribute_score.auction_header_id = template_attribute.auction_header_id
1478 and template_attribute_score.line_number = template_attribute.line_number
1479 and auction_attributes.auction_header_id = pah1.auction_header_id
1480 and auction_attributes.attribute_name = template_attribute.attribute_name
1481 and interface_attributes.batch_id = g_batch_id
1482 and interface_attributes.auction_header_id = auction_attributes.auction_header_id
1483 and interface_attributes.auction_line_number = auction_attributes.line_number
1484 and interface_attributes.attribute_name = auction_attributes.attribute_name
1485 and interface_attributes.response_type_name = 'PON_FROM_TEMPLATE' ;
1486
1487
1488 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1489 print_debug_log(l_module,'ADD_ATTRIBUTE_SCORES END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1490 END IF;
1491
1492 --}
1493 END ADD_ATTRIBUTE_SCORES ;
1494
1495
1496 /*======================================================================
1497 PROCEDURE: ADD_NEW_LINE_WITH_CHILDREN PRIVATE
1498
1499 PARAMETERS: NONE
1500
1501 COMMENT : This procedure will add the copy the new created in the
1502 spreadsheet lines with their children.
1503 ======================================================================*/
1504 PROCEDURE ADD_NEW_LINE_WITH_CHILDREN is
1505
1506 l_module CONSTANT VARCHAR2(26) := 'ADD_NEW_LINE_WITH_CHILDREN';
1507 l_bid_ranking pon_auction_headers_all.bid_ranking%type;
1508 l_template_id pon_auction_headers_all.template_id%type;
1509
1510 BEGIN
1511 --{
1512 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1513 print_debug_log(l_module,'ADD_NEW_LINE_WITH_CHILDREN START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1514 END IF;
1515
1516 SELECT
1517 pah.bid_ranking,
1518 pah.template_id
1519 INTO
1520 l_bid_ranking,
1521 l_template_id
1522 FROM
1523 pon_auction_headers_all pah
1524 WHERE
1525 pah.auction_header_id = g_auction_header_id;
1526
1527 add_lines;
1528
1529 if(g_price_differentials_flag = 'Y') then
1530
1531 add_price_differentials ;
1532
1533 end if;
1534
1535 if(g_price_element_enabled_flag = 'Y') then
1536
1537 add_price_factors;
1538
1539 end if;
1540
1541 if(g_line_attribute_enabled = 'Y') then
1542
1543 add_attributes;
1544
1545 if ( g_attribute_score_enabled_flag = 'Y' and l_template_id is not null) then
1546
1547 add_attribute_scores;
1548
1549 END if;
1550
1551 end if;
1552
1553 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1554 print_debug_log(l_module,'ADD_NEW_LINE_WITH_CHILDREN END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1555 END IF;
1556
1557 --}
1558 END ADD_NEW_LINE_WITH_CHILDREN ;
1559
1560
1561
1562 /*======================================================================
1563 PROCEDURE: UPDATE_LINES PRIVATE
1564
1565 PARAMETERS: NONE
1566
1567 COMMENT : This will copy all the lines that are to be updated
1568 ======================================================================*/
1569 PROCEDURE UPDATE_LINES is
1570
1571 l_module CONSTANT VARCHAR2(12) := 'UPDATE_LINES';
1572 l_contract_type pon_auction_headers_all.contract_type%TYPE;
1573 l_last_amendment_update pon_auction_headers_all.amendment_number%TYPE;
1574 l_max_internal_line_num pon_auction_headers_all.max_internal_line_num%TYPE;
1575
1576 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1577 l_group_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
1578 l_item_description PON_NEG_COPY_DATATYPES_GRP.VARCHAR2500_TYPE;
1579 l_category_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1580 l_category_name PON_NEG_COPY_DATATYPES_GRP.VARCHAR300_TYPE;
1581 l_ip_category_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1582 l_quantity PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1583 l_uom_code PON_NEG_COPY_DATATYPES_GRP.VARCHAR20_TYPE;
1584 l_unit_of_measure PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
1585 l_need_by_start_date PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
1586 l_need_by_date PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
1587 l_target_price PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1588 l_bid_start_price PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1589 l_note_to_bidders PON_NEG_COPY_DATATYPES_GRP.VARCHAR4000_TYPE;
1590 l_ship_to_location_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1591 l_current_price PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1592 l_reserve_price PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1593 l_display_target_price_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
1594 l_po_min_rel_amount PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1595 l_line_type_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1596 l_order_type_lookup_code PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
1597 l_item_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1598 l_item_number PON_NEG_COPY_DATATYPES_GRP.VARCHAR1000_TYPE;
1599 l_item_revision PON_NEG_COPY_DATATYPES_GRP.VARCHAR20_TYPE;
1600 l_job_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1601 l_additional_job_details PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
1602 l_po_agreed_amount PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1603 l_unit_target_price PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1604 l_unit_display_target_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
1605 l_differential_response_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR20_TYPE;
1606 l_purchase_basis PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
1607 l_price_disabled_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
1608 l_quantity_disabled_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
1609 --R12 - Complex work
1610 l_advance_amount PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1611 l_recoupment_rate_percent PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1612 l_progress_pymt_rate_percent PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1613 l_retainage_rate_percent PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1614 l_max_retainage_amount PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1615 l_project_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1616 l_project_task_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1617 l_project_award_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1618 l_project_expenditure_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
1619 l_project_exp_organization_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1620 l_project_expenditure_item_dt PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
1621 l_work_approver_user_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1622
1623 BEGIN
1624 --{
1625 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1626 print_debug_log(l_module,'UPDATE_LINES START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1627 END IF;
1628
1629 SELECT
1630 contract_type,
1631 nvl(amendment_number,0),
1632 max_internal_line_num
1633 INTO
1634 l_contract_type,
1635 l_last_amendment_update,
1636 l_max_internal_line_num
1637 FROM
1638 pon_auction_headers_all
1639 WHERE
1640 auction_header_id = g_auction_header_id;
1641
1642 -- delete category descriptors if ip category has changed
1643
1644 IF (l_contract_type in ('BLANKET', 'CONTRACT')) THEN
1645
1646 -- 1) delete scores first
1647
1648 delete from
1649 (select *
1650 from pon_attribute_scores
1651 where auction_header_id = g_auction_header_id and
1652 line_number in (select paip.line_number
1653 from pon_item_prices_interface p1,
1654 pon_auction_item_prices_all paip
1655 where p1.batch_id = g_batch_id and
1656 p1.auction_header_id = paip.auction_header_id and
1657 p1.auction_line_number = paip.line_number and
1658 paip.ip_category_id is not null and
1659 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1))) pas
1660 where auction_header_id = g_auction_header_id and
1661 exists (select null
1662 from pon_auction_attributes paa
1663 where paa.auction_header_id = pas.auction_header_id and
1664 paa.line_number = pas.line_number and
1665 paa.sequence_number = pas.attribute_sequence_number and
1666 paa.ip_category_id is not null and
1667 paa.ip_category_id <> 0);
1668
1669 -- 2) then delete attributes
1670 delete from pon_auction_attributes paa
1671 where paa.auction_header_id = g_auction_header_id and
1672 paa.ip_category_id is not null and
1673 paa.ip_category_id <> 0 and
1674 paa.line_number in (select paip.line_number
1675 from pon_item_prices_interface p1,
1676 pon_auction_item_prices_all paip
1677 where p1.batch_id = g_batch_id and
1678 p1.auction_header_id = paip.auction_header_id and
1679 p1.auction_line_number = paip.line_number and
1680 paip.ip_category_id is not null and
1681 nvl(p1.ip_category_id, -1) <> nvl(paip.ip_category_id, -1));
1682
1683 END IF;
1684
1685 SELECT
1686 p1.AUCTION_LINE_NUMBER,
1687 p1.GROUP_TYPE,
1688 p1.ITEM_DESCRIPTION,
1689 p1.CATEGORY_ID,
1690 p1.CATEGORY_NAME,
1691 p1.IP_CATEGORY_ID,
1692 p1.QUANTITY,
1693 decode(nvl(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','',p1.UOM_CODE),
1694 decode(nvl(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','',p1.UNIT_OF_MEASURE),
1695 p1.NEED_BY_START_DATE,
1696 p1.NEED_BY_DATE,
1697 p1.TARGET_PRICE,
1698 p1.BID_START_PRICE,
1699 p1.NOTE_TO_BIDDERS,
1700 p1.SHIP_TO_LOCATION_ID,
1701 p1.CURRENT_PRICE,
1702 p1.RESERVE_PRICE,
1703 p1.DISPLAY_TARGET_PRICE_FLAG,
1704 p1.PO_MIN_REL_AMOUNT,
1705 p1.LINE_TYPE_ID,
1706 p1.ORDER_TYPE_LOOKUP_CODE,
1707 p1.ITEM_ID,
1708 p1.ITEM_NUMBER,
1709 p1.ITEM_REVISION,
1710 p1.JOB_ID,
1711 p1.ADDITIONAL_JOB_DETAILS,
1712 p1.PO_AGREED_AMOUNT,
1713 p1.UNIT_TARGET_PRICE,
1714 p1.UNIT_DISPLAY_TARGET_FLAG,
1715 decode(p1.DIFFERENTIAL_RESPONSE_TYPE, PON_AUCTION_PKG.getMessage('PON_AUCTS_REQUIRED'),'REQUIRED', PON_AUCTION_PKG.getMessage('PON_AUCTS_OPTIONAL'),'OPTIONAL', PON_AUCTION_PKG.getMessage('PON_AUCTS_DISPLAY_ONLY'),'DISPLAY_ONLY',
1716 null),
1717 p1.PURCHASE_BASIS,
1718 decode(NVL(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','Y','N'),
1719 decode(NVL(p1.PRICE_AND_QUANTITY_APPLY,'Y'),'N','Y','N'),
1720 --R12 - Complex work
1721 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.ADVANCE_AMOUNT),
1722 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.RECOUPMENT_RATE_PERCENT),
1723 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROGRESS_PYMT_RATE_PERCENT),
1724 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.RETAINAGE_RATE_PERCENT),
1725 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.MAX_RETAINAGE_AMOUNT),
1726 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_ID),
1727 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_TASK_ID),
1728 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_AWARD_ID),
1729 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXPENDITURE_TYPE),
1730 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXP_ORGANIZATION_ID),
1731 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.PROJECT_EXPENDITURE_ITEM_DATE),
1732 decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.WORK_APPROVER_USER_ID)
1733 BULK COLLECT INTO
1734 l_line_number,
1735 l_group_type,
1736 l_item_description,
1737 l_category_id,
1738 l_category_name,
1739 l_ip_category_id,
1740 l_quantity,
1741 l_uom_code,
1742 l_unit_of_measure,
1743 l_need_by_start_date,
1744 l_need_by_date,
1745 l_target_price,
1746 l_bid_start_price,
1747 l_note_to_bidders,
1748 l_ship_to_location_id,
1749 l_current_price,
1750 l_reserve_price,
1751 l_display_target_price_flag,
1752 l_po_min_rel_amount,
1753 l_line_type_id,
1754 l_order_type_lookup_code,
1755 l_item_id,
1756 l_item_number,
1757 l_item_revision,
1758 l_job_id,
1759 l_additional_job_details,
1760 l_po_agreed_amount,
1761 l_unit_target_price,
1762 l_unit_display_target_flag,
1763 l_differential_response_type,
1764 l_purchase_basis,
1765 l_price_disabled_flag,
1766 l_quantity_disabled_flag,
1767 --R12 - Complex work
1768 l_advance_amount,
1769 l_recoupment_rate_percent,
1770 l_progress_pymt_rate_percent,
1771 l_retainage_rate_percent,
1772 l_max_retainage_amount,
1773 l_project_id,
1774 l_project_task_id,
1775 l_project_award_id,
1776 l_project_expenditure_type,
1777 l_project_exp_organization_id,
1778 l_project_expenditure_item_dt,
1779 l_work_approver_user_id
1780 from
1781 pon_item_prices_interface p1
1782 where
1783 p1.batch_id = g_batch_id
1784 and p1.action = g_update_action;
1785
1786 FORALL x in 1..l_line_number.COUNT
1787 UPDATE PON_AUCTION_ITEM_PRICES_ALL
1788 SET
1789 GROUP_TYPE = l_group_type(x),
1790 ITEM_DESCRIPTION = l_item_description(x),
1791 CATEGORY_ID = l_category_id(x),
1792 CATEGORY_NAME = l_category_name(x),
1793 IP_CATEGORY_ID = l_ip_category_id(x),
1794 QUANTITY = l_quantity(x),
1795 UOM_CODE = l_uom_code(x),
1796 UNIT_OF_MEASURE = l_unit_of_measure(x),
1797 NEED_BY_START_DATE = l_need_by_start_date(x),
1798 NEED_BY_DATE = l_need_by_date(x),
1799 TARGET_PRICE = l_target_price(x),
1800 BID_START_PRICE = l_bid_start_price(x),
1801 NOTE_TO_BIDDERS = l_note_to_bidders(x),
1802 SHIP_TO_LOCATION_ID = l_ship_to_location_id(x),
1803 CURRENT_PRICE = l_current_price(x),
1804 RESERVE_PRICE = l_reserve_price(x),
1805 DISPLAY_TARGET_PRICE_FLAG = l_display_target_price_flag(x),
1806 PO_MIN_REL_AMOUNT = l_po_min_rel_amount(x),
1807 LINE_TYPE_ID = l_line_type_id(x),
1808 ORDER_TYPE_LOOKUP_CODE = l_order_type_lookup_code(x),
1809 ITEM_ID = l_item_id(x),
1810 ITEM_NUMBER = l_item_number(x),
1811 ITEM_REVISION = l_item_revision(x),
1812 JOB_ID = l_job_id(x),
1813 ADDITIONAL_JOB_DETAILS = l_additional_job_details(x),
1814 PO_AGREED_AMOUNT = l_po_agreed_amount(x),
1815 UNIT_TARGET_PRICE = l_unit_target_price(x),
1816 UNIT_DISPLAY_TARGET_FLAG = l_unit_display_target_flag(x),
1817 DIFFERENTIAL_RESPONSE_TYPE = l_differential_response_type(x),
1818 PURCHASE_BASIS = l_purchase_basis(x),
1819 PRICE_DISABLED_FLAG = l_price_disabled_flag(x),
1820 QUANTITY_DISABLED_FLAG = l_quantity_disabled_flag(x),
1821 --R12-Complexworkl_--R12 - Complex work
1822 ADVANCE_AMOUNT = l_advance_amount(x),
1823 RECOUPMENT_RATE_PERCENT = l_recoupment_rate_percent(x),
1824 PROGRESS_PYMT_RATE_PERCENT = l_progress_pymt_rate_percent(x),
1825 RETAINAGE_RATE_PERCENT = l_retainage_rate_percent(x),
1826 MAX_RETAINAGE_AMOUNT = l_max_retainage_amount(x),
1827 PROJECT_ID = l_project_id(x),
1828 PROJECT_TASK_ID = l_project_task_id(x),
1829 PROJECT_AWARD_ID = l_project_award_id(x),
1830 PROJECT_EXPENDITURE_TYPE = l_project_expenditure_type(x),
1831 PROJECT_EXP_ORGANIZATION_ID = l_project_exp_organization_id(x),
1832 PROJECT_EXPENDITURE_ITEM_DATE = l_project_expenditure_item_dt(x),
1833 WORK_APPROVER_USER_ID = l_work_approver_user_id(x),
1834 LAST_UPDATE_DATE = sysdate,
1835 LAST_UPDATED_BY = g_user_id,
1836 LAST_UPDATE_LOGIN = fnd_global.login_id
1837 WHERE
1838 AUCTION_HEADER_ID = g_auction_header_id AND
1839 LINE_NUMBER = l_line_number (x);
1840
1841 -- identify parent of children that have been updated/ added or modified
1842 -- or parents that have been updated
1843
1844 -- Identify any parent from the previous round whose child exists in the
1845 -- interface table. If a child exists in the interfacetable it indicates
1846 -- that the child was added , modified or deleted. In all these cases we
1847 -- need to mark the parent modified.
1848 SELECT
1849 DISTINCT INTERFACE.parent_line_number
1850 BULK COLLECT INTO
1851 l_line_number
1852 FROM
1853 pon_item_prices_interface INTERFACE
1854 WHERE
1855 INTERFACE.batch_id = g_batch_id
1856 AND INTERFACE.parent_line_number <= l_max_internal_line_num
1857 AND INTERFACE.group_type IN ('LOT_LINE','GROUP_LINE');
1858
1859
1860 FORALL x in 1..l_line_number.COUNT
1861 UPDATE PON_AUCTION_ITEM_PRICES_ALL
1862 SET
1863 LAST_AMENDMENT_UPDATE = decode(l_last_amendment_update,0,LAST_AMENDMENT_UPDATE,l_last_amendment_update),
1864 MODIFIED_DATE = sysdate,
1865 MODIFIED_FLAG = 'Y'
1866 where
1867 AUCTION_HEADER_ID = g_auction_header_id AND
1868 LINE_NUMBER = l_line_number (x);
1869
1870 -- Not combining this with the above sql for performance reasons
1871 SELECT
1872 interface.auction_line_number
1873 BULK COLLECT INTO
1874 l_line_number
1875 FROM
1876 pon_item_prices_interface INTERFACE
1877 WHERE
1878 INTERFACE.batch_id = g_batch_id
1879 AND INTERFACE.auction_line_number <= l_max_internal_line_num;
1880
1881 FORALL x in 1..l_line_number.COUNT
1882 UPDATE PON_AUCTION_ITEM_PRICES_ALL
1883 SET
1884 LAST_AMENDMENT_UPDATE = decode(l_last_amendment_update,0,LAST_AMENDMENT_UPDATE,l_last_amendment_update),
1885 MODIFIED_DATE = sysdate,
1886 MODIFIED_FLAG = 'Y'
1887 where
1888 AUCTION_HEADER_ID = g_auction_header_id AND
1889 LINE_NUMBER = l_line_number (x);
1890
1891
1892 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1893 print_debug_log(l_module,'UPDATE_LINES END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1894 END IF;
1895
1896 --}
1897 END UPDATE_LINES ;
1898
1899
1900 /*======================================================================
1901 PROCEDURE: UPDATE_PRICE_DIFFERNTIALS PRIVATE
1902
1903 PARAMETERS: NONE
1904
1905 COMMENT : This will perform the following actions for price differntials
1906 for lines that have updated by the spread sheet.
1907 i. Delete existing price differentials for updated lines
1908 ii. Add price differentials from the spreadsheet.
1909 ======================================================================*/
1910 PROCEDURE UPDATE_PRICE_DIFFERNTIALS is
1911
1912 l_module CONSTANT VARCHAR2(25) := 'UPDATE_PRICE_DIFFERNTIALS';
1913
1914 BEGIN
1915 --{
1916 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1917 print_debug_log(l_module,'UPDATE_PRICE_DIFFERNTIALS START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1918 END IF;
1919
1920 -- Delete existing price differentials for updated lines
1921 delete from PON_PRICE_DIFFERENTIALS price_differentials
1922 where
1923 auction_header_id = g_auction_header_id
1924 and
1925 LINE_NUMBER =
1926 (select
1927 LINE_NUMBER
1928 from
1929 pon_item_prices_interface paip_int
1930 where
1931 paip_int.batch_id = g_batch_id
1932 and price_differentials.line_number = paip_int.auction_line_number
1933 and paip_int.action = g_update_action);
1934
1935 -- Add price differentials from the spreadsheet.
1936 insert into pon_price_differentials
1937 fields
1938 (auction_header_id,
1939 line_number,
1940 shipment_number,
1941 price_differential_number,
1942 price_type,
1943 multiplier,
1944 creation_date,
1945 created_by,
1946 last_update_date,
1947 last_updated_by,
1948 last_update_login
1949 )
1950 select
1951 price_diff_int.auction_header_id,
1952 price_diff_int.auction_line_number,
1953 -1,
1954 price_diff_int.sequence_number,
1955 price_diff_int.price_type,
1956 price_diff_int.multiplier,
1957 sysdate,
1958 g_user_id,
1959 sysdate,
1960 g_user_id,
1961 fnd_global.login_id
1962 FROM
1963 pon_item_prices_interface paip_int,
1964 pon_auc_price_differ_int price_diff_int
1965 WHERE
1966 paip_int.batch_id = g_batch_id
1967 and price_diff_int.batch_id = paip_int.batch_id
1968 and price_diff_int.auction_line_number = paip_int.auction_line_number
1969 and paip_int.action = g_update_action;
1970
1971
1972
1973 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1974 print_debug_log(l_module,'UPDATE_PRICE_DIFFERNTIALS END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
1975 END IF;
1976
1977 --}
1978 END UPDATE_PRICE_DIFFERNTIALS ;
1979
1980
1981 /*======================================================================
1982 PROCEDURE: UPDATE_PRICE_FACTORS PRIVATE
1983
1984 PARAMETERS: NONE
1985
1986 COMMENT : This will perform the following actions for price differntials
1987 for lines that have updated by the spread sheet.
1988 i. Delete supplier price factors from auction tables that are
1989 not in interface tables
1990 ii. Update Price Elements that exist in the spreadsheet and the lines
1991 iii.Insert price Elements that do not exist in the PON_PRICE_ELEMENTS
1992 but are present in the spreadsheet.
1993 ======================================================================*/
1994 PROCEDURE UPDATE_PRICE_FACTORS is
1995
1996 l_module CONSTANT VARCHAR2(20) := 'UPDATE_PRICE_FACTORS';
1997
1998 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
1999 l_price_element_type_id PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2000 l_pricing_basis PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2001 l_value PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2002 l_display_target_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
2003 l_display_to_suppliers_flag VARCHAR2(1) := 'Y';
2004 BEGIN
2005 --{
2006 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2007 print_debug_log(l_module,'UPDATE_PRICE_FACTORS START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2008 END IF;
2009
2010 -- Delete supplier price factors from auction tables that are
2011 -- not in interface tables
2012 delete from PON_PRICE_ELEMENTS pe
2013 where
2014 pe.auction_header_id = g_auction_header_id
2015 and pe.pf_type = 'SUPPLIER'
2016 and exists (select
2017 1
2018 from
2019 PON_ITEM_PRICES_INTERFACE line_interface
2020 where
2021 line_interface.action = g_update_action
2022 and line_interface.batch_id = g_batch_id
2023 and line_interface.auction_header_id = pe.auction_header_id
2024 and line_interface.auction_line_number = pe.line_number)
2025 and not exists
2026 (select 1
2027 from
2028 PON_ITEM_PRICES_INTERFACE paip_int,
2029 PON_AUC_PRICE_ELEMENTS_INT pe_int
2030 where
2031 paip_int.action = g_update_action
2032 and paip_int.batch_id = g_batch_id
2033 and paip_int.batch_id = pe_int.batch_id
2034 and paip_int.auction_line_number = pe_int.auction_line_number
2035 and paip_int.auction_header_id = pe_int.auction_header_id
2036 and pe_int.auction_header_id = pe.auction_header_id
2037 and pe_int.auction_line_number = pe.line_number
2038 and pe_int.price_element_type_id = pe.price_element_type_id
2039 and pe_int.PF_TYPE = 'SUPPLIER');
2040
2041 --Update Price Elements that exist in the spreadsheet and the lines
2042 select
2043 pe_int.AUCTION_LINE_NUMBER,
2044 pe_int.PRICE_ELEMENT_TYPE_ID,
2045 pe_int.PRICING_BASIS,
2046 pe_int.VALUE,
2047 decode(g_is_amendment,'N',pe_int.DISPLAY_TARGET_FLAG,
2048 decode(greatest(pe_int.AUCTION_LINE_NUMBER,g_max_prev_line_num_plus_one),
2049 pe_int.AUCTION_LINE_NUMBER,pe_int.DISPLAY_TARGET_FLAG,'X'))
2050 BULK COLLECT INTO
2051 l_line_number,
2052 l_price_element_type_id,
2053 l_pricing_basis,
2054 l_value,
2055 l_display_target_flag
2056 from
2057 PON_AUC_PRICE_ELEMENTS_INT pe_int,
2058 PON_ITEM_PRICES_INTERFACE paip_int
2059 where
2060 paip_int.action = g_update_action
2061 and paip_int.batch_id = g_batch_id
2062 and paip_int.batch_id = pe_int.batch_id
2063 and paip_int.auction_line_number = pe_int.auction_line_number
2064 and paip_int.auction_header_id = pe_int.auction_header_id
2065 and pe_int.PF_TYPE = 'SUPPLIER';
2066
2067 FORALL x in 1..l_line_number.COUNT
2068 UPDATE PON_PRICE_ELEMENTS
2069 SET
2070 PRICING_BASIS = l_pricing_basis(x),
2071 VALUE = l_value(x),
2072 DISPLAY_TARGET_FLAG = decode(l_display_target_flag(x),'X',DISPLAY_TARGET_FLAG,l_display_target_flag(x))
2073 WHERE
2074 AUCTION_HEADER_ID = g_auction_header_id
2075 and LINE_NUMBER = l_line_number (x)
2076 and PRICE_ELEMENT_TYPE_ID = l_price_element_type_id(x);
2077
2078
2079 --Insert price Elements that do not exist in the PON_PRICE_ELEMENTS
2080 --but are present in the spreadsheet.
2081
2082 insert into PON_PRICE_ELEMENTS
2083 fields
2084 (AUCTION_HEADER_ID,
2085 LINE_NUMBER,
2086 SEQUENCE_NUMBER,
2087 PRICE_ELEMENT_TYPE_ID,
2088 PRICING_BASIS,
2089 VALUE,
2090 DISPLAY_TARGET_FLAG,
2091 PF_TYPE,
2092 DISPLAY_TO_SUPPLIERS_FLAG,
2093 LIST_ID,
2094 CREATION_DATE,
2095 CREATED_BY,
2096 LAST_UPDATE_DATE,
2097 LAST_UPDATED_BY)
2098 select
2099 pe_int.AUCTION_HEADER_ID,
2100 pe_int.AUCTION_LINE_NUMBER,
2101 PON_CP_INTRFAC_TO_TRANSACTION.get_next_pe_sequence_number(pe_int.auction_header_id,pe_int.AUCTION_LINE_NUMBER),
2102 pe_int.PRICE_ELEMENT_TYPE_ID,
2103 pe_int.PRICING_BASIS,
2104 pe_int.VALUE,
2105 pe_int.DISPLAY_TARGET_FLAG,
2106 pe_int.PF_TYPE,
2107 pe_int.DISPLAY_TO_SUPPLIERS_FLAG,
2108 -1,
2109 sysdate,
2110 g_user_id,
2111 sysdate,
2112 g_user_id
2113 from
2114 pon_auc_price_elements_int pe_int,
2115 pon_item_prices_interface paip_int
2116 where
2117 paip_int.action = g_update_action
2118 and paip_int.batch_id = g_batch_id
2119 and pe_int.batch_id = paip_int.batch_id
2120 and paip_int. auction_line_number = pe_int.auction_line_number
2121 and pe_int.price_element_type_id not in
2122 (select
2123 pe1.price_element_type_id
2124 from
2125 PON_PRICE_ELEMENTS pe1
2126 where
2127 pe_int.auction_header_id = pe1.auction_header_id
2128 and pe_int.auction_line_number = pe1.line_number
2129 and pe_int.price_element_type_id = pe1.price_element_type_id)
2130 order by pe_int.sequence_number;
2131
2132 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2133 print_debug_log(l_module,'UPDATE_PRICE_FACTORS END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2134 END IF;
2135
2136 --}
2137 END UPDATE_PRICE_FACTORS ;
2138
2139
2140 /*======================================================================
2141 PROCEDURE: UPDATE_LINE_ATTRIBUTES PRIVATE
2142
2143 PARAMETERS: NONE
2144
2145 COMMENT : The following logic is used for attributes for lines that are
2146 updated by the spreadsheet.
2147 i. Remove Attributes and their scores for attributes that are
2148 not present in the interface tables.
2149 ii. If the attribute name exists previously
2150 a. Clear Scores if the attribute data type has changed
2151 b. Update attributes that have been updated.
2152 iii. Insert Attributes that do not exist.
2153 ======================================================================*/
2154 PROCEDURE UPDATE_LINE_ATTRIBUTES is
2155
2156 l_module CONSTANT VARCHAR2(22) := 'UPDATE_LINE_ATTRIBUTES';
2157 l_bid_ranking pon_auction_headers_all.bid_ranking%type;
2158 l_max_neg_line_attr_seq_num number;
2159 l_attribute_seq_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2160 l_attribute_name PON_NEG_COPY_DATATYPES_GRP.VARCHAR4000_TYPE;
2161 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2162 l_attr_group PON_NEG_COPY_DATATYPES_GRP.VARCHAR300_TYPE;
2163 l_mandatory_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
2164 l_display_only_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
2165 l_internal_attr_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
2166 l_datatype PON_NEG_COPY_DATATYPES_GRP.VARCHAR20_TYPE;
2167 l_display_target_flag PON_NEG_COPY_DATATYPES_GRP.VARCHAR1_TYPE;
2168 l_value PON_NEG_COPY_DATATYPES_GRP.VARCHAR4000_TYPE;
2169 l_scoring_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2170 l_attr_level PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2171 l_attr_group_seq_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2172 l_attr_disp_seq_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2173
2174
2175 BEGIN
2176 --{
2177 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2178 print_debug_log(l_module,'UPDATE_LINE_ATTRIBUTES START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2179 END IF;
2180
2181
2182 SELECT
2183 pah.bid_ranking
2184 INTO
2185 l_bid_ranking
2186 FROM
2187 pon_auction_headers_all pah
2188 WHERE
2189 pah.auction_header_id = g_auction_header_id;
2190
2191 if ( g_attribute_score_enabled_flag = 'Y' ) then
2192
2193 -- The sql below clears data from the scores table for
2194 -- Attributes that have been deleted
2195 -- Attribute data type of an existing attribute has changed
2196 select
2197 sequence_number,
2198 line_number
2199 bulk collect into
2200 l_attribute_seq_number,
2201 l_line_number
2202 from
2203 pon_auction_attributes auction_attributes,
2204 pon_item_prices_interface line_interface
2205 where
2206 line_interface.action = g_update_action
2207 and line_interface.batch_id = g_batch_id
2208 and line_interface.auction_header_id = g_auction_header_id
2209 and auction_attributes.auction_header_id = line_interface.auction_header_id
2210 and auction_attributes.line_number = line_interface.auction_line_number
2211 and not exists
2212 (select
2213 1
2214 from
2215 pon_auc_attributes_interface interface_attributes
2216 where
2217 line_interface.batch_id = interface_attributes.batch_id
2218 and line_interface.auction_line_number = interface_attributes.auction_line_number
2219 and interface_attributes.auction_header_id = auction_attributes.auction_header_id
2220 and interface_attributes.attribute_name = auction_attributes.attribute_name
2221 and interface_attributes.datatype = auction_attributes.datatype);
2222
2223
2224 FORALL x in 1..l_line_number.COUNT
2225 delete from pon_attribute_scores attribute_scores
2226 where
2227 attribute_scores.auction_header_id = g_auction_header_id
2228 and attribute_scores.line_number = l_line_number(x)
2229 and attribute_scores.attribute_sequence_number = l_attribute_seq_number(x);
2230
2231
2232 end if ;
2233
2234 -- The sql below clears data from the attributes table for attributes that have been deleted
2235 select
2236 attribute_name,
2237 line_number
2238 bulk collect into
2239 l_attribute_name,
2240 l_line_number
2241 from
2242 pon_auction_attributes auction_attributes,
2243 pon_item_prices_interface line_interface
2244 where
2245 line_interface.action = g_update_action
2246 and line_interface.batch_id = g_batch_id
2247 and line_interface.auction_header_id = g_auction_header_id
2248 and auction_attributes.auction_header_id = line_interface.auction_header_id
2249 and auction_attributes.line_number = line_interface.auction_line_number
2250 and not exists
2251 (select
2252 1
2253 from
2254 pon_auc_attributes_interface interface_attributes
2255 where
2256 line_interface.batch_id = interface_attributes.batch_id
2257 and line_interface.auction_line_number = interface_attributes.auction_line_number
2258 and interface_attributes.auction_header_id = auction_attributes.auction_header_id
2259 and interface_attributes.attribute_name = auction_attributes.attribute_name);
2260
2261 FORALL x in 1..l_line_number.COUNT
2262 delete from pon_auction_attributes auction_attributes
2263 where
2264 auction_attributes.auction_header_id = g_auction_header_id
2265 and auction_attributes.line_number = l_line_number(x)
2266 and auction_attributes.attribute_name = l_attribute_name(x);
2267
2268 -- Update attributes that have been updated.
2269 select
2270 interface_attributes.auction_line_number,
2271 interface_attributes.attribute_name,
2272 nvl(interface_attributes.GROUP_CODE, g_default_attribute_group),
2273 decode(interface_attributes.RESPONSE_TYPE,'REQUIRED','Y','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'N'),
2274 decode(interface_attributes.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'Y','OPTIONAL', 'N','INTERNAL', 'N'),
2275 decode(interface_attributes.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'Y'),
2276 interface_attributes.DATATYPE,
2277 decode(g_is_amendment,'N',interface_attributes.DISPLAY_TARGET_FLAG,
2278 decode(greatest(line_interface.AUCTION_LINE_NUMBER,g_max_prev_line_num_plus_one),
2279 line_interface.AUCTION_LINE_NUMBER,interface_attributes.DISPLAY_TARGET_FLAG,'X')),
2280 interface_attributes.VALUE,
2281 interface_attributes.SCORING_TYPE,
2282 'LINE',
2283 interface_attributes.ATTR_GROUP_SEQ_NUMBER,
2284 interface_attributes.ATTR_DISP_SEQ_NUMBER
2285 bulk collect into
2286 l_line_number,
2287 l_attribute_name,
2288 l_attr_group,
2289 l_mandatory_flag,
2290 l_display_only_flag,
2291 l_internal_attr_flag,
2292 l_datatype,
2293 l_display_target_flag,
2294 l_value,
2295 l_scoring_type,
2296 l_attr_level,
2297 l_attr_group_seq_number,
2298 l_attr_disp_seq_number
2299 from
2300 pon_item_prices_interface line_interface,
2301 pon_auc_attributes_interface interface_attributes,
2302 pon_auction_attributes auction_attributes
2303 where
2304 interface_attributes.batch_id = g_batch_id
2305 and interface_attributes.auction_header_id = auction_attributes.auction_header_id
2306 and interface_attributes.auction_line_number = auction_attributes.line_number
2307 and interface_attributes.attribute_name = auction_attributes.attribute_name
2308 and line_interface.auction_line_number = interface_attributes.auction_line_number
2309 and line_interface.batch_id = interface_attributes.batch_id
2310 and line_interface.action = g_update_action;
2311
2312 FORALL x in 1..l_line_number.COUNT
2313 update pon_auction_attributes auction_attributes
2314 set
2315 attr_group = l_attr_group(x),
2316 mandatory_flag = l_mandatory_flag(x),
2317 display_only_flag = l_display_only_flag(x),
2318 internal_attr_flag = l_internal_attr_flag(x),
2319 datatype = l_datatype(x),
2320 display_target_flag = decode(l_display_target_flag(x),'X',display_target_flag,l_display_target_flag(x)),
2321 value = l_value(x),
2322 scoring_type = l_scoring_type(x),
2323 attr_level = l_attr_level(x),
2324 attr_group_seq_number = l_attr_group_seq_number(x),
2325 attr_disp_seq_number = l_attr_disp_seq_number(x),
2326 last_update_date = sysdate,
2327 last_updated_by = g_user_id
2328 where
2329 auction_attributes.auction_header_id = g_auction_header_id
2330 and auction_attributes.line_number = l_line_number(x)
2331 and auction_attributes.attribute_name = l_attribute_name(x);
2332
2333
2334 -- Insert Attributes that do not exist.
2335 SELECT nvl(max(SEQUENCE_NUMBER),10)
2336 into
2337 l_max_neg_line_attr_seq_num
2338 FROM
2339 pon_auction_attributes
2340 WHERE
2341 AUCTION_HEADER_ID = g_auction_header_id;
2342
2343 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2344 print_debug_log(l_module,'UPDATE_LINE_ATTRIBUTES END g_batch_id = '||g_batch_id ||
2345 ' g_auction_header_id '||g_auction_header_id ||
2346 ' l_max_neg_line_attr_seq_num = '||l_max_neg_line_attr_seq_num);
2347 END IF;
2348
2349 insert into pon_auction_attributes
2350 fields
2351 (AUCTION_HEADER_ID,
2352 LINE_NUMBER,
2353 SEQUENCE_NUMBER,
2354 ATTR_GROUP,
2355 MANDATORY_FLAG,
2356 DISPLAY_ONLY_FLAG,
2357 INTERNAL_ATTR_FLAG,
2358 ATTRIBUTE_NAME,
2359 DATATYPE,
2360 DISPLAY_TARGET_FLAG,
2361 VALUE,
2362 SCORING_TYPE,
2363 ATTR_LEVEL,
2364 ATTR_GROUP_SEQ_NUMBER,
2365 ATTR_DISP_SEQ_NUMBER,
2366 ATTRIBUTE_LIST_ID,
2367 WEIGHT,
2368 ATTR_MAX_SCORE,
2369 IP_CATEGORY_ID,
2370 IP_DESCRIPTOR_ID,
2371 CREATION_DATE,
2372 CREATED_BY,
2373 LAST_UPDATE_DATE,
2374 LAST_UPDATED_BY)
2375 select
2376 paa_int.AUCTION_HEADER_ID,
2377 paa_int.AUCTION_LINE_NUMBER,
2378 l_max_neg_line_attr_seq_num + (rownum*10),
2379 nvl(paa_int.GROUP_CODE, g_default_attribute_group),
2380 decode(paa_int.RESPONSE_TYPE,'REQUIRED','Y','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'N'),
2381 decode(paa_int.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'Y','OPTIONAL', 'N','INTERNAL', 'N'),
2382 decode(paa_int.RESPONSE_TYPE,'REQUIRED','N','DISPLAY_ONLY', 'N','OPTIONAL', 'N','INTERNAL', 'Y'),
2383 paa_int.ATTRIBUTE_NAME,
2384 paa_int.DATATYPE,
2385 paa_int.DISPLAY_TARGET_FLAG,
2386 paa_int.VALUE,
2387 paa_int.SCORING_TYPE,
2388 'LINE',
2389 paa_int.ATTR_GROUP_SEQ_NUMBER,
2390 paa_int.ATTR_DISP_SEQ_NUMBER,
2391 -1,
2392 0,
2393 0,
2394 paa_int.IP_CATEGORY_ID,
2395 paa_int.IP_DESCRIPTOR_ID,
2396 sysdate,
2397 g_user_id,
2398 sysdate,
2399 g_user_id
2400 from
2401 pon_auc_attributes_interface paa_int,
2402 pon_item_prices_interface p1
2403 where
2404 paa_int.batch_id= g_batch_id
2405 and p1.batch_id = paa_int.batch_id
2406 and p1.action = g_update_action
2407 and paa_int.auction_line_number = p1.auction_line_number
2408 and not exists (select 'x'
2409 from
2410 pon_auction_attributes auction_attributes
2411 where
2412 paa_int.auction_header_id = auction_attributes.auction_header_id
2413 and paa_int.auction_line_number = auction_attributes.line_number
2414 and paa_int.attribute_name = auction_attributes.attribute_name)
2415 order by paa_int.auction_line_number,paa_int.sequence_number;
2416
2417 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2418 print_debug_log(l_module,'UPDATE_LINE_ATTRIBUTES END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2419 END IF;
2420
2421 --}
2422 END UPDATE_LINE_ATTRIBUTES ;
2423
2424
2425
2426 /*======================================================================
2427 PROCEDURE: UPDATE_LINES_WITH_CHILDREN PRIVATE
2428
2429 PARAMETERS: NONE
2430
2431 COMMENT : This procedure will add the update lines and their children
2432 for lines that are marked as updated in the interface tables.
2433 ======================================================================*/
2434 PROCEDURE UPDATE_LINES_WITH_CHILDREN is
2435
2436 l_module CONSTANT VARCHAR2(26) := 'UPDATE_LINES_WITH_CHILDREN';
2437
2438 BEGIN
2439 --{
2440 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2441 print_debug_log(l_module,'UPDATE_LINES_WITH_CHILDREN START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2442 END IF;
2443
2444 UPDATE_LINES;
2445
2446 if(g_price_differentials_flag = 'Y') then
2447
2448 UPDATE_PRICE_DIFFERNTIALS;
2449
2450 end if;
2451
2452 if(g_price_element_enabled_flag = 'Y') then
2453
2454 UPDATE_PRICE_FACTORS;
2455
2456 end if;
2457
2458 if(g_line_attribute_enabled = 'Y') then
2459
2460 UPDATE_LINE_ATTRIBUTES;
2461
2462 end if;
2463
2464 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2465 print_debug_log(l_module,'UPDATE_LINES_WITH_CHILDREN END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2466 END IF;
2467
2468 --}
2469 END UPDATE_LINES_WITH_CHILDREN ;
2470
2471
2472 /*======================================================================
2473 PROCEDURE: SYNCH_FROM_INTERFACE PUBLIC
2474
2475 PARAMETERS:
2476
2477 COMMENT : This procedure will synch up the large neg pf values table
2478 for the items uploaded.
2479 ======================================================================*/
2480 PROCEDURE SYNCH_PF_VALUES_FOR_UPLOAD is
2481
2482 l_module CONSTANT VARCHAR2(27) := 'SYNCH_PF_VALUES_FOR_UPLOAD';
2483
2484 l_PRICE_ELEMENT_TYPE_ID PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2485 l_PRICING_BASIS PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2486
2487 begin
2488 --{
2489
2490 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2491 print_debug_log(l_module,'SYNCH_PF_VALUES_FOR_UPLOAD START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2492 END IF;
2493
2494 -- Get distinct price factor and pricing basis by scanning the pon_price_elements
2495 -- once. Scanning pon_price_elements might be huge for large auctions.
2496 SELECT distinct
2497 PRICE_ELEMENT_TYPE_ID,
2498 PRICING_BASIS
2499 BULK COLLECT INTO
2500 l_PRICE_ELEMENT_TYPE_ID,
2501 l_PRICING_BASIS
2502 FROM
2503 pon_price_elements
2504 WHERE
2505 auction_header_id = g_auction_header_id
2506 and PF_TYPE = 'BUYER';
2507
2508 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2509 print_debug_log(l_module,'SYNCH_PF_VALUES_FOR_UPLOAD START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id|| ' : Got Pf Values');
2510 END IF;
2511
2512 -- Delete values that do not exist
2513 FORALL x IN 1..l_PRICE_ELEMENT_TYPE_ID.COUNT
2514 DELETE
2515 FROM PON_LARGE_NEG_PF_VALUES
2516 WHERE
2517 auction_header_id = g_auction_header_id
2518 and PRICE_ELEMENT_TYPE_ID <> l_PRICE_ELEMENT_TYPE_ID(x)
2519 and PRICING_BASIS <> l_PRICING_BASIS(x);
2520
2521 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2522 print_debug_log(l_module,'SYNCH_PF_VALUES_FOR_UPLOAD START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id|| ' : Deleted unused Pf Values');
2523 END IF;
2524
2525 -- Insert new values
2526 FORALL x IN 1..l_PRICE_ELEMENT_TYPE_ID.COUNT
2527 insert into
2528 PON_LARGE_NEG_PF_VALUES
2529 (auction_header_id,
2530 price_element_type_id,
2531 pricing_basis,
2532 supplier_seq_number,
2533 value,
2534 creation_date,
2535 created_by,
2536 last_update_date,
2537 last_updated_by,
2538 last_update_login)
2539 select
2540 g_auction_header_id,
2541 l_PRICE_ELEMENT_TYPE_ID(x),
2542 l_PRICING_BASIS(x),
2543 PBP.sequence,
2544 null,
2545 sysdate,
2546 g_user_id,
2547 sysdate,
2548 g_user_id,
2549 fnd_global.login_id
2550 from
2551 PON_BIDDING_PARTIES PBP
2552 where
2553 PBP.auction_header_id = g_auction_header_id and
2554 not exists (
2555 select 1
2556 from
2557 PON_LARGE_NEG_PF_VALUES pf_values
2558 where pf_values.auction_header_id = PBP.auction_header_id
2559 and pf_values.supplier_seq_number = PBP.sequence
2560 and pf_values.price_element_type_id = l_PRICE_ELEMENT_TYPE_ID(x)
2561 and pf_values.pricing_basis = l_PRICING_BASIS(x));
2562
2563 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2564 print_debug_log(l_module,'SYNCH_PF_VALUES_FOR_UPLOAD END g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2565 END IF;
2566
2567 --}
2568 END SYNCH_PF_VALUES_FOR_UPLOAD;
2569
2570
2571
2572 /*======================================================================
2573 PROCEDURE: SYNCH_FROM_INTERFACE PUBLIC
2574
2575 PARAMETERS:
2576 IN : p_batch_id NUMBER batch id for which the data needs to be
2577 copied, deleted or added from the interface
2578 tables in the transaction tables.
2579
2580 IN : p_auction_header_id NUMBER auction header id for which the data needs
2581 to be copied, deleted or added from the interface
2582 tables in the transaction tables.
2583
2584 IN : p_user_id NUMBER User id of the person who uploaded the spreadsheet
2585 This will be used to update the standard who columns.
2586 We will not use the fnd_global.user_id as this will also
2587 be called from the concurrent program.
2588
2589 IN : p_party_id NUMBER party id of the person who uploaded the spreadsheet.
2590
2591 COMMENT : This procedure will update/add or the lines and their children
2592 based on the records in the transaction tables for the batch id
2593 and auction header id passed as a parameter to the procedure.
2594 This will also set the global variables g_batch_id and
2595 g_auction_header_id.
2596 This will also re-number all the lines and set flags in
2597 pon_auction_item_prices_all.
2598 ======================================================================*/
2599 PROCEDURE SYNCH_FROM_INTERFACE(
2600 p_batch_id IN NUMBER,
2601 p_auction_header_id IN NUMBER,
2602 p_user_id IN NUMBER,
2603 p_party_id IN NUMBER,
2604 x_number_of_lines OUT NOCOPY NUMBER,
2605 x_max_disp_line OUT NOCOPY NUMBER,
2606 x_last_line_close_date OUT NOCOPY DATE,
2607 x_result OUT NOCOPY VARCHAR2, -- S: Success, F: failure
2608 x_error_code OUT NOCOPY VARCHAR2,
2609 x_error_message OUT NOCOPY VARCHAR2
2610 )is
2611
2612 CURSOR l_attachment_cursor
2613 IS
2614 SELECT paip.attachment_desc,
2615 paip.attachment_url,
2616 paip.auction_line_number
2617 FROM pon_item_prices_interface paip
2618 WHERE paip.auction_header_id = g_auction_header_id
2619 AND nvl(paip.action,g_add_action) <> g_delete_action
2620 AND paip.attachment_url IS NOT NULL
2621 AND paip.attachment_desc IS NOT NULL;
2622
2623 l_module CONSTANT VARCHAR2(32) := 'SYNCH_FROM_INTERFACE';
2624 l_number_of_lines pon_auction_headers_all.number_of_lines%TYPE;
2625 l_max_display_number pon_auction_headers_all.last_line_number%TYPE;
2626 l_progress varchar2(200);
2627
2628 -- for attachments
2629 l_sequence NUMBER :=0;
2630
2631 l_line_attribute_enabled_flag pon_auction_headers_all.line_attribute_enabled_flag%TYPE;
2632 l_contract_type pon_auction_headers_all.contract_type%TYPE;
2633 l_internal_name pon_auc_doctypes.internal_name%TYPE;
2634 l_is_global_agreement pon_auction_headers_all.global_agreement_flag%TYPE;
2635 l_price_element_enabled_flag pon_auction_headers_all.price_element_enabled_flag%TYPE;
2636 l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
2637 l_bid_ranking pon_auction_headers_all.bid_ranking%type;
2638 l_auction_round_number pon_auction_headers_all.auction_round_number%type;
2639 l_amendment_number pon_auction_headers_all.amendment_number%type;
2640 l_large_neg_enabled_flag pon_auction_headers_all.large_neg_enabled_flag%type;
2641 l_template_id pon_auction_headers_all.template_id%type;
2642 l_supplier_view_type pon_auction_headers_all.supplier_view_type%type;
2643 l_full_quantity_bid_code pon_auction_headers_all.full_quantity_bid_code%type;
2644 l_max_internal_line_num pon_auction_headers_all.max_internal_line_num%type;
2645 l_first_line_close_date pon_auction_headers_all.first_line_close_date%TYPE;
2646 l_staggered_closing_interval pon_auction_headers_all.staggered_closing_interval%TYPE;
2647
2648 BEGIN
2649 --{
2650 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2651 print_debug_log(l_module,'SYNCH_FROM_INTERFACE START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id);
2652 END IF;
2653
2654 -- Update Global variables
2655 g_batch_id := p_batch_id;
2656 g_auction_header_id := p_auction_header_id;
2657 g_user_id := p_user_id;
2658 INITIALIZE_LINE_ATTR_GROUP(p_party_id);
2659
2660 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2661 l_progress := 'INITIALIZE_LINE_ATTR_GROUP completed for p_auction_header_id = '||p_auction_header_id;
2662 END if;
2663
2664 select
2665 NVL(pah.line_attribute_enabled_flag,'Y'),
2666 pah.contract_type,
2667 doctypes.internal_name,
2668 NVL(pah.global_agreement_flag,'N'),
2669 nvl (pah.price_element_enabled_flag, 'Y'),
2670 pah.pf_type_allowed,
2671 bid_ranking,
2672 nvl(auction_round_number,0),
2673 nvl(amendment_number,0),
2674 large_neg_enabled_flag,
2675 nvl(template_id,0),
2676 supplier_view_type,
2677 full_quantity_bid_code,
2678 nvl(max_internal_line_num,0),
2679 first_line_close_date,
2680 staggered_closing_interval
2681 into
2682 l_line_attribute_enabled_flag,
2683 l_contract_type,
2684 l_internal_name,
2685 l_is_global_agreement,
2686 l_price_element_enabled_flag,
2687 l_pf_type_allowed,
2688 l_bid_ranking,
2689 l_auction_round_number,
2690 l_amendment_number,
2691 l_large_neg_enabled_flag,
2692 l_template_id,
2693 l_supplier_view_type,
2694 l_full_quantity_bid_code,
2695 l_max_internal_line_num,
2696 l_first_line_close_date,
2697 l_staggered_closing_interval
2698 from
2699 pon_auction_headers_all pah,
2700 pon_auc_doctypes doctypes
2701 where
2702 auction_header_id = p_auction_header_id
2703 and doctypes.doctype_id = pah.doctype_id;
2704
2705
2706 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2707 print_debug_log(l_module,'SYNCH_FROM_INTERFACE START g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id ||
2708 ' l_line_attribute_enabled_flag = '|| l_line_attribute_enabled_flag ||
2709 ' l_contract_type = '|| l_contract_type ||
2710 ' l_internal_name = '|| l_internal_name ||
2711 ' l_is_global_agreement = '|| l_is_global_agreement ||
2712 ' l_pf_type_allowed = '|| l_pf_type_allowed ||
2713 ' l_bid_ranking = '|| l_bid_ranking ||
2714 ' l_auction_round_number = '|| l_auction_round_number ||
2715 ' l_amendment_number = '|| l_amendment_number ||
2716 ' l_large_neg_enabled_flag = '|| l_large_neg_enabled_flag ||
2717 ' l_template_id = '|| l_template_id ||
2718 ' l_supplier_view_type = '|| l_supplier_view_type ||
2719 ' l_full_quantity_bid_code = '|| l_full_quantity_bid_code ||
2720 ' l_max_internal_line_num = '|| l_max_internal_line_num);
2721 END if;
2722
2723 g_line_attribute_enabled := 'N';
2724 g_price_differentials_flag := 'N';
2725 g_price_element_enabled_flag := 'N';
2726 g_attribute_score_enabled_flag := 'N';
2727 g_is_amendment := 'N';
2728 g_max_prev_line_num_plus_one := l_max_internal_line_num + 1;
2729
2730
2731 -- Determine if Line Attribute and Scores are applicable for this negotiation
2732 IF (l_line_attribute_enabled_flag = 'Y') THEN
2733
2734 g_line_attribute_enabled := 'Y';
2735
2736 IF (l_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN
2737 g_attribute_score_enabled_flag := 'Y';
2738 END IF;
2739
2740 END if;
2741
2742 -- Determine if Price Differentials are applicable for this negotiation
2743 IF ((l_internal_name = 'REQUEST_FOR_INFORMATION' OR
2744 l_is_global_agreement = 'Y')) --AND NVL (l_price_differentials_flag, 'Y') = 'Y')
2745 THEN
2746
2747 g_price_differentials_flag := 'Y';
2748
2749 END IF;
2750
2751 -- Determine if Price Elements/Factors are applicable for this negotiation
2752 IF (NVL (l_pf_type_allowed, 'NONE') <> 'NONE' AND
2753 l_price_element_enabled_flag = 'Y') THEN
2754
2755 g_price_element_enabled_flag := 'Y';
2756
2757 END IF;
2758
2759 IF (l_amendment_number > 0) THEN
2760 g_is_amendment := 'Y';
2761 END IF;
2762
2763
2764 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2765 print_debug_log(l_module,'SYNCH_FROM_INTERFACE g_batch_id = '||g_batch_id ||' g_auction_header_id '||g_auction_header_id ||
2766 ' g_line_attribute_enabled = '|| g_line_attribute_enabled ||
2767 ' g_attribute_score_enabled_flag = '|| g_attribute_score_enabled_flag ||
2768 ' g_price_differentials_flag = '|| g_price_differentials_flag ||
2769 ' g_price_element_enabled_flag = '|| g_price_element_enabled_flag );
2770 END if;
2771
2772 -- Delete update and add lines with their children based on the
2773 -- transaction table data.
2774 IF (l_auction_round_number > 0 or l_amendment_number > 0) THEN
2775 delete_lines_with_children;
2776 update_lines_with_children;
2777 END IF;
2778
2779 add_new_line_with_children;
2780
2781 --create URL Attachments
2782
2783 FOR l_attachment_record IN l_attachment_cursor LOOP
2784
2785 PON_OA_UTIL_PKG.create_url_attachment(
2786 p_seq_num => l_sequence,
2787 p_category_name => 'Vendor',
2788 p_document_description => l_attachment_record.attachment_desc,
2789 p_datatype_id => 5,
2790 p_url => l_attachment_record.attachment_url,
2791 p_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2792 p_pk1_value => g_auction_header_id,
2793 p_pk2_value => l_attachment_record.auction_line_number,
2794 p_pk3_value => NULL,
2795 p_pk4_value => NULL,
2796 p_pk5_value => NULL);
2797 END LOOP;
2798
2799 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2800 l_progress := 'Data copied for p_auction_header_id = '||p_auction_header_id;
2801 END if;
2802
2803 /* Set the following fields for all records in pon_auction_item_prices all
2804 HAS_ATTRIBUTES_FLAG,
2805 HAS_SHIPMENTS_FLAG
2806 HAS_PRICE_ELEMENTS_FLAG
2807 HAS_BUYER_PFS_FLAG
2808 HAS_PRICE_DIFFERENTIALS_FLAG
2809 HAS_QUANTITY_TIERS
2810 */
2811 PON_NEGOTIATION_PUBLISH_PVT.SET_ITEM_HAS_CHILDREN_FLAGS
2812 (p_auction_header_id => p_auction_header_id,
2813 p_close_bidding_date => null);
2814
2815 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2816 l_progress := 'PON_NEGOTIATION_PUBLISH_PVT.SET_ITEM_HAS_CHILDREN_FLAGS p_auction_header_id = '||p_auction_header_id;
2817 END if;
2818
2819 /*
2820 * In case of staggered auctions we need to set the close date on all the lines.
2821 * Before a negotiation is published the only way to determine if the
2822 * auction is staggered or not is by ensuring that both
2823 * first_line_close_date and staggered_closing_interval are not null.
2824 */
2825 if (l_first_line_close_date is not null and l_staggered_closing_interval is not null) then
2826 PON_NEGOTIATION_HELPER_PVT.UPDATE_STAG_LINES_CLOSE_DATES (
2827 x_result => x_result,
2828 x_error_code => x_error_code,
2829 x_error_message => x_error_message,
2830 p_auction_header_id => p_auction_header_id,
2831 p_first_line_close_date => l_first_line_close_date,
2832 p_staggered_closing_interval => l_staggered_closing_interval,
2833 p_start_disp_line_number => 0,
2834 x_last_line_close_date => x_last_line_close_date);
2835 end if;
2836
2837 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2838 l_progress := 'PON_NEGOTIATION_PUBLISH_PVT.UPDATE_STAG_LINES_CLOSE_DATES' ||
2839 'l_first_line_close_date = ' || to_char(l_first_line_close_date, 'dd-mon-yyyy hh24:mi:ss') ||
2840 'l_staggered_closing_interval = ' || l_staggered_closing_interval ||
2841 'x_last_line_close_date = ' || to_char(x_last_line_close_date, 'dd-mon-yyyy hh24:mi:ss');
2842 END if;
2843
2844 IF (l_auction_round_number > 0 or l_amendment_number > 0) THEN
2845
2846 -- Call the renumber API
2847 PON_NEGOTIATION_HELPER_PVT.RENUMBER_LINES (
2848 x_result => x_result,
2849 x_error_code => x_error_message,
2850 x_error_message => x_error_code,
2851 p_auction_header_id => p_auction_header_id,
2852 p_min_disp_line_number_parent => 0,
2853 p_min_disp_line_number_child => 0,
2854 p_min_child_parent_line_num => 0,
2855 x_last_line_number => l_max_display_number);
2856
2857 -- Determine total number of lines
2858 SELECT count(line_number)
2859 INTO l_number_of_lines
2860 FROM pon_auction_item_prices_all
2861 WHERE auction_header_id = g_auction_header_id;
2862
2863 else
2864
2865 -- If this is not an amendment or new round then we need to synch the PON_LARGE_NEG_PF_VALUES
2866 -- If a template is applied
2867 IF (g_price_element_enabled_flag = 'Y' and
2868 l_large_neg_enabled_flag = 'Y' and
2869 l_template_id <> 0) THEN
2870
2871 SYNCH_PF_VALUES_FOR_UPLOAD;
2872
2873 END IF;
2874
2875
2876 select
2877 COUNT(line_number),
2878 MAX (DECODE (paip.group_type, 'LOT_LINE', 0, 'GROUP_LINE', 0, paip.sub_line_sequence_number))
2879 into
2880 l_number_of_lines,
2881 l_max_display_number
2882 FROM
2883 pon_auction_item_prices_all paip
2884 where
2885 paip.auction_header_id = p_auction_header_id;
2886 end if;
2887
2888
2889 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2890 l_progress := 'PON_NEGOTIATION_PUBLISH_PVT.RENUMBER_LINES p_auction_header_id = '||p_auction_header_id||' l_max_display_number = '||l_max_display_number;
2891 END if;
2892
2893
2894 -- Clear the interface tables
2895 -- What is there is an error? need to clear these always
2896 delete from pon_item_prices_interface where batch_id = g_batch_id;
2897 delete from pon_auc_attributes_interface where batch_id = g_batch_id;
2898 delete from pon_auc_payments_interface where batch_id = g_batch_id;
2899 delete from pon_auc_price_differ_int where batch_id = g_batch_id;
2900 delete from pon_auc_price_elements_int where batch_id = g_batch_id;
2901
2902 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2903 l_progress := 'delete completed p_auction_header_id = '||p_auction_header_id;
2904 END if;
2905
2906 SELECT count(line_number)
2907 INTO l_number_of_lines
2908 FROM pon_auction_item_prices_all
2909 WHERE auction_header_id = g_auction_header_id;
2910
2911 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2912 l_progress := 'delete completed g_batch_id = '||g_batch_id ||
2913 ' g_auction_header_id ='||g_auction_header_id;
2914 END if;
2915
2916 x_number_of_lines := l_number_of_lines;
2917 x_max_disp_line := l_max_display_number;
2918
2919 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2920 l_progress := 'delete completed g_batch_id = '||g_batch_id ||
2921 ' g_auction_header_id ='||g_auction_header_id;
2922
2923 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2924 print_debug_log(l_module,'SYNCH_FROM_INTERFACE END g_batch_id = '||g_batch_id ||
2925 ' g_auction_header_id ='||g_auction_header_id);
2926 END IF;
2927
2928 END if;
2929
2930
2931 x_result := 'S';
2932
2933 EXCEPTION
2934 WHEN OTHERS THEN
2935 x_result := 'F';
2936 x_error_code := SQLCODE;
2937 x_error_message := SUBSTR(SQLERRM, 1, 100);
2938 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2939 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
2940 END if;
2941
2942 --}
2943 END SYNCH_FROM_INTERFACE ;
2944
2945
2946
2947 /*======================================================================
2948 PROCEDURE: SYNCH_PAYMENTS_FROM_INTERFACE PUBLIC
2949
2950 PARAMETERS:
2951 IN : p_batch_id NUMBER batch id for which the data needs to
2952 be copied, deleted or added from the
2953 interface tables in the transaction
2954 tables.
2955
2956 IN : p_auction_header_id NUMBER auction header id for which the data
2957 needs to be copied, deleted or added
2958 from the interface tables in the
2959 transaction tables.
2960
2961 COMMENT : This procedure will update/add the payments based on the records
2962 in the transaction tables for the batch id
2963 and auction header id passed as a parameter to the procedure.
2964 ======================================================================*/
2965 PROCEDURE SYNCH_PAYMENTS_FROM_INTERFACE(
2966 p_batch_id IN NUMBER,
2967 p_auction_header_id IN NUMBER,
2968 x_result OUT NOCOPY VARCHAR2, -- S: Success, E: failure
2969 x_error_code OUT NOCOPY VARCHAR2,
2970 x_error_message OUT NOCOPY VARCHAR2
2971 )is
2972
2973 l_module CONSTANT VARCHAR2(32) := 'SYNCH_PAYMENTS_FROM_INTERFACE';
2974 l_progress varchar2(200);
2975 l_sequence NUMBER :=0;
2976 l_previous_line_number pon_auction_item_prices_all.document_disp_line_number%TYPE;
2977 l_prev_amend_auc_id pon_auction_headers_all.auction_header_id_prev_amend%TYPE;
2978 l_supplier_modify_flag pon_auction_headers_all.supplier_enterable_pymt_flag%TYPE;
2979
2980
2981 CURSOR l_attachment_cursor
2982 IS
2983 SELECT papi.attachment_desc,
2984 papi.attachment_url,
2985 paps.payment_id,
2986 paps.auction_header_id,
2987 paps.line_number,
2988 papi.document_disp_line_number
2989 FROM pon_auc_payments_interface papi,
2990 pon_auction_item_prices_all pai,
2991 pon_auc_payments_shipments paps
2992 WHERE papi.auction_header_id = pai.auction_header_id
2993 AND papi.document_disp_line_number = pai.document_disp_line_number
2994 AND paps.auction_header_id = pai.auction_header_id
2995 AND paps.line_number = pai.line_number
2996 AND paps.payment_display_number = papi.payment_display_number
2997 AND papi.batch_id = p_batch_id
2998 AND papi.attachment_desc IS NOT NULL;
2999
3000 BEGIN
3001 --{
3002 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3003 print_debug_log(l_module,'SYNCH_PAYMENTS_FROM_INTERFACE START p_batch_id = '||p_batch_id ||' p_auction_header_id '||p_auction_header_id);
3004 END IF;
3005
3006 -- If the payment_display_number and line_number combination already exists
3007 -- in the transaction table update the record
3008
3009 -- If the payment_display_number and line_number combination does not exist
3010 -- in the transaction table insert the record
3011
3012 MERGE INTO PON_AUC_PAYMENTS_SHIPMENTS paps
3013 USING (SELECT ppi.BATCH_ID,
3014 ppi.INTERFACE_LINE_ID,
3015 ppi.AUCTION_HEADER_ID,
3016 pai.line_number,
3017 ppi.PAYMENT_DISPLAY_NUMBER,
3018 ppi.PAYMENT_DESCRIPTION,
3019 fl.lookup_code PAYMENT_TYPE_CODE,
3020 ppi.DOCUMENT_DISP_LINE_NUMBER,
3021 DECODE(fl.lookup_code, 'RATE', ppi.QUANTITY, NULL) QUANTITY,
3022 DECODE(fl.lookup_code, 'RATE', uom.uom_code, NULL) UOM_CODE,
3023 ppi.TARGET_PRICE,
3024 ppi.NEED_BY_DATE,
3025 DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, hrl.location_id) SHIP_TO_LOCATION_ID,
3026 DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, fu.user_id) WORK_APPROVER_USER_ID,
3027 ppi.NOTE_TO_BIDDERS,
3028 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, pro.project_id)) PROJECT_ID,
3029 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL,
3030 (SELECT task_id FROM PA_TASKS_EXPEND_V task WHERE task.task_number = ppi.project_task_number AND task.project_number=ppi.project_number))) PROJECT_TASK_ID,
3031 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, (SELECT award_id FROM GMS_AWARDS_BASIC_V award WHERE award.award_number = ppi.project_award_number))) PROJECT_AWARD_ID,
3032 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, ppi.PROJECT_EXPENDITURE_TYPE)) PROJECT_EXPENDITURE_TYPE,
3033 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, porg.organization_id)) PROJECT_EXP_ORGANIZATION_ID,
3034 DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, ppi.PROJECT_EXPENDITURE_ITEM_DATE)) PROJECT_EXPENDITURE_ITEM_DATE
3035 FROM PON_AUC_PAYMENTS_INTERFACE ppi,
3036 PON_AUCTION_ITEM_PRICES_ALL pai,
3037 PON_AUCTION_HEADERS_ALL pah,
3038 FND_USER fu,
3039 HR_LOCATIONS_ALL hrl,
3040 MTL_UNITS_OF_MEASURE uom,
3041 PO_LOOKUP_CODES fl,
3042 PA_PROJECTS_EXPEND_V pro,
3043 PA_ORGANIZATIONS_EXPEND_V porg
3044 WHERE ppi.auction_header_id = pai.auction_header_id
3045 AND ppi.batch_id = p_batch_id
3046 AND ppi.document_disp_line_number = pai.document_disp_line_number
3047 AND ppi.auction_header_id = p_auction_header_id
3048 AND pah.auction_header_id = pai.auction_header_id
3049 AND pai.group_type NOT IN ('GROUP', 'LOT_LINE')
3050 AND ppi.ship_to_location_code = hrl.location_code(+)
3051 AND ppi.work_approver_user_name = fu.user_name(+)
3052 AND ppi.project_number = pro.project_number(+)
3053 AND ppi.project_exp_organization_name = porg.name(+)
3054 AND ppi.unit_of_measure = uom.unit_of_measure_tl(+)
3055 AND uom.language (+) = userenv('LANG')
3056 AND ppi.payment_type = fl.displayed_field (+)
3057 AND fl.lookup_type(+) = 'PAYMENT TYPE') papi
3058 ON( paps.payment_display_number = papi.payment_display_number
3059 AND paps.line_number = papi.line_number
3060 AND paps.auction_header_id = papi.auction_header_id)
3061 WHEN MATCHED THEN
3062 UPDATE SET paps.payment_description = papi.payment_description,
3063 paps.payment_type_code = papi.payment_type_code,
3064 paps.quantity = papi.quantity,
3065 paps.uom_code = papi.uom_code,
3066 paps.target_price = papi.target_price,
3067 paps.need_by_date = papi.need_by_date,
3068 paps.ship_to_location_id = papi.ship_to_location_id,
3069 paps.work_approver_user_id = papi.work_approver_user_id,
3070 paps.note_to_bidders = papi.note_to_bidders,
3071 paps.project_id = papi.project_id,
3072 paps.project_task_id = papi.project_task_id,
3073 paps.project_award_id = papi.project_award_id,
3074 paps.project_exp_organization_id = papi.project_exp_organization_id,
3075 paps.project_expenditure_type = papi.project_expenditure_type,
3076 paps.project_expenditure_item_date = papi.project_expenditure_item_date,
3077 paps.last_update_date = sysdate,
3078 paps.last_updated_by = fnd_global.user_id,
3079 paps.last_update_login = fnd_global.login_id
3080 WHEN NOT MATCHED THEN
3081 INSERT (
3082 PAYMENT_ID ,
3083 AUCTION_HEADER_ID ,
3084 LINE_NUMBER ,
3085 PAYMENT_DISPLAY_NUMBER ,
3086 PAYMENT_DESCRIPTION ,
3087 PAYMENT_TYPE_CODE ,
3088 SHIP_TO_LOCATION_ID ,
3089 QUANTITY ,
3090 UOM_CODE ,
3091 TARGET_PRICE ,
3092 NEED_BY_DATE ,
3093 WORK_APPROVER_USER_ID ,
3094 NOTE_TO_BIDDERS ,
3095 PROJECT_ID ,
3096 PROJECT_TASK_ID ,
3097 PROJECT_AWARD_ID ,
3098 PROJECT_EXPENDITURE_TYPE ,
3099 PROJECT_EXP_ORGANIZATION_ID ,
3100 PROJECT_EXPENDITURE_ITEM_DATE ,
3101 CREATION_DATE ,
3102 CREATED_BY ,
3103 LAST_UPDATE_DATE ,
3104 LAST_UPDATED_BY ,
3105 LAST_UPDATE_LOGIN
3106 )
3107 VALUES (
3108 PON_AUC_PAYMENTS_SHIPMENTS_S1.nextval ,
3109 papi.AUCTION_HEADER_ID ,
3110 papi.LINE_NUMBER ,
3111 papi.PAYMENT_DISPLAY_NUMBER ,
3112 papi.PAYMENT_DESCRIPTION ,
3113 papi.PAYMENT_TYPE_CODE ,
3114 papi.SHIP_TO_LOCATION_ID ,
3115 papi.QUANTITY ,
3116 papi.UOM_CODE ,
3117 papi.TARGET_PRICE ,
3118 papi.NEED_BY_DATE ,
3119 papi.WORK_APPROVER_USER_ID ,
3120 papi.NOTE_TO_BIDDERS ,
3121 papi.PROJECT_ID ,
3122 papi.PROJECT_TASK_ID ,
3123 papi.PROJECT_AWARD_ID ,
3124 papi.PROJECT_EXPENDITURE_TYPE ,
3125 papi.PROJECT_EXP_ORGANIZATION_ID ,
3126 papi.PROJECT_EXPENDITURE_ITEM_DATE ,
3127 SYSDATE ,
3128 fnd_global.user_id ,
3129 SYSDATE ,
3130 fnd_global.user_id ,
3131 fnd_global.login_id
3132 ) ;
3133
3134
3135 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3136 l_progress := 'Merge into pon_auc_payments_shipments is successful for auction_header_id = '||p_auction_header_id;
3137 END if;
3138
3139 --create URL Attachments
3140 FOR l_attachment_record IN l_attachment_cursor LOOP
3141
3142 IF l_previous_line_number IS NULL OR l_attachment_record.document_disp_line_number <> l_previous_line_number THEN
3143 l_sequence := 1;
3144 l_previous_line_number := l_attachment_record.document_disp_line_number;
3145 ELSE
3146 l_sequence := l_sequence+1;
3147
3148 END IF;
3149
3150 PON_OA_UTIL_PKG.create_url_attachment(
3151 p_seq_num => l_sequence,
3152 p_category_name => 'Vendor',
3153 p_document_description => l_attachment_record.attachment_desc,
3154 p_datatype_id => 5,
3155 p_url => l_attachment_record.attachment_url,
3156 p_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
3157 p_pk1_value => l_attachment_record.auction_header_id,
3158 p_pk2_value => l_attachment_record.line_number,
3159 p_pk3_value => l_attachment_record.payment_id,
3160 p_pk4_value => NULL,
3161 p_pk5_value => NULL);
3162 END LOOP;
3163
3164 -- Clear the interface tables
3165 -- What is there is an error? need to clear these always
3166 delete from pon_auc_payments_interface where batch_id = p_batch_id;
3167
3168 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3169 l_progress := 'delete from pon_auc_payments_interface completed for p_batch_id = '||p_batch_id||'p_auction_header_id = '||p_auction_header_id;
3170 END if;
3171
3172 --Mark which lines are changed during amendment
3173 BEGIN
3174 SELECT pah.auction_header_id_prev_amend,
3175 pah.supplier_enterable_pymt_flag
3176 INTO l_prev_amend_auc_id,
3177 l_supplier_modify_flag
3178 FROM PON_AUCTION_HEADERS_ALL pah
3179 WHERE auction_header_id = p_auction_header_id;
3180
3181 IF l_prev_amend_auc_id IS NOT NULL THEN
3182 --Update pon_auction_item_prices_all to set lastupdatedate
3183 -- for changed payments
3184 UPDATE pon_auction_item_prices_all al
3185 SET modified_flag = 'Y'
3186 , modified_date = SYSDATE
3187 , last_update_date = SYSDATE
3188 , last_updated_by = fnd_global.user_id
3189 , last_update_login = fnd_global.login_id
3190 WHERE al.auction_header_id = p_auction_header_id
3191 AND (EXISTS (
3192 SELECT 1
3193 FROM pon_auc_payments_shipments pap1,
3194 pon_auc_payments_shipments pap2
3195 WHERE pap1.auction_header_id = al.auction_header_id
3196 AND pap1.line_number = al.line_number
3197 AND pap1.payment_display_number = pap2.payment_display_number
3198 AND pap2.auction_header_id = l_prev_amend_auc_id
3199 AND pap1.line_number = pap2.line_number
3200 AND (nvl(pap1.payment_description,FND_API.G_NULL_CHAR) <> NVL(pap2.payment_description, FND_API.G_NULL_CHAR)
3201 OR nvl(pap1.payment_type_code,FND_API.G_NULL_CHAR) <> nvl(pap2.payment_type_code,FND_API.G_NULL_CHAR)
3202 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.ship_to_location_id,fnd_api.G_NULL_NUM))
3203 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.ship_to_location_id,fnd_api.G_NULL_NUM))
3204 OR nvl(pap1.quantity,fnd_api.G_NULL_NUM) <> nvl(pap2.quantity,fnd_api.G_NULL_NUM)
3205 OR nvl(pap1.uom_code,FND_API.G_NULL_CHAR) <> nvl(pap2.uom_code,FND_API.G_NULL_CHAR)
3206 OR nvl(pap1.target_price,fnd_api.G_NULL_NUM) <> nvl(pap2.target_price,fnd_api.G_NULL_NUM)
3207 OR nvl(pap1.need_by_date,fnd_api.G_NULL_DATE) <> nvl(pap2.need_by_date,fnd_api.G_NULL_DATE)
3208 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.work_approver_user_id,fnd_api.G_NULL_NUM))
3209 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.work_approver_user_id,fnd_api.G_NULL_NUM))
3210 OR DECODE(l_supplier_modify_flag, 'Y','Y',nvl(pap1.note_to_bidders,FND_API.G_NULL_CHAR))
3211 <> DECODE(l_supplier_modify_flag, 'Y','Y',nvl(pap2.note_to_bidders,FND_API.G_NULL_CHAR))
3212 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.project_id,fnd_api.G_NULL_NUM))
3213 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.project_id,fnd_api.G_NULL_NUM))
3214 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.project_task_id,fnd_api.G_NULL_NUM))
3215 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.project_task_id,fnd_api.G_NULL_NUM))
3216 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.project_award_id,fnd_api.G_NULL_NUM))
3217 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.project_award_id,fnd_api.G_NULL_NUM))
3218 OR DECODE(l_supplier_modify_flag, 'Y','Y',nvl(pap1.project_expenditure_type,FND_API.G_NULL_CHAR))
3219 <> DECODE(l_supplier_modify_flag, 'Y','Y',nvl(pap2.project_expenditure_type,FND_API.G_NULL_CHAR))
3220 OR DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap1.project_exp_organization_id,fnd_api.G_NULL_NUM))
3221 <> DECODE(l_supplier_modify_flag, 'Y',1,nvl(pap2.project_exp_organization_id,fnd_api.G_NULL_NUM))
3222 OR DECODE(l_supplier_modify_flag, 'Y',sysdate,nvl(pap1.project_expenditure_item_date,fnd_api.G_NULL_DATE))
3223 <> DECODE(l_supplier_modify_flag, 'Y',sysdate,nvl(pap2.project_expenditure_item_date,fnd_api.G_NULL_DATE)))
3224
3225 OR
3226 EXISTS (
3227 SELECT 1
3228 FROM pon_auc_payments_shipments pap1
3229 WHERE pap1.auction_header_id = al.auction_header_id
3230 AND pap1.line_number = al.line_number
3231 AND NOT EXISTS (
3232 SELECT 1
3233 FROM pon_auc_payments_shipments pap2
3234 WHERE pap2.auction_header_id = l_prev_amend_auc_id
3235 AND pap2.line_number = pap1.line_number
3236 AND pap2.payment_display_number = pap1.payment_display_number
3237 )))
3238
3239 );
3240
3241 END IF;
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244
3245 x_result := 'E';
3246 x_error_code := SQLCODE;
3247 x_error_message := SUBSTR(SQLERRM, 1, 100);
3248 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3249 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
3250 END if;
3251 END;
3252
3253 --End of mark lines as changed
3254
3255 x_result := 'S';
3256
3257 EXCEPTION
3258 WHEN OTHERS THEN
3259 x_result := 'E';
3260 x_error_code := SQLCODE;
3261 x_error_message := SUBSTR(SQLERRM, 1, 100);
3262 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3263 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
3264 END if;
3265
3266 --}
3267 END SYNCH_PAYMENTS_FROM_INTERFACE ;
3268
3269
3270 /*======================================================================
3271 PROCEDURE: UPDATE_CONCURRENT_ERRORS PUBLIC
3272
3273 PARAMETERS:
3274 IN : p_batch_id NUMBER batch id for which the errors are to
3275 be copied
3276
3277 IN : p_auction_header_id NUMBER auction_header_id for which the file
3278 was uploaded
3279
3280 IN : p_request_id NUMBER Request id of the cocurrent program
3281
3282 COMMENT : This procedure will copy all the errors into pl/sql tables,
3283 ROLLBACK the transaction and then copy the errors back to the database.
3284 This is ONLY CALLED FROM THE CONCURRENT PROGRAM.
3285 ======================================================================*/
3286 PROCEDURE UPDATE_CONCURRENT_ERRORS (
3287 p_batch_id IN NUMBER,
3288 p_auction_header_id IN NUMBER,
3289 x_result OUT NOCOPY VARCHAR2, -- S: Success, E: failure
3290 x_error_code OUT NOCOPY VARCHAR2,
3291 x_error_message OUT NOCOPY VARCHAR2
3292 ) is
3293
3294 l_INTERFACE_TYPE PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3295 l_COLUMN_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3296 l_TABLE_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3297 l_INTERFACE_LINE_ID PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3298 l_ERROR_MESSAGE_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3299 l_ERROR_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR100_TYPE;
3300 l_CREATED_BY PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3301 l_CREATION_DATE PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
3302 l_LAST_UPDATED_BY PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3303 l_LAST_UPDATE_DATE PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
3304 l_LAST_UPDATE_LOGIN PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3305 l_ENTITY_TYPE PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3306 l_ENTITY_ATTR_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3307 l_ERROR_VALUE_DATE PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
3308 l_ERROR_VALUE_NUMBER PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3309 l_ERROR_VALUE_DATATYPE PON_NEG_COPY_DATATYPES_GRP.VARCHAR20_TYPE;
3310 l_BID_NUMBER PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3311 l_LINE_NUMBER PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3312 l_ATTRIBUTE_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR300_TYPE;
3313 l_PRICE_ELEMENT_TYPE_ID PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3314 l_SHIPMENT_NUMBER PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3315 l_PRICE_DIFFERENTIAL_NUMBER PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3316 l_TOKEN1_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3317 l_TOKEN1_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3318 l_TOKEN2_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3319 l_TOKEN2_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3320 l_TOKEN3_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3321 l_TOKEN3_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3322 l_TOKEN4_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3323 l_TOKEN4_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3324 l_TOKEN5_NAME PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
3325 l_TOKEN5_VALUE PON_NEG_COPY_DATATYPES_GRP.VARCHAR2000_TYPE;
3326
3327 l_progress varchar2(200);
3328 l_module CONSTANT VARCHAR2(30) := 'SYNCH_PAYMENTS_FROM_INTERFACE';
3329
3330 BEGIN
3331
3332 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3333 print_debug_log(l_module,'UPDATE_CONCURRENT_ERRORS Start g_batch_id = '||g_batch_id ||
3334 ' g_auction_header_id ='||g_auction_header_id);
3335 END if;
3336
3337 SELECT
3338 NVL(INTERFACE_TYPE,'ITEMUPLOAD'),
3339 COLUMN_NAME,
3340 TABLE_NAME,
3341 INTERFACE_LINE_ID+1,
3342 ERROR_MESSAGE_NAME,
3343 ERROR_VALUE,
3344 CREATED_BY,
3345 CREATION_DATE,
3346 LAST_UPDATED_BY,
3347 LAST_UPDATE_DATE,
3348 LAST_UPDATE_LOGIN,
3349 nvl(ENTITY_TYPE,'TXT'),
3350 ENTITY_ATTR_NAME,
3351 ERROR_VALUE_DATE,
3352 ERROR_VALUE_NUMBER,
3353 ERROR_VALUE_DATATYPE,
3354 BID_NUMBER,
3355 LINE_NUMBER,
3356 ATTRIBUTE_NAME,
3357 PRICE_ELEMENT_TYPE_ID,
3358 SHIPMENT_NUMBER,
3359 PRICE_DIFFERENTIAL_NUMBER,
3360 TOKEN1_NAME,
3361 TOKEN1_VALUE,
3362 TOKEN2_NAME,
3363 TOKEN2_VALUE,
3364 TOKEN3_NAME,
3365 TOKEN3_VALUE,
3366 TOKEN4_NAME,
3367 TOKEN4_VALUE,
3368 TOKEN5_NAME,
3369 TOKEN5_VALUE
3370 BULK COLLECT INTO
3371 l_INTERFACE_TYPE,
3372 l_COLUMN_NAME,
3373 l_TABLE_NAME,
3374 l_INTERFACE_LINE_ID,
3375 l_ERROR_MESSAGE_NAME,
3376 l_ERROR_VALUE,
3377 l_CREATED_BY,
3378 l_CREATION_DATE,
3379 l_LAST_UPDATED_BY,
3380 l_LAST_UPDATE_DATE,
3381 l_LAST_UPDATE_LOGIN,
3382 l_ENTITY_TYPE,
3383 l_ENTITY_ATTR_NAME,
3384 l_ERROR_VALUE_DATE,
3385 l_ERROR_VALUE_NUMBER,
3386 l_ERROR_VALUE_DATATYPE,
3387 l_BID_NUMBER,
3388 l_LINE_NUMBER,
3389 l_ATTRIBUTE_NAME,
3390 l_PRICE_ELEMENT_TYPE_ID,
3391 l_SHIPMENT_NUMBER,
3392 l_PRICE_DIFFERENTIAL_NUMBER,
3393 l_TOKEN1_NAME,
3394 l_TOKEN1_VALUE,
3395 l_TOKEN2_NAME,
3396 l_TOKEN2_VALUE,
3397 l_TOKEN3_NAME,
3398 l_TOKEN3_VALUE,
3399 l_TOKEN4_NAME,
3400 l_TOKEN4_VALUE,
3401 l_TOKEN5_NAME,
3402 l_TOKEN5_VALUE
3403 FROM PON_INTERFACE_ERRORS
3404 WHERE BATCH_ID = p_batch_id
3405 order by interface_line_id;
3406
3407 l_progress := 'PL/SQL Table of Records fetched';
3408
3409 rollback;
3410
3411 l_progress := 'Rollback completed';
3412
3413 FORALL x IN 1..l_INTERFACE_TYPE.COUNT
3414 INSERT INTO PON_INTERFACE_ERRORS (
3415 INTERFACE_TYPE,
3416 COLUMN_NAME,
3417 TABLE_NAME,
3418 BATCH_ID,
3419 INTERFACE_LINE_ID,
3420 ERROR_MESSAGE_NAME,
3421 ERROR_VALUE,
3422 CREATED_BY,
3423 CREATION_DATE,
3424 LAST_UPDATED_BY,
3425 LAST_UPDATE_DATE,
3426 LAST_UPDATE_LOGIN,
3427 REQUEST_ID,
3428 ENTITY_TYPE,
3429 ENTITY_ATTR_NAME,
3430 ERROR_VALUE_DATE,
3431 ERROR_VALUE_NUMBER,
3432 ERROR_VALUE_DATATYPE,
3433 AUCTION_HEADER_ID,
3434 BID_NUMBER,
3435 LINE_NUMBER,
3436 ATTRIBUTE_NAME,
3437 PRICE_ELEMENT_TYPE_ID,
3438 SHIPMENT_NUMBER,
3439 PRICE_DIFFERENTIAL_NUMBER,
3440 EXPIRATION_DATE,
3441 TOKEN1_NAME,
3442 TOKEN1_VALUE,
3443 TOKEN2_NAME,
3444 TOKEN2_VALUE,
3445 TOKEN3_NAME,
3446 TOKEN3_VALUE,
3447 TOKEN4_NAME,
3448 TOKEN4_VALUE,
3449 TOKEN5_NAME,
3450 TOKEN5_VALUE)
3451 VALUES
3452 (
3453 l_INTERFACE_TYPE(x),
3454 l_COLUMN_NAME(x),
3455 l_TABLE_NAME(x),
3456 p_batch_id,
3457 l_INTERFACE_LINE_ID(x),
3458 l_ERROR_MESSAGE_NAME(x),
3459 l_ERROR_VALUE(x),
3460 l_CREATED_BY(x),
3461 l_CREATION_DATE(x),
3462 l_LAST_UPDATED_BY(x),
3463 l_LAST_UPDATE_DATE(x),
3464 l_LAST_UPDATE_LOGIN(x),
3465 fnd_global.conc_request_id,
3466 l_ENTITY_TYPE(x),
3467 l_ENTITY_ATTR_NAME(x),
3468 l_ERROR_VALUE_DATE(x),
3469 l_ERROR_VALUE_NUMBER(x),
3470 l_ERROR_VALUE_DATATYPE(x),
3471 p_auction_header_id,
3472 l_BID_NUMBER(x),
3473 l_LINE_NUMBER(x),
3474 l_ATTRIBUTE_NAME(x),
3475 l_PRICE_ELEMENT_TYPE_ID(x),
3476 l_SHIPMENT_NUMBER(x),
3477 l_PRICE_DIFFERENTIAL_NUMBER(x),
3478 sysdate+7,
3479 l_TOKEN1_NAME(x),
3480 l_TOKEN1_VALUE(x),
3481 l_TOKEN2_NAME(x),
3482 l_TOKEN2_VALUE(x),
3483 l_TOKEN3_NAME(x),
3484 l_TOKEN3_VALUE(x),
3485 l_TOKEN4_NAME(x),
3486 l_TOKEN4_VALUE(x),
3487 l_TOKEN5_NAME(x),
3488 l_TOKEN5_VALUE(x)
3489 );
3490
3491 l_progress := 'Records inserted';
3492
3493
3494 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3495 print_debug_log(l_module,'UPDATE_CONCURRENT_ERRORS END g_batch_id = '||g_batch_id ||
3496 ' g_auction_header_id ='||g_auction_header_id);
3497 END if;
3498
3499 x_result := 'S';
3500
3501 EXCEPTION
3502 WHEN OTHERS THEN
3503 x_result := 'F';
3504 x_error_code := SQLCODE;
3505 x_error_message := SUBSTR(SQLERRM, 1, 100);
3506 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3507 print_error_log(l_module, 'EXCEPTION - l_progress='|| l_progress ||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
3508 END if;
3509
3510
3511 END UPDATE_CONCURRENT_ERRORS;
3512
3513 /*======================================================================
3514 PROCEDURE: PRINT_DEBUG_LOG PRIVATE
3515 PARAMETERS:
3516 COMMENT : This procedure is used to print debug messages into
3517 FND logs
3518 ======================================================================*/
3519 PROCEDURE print_debug_log(p_module IN VARCHAR2,
3520 p_message IN VARCHAR2)
3521 IS
3522
3523 BEGIN
3524
3525 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3526 FND_LOG.string(log_level => FND_LOG.level_statement,
3527 module => g_module_prefix || p_module,
3528 message => p_message);
3529 END if;
3530
3531 END;
3532
3533 /*======================================================================
3534 PROCEDURE: PRINT_ERROR_LOG PRIVATE
3535 PARAMETERS:
3536 COMMENT : This procedure is used to print unexpected exceptions or
3537 error messages into FND logs
3538 ======================================================================*/
3539
3540 PROCEDURE print_error_log(p_module IN VARCHAR2,
3541 p_message IN VARCHAR2)
3542 IS
3543 BEGIN
3544
3545 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3546 FND_LOG.string(log_level => FND_LOG.level_procedure,
3547 module => g_module_prefix || p_module,
3548 message => p_message);
3549 END if;
3550
3551 END;
3552
3553
3554 END PON_CP_INTRFAC_TO_TRANSACTION;