[Home] [Help]
PACKAGE BODY: APPS.PON_ATTR_MAPPING
Source
1 PACKAGE BODY PON_ATTR_MAPPING AS
2 /* $Header: PONATMPB.pls 120.4.12020000.6 2013/03/19 23:22:47 atjen ship $ */
3 PROCEDURE Get_Supp_Related_Class_Codes (
4 p_vendor_id IN NUMBER
5 , p_party_id IN NUMBER
6 , p_object_id IN NUMBER
7 , p_data_level_name IN VARCHAR2
8 , p_attr_group_id IN NUMBER
9 , p_class_code_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
10 ) AS
11
12 class_code VARCHAR2(3000) := NULL;
13 scheme_code VARCHAR2(5) := NULL;
14 return_code VARCHAR2(3000) := NULL;
15 end_position NUMBER := 0;
16 start_position NUMBER := 1;
17 segment_definition VARCHAR2(3000) := NULL;
18 segment_count NUMBER := 0;
19 po_category_set_id NUMBER := 0;
20 x_delimiter VARCHAR2(10) := NULL;
21 st_code VARCHAR2(40) := NULL;
22
23 CURSOR class_cursor(x_object_id NUMBER, x_data_level_name VARCHAR2, x_attr_group_id NUMBER)
24 IS
25 SELECT classification_code
26 FROM EGO_OBJ_AG_ASSOCS_B
27 WHERE object_id = x_object_id
28 AND data_level = x_data_level_name
29 AND attr_group_id = x_attr_group_id;
30
31 CURSOR bc_cursor(x_party_id NUMBER)
32 IS
33 SELECT 'BC:'||lookup_code AS Code
34 FROM pos_bus_class_attr
35 WHERE party_id = x_party_id
36 AND start_date_active <= SYSDATE
37 AND NVL(end_date_active, SYSDATE) >= SYSDATE
38 AND status = 'A';
39
40 CURSOR ac_cursor(x_party_id NUMBER)
41 IS
42 SELECT DISTINCT 'AC:'||hzl.country AS Code
43 FROM hz_locations hzl, hz_party_sites hzps
44 WHERE hzps.party_id = x_party_id
45 AND hzl.location_id = hzps.location_id;
46
47 CURSOR ap_cursor(x_party_id NUMBER, x_vendor_id NUMBER)
48 IS
49 SELECT 'AP:' || site_use_type AS Code
50 FROM hz_party_sites hzps, hz_party_site_uses hzpsu
51 WHERE hzps.party_id = x_party_id
52 AND hzpsu.party_site_id = hzps.party_site_id
53 AND hzpsu.status = 'A'
54 UNION
55 SELECT 'AP:PURCHASING' as code
56 FROM ap_supplier_sites_all
57 WHERE vendor_id = x_vendor_id
58 AND purchasing_site_flag = 'Y'
59 UNION
60 SELECT 'AP:PAY'
61 FROM ap_supplier_sites_all
62 WHERE vendor_id = x_vendor_id
63 AND pay_site_flag = 'Y'
64 UNION
65 SELECT 'AP:PRIMARY_PAY'
66 FROM ap_supplier_sites_all
67 WHERE vendor_id = x_vendor_id
68 AND primary_pay_site_flag = 'Y'
69 UNION
70 SELECT 'AP:RFQ'
71 FROM ap_supplier_sites_all
72 WHERE vendor_id = x_vendor_id
73 AND rfq_only_site_flag = 'Y'
74 UNION
75 SELECT 'AP:PCARD'
76 FROM ap_supplier_sites_all
77 WHERE vendor_id = x_vendor_id
78 AND pcard_site_flag = 'Y';
79
80 CURSOR ps_cursor(x_vendor_id NUMBER)
81 IS
82 SELECT 'PS:' || pos_product_service_utl_pkg.get_concat_code(classification_id) as code
83 FROM pos_sup_products_services
84 WHERE vendor_id = x_vendor_id;
85
86 CURSOR hz_cursor(x_party_id NUMBER)
87 IS
88 SELECT 'HZ:'|| hccr.class_category || ':' || hccr.class_code AS code
89 FROM hz_class_code_relations hccr,
90 ( SELECT class_category, class_code, owner_table_id
91 FROM hz_code_assignments
92 WHERE owner_table_name = 'HZ_PARTIES'
93 AND owner_table_id = x_party_id
94 AND start_date_active <= SYSDATE
95 AND NVL(end_date_active, SYSDATE) >= SYSDATE
96 AND status = 'A' ) v
97 WHERE hccr.class_category = v.class_category
98 START WITH hccr.class_code = v.class_code
99 CONNECT BY PRIOR hccr.class_code = hccr.sub_class_code
100 UNION
101 SELECT 'HZ:'|| fnd.lookup_type || ':' || fnd.lookup_code
102 FROM fnd_lookup_values_vl fnd,
103 ( SELECT class_category, class_code, owner_table_id
104 FROM hz_code_assignments
105 WHERE owner_table_name = 'HZ_PARTIES'
106 AND owner_table_id = x_party_id
107 AND start_date_active <= SYSDATE
108 AND nvl(end_date_active, SYSDATE) >= SYSDATE
109 AND status = 'A' ) v
110 WHERE fnd.lookup_type = v.class_category
111 AND fnd.lookup_code = v.class_code;
112
113 BEGIN
114 IF (p_class_code_pairs IS NOT NULL) THEN
115 p_class_code_pairs := NULL;
116 END IF;
117
118 FOR class_rec IN class_cursor(p_object_id, p_data_level_name, p_attr_group_id) LOOP
119 class_code := class_rec.classification_code;
120 scheme_code := SUBSTR(class_code, 1, 2);
121 CASE scheme_code
122 WHEN 'BS' THEN
123 IF (class_code = 'BS:BASE') THEN
124 return_code := class_code;
125 END IF;
126 WHEN 'ST' THEN
127 SELECT 'ST:'||vendor_type_lookup_code
128 INTO st_code
129 FROM AP_SUPPLIERS
130 WHERE party_id = p_party_id;
131 IF (class_code = st_code) THEN
132 return_code := class_code;
133 END IF;
134 WHEN 'BC' THEN
135 FOR bc_rec IN bc_cursor(p_party_id) LOOP
136 IF (class_code = bc_rec.code) THEN
137 return_code := class_code;
138 EXIT;
139 END IF;
140 END LOOP;
141 WHEN 'AC' THEN
142 FOR ac_rec IN ac_cursor(p_party_id) LOOP
143 IF (class_code = ac_rec.code) THEN
144 return_code := class_code;
145 EXIT;
146 END IF;
147 END LOOP;
148 WHEN 'AP' THEN
149 FOR ap_rec IN ap_cursor(p_party_id, p_vendor_id) LOOP
150 IF (class_code = ap_rec.code) THEN
151 return_code := class_code;
152 EXIT;
153 END IF;
154 END LOOP;
155 WHEN 'PS' THEN
156 pos_product_service_utl_pkg.get_product_meta_data(segment_definition, segment_count, po_category_set_id, x_delimiter);
157 FOR ps_rec IN ps_cursor(p_vendor_id) LOOP
158 IF (class_code = ps_rec.code) THEN
159 return_code := class_code;
160 EXIT;
161 END IF;
162 start_position := 1;
163 end_position := 0;
164 LOOP
165 end_position := INSTR(ps_rec.code, x_delimiter, start_position);
166 EXIT WHEN end_position = 0;
167 IF (class_code = SUBSTR(ps_rec.code, 0, end_position-1)) THEN
168 return_code := class_code;
169 EXIT;
170 END IF;
171 start_position := end_position + 1;
172 END LOOP;
173 END LOOP;
174 WHEN 'HZ' THEN
175 FOR hz_rec IN hz_cursor(p_party_id) LOOP
176 IF (class_code = hz_rec.code) THEN
177 return_code := class_code;
178 EXIT;
179 END IF;
180 END LOOP;
181 ELSE return_code := NULL;
182 END CASE;
183 END LOOP;
184
185 IF (return_code IS NOT NULL) THEN
186 p_class_code_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
187 p_class_code_pairs.EXTEND();
188 p_class_code_pairs(p_class_code_pairs.LAST)
189 := EGO_COL_NAME_VALUE_PAIR_OBJ('CLASSIFICATION_CODE', return_code);
190 END IF;
191
192 END Get_Supp_Related_Class_Codes;
193
194 PROCEDURE Get_Item_Related_Class_Codes (
195 p_item_id IN NUMBER
196 , p_class_code_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
197 ) AS
198 l_related_class_codes_list VARCHAR2(150) := NULL;
199 l_item_catalog_group_id NUMBER := NULL;
200 BEGIN
201 SELECT ITEM_CATALOG_GROUP_ID
202 INTO l_item_catalog_group_id
203 FROM MTL_SYSTEM_ITEMS_B
204 WHERE INVENTORY_ITEM_ID = p_item_id
205 AND ROWNUM = 1;
206
207 IF (p_class_code_pairs IS NOT NULL) THEN
208 p_class_code_pairs := NULL;
209 END IF;
210
211 IF (l_item_catalog_group_id IS NOT NULL) THEN
212 EGO_ITEM_PVT.Get_Related_Class_Codes (
213 l_item_catalog_group_id
214 , NULL
215 , NULL
216 , NULL
217 , l_related_class_codes_list
218 );
219 p_class_code_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
220 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', TO_CHAR(l_item_catalog_group_id))
221 , EGO_COL_NAME_VALUE_PAIR_OBJ('RELATED_CLASS_CODE_LIST', l_related_class_codes_list)
222 );
223 END IF;
224 END Get_Item_Related_Class_Codes;
225
226 PROCEDURE Process_User_Attrs_Data (
227 p_auction_header_id IN NUMBER
228 , p_bid_number IN NUMBER
229 , x_return_status OUT NOCOPY VARCHAR2
230 , x_err_msg OUT NOCOPY VARCHAR2
231 ) AS
232
233 l_auction_title VARCHAR2(80) := NULL;
234 l_document_number pon_auction_headers_all.document_number%TYPE;
235
236 l_extension_id NUMBER := NULL;
237 l_mode VARCHAR2(2000) := NULL;
238 l_return_status VARCHAR2(1) := NULL;
239 l_error_code VARCHAR2(2000) := NULL;
240 l_msg_count NUMBER := NULL;
241 l_msg_data VARCHAR2(2000) := NULL;
242 l_entity_id VARCHAR2(1000) := NULL;
243 l_message_type VARCHAR2(1000) := NULL;
244
245 l_intgr_hdr_flag VARCHAR2(1) := NULL;
246 l_intgr_cat_line_flag VARCHAR2(1) := NULL;
247 l_intgr_item_line_flag VARCHAR2(1) := NULL;
248 l_hdr_enable_weights_flag VARCHAR2(1) := NULL;
249
250 l_evaluator_name VARCHAR2(360) := NULL;
251 l_supp_contact_name VARCHAR2(360) := NULL;
252 l_publish_date DATE := NULL;
253 l_party_id NUMBER := NULL;
254 l_vendor_id NUMBER := NULL;
255 l_vendor_name VARCHAR2(240) := NULL;
256 l_vendor_site_id NUMBER := NULL;
257 l_ship_to_org_id NUMBER := NULL;
258 l_evaluation_flag VARCHAR2(1) := NULL;
259
260 l_item_id NUMBER := NULL;
261 l_item_number VARCHAR2(820) := NULL;
262 l_org_id NUMBER := NULL;
263
264 l_datatype VARCHAR2(3) := NULL;
265 l_value VARCHAR2(4000) := NULL;
266 l_score NUMBER := NULL;
267
268 l_counter NUMBER := 0;
269 l_attr_value_str VARCHAR2(1000) := NULL;
270 l_attr_value_num NUMBER := NULL;
271 l_attr_value_date DATE := NULL;
272 l_attr_disp_value VARCHAR2(1000) := NULL;
273 l_value_set_id NUMBER := NULL;
274
275 l_object_id NUMBER := NULL;
276 l_object_name VARCHAR2(430) := NULL;
277 l_application_id NUMBER := NULL;
278 l_attr_group_type VARCHAR2(40) := NULL;
279 l_attr_group_name VARCHAR2(30) := NULL;
280 l_attr_group_disp_name VARCHAR2(80) := NULL;
281 l_data_level_name VARCHAR2(30) := NULL;
282 l_user_data_level_name VARCHAR2(240) := NULL;
283
284 l_row_attrs_table EGO_USER_ATTR_DATA_TABLE := NULL;
285 l_current_data_element EGO_USER_ATTR_DATA_OBJ := NULL;
286 l_pk_column_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY := NULL;
287 l_class_code_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY := NULL;
288 l_data_level_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY := NULL;
289
290 CURSOR supp_ag_dl_cursor(p_header_id IN NUMBER)
291 IS
292 SELECT DISTINCT
293 ATTR_GROUP_ID
294 , DATA_LEVEL_ID
295 , LINE_NUMBER
296 FROM PON_AUCTION_ATTR_MAPPING_B
297 WHERE AUCTION_HEADER_ID = p_header_id
298 AND MAPPING_TYPE IN ('DOC_HEADER','DOC_REQ', 'CAT_LINE', 'DOC_SEC_SCORE'); --Bug13471195 Header Mapping Issue
299
300 CURSOR supp_mapping_setup_cursor(p_header_id IN NUMBER
301 , p_attr_group_id IN NUMBER
302 , p_data_level_id IN NUMBER
303 , p_line_number IN NUMBER)
304 IS
305 SELECT SECTION_ID
306 , SEQUENCE_NUMBER
307 , MAPPING_TYPE
308 , RESPONSE
309 , ATTR_INT_NAME
310 FROM PON_AUCTION_ATTR_MAPPING_B
311 WHERE AUCTION_HEADER_ID = p_header_id
312 AND ATTR_GROUP_ID = p_attr_group_id
313 AND DATA_LEVEL_ID = p_data_level_id
314 AND ( ( LINE_NUMBER = p_line_number AND mapping_type IN ('DOC_REQ', 'CAT_LINE', 'DOC_SEC_SCORE') )
315 OR ( LINE_NUMBER = -1 AND mapping_type = 'DOC_HEADER') );
316
317 CURSOR item_ag_dl_cursor(p_header_id IN NUMBER)
318 IS
319 SELECT DISTINCT
320 ATTR_GROUP_ID
321 , DATA_LEVEL_ID
322 , LINE_NUMBER
323 FROM PON_AUCTION_ATTR_MAPPING_B
324 WHERE AUCTION_HEADER_ID = p_header_id
325 AND MAPPING_TYPE IN ('ITEM_LINE','ITEM_HEADER'); ----Bug13471195 Header Mapping Issue
326
327 CURSOR item_mapping_setup_cursor(p_header_id IN NUMBER
328 , p_attr_group_id IN NUMBER
329 , p_data_level_id IN NUMBER
330 , p_line_number IN NUMBER)
331 IS
332 SELECT SEQUENCE_NUMBER
333 , MAPPING_TYPE
334 , RESPONSE
335 , ATTR_INT_NAME
336 FROM PON_AUCTION_ATTR_MAPPING_B
337 WHERE AUCTION_HEADER_ID = p_header_id
338 AND ATTR_GROUP_ID = p_attr_group_id
339 AND DATA_LEVEL_ID = p_data_level_id
340 AND ( ( LINE_NUMBER = p_line_number AND mapping_type = 'ITEM_LINE' )
341 OR ( LINE_NUMBER = -1 AND mapping_type = 'ITEM_HEADER') );
342
343 INCORRECT_DATA EXCEPTION;
344
345 BEGIN
346 SAVEPOINT Process_User_Attrs_Data_PUB;
347 x_return_status := FND_API.G_RET_STS_SUCCESS;
348 x_err_msg := NULL;
349
350 -- Bug 16401315
351 -- Added document_number to the query for mapping RFx Number
352 SELECT AUCTION_TITLE
353 , DOCUMENT_NUMBER
354 , INTGR_HDR_ATTR_FLAG
355 , INTGR_CAT_LINE_ATTR_FLAG
356 , INTGR_ITEM_LINE_ATTR_FLAG
357 , HDR_ATTR_ENABLE_WEIGHTS
358 INTO l_auction_title
359 , l_document_number
360 , l_intgr_hdr_flag
361 , l_intgr_cat_line_flag
362 , l_intgr_item_line_flag
363 , l_hdr_enable_weights_flag
364 FROM PON_AUCTION_HEADERS_ALL
365 WHERE AUCTION_HEADER_ID = p_auction_header_id;
366
367 /* PARTY ID, SUPPLIER ID, SUPPLIER SITE ID IF ANY */
368 SELECT HZ1.PARTY_NAME
369 , HZ2.PARTY_NAME
370 , PON.PUBLISH_DATE
371 , PON.TRADING_PARTNER_ID
372 , PON.VENDOR_ID
373 , PON.VENDOR_SITE_ID
374 , AP.VENDOR_NAME
375 , PON.EVALUATION_FLAG
376 INTO l_evaluator_name
377 , l_supp_contact_name
378 , l_publish_date
379 , l_party_id
380 , l_vendor_id
381 , l_vendor_site_id
382 , l_vendor_name
383 , l_evaluation_flag
384 FROM PON_BID_HEADERS PON
385 , HZ_PARTIES HZ1
386 , HZ_PARTIES HZ2
387 , AP_SUPPLIERS AP
388 WHERE PON.AUCTION_HEADER_ID = p_auction_header_id
389 AND PON.BID_NUMBER = p_bid_number
390 AND HZ1.PARTY_ID (+) = PON.EVALUATOR_ID
391 AND HZ2.PARTY_ID (+) = PON.TRADING_PARTNER_CONTACT_ID
392 AND AP.VENDOR_ID = PON.VENDOR_ID;
393
394 IF (l_party_id IS NULL or l_vendor_id IS NULL) THEN
395 RETURN;
396 END IF;
397
398 FOR supp_ag_dl_rec IN supp_ag_dl_cursor(p_auction_header_id) LOOP
399
400 IF ( (supp_ag_dl_rec.line_number = -1 AND l_intgr_hdr_flag = 'Y') OR
401 (supp_ag_dl_rec.line_number <> -1 AND l_intgr_cat_line_flag = 'Y') ) THEN
402
403 l_extension_id := NULL;
404 l_mode := NULL;
405 l_return_status := NULL;
406 l_error_code := NULL;
407 l_msg_count := NULL;
408 l_msg_data := NULL;
409 l_entity_id := NULL;
410 l_message_type := NULL;
411 l_attr_group_name := NULL;
412 l_attr_group_disp_name := NULL;
413 l_data_level_name := NULL;
414 l_user_data_level_name := NULL;
415
416 IF (l_row_attrs_table IS NOT NULL) THEN
417 l_row_attrs_table.DELETE;
418 END IF;
419 l_row_attrs_table := EGO_USER_ATTR_DATA_TABLE();
420
421 l_counter := 0;
422
423 SELECT ATTR_GROUP_NAME
424 , ATTR_GROUP_DISP_NAME
425 INTO l_attr_group_name
426 , l_attr_group_disp_name
427 FROM EGO_ATTR_GROUPS_V
428 WHERE ATTR_GROUP_ID = supp_ag_dl_rec.attr_group_id;
429
430 SELECT DATA_LEVEL_NAME
431 , USER_DATA_LEVEL_NAME
432 INTO l_data_level_name
433 , l_user_data_level_name
434 FROM EGO_DATA_LEVEL_VL
435 WHERE DATA_LEVEL_ID = supp_ag_dl_rec.data_level_id;
436
437 FOR supp_mapping_setup_rec IN supp_mapping_setup_cursor(p_auction_header_id
438 , supp_ag_dl_rec.attr_group_id
439 , supp_ag_dl_rec.data_level_id
440 , supp_ag_dl_rec.line_number) LOOP
441 BEGIN
442 /* get auction attribute value, type and score */
443 l_value := NULL;
444 l_datatype := NULL;
445 l_score := NULL;
446 IF (supp_mapping_setup_rec.mapping_type = 'DOC_HEADER') THEN
447 IF (supp_mapping_setup_rec.sequence_number = 10) THEN
448 l_value := l_auction_title;
449 l_datatype := 'TXT';
450 ELSIF (supp_mapping_setup_rec.sequence_number = 20) THEN
451 l_value := l_document_number;
452 l_datatype := 'RFN';
453 ELSIF (supp_mapping_setup_rec.sequence_number = 30 AND l_evaluation_flag <> 'Y') THEN
454 l_value := p_bid_number;
455 l_datatype := 'NUM';
456 ELSIF (supp_mapping_setup_rec.sequence_number = 40 AND l_evaluation_flag <> 'Y') THEN
457 l_value := l_publish_date;
458 l_datatype := 'DAT';
459 ELSIF (supp_mapping_setup_rec.sequence_number = 50 AND l_evaluation_flag <> 'Y') THEN
460 l_value := l_supp_contact_name;
461 l_datatype := 'TXT';
462 ELSIF (supp_mapping_setup_rec.sequence_number = 60 AND l_evaluation_flag = 'Y') THEN
463 l_value := p_bid_number;
464 l_datatype := 'NUM';
465 ELSIF (supp_mapping_setup_rec.sequence_number = 70 AND l_evaluation_flag = 'Y') THEN
466 l_value := l_publish_date;
467 l_datatype := 'DAT';
468 ELSIF (supp_mapping_setup_rec.sequence_number = 80 AND l_evaluation_flag = 'Y') THEN
469 l_value := l_evaluator_name;
470 l_datatype := 'TXT';
471 END IF;
472 ELSIF (supp_mapping_setup_rec.mapping_type = 'DOC_SEC_SCORE') THEN
473 IF (supp_mapping_setup_rec.section_id = -10000) THEN
474 SELECT 'NUM'
475 , NULL
476 , DECODE(l_hdr_enable_weights_flag, 'Y', SUM(WEIGHTED_SCORE), 'N', SUM(SCORE), NULL)
477 INTO l_datatype
478 , l_value
479 , l_score
480 FROM PON_BID_ATTRIBUTE_VALUES
481 WHERE AUCTION_HEADER_ID = p_auction_header_id
482 AND BID_NUMBER = p_bid_number
483 AND AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number;
484 ELSE
485 SELECT 'NUM'
486 , NULL
487 , DECODE(l_hdr_enable_weights_flag, 'Y', SUM(WEIGHTED_SCORE), 'N', SUM(SCORE), NULL)
488 INTO l_datatype
489 , l_value
490 , l_score
491 FROM PON_BID_ATTRIBUTE_VALUES BID
492 , PON_AUCTION_ATTRIBUTES ATTR
493 , PON_AUCTION_SECTIONS SEC
494 WHERE BID.AUCTION_HEADER_ID = p_auction_header_id
495 AND BID.BID_NUMBER = p_bid_number
496 AND BID.AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number
497 AND BID.AUCTION_HEADER_ID = ATTR.AUCTION_HEADER_ID
498 AND BID.AUCTION_HEADER_ID = SEC.AUCTION_HEADER_ID
499 AND BID.LINE_NUMBER = ATTR.LINE_NUMBER
500 AND BID.LINE_NUMBER = SEC.LINE_NUMBER
501 AND ATTR.SECTION_NAME = SEC.SECTION_NAME
502 AND SEC.SECTION_ID = supp_mapping_setup_rec.section_id
503 AND BID.SEQUENCE_NUMBER = ATTR.SEQUENCE_NUMBER;
504 END IF;
505 ELSIF (supp_mapping_setup_rec.mapping_type = 'CAT_LINE' AND supp_mapping_setup_rec.sequence_number = -10000) THEN
506 l_value := supp_ag_dl_rec.line_number;
507 l_datatype := 'NUM';
508 ELSE
509 SELECT DATATYPE
510 , VALUE
511 , DECODE(l_hdr_enable_weights_flag, 'Y', WEIGHTED_SCORE, 'N', SCORE, NULL)
512 INTO l_datatype
513 , l_value
514 , l_score
515 FROM PON_BID_ATTRIBUTE_VALUES
516 WHERE AUCTION_HEADER_ID = p_auction_header_id
517 AND BID_NUMBER = p_bid_number
518 AND AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number
519 AND SEQUENCE_NUMBER = supp_mapping_setup_rec.sequence_number;
520 IF (L_DATATYPE = 'DAT') THEN
521 L_VALUE := TO_DATE(L_VALUE,'DD-MM-YYYY'); --Bug 14170832 Date format error
522 END IF;
523 END IF;
524
525 IF (l_value IS NULL AND l_score IS NULL) THEN
526 RAISE INCORRECT_DATA;
527 END IF;
528
529 l_attr_value_str := NULL;
530 l_attr_value_num := NULL;
531 l_attr_value_date := NULL;
532 l_attr_disp_value := NULL;
533 l_value_set_id := NULL;
534
535 SELECT VALUE_SET_ID
536 INTO l_value_set_id
537 FROM EGO_ATTRS_V
538 WHERE ATTR_GROUP_NAME = l_attr_group_name
539 AND ATTR_NAME = supp_mapping_setup_rec.attr_int_name;
540
541 IF (supp_mapping_setup_rec.response IS NULL OR supp_mapping_setup_rec.response = 'V') THEN
542 IF (l_value_set_id IS NOT NULL) THEN
543 l_attr_disp_value := l_value;
544 ELSIF (l_datatype = 'RFN') THEN -- Bug 16401315
545 l_attr_value_str := l_value;
546 l_attr_value_num := p_auction_header_id;
547 ELSIF (l_datatype = 'TXT' OR l_datatype = 'URL') THEN
548 l_attr_value_str := l_value;
549 ELSIF (l_datatype = 'NUM') THEN
550 l_attr_value_num := l_value;
551 ELSIF (l_datatype = 'DAT') THEN
552 l_attr_value_date := l_value;
553 ELSE
554 RAISE INCORRECT_DATA;
555 END IF;
556 ELSIF (supp_mapping_setup_rec.response = 'S') THEN
557 l_attr_value_num := l_score;
558 ELSE
559 RAISE INCORRECT_DATA;
560 END IF;
561
562 l_current_data_element := EGO_USER_ATTR_DATA_OBJ(l_counter
563 , supp_mapping_setup_rec.attr_int_name
564 , l_attr_value_str
565 , l_attr_value_num
566 , l_attr_value_date
567 , l_attr_disp_value
568 , NULL
569 , l_counter);
570 l_row_attrs_table.EXTEND;
571 l_row_attrs_table(l_row_attrs_table.LAST) := l_current_data_element;
572 l_counter := l_counter + 1;
573
574 EXCEPTION
575 -- if mapping is defined for a requirement/attribute, but no response is provided, skip the mapping row
576 WHEN OTHERS THEN
577 NULL;
578 END;
579
580 END LOOP; -- supp_mapping_setup_cursor
581
582 IF (l_row_attrs_table.COUNT > 0) THEN
583 l_object_id := EGO_EXT_FWK_PUB.Get_Object_Id_From_Name('HZ_PARTIES');
584 l_object_name := 'HZ_PARTIES';
585 l_application_id := 177;
586 l_attr_group_type := 'POS_SUPP_PROFMGMT_GROUP';
587 l_pk_column_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PARTY_ID', l_party_id));
588 Get_Supp_Related_Class_Codes(l_vendor_id, l_party_id, l_object_id, l_data_level_name, supp_ag_dl_rec.attr_group_id, l_class_code_pairs);
589
590 -- Bug 12815017
591 -- If l_class_code_pairs is null, it means that none of the
592 -- supplier's classifications matches the attribute group's
593 -- classifications, and hence no need to do the mapping.
594 -- CONTINUE WHEN l_class_code_pairs IS NULL;
595
596 -- Bug 16169826
597 -- Modified code to use IF stement instead of CONTINUE
598 IF (l_class_code_pairs IS NOT NULL) THEN
599
600 l_data_level_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('IS_PROSPECT', 'N'));
601
602 /* call ego api to sync data */
603 ego_user_attrs_data_pvt.Process_Row(
604 p_api_version => 1.0
605 , p_object_id => l_object_id
606 , p_object_name => l_object_name-- HZ_PARTIES/EGO_ITEM
607 , p_attr_group_id => supp_ag_dl_rec.attr_group_id-- input
608 , p_application_id => l_application_id-- 177 for supplier, 431 for item
609 , p_attr_group_type => l_attr_group_type-- POS_SUPP_PROFMGMT_GROUP/EGO_ITEMMGMT_GROUP
610 , p_attr_group_name => l_attr_group_name-- input
611 , p_validate_hierarchy => FND_API.G_FALSE
612 , p_pk_column_name_value_pairs => l_pk_column_pairs-- input
613 , p_class_code_name_value_pairs => l_class_code_pairs-- input
614 , p_data_level => l_data_level_name-- input
615 , p_data_level_name_value_pairs => l_data_level_pairs-- input
616 , p_extension_id => NULL
617 , p_attr_name_value_pairs => l_row_attrs_table-- input
618 , p_entity_id => NULL
619 , p_entity_index => NULL
620 , p_entity_code => NULL
621 , p_validate_only => FND_API.G_FALSE
622 , p_language_to_process => NULL
623 , p_mode => ego_user_attrs_data_pvt.G_SYNC_MODE
624 , p_change_obj => NULL
625 , p_pending_b_table_name => NULL
626 , p_pending_tl_table_name => NULL
627 , p_pending_vl_name => NULL
628 , p_init_fnd_msg_list => FND_API.G_FALSE
629 , p_add_errors_to_fnd_stack => FND_API.G_FALSE
630 , p_commit => FND_API.G_FALSE
631 , p_raise_business_event => FALSE
632 , x_extension_id => l_extension_id
633 , x_mode => l_mode
634 , x_return_status => l_return_status
635 , x_errorcode => l_error_code
636 , x_msg_count => l_msg_count
637 , x_msg_data => l_msg_data
638 );
639
640 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
641 x_return_status := l_return_status;
642 ERROR_HANDLER.Get_Message(l_msg_data, l_error_code, l_entity_id, l_message_type);
643 x_err_msg := x_err_msg || G_DELIMITER
644 || 'Supplier: ' || l_vendor_name
645 || ', Attribute Group: ' || l_attr_group_disp_name
646 || ', Data Level: ' || l_user_data_level_name
647 || ', Error Message: ' || l_msg_data;
648 IF (LENGTH(x_err_msg) > 30000) THEN
649 EXIT;
650 END IF;
651 END IF;
652
653 END IF; -- (l_class_code_pairs IS NOT NULL)
654
655 END IF;
656
657 END IF; -- (supp_ag_dl_rec.line_number = -1 AND l_intgr_hdr_flag = 'Y') OR
658 -- (supp_ag_dl_rec.line_number <> -1 AND l_intgr_cat_line_flag = 'Y')
659
660 END LOOP; -- supp_ag_dl_cursor
661
662 IF (l_intgr_item_line_flag = 'Y') THEN
663
664 FOR item_ag_dl_rec IN item_ag_dl_cursor(p_auction_header_id) LOOP
665 BEGIN
666
667 l_extension_id := NULL;
668 l_mode := NULL;
669 l_return_status := NULL;
670 l_error_code := NULL;
671 l_msg_count := NULL;
672 l_msg_data := NULL;
673 l_entity_id := NULL;
674 l_message_type := NULL;
675 l_attr_group_name := NULL;
676 l_attr_group_disp_name := NULL;
677 l_data_level_name := NULL;
678 l_user_data_level_name := NULL;
679 l_item_id := NULL;
680 l_item_number := NULL;
681 l_org_id := NULL;
682 l_ship_to_org_id := NULL;
683
684 IF (l_row_attrs_table IS NOT NULL) THEN
685 l_row_attrs_table.DELETE;
686 END IF;
687 l_row_attrs_table := EGO_USER_ATTR_DATA_TABLE();
688
689 l_counter := 0;
690
691 SELECT ATTR_GROUP_NAME
692 , ATTR_GROUP_DISP_NAME
693 INTO l_attr_group_name
694 , l_attr_group_disp_name
695 FROM EGO_ATTR_GROUPS_V
696 WHERE ATTR_GROUP_ID = item_ag_dl_rec.attr_group_id;
697
698 SELECT DATA_LEVEL_NAME
699 , USER_DATA_LEVEL_NAME
700 INTO l_data_level_name
701 , l_user_data_level_name
702 FROM EGO_DATA_LEVEL_VL
703 WHERE DATA_LEVEL_ID = item_ag_dl_rec.data_level_id;
704
705 /* ORG_ID in PON_AUCTION_ITEM_PRICES_ALL is only the OU id, and the actual INVENTORY_ORGANIZATION_ID is in FINANCIALS_SYSTEM_PARAMS_ALL */
706 SELECT ITEM_ID
707 , ITEM_NUMBER
708 , F.INVENTORY_ORGANIZATION_ID
709 INTO l_item_id
710 , l_item_number
711 , l_org_id
712 FROM PON_AUCTION_ITEM_PRICES_ALL P
713 , FINANCIALS_SYSTEM_PARAMS_ALL F
714 WHERE P.AUCTION_HEADER_ID = p_auction_header_id
715 AND P.LINE_NUMBER = item_ag_dl_rec.line_number
716 AND P.ORG_ID = F.ORG_ID;
717
718 IF (l_vendor_id IS NULL OR l_item_id IS NULL OR l_org_id IS NULL) THEN
719 RAISE INCORRECT_DATA;
720 END IF;
721
722 IF (item_ag_dl_rec.data_level_id = 43104) THEN
723 IF (l_vendor_site_id IS NULL) THEN
724 RAISE INCORRECT_DATA;
725 END IF;
726 END IF;
727
728 IF (item_ag_dl_rec.data_level_id = 43105) THEN
729 SELECT SHIP_TO_LOCATION_ID
730 INTO l_ship_to_org_id
731 FROM PON_BID_ITEM_PRICES
732 WHERE AUCTION_HEADER_ID = p_auction_header_id
733 AND BID_NUMBER = p_bid_number
734 AND LINE_NUMBER = item_ag_dl_rec.line_number;
735
736 IF (l_ship_to_org_id IS NULL) THEN
737 RAISE INCORRECT_DATA;
738 END IF;
739 END IF;
740
741 IF (l_current_data_element IS NOT NULL) THEN
742 l_current_data_element := NULL;
743 END IF;
744
745 FOR item_mapping_setup_rec IN item_mapping_setup_cursor(p_auction_header_id
746 , item_ag_dl_rec.attr_group_id
747 , item_ag_dl_rec.data_level_id
748 , item_ag_dl_rec.line_number) LOOP
749 BEGIN
750 /* get auction attribute value, type and score */
751 l_value := NULL;
752 l_datatype := NULL;
753 l_score := NULL;
754 IF (item_mapping_setup_rec.mapping_type = 'ITEM_HEADER') THEN
755 IF (item_mapping_setup_rec.sequence_number = 10) THEN
756 l_value := l_auction_title;
757 l_datatype := 'TXT';
758 ELSIF (item_mapping_setup_rec.sequence_number = 20) THEN
759 l_value := l_document_number;
760 l_datatype := 'RFN';
761 ELSIF (item_mapping_setup_rec.sequence_number = 30 AND l_evaluation_flag <> 'Y') THEN
762 l_value := p_bid_number;
763 l_datatype := 'NUM';
764 ELSIF (item_mapping_setup_rec.sequence_number = 40 AND l_evaluation_flag <> 'Y') THEN
765 l_value := l_publish_date;
766 l_datatype := 'DAT';
767 ELSIF (item_mapping_setup_rec.sequence_number = 50 AND l_evaluation_flag <> 'Y') THEN
768 l_value := l_supp_contact_name;
769 l_datatype := 'TXT';
770 ELSIF (item_mapping_setup_rec.sequence_number = 60 AND l_evaluation_flag = 'Y') THEN
771 l_value := p_bid_number;
772 l_datatype := 'NUM';
773 ELSIF (item_mapping_setup_rec.sequence_number = 70 AND l_evaluation_flag = 'Y') THEN
774 l_value := l_publish_date;
775 l_datatype := 'DAT';
776 ELSIF (item_mapping_setup_rec.sequence_number = 80 AND l_evaluation_flag = 'Y') THEN
777 l_value := l_evaluator_name;
778 l_datatype := 'TXT';
779 END IF;
780 ELSIF (item_mapping_setup_rec.mapping_type = 'ITEM_LINE' AND item_mapping_setup_rec.sequence_number = -10000) THEN
781 l_value := item_ag_dl_rec.line_number;
782 l_datatype := 'NUM';
783 ELSE
784 SELECT DATATYPE
785 , VALUE
786 , DECODE(l_hdr_enable_weights_flag, 'Y', WEIGHTED_SCORE, 'N', SCORE, NULL)
787 INTO l_datatype
788 , l_value
789 , l_score
790 FROM PON_BID_ATTRIBUTE_VALUES
791 WHERE AUCTION_HEADER_ID = p_auction_header_id
792 AND BID_NUMBER = p_bid_number
793 AND AUCTION_LINE_NUMBER = item_ag_dl_rec.line_number
794 AND SEQUENCE_NUMBER = item_mapping_setup_rec.sequence_number;
795 IF (L_DATATYPE = 'DAT') THEN
796 L_VALUE := TO_DATE(L_VALUE,'DD-MM-YYYY'); --Bug 14170832 Date format error
797 END IF;
798 END IF;
799
800 IF (l_value IS NULL AND l_score IS NULL) THEN
801 RAISE INCORRECT_DATA;
802 END IF;
803
804 l_attr_value_str := NULL;
805 l_attr_value_num := NULL;
806 l_attr_value_date := NULL;
807 l_attr_disp_value := NULL;
808 l_value_set_id := NULL;
809
810 SELECT VALUE_SET_ID
811 INTO l_value_set_id
812 FROM EGO_ATTRS_V
813 WHERE ATTR_GROUP_NAME = l_attr_group_name
814 AND ATTR_NAME = item_mapping_setup_rec.attr_int_name;
815
816 IF (item_mapping_setup_rec.response IS NULL OR item_mapping_setup_rec.response = 'V') THEN
817 IF (l_value_set_id IS NOT NULL) THEN
818 l_attr_disp_value := l_value;
819 ELSIF (l_datatype = 'RFN') THEN -- Bug 16401315
820 l_attr_value_str := l_value;
821 l_attr_value_num := p_auction_header_id;
822 ELSIF (l_datatype = 'TXT' OR l_datatype = 'URL') THEN
823 l_attr_value_str := l_value;
824 ELSIF (l_datatype = 'NUM') THEN
825 l_attr_value_num := l_value;
826 ELSIF (l_datatype = 'DAT') THEN
827 l_attr_value_date := l_value;
828 ELSE
829 RAISE INCORRECT_DATA;
830 END IF;
831 ELSIF (item_mapping_setup_rec.response = 'S') THEN
832 l_attr_value_num := l_score;
833 ELSE
834 RAISE INCORRECT_DATA;
835 END IF;
836
837 l_current_data_element := EGO_USER_ATTR_DATA_OBJ(l_counter
838 , item_mapping_setup_rec.attr_int_name
839 , l_attr_value_str
840 , l_attr_value_num
841 , l_attr_value_date
842 , l_attr_disp_value
843 , NULL
844 , l_counter);
845 l_row_attrs_table.EXTEND;
846 l_row_attrs_table(l_row_attrs_table.LAST) := l_current_data_element;
847 l_counter := l_counter + 1;
848
849 EXCEPTION
850 -- if mapping is defined for a requirement/attribute, but no response is provided, skip the mapping row
851 WHEN OTHERS THEN
852 NULL;
853 END;
854
855 END LOOP; -- item_mapping_setup_cursor
856
857 IF (l_row_attrs_table.COUNT > 0) THEN
858 l_object_id := EGO_EXT_FWK_PUB.Get_Object_Id_From_Name('EGO_ITEM');
859 l_object_name := 'EGO_ITEM';
860 l_application_id := 431;
861 l_attr_group_type := 'EGO_ITEMMGMT_GROUP';
862 IF (item_ag_dl_rec.data_level_id = 43103) THEN
863 l_pk_column_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', l_item_id)
864 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', l_org_id));
865 Get_Item_Related_Class_Codes(l_item_id, l_class_code_pairs);
866 l_data_level_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PK1_VALUE', l_vendor_id));
867 ELSIF (item_ag_dl_rec.data_level_id = 43104) THEN
868 l_pk_column_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', l_item_id)
869 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', l_org_id));
870 Get_Item_Related_Class_Codes(l_item_id, l_class_code_pairs);
871 l_data_level_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PK1_VALUE', l_vendor_id)
872 , EGO_COL_NAME_VALUE_PAIR_OBJ('PK2_VALUE', l_vendor_site_id));
873 ELSIF (item_ag_dl_rec.data_level_id = 43105) THEN
874 l_pk_column_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', l_item_id)
875 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', l_ship_to_org_id));
876 Get_Item_Related_Class_Codes(l_item_id, l_class_code_pairs);
877 l_data_level_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PK1_VALUE', l_vendor_id)
878 , EGO_COL_NAME_VALUE_PAIR_OBJ('PK2_VALUE', l_vendor_site_id));
879 END IF;
880
881 /* call ego api to sync data */
882 ego_user_attrs_data_pvt.Process_Row(
883 p_api_version => 1.0
884 , p_object_id => l_object_id
885 , p_object_name => l_object_name-- HZ_PARTIES/EGO_ITEM
886 , p_attr_group_id => item_ag_dl_rec.attr_group_id-- input
887 , p_application_id => l_application_id-- 177 for supplier, 431 for item
888 , p_attr_group_type => l_attr_group_type-- POS_SUPP_PROFMGMT_GROUP/EGO_ITEMMGMT_GROUP
889 , p_attr_group_name => l_attr_group_name-- input
890 , p_validate_hierarchy => FND_API.G_FALSE
891 , p_pk_column_name_value_pairs => l_pk_column_pairs-- input
892 , p_class_code_name_value_pairs => l_class_code_pairs-- input
893 , p_data_level => l_data_level_name-- input
894 , p_data_level_name_value_pairs => l_data_level_pairs-- input
895 , p_extension_id => NULL
896 , p_attr_name_value_pairs => l_row_attrs_table-- input
897 , p_entity_id => NULL
898 , p_entity_index => NULL
899 , p_entity_code => NULL
900 , p_validate_only => FND_API.G_FALSE
901 , p_language_to_process => NULL
902 , p_mode => ego_user_attrs_data_pvt.G_SYNC_MODE
903 , p_change_obj => NULL
904 , p_pending_b_table_name => NULL
905 , p_pending_tl_table_name => NULL
906 , p_pending_vl_name => NULL
907 , p_init_fnd_msg_list => FND_API.G_FALSE
908 , p_add_errors_to_fnd_stack => FND_API.G_FALSE
909 , p_commit => FND_API.G_FALSE
910 , p_raise_business_event => FALSE
911 , x_extension_id => l_extension_id
912 , x_mode => l_mode
913 , x_return_status => l_return_status
914 , x_errorcode => l_error_code
915 , x_msg_count => l_msg_count
916 , x_msg_data => l_msg_data
917 );
918 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
919 x_return_status := l_return_status;
920 ERROR_HANDLER.Get_Message(l_msg_data, l_error_code, l_entity_id, l_message_type);
921 x_err_msg := x_err_msg || G_DELIMITER
922 || 'Supplier: ' || l_vendor_name
923 || ', Item: ' || l_item_number
924 || ', Attribute Group: ' || l_attr_group_disp_name
925 || ', Data Level: ' || l_user_data_level_name
926 || ', Error Message: ' || l_msg_data;
927 IF (LENGTH(x_err_msg) > 30000) THEN
928 EXIT;
929 END IF;
930 END IF;
931 END IF;
932
933 EXCEPTION
934 -- if data provided are not complete for the item line, skip the entire line
935 WHEN OTHERS THEN
936 NULL;
937 END;
938
939 END LOOP; -- item_ag_dl_cursor
940
941 END IF; -- l_intgr_item_line_flag = 'Y'
942
943 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
944 ROLLBACK TO Process_User_Attrs_Data_PUB;
945 END IF;
946
947 END Process_User_Attrs_Data;
948
949 PROCEDURE Sync_Approved_Supplier_List(p_auction_header_id IN NUMBER)
950 AS
951
952 l_asl_row_id VARCHAR2(18) := NULL;
953 l_asl_id NUMBER := NULL;
954 l_attr_row_id VARCHAR2(18) := NULL;
955 l_intgr_cat_line_asl VARCHAR2(1) := NULL;
956
957
958 -- Bug 12895392
959 -- 1. Use Ship-To organization_id instead of location_id
960 -- 2. If there is no Ship-To Location, mark category as Global in ASL
961
962 CURSOR cate_line_cursor (p_header_id IN NUMBER)
963 IS
964 SELECT item.bid_number
965 , item.line_number
966 , bid.vendor_id
967 , bid.vendor_site_id
968 , item.ship_to_location_id
969 , item.category_id
970 , item.approval_status
971 , f.inventory_organization_id
972 , NVL(hl.inventory_organization_id, -1) ship_to_org_id
973 FROM pon_bid_headers bid
974 , pon_bid_item_prices item
975 , pon_auction_item_prices_all price
976 , financials_system_params_all f
977 , hr_locations_all hl
978 WHERE bid.bid_number = item.bid_number
979 AND item.line_number = price.line_number
980 AND bid.auction_header_id = price.auction_header_id
981 AND price.item_id IS NULL
982 AND bid.auction_header_id = p_header_id
983 AND item.approval_status = 'APPROVED'
984 AND f.org_id = price.org_id
985 AND hl.location_id(+) = item.ship_to_location_id;
986
987 BEGIN
988 SELECT INTGR_CAT_LINE_ASL_FLAG
989 INTO l_intgr_cat_line_asl
990 FROM PON_AUCTION_HEADERS_ALL
991 WHERE AUCTION_HEADER_ID = p_auction_header_id;
992
993 IF (l_intgr_cat_line_asl <> 'Y') THEN
994 RETURN;
995 END IF;
996
997 FOR cate_lin_rec IN cate_line_cursor(p_auction_header_id) LOOP
998 l_asl_row_id := NULL;
999 l_asl_id := NULL;
1000 l_attr_row_id := NULL;
1001
1002 -- Bug 12895392
1003 IF (cate_lin_rec.vendor_site_id = -1) THEN
1004 cate_lin_rec.vendor_site_id := NULL;
1005 END IF;
1006
1007 BEGIN
1008 PO_ASL_THS.insert_row(
1009 l_asl_row_id,
1010 l_asl_id,
1011 cate_lin_rec.ship_to_org_id, -- ship to org
1012 cate_lin_rec.inventory_organization_id, --x_owning_organization_id
1013 'DIRECT', --x_vendor_business_type, -- direct, manufacture, or distributor, if distributor, need to provide manufacturer_id
1014 2, --x_asl_status_id, 1 for new, 2 for approved
1015 sysdate, --x_last_update_date,
1016 fnd_global.user_id, --x_last_updated_by,
1017 sysdate, --x_creation_date,
1018 fnd_global.user_id, --x_created_by,
1019 NULL, --x_manufacturer_id,
1020 cate_lin_rec.vendor_id, --x_vendor_id,
1021 NULL, --x_item_id,
1022 cate_lin_rec.category_id, --x_category_id,
1023 cate_lin_rec.vendor_site_id, --x_vendor_site_id,
1024 NULL, --x_primary_vendor_item,
1025 NULL, --x_manufacturer_asl_id,
1026 NULL, --x_comments,
1027 NULL, --x_review_by_date,
1028 NULL, --x_attribute_category,
1029 NULL, --x_attribute1,
1030 NULL, --x_attribute2,
1031 NULL, --x_attribute3,
1032 NULL, --x_attribute4,
1033 NULL, --x_attribute5,
1034 NULL, --x_attribute6,
1035 NULL, --x_attribute7,
1036 NULL, --x_attribute8,
1037 NULL, --x_attribute9,
1038 NULL, --x_attribute10,
1039 NULL, --x_attribute11,
1040 NULL, --x_attribute12,
1041 NULL, --x_attribute13,
1042 NULL, --x_attribute14,
1043 NULL, --x_attribute15,
1044 fnd_global.user_id, --x_last_update_login,
1045 NULL --x_disable_flag
1046 );
1047
1048 IF (l_asl_id IS NOT NULL) THEN
1049 PO_ASL_ATTRIBUTES_THS.insert_row(
1050 l_attr_row_id,
1051 l_asl_id,
1052 cate_lin_rec.ship_to_org_id, --x_using_organization_id NUMBER,
1053 sysdate, --x_last_update_date DATE,
1054 fnd_global.user_id, --x_last_updated_by NUMBER,
1055 sysdate, --x_creation_date DATE,
1056 fnd_global.user_id, --x_created_by NUMBER,
1057 'ASL', --x_document_sourcing_method VARCHAR2,
1058 NULL, --x_release_generation_method VARCHAR2,
1059 NULL, --x_purchasing_unit_of_measure VARCHAR2,
1060 'N', --x_enable_plan_schedule_flag VARCHAR2,
1061 'N', --x_enable_ship_schedule_flag VARCHAR2,
1062 NULL, --x_plan_schedule_type VARCHAR2,
1063 NULL, --x_ship_schedule_type VARCHAR2,
1064 NULL, --x_plan_bucket_pattern_id NUMBER,
1065 NULL, --x_ship_bucket_pattern_id NUMBER,
1066 'N', --x_enable_autoschedule_flag VARCHAR2,
1067 NULL, --x_scheduler_id NUMBER,
1068 'N', --x_enable_authorizations_flag VARCHAR2,
1069 cate_lin_rec.vendor_id, --x_vendor_id NUMBER,
1070 cate_lin_rec.vendor_site_id, --x_site_id NUMBER,
1071 NULL, --x_item_id NUMBER,
1072 cate_lin_rec.category_id, --x_category_id NUMBER,
1073 NULL, --x_attribute_category VARCHAR2,
1074 NULL, --x_attribute1 VARCHAR2,
1075 NULL, --x_attribute2 VARCHAR2,
1076 NULL, --x_attribute3 VARCHAR2,
1077 NULL, --x_attribute4 VARCHAR2,
1078 NULL, --x_attribute5 VARCHAR2,
1079 NULL, --x_attribute6 VARCHAR2,
1080 NULL, --x_attribute7 VARCHAR2,
1081 NULL, --x_attribute8 VARCHAR2,
1082 NULL, --x_attribute9 VARCHAR2,
1083 NULL, --x_attribute10 VARCHAR2,
1084 NULL, --x_attribute11 VARCHAR2,
1085 NULL, --x_attribute12 VARCHAR2,
1086 NULL, --x_attribute13 VARCHAR2,
1087 NULL, --x_attribute14 VARCHAR2,
1088 NULL, --x_attribute15 VARCHAR2,
1089 fnd_global.user_id, --x_last_update_login NUMBER,
1090 NULL, --x_price_update_tolerance NUMBER,
1091 NULL, --x_processing_lead_time NUMBER,
1092 NULL, --x_delivery_calendar VARCHAR2,
1093 NULL, --x_min_order_qty NUMBER,
1094 NULL, --x_fixed_lot_multiple NUMBER,
1095 NULL, --x_country_of_origin_code VARCHAR2,
1096 NULL, --x_enable_vmi_flag VARCHAR2,
1097 NULL, --x_vmi_min_qty NUMBER,
1098 NULL, --x_vmi_max_qty NUMBER,
1099 NULL, --x_enable_vmi_auto_repl_flag VARCHAR2,
1100 NULL, --x_vmi_replenishment_approval VARCHAR2,
1101 NULL, --x_consigned_from_supplier_flag VARCHAR2,
1102 NULL, --x_consigned_billing_cycle NUMBER ,
1103 NULL, --x_last_billing_date DATE,
1104 NULL, --x_replenishment_method NUMBER,
1105 NULL, --x_vmi_min_days NUMBER,
1106 NULL, --x_vmi_max_days NUMBER,
1107 NULL, --x_fixed_order_quantity NUMBER,
1108 NULL, --x_forecast_horizon NUMBER,
1109 NULL, --x_consume_on_aging_flag VARCHAR2,
1110 NULL --x_aging_period NUMBER
1111 );
1112 END IF;
1113 EXCEPTION
1114 -- When the ASL already exists, move on to the next supplier
1115 WHEN OTHERS THEN
1116 NULL;
1117 END;
1118
1119 END LOOP; -- cate_line_cursor
1120
1121 END Sync_Approved_Supplier_List;
1122
1123 PROCEDURE Sync_User_Attrs_Data (
1124 p_auction_header_id IN NUMBER
1125 , p_vendor_id IN NUMBER
1126 , x_return_status OUT NOCOPY VARCHAR2
1127 , x_err_msg OUT NOCOPY VARCHAR2
1128 ) AS
1129
1130 l_vendor_id NUMBER := NULL;
1131 l_return_status VARCHAR2(1) := NULL;
1132 l_msg_data VARCHAR2(2000) := NULL;
1133
1134 -- Bug 16198923
1135 -- Added order by clause so that supplier response is always mapped
1136 -- after evaluation response. This way, when mapping RFx scores to
1137 -- single row UDA, it will always be the supplier response's scores.
1138 CURSOR bids_cursor(p_header_id IN NUMBER)
1139 IS
1140 SELECT BID_NUMBER
1141 FROM PON_BID_HEADERS
1142 WHERE AUCTION_HEADER_ID = p_header_id
1143 AND BID_STATUS = 'ACTIVE'
1144 AND VENDOR_ID <> -1
1145 ORDER BY VENDOR_ID,
1146 EVALUATION_FLAG DESC,
1147 BID_NUMBER;
1148
1149 CURSOR auctions_cursor(p_v_id IN NUMBER)
1150 IS
1151 SELECT BID.AUCTION_HEADER_ID
1152 , BID.BID_NUMBER
1153 FROM PON_BID_HEADERS BID
1154 , PON_AUCTION_HEADERS_ALL AUCTION
1155 WHERE BID.VENDOR_ID = p_v_id
1156 AND BID.AUCTION_HEADER_ID = AUCTION.AUCTION_HEADER_ID
1157 AND AUCTION.AUCTION_STATUS = 'AUCTION_CLOSED'
1158 ORDER BY AUCTION_HEADER_ID,
1159 EVALUATION_FLAG DESC,
1160 BID_NUMBER;
1161
1162 BEGIN
1163 SAVEPOINT Sync_User_Attrs_Data_PUB;
1164 x_return_status := FND_API.G_RET_STS_SUCCESS;
1165 x_err_msg := NULL;
1166
1167 IF (p_auction_header_id IS NOT NULL AND p_vendor_id IS NULL) THEN
1168 FOR bids_rec IN bids_cursor(p_auction_header_id) LOOP
1169 l_return_status := NULL;
1170 l_msg_data := NULL;
1171 Process_User_Attrs_Data(p_auction_header_id, bids_rec.bid_number, l_return_status, l_msg_data);
1172 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1173 x_return_status := l_return_status;
1174 x_err_msg := x_err_msg || l_msg_data;
1175 END IF;
1176 END LOOP;
1177 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1178 Sync_Approved_Supplier_List(p_auction_header_id);
1179 END IF;
1180 ELSIF (p_auction_header_id IS NULL AND p_vendor_id IS NOT NULL) THEN
1181 FOR auctions_rec IN auctions_cursor(p_vendor_id) LOOP
1182 l_return_status := NULL;
1183 l_msg_data := NULL;
1184 Process_User_Attrs_Data(auctions_rec.auction_header_id, auctions_rec.bid_number, l_return_status, l_msg_data);
1185 END LOOP;
1186 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1187 x_return_status := l_return_status;
1188 x_err_msg := x_err_msg || l_msg_data;
1189 END IF;
1190 END IF;
1191
1192 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1193 ROLLBACK TO Sync_User_Attrs_Data_PUB;
1194 END IF;
1195
1196 END Sync_User_Attrs_Data;
1197
1198 END PON_ATTR_MAPPING;