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