[Home] [Help]
PACKAGE BODY: APPS.QP_BULK_VALUE_TO_ID
Source
1 PACKAGE BODY QP_BULK_VALUE_TO_ID AS
2 /* $Header: QPXBVIDB.pls 120.3.12010000.4 2008/09/02 06:55:56 jputta ship $ */
3
4 PROCEDURE HEADER
5 (p_request_id IN NUMBER)
6
7 IS
8 BEGIN
9
10 qp_bulk_loader_pub.write_log( 'Entering Value to ID Header');
11
12 -- 1. Currency Header
13
14 -- If Multi Currency Option is Set to Yes then process_status_flag should be set to 'E'
15 -- Fix for bug# 3570115
16
17 If (NVL(fnd_profile.value('QP_MULTI_CURRENCY_INSTALLED'),'N') = 'Y') THEN
18 Update qp_interface_list_headers ih
19 set (currency_header_id, process_status_flag, attribute_status)=
20 (select c.currency_header_id,
21 decode(c.currency_header_id, null, null, 'P'),
22 decode(c.currency_header_id, null, ih.attribute_status||'001', ih.attribute_status)
23 from qp_currency_lists_vl c,
24 qp_interface_list_headers iih
25 where iih.currency_header = c.name(+)
26 and iih.currency_code = c.base_currency_code(+)
27 and ih.rowid = iih.rowid
28 )
29 Where ih.request_id = p_request_id
30 and ih.process_status_flag = 'P' --IS NOT NULL
31 and ih.currency_header is not null
32 --Bug# 5412029
33 --Don't do conversion if Value is G_NULL_CHAR
34 and ih.currency_header <> QP_BULK_LOADER_PUB.G_NULL_CHAR
35 and ih.currency_header_id is null
36 and ih.interface_action_code in ('INSERT','UPDATE');
37 End If;
38
39 --2. Freight Terms Code
40 Update qp_interface_list_headers ih
41 set (freight_terms_code, process_status_flag, attribute_status)=
42 (select c.freight_terms_code,
43 decode(c.freight_terms_code, null, null, 'P'),
44 decode(c.freight_terms_code, null, ih.attribute_status||'002', ih.attribute_status)
45 from OE_FRGHT_TERMS_ACTIVE_V c,
46 qp_interface_list_headers iih
47 where iih.freight_terms = c.freight_terms(+)
48 and sysdate between nvl(c.start_date_active, sysdate)
49 and nvl(c.end_date_active, sysdate)
50 and ih.rowid = iih.rowid
51 )
52 Where ih.request_id = p_request_id
53 and ih.process_status_flag = 'P' --IS NOT NULL
54 and ih.freight_terms is not null
55 --Bug# 5412029
56 --Don't do conversion if Value is G_NULL_CHAR
57 and ih.freight_terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
58 and ih.freight_terms_code is null
59 and ih.interface_action_code in ('INSERT','UPDATE');
60
61 -- 3. Ship Method Code
62
63 Update qp_interface_list_headers ih
64 set (ship_method_code, process_status_flag, attribute_status)=
65 (select c.lookup_code,
66 decode(c.lookup_code, null, null, 'P'),
67 decode(c.lookup_code, null, ih.attribute_status||'003', ih.attribute_status)
68 from OE_SHIP_METHODS_V c,
69 qp_interface_list_headers iih
70 where iih.ship_method = c.meaning(+)
71 and c.lookup_type(+) = 'SHIP_METHOD'
72 and sysdate between nvl(c.start_date_active, sysdate)
73 and nvl(c.end_date_active, sysdate)
74 and ih.rowid = iih.rowid
75 )
76 Where ih.request_id = p_request_id
77 and ih.process_status_flag = 'P' --IS NOT NULL
78 and ih.ship_method is not null
79 --Bug# 5412029
80 --Don't do conversion if Value is G_NULL_CHAR
81 and ih.ship_method <> QP_BULK_LOADER_PUB.G_NULL_CHAR
82 and ih.ship_method_code is null
83 and ih.interface_action_code in ('INSERT','UPDATE');
84
85 -- 4. Terms Id
86
87 Update qp_interface_list_headers ih
88 set (terms_id, process_status_flag, attribute_status)=
89 (select c.term_id,
90 decode(c.term_id, null, null, 'P'),
91 decode(c.term_id, null, ih.attribute_status||'004', ih.attribute_status)
92 from RA_TERMS c,
93 qp_interface_list_headers iih
94 where iih.terms = c.name(+)
95 and sysdate between nvl(c.start_date_active, sysdate)
96 and nvl(c.end_date_active, sysdate)
97 and ih.rowid = iih.rowid
98 )
99 Where ih.request_id = p_request_id
100 and ih.process_status_flag = 'P' --IS NOT NULL
101 and ih.terms is not null
102 --Bug# 5412029
103 --Don't do conversion if Value is G_NULL_CHAR
104 and ih.terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
105 and ih.terms_id is null
106 and ih.interface_action_code in ('INSERT','UPDATE');
107
108 qp_bulk_loader_pub.write_log( 'Leaving Value to ID Header');
109
110 EXCEPTION
111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
112 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.HEADER:'||sqlerrm);
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 WHEN OTHERS THEN
115 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.HEADER:'||sqlerrm);
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117
118 END HEADER;
119
120 PROCEDURE QUALIFIER
121 (p_request_id IN NUMBER)
122 IS
123
124 BEGIN
125
126 qp_bulk_loader_pub.write_log( 'Entering Value to ID Qualifier');
127 -- 1. CREATED_FROM_RULE
128 UPDATE QP_INTERFACE_QUALIFIERS a
129 SET(CREATED_FROM_RULE_ID, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
130 (SELECT b.QUALIFIER_RULE_ID,
131 DECODE(b.QUALIFIER_RULE_ID, NULL, null, 'P'),
132 DECODE(b.QUALIFIER_RULE_ID,NULL, a.ATTRIBUTE_STATUS||'001', a.ATTRIBUTE_STATUS)
133 FROM QP_QUALIFIER_RULES b, QP_INTERFACE_QUALIFIERS c
134 WHERE c.CREATED_FROM_RULE = b.NAME(+)
135 AND a.rowid = c.rowid
136 )
137 WHERE a.REQUEST_ID = P_REQUEST_ID
138 and a.process_status_flag = 'P' --IS NOT NULL
139 AND a.CREATED_FROM_RULE_ID IS NULL
140 --Bug# 5456164
141 --Don't do conversion if Value is G_NULL_CHAR
142 AND a.CREATED_FROM_RULE <> QP_BULK_LOADER_PUB.G_NULL_CHAR
143 AND a.CREATED_FROM_RULE IS NOT NULL
144 AND a.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
145
146 -- 2.QUALIFIER_RULE
147
148 UPDATE QP_INTERFACE_QUALIFIERS a
149 SET(QUALIFIER_RULE_ID, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
150 (SELECT b.QUALIFIER_RULE_ID,
151 DECODE(b.QUALIFIER_RULE_ID, NULL, null, 'P'),
152 DECODE(b.QUALIFIER_RULE_ID,NULL, a.ATTRIBUTE_STATUS||'002', a.ATTRIBUTE_STATUS)
153 FROM QP_QUALIFIER_RULES b, QP_INTERFACE_QUALIFIERS c
154 WHERE c.QUALIFIER_RULE = b.NAME(+)
155 AND a.rowid = c.rowid
156 )
157 WHERE a.REQUEST_ID = P_REQUEST_ID
158 and a.process_status_flag = 'P' --IS NOT NULL
159 AND a.QUALIFIER_RULE_ID IS NULL
160 --Bug# 5456164
161 --Don't do conversion if Value is G_NULL_CHAR
162 AND a.QUALIFIER_RULE <> QP_BULK_LOADER_PUB.G_NULL_CHAR
163 AND a.QUALIFIER_RULE IS NOT NULL
164 AND a.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
165
166 --3.Qualifier Attribute
167
168 UPDATE QP_INTERFACE_QUALIFIERS qiq
169 SET(QUALIFIER_ATTRIBUTE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
170 (SELECT a.segment_mapping_column,
171 DECODE(a.segment_mapping_column, NULL, null, 'P'),
172 DECODE(a.segment_mapping_column, NULL, qiq.ATTRIBUTE_STATUS||'003',qiq.ATTRIBUTE_STATUS)
173 FROM qp_segments_v a, qp_prc_contexts_b b, qp_interface_qualifiers c
174 WHERE b.prc_context_id = a.prc_context_id
175 AND b.prc_context_code = c.QUALIFIER_CONTEXT
176 AND a.segment_code = c.QUALIFIER_ATTRIBUTE_CODE
177 AND a.segment_mapping_column like 'QUALIFIER%'
178 AND qiq.rowid = c.rowid)
179 WHERE qiq.REQUEST_ID = P_REQUEST_ID
180 and qiq.process_status_flag = 'P' --IS NOT NULL
181 AND qiq.QUALIFIER_ATTRIBUTE IS NULL
182 --Bug# 5456164
183 --Don't do conversion if Value is G_NULL_CHAR
184 AND qiq.QUALIFIER_ATTRIBUTE_CODE <> QP_BULK_LOADER_PUB.G_NULL_CHAR
185 AND qiq.QUALIFIER_ATTRIBUTE_CODE IS NOT NULL
186 AND qiq.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
187
188
189 qp_bulk_loader_pub.write_log( 'Leaving Value to ID Qualifier');
190
191 EXCEPTION
192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
193 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.QUALIFIER:'||sqlerrm);
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 WHEN OTHERS THEN
196 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.QUALIFIER:'||sqlerrm);
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198
199
200 END QUALIFIER;
201
202
203 PROCEDURE LINE
204 (p_request_id IN NUMBER)
205 IS
206
207 CURSOR c_price_attr IS
208 SELECT orig_sys_pricing_attr_ref,
209 orig_sys_header_ref,
210 orig_sys_line_ref,
211 product_attribute_context,
212 product_attribute,
213 product_attr_code,
214 product_attr_value,
215 product_attr_val_disp,
216 pricing_attribute_context,
217 pricing_attribute,
218 pricing_attr_code,
219 pricing_attr_value_from,
220 pricing_attr_value_from_disp,
221 pricing_attr_value_to,
222 pricing_attr_value_to_disp
223 FROM qp_interface_pricing_attribs pa
224 WHERE pa.request_id = p_request_id
225 AND pa.process_status_flag = 'P'
226 AND pa.interface_action_code IN ('INSERT','UPDATE');
227
228 l_product_attribute VARCHAR2(50);
229 l_product_attr_value VARCHAR2(50);
230 l_pricing_attribute VARCHAR2(50);
231 l_pricing_attr_value_from VARCHAR2(50);
232 l_pricing_attr_value_to VARCHAR2(50);
233 l_segment_name VARCHAR2(240);
234 x_value VARCHAR2(240);
235 x_id VARCHAR2(150);
236 x_format_type VARCHAR2(1);
237 l_orig_sys_line_ref VARCHAR2(50):=NULL;
238 l_context_error VARCHAR2(1);
239 l_attribute_error VARCHAR2(1);
240 l_value_error VARCHAR2(1);
241 l_datatype VARCHAR2(1);
242 l_precedence NUMBER;
243 l_error_code NUMBER;
244
245 vset FND_VSET.valueset_r;
246 fmt FND_VSET.valueset_dr;
247 found BOOLEAN;
248 row NUMBER;
249 value FND_VSET.value_dr;
250 x_vsid NUMBER;
251 x_validation_type VARCHAR2(1);
252
253 BEGIN
254 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines');
255 -- 1.PRICE_BY_FORMULA
256
257 UPDATE QP_INTERFACE_LIST_LINES a
258 SET(PRICE_BY_FORMULA_ID, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
259 (SELECT b.PRICE_FORMULA_ID,
260 DECODE(b.PRICE_FORMULA_ID, NULL, null, 'P'),
261 DECODE(b.PRICE_FORMULA_ID,NULL, a.ATTRIBUTE_STATUS||'001', a.ATTRIBUTE_STATUS)
262 FROM QP_PRICE_FORMULAS_VL b, QP_INTERFACE_LIST_LINES c
263 WHERE c.PRICE_BY_FORMULA = b.NAME(+)
264 AND a.rowid = c.rowid
265 )
266 WHERE a.REQUEST_ID = P_REQUEST_ID
267 AND a.PRICE_BY_FORMULA_ID IS NULL
268 AND a.PRICE_BY_FORMULA IS NOT NULL
269 --Bug# 5412029
270 --Don't do conversion if Value is G_NULL_CHAR
271 and a.PRICE_BY_FORMULA <> QP_BULK_LOADER_PUB.G_NULL_CHAR
272 AND a.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
273
274 -- 2.GENERATE_USING_FORMULA
275 UPDATE QP_INTERFACE_LIST_LINES a
276 SET(GENERATE_USING_FORMULA_ID, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
277 (SELECT b.PRICE_FORMULA_ID,
278 DECODE(b.PRICE_FORMULA_ID, NULL, null, 'P'),
279 DECODE(b.PRICE_FORMULA_ID,NULL, a.ATTRIBUTE_STATUS||'002', a.ATTRIBUTE_STATUS)
280 FROM QP_PRICE_FORMULAS_VL b, QP_INTERFACE_LIST_LINES c
281 WHERE c.GENERATE_USING_FORMULA = b.NAME(+)
282 AND a.rowid = c.rowid
283 )
284 WHERE a.REQUEST_ID = P_REQUEST_ID
285 AND a.GENERATE_USING_FORMULA_ID IS NULL
286 AND a.GENERATE_USING_FORMULA IS NOT NULL
287 --Bug# 5412029
288 --Don't do conversion if Value is G_NULL_CHAR
289 and a.GENERATE_USING_FORMULA <> QP_BULK_LOADER_PUB.G_NULL_CHAR
290 AND a.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
291
292 -- 3.Product Attribute
293 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Attribute');
294 UPDATE QP_INTERFACE_PRICING_ATTRIBS qipa
295 SET (PRODUCT_ATTRIBUTE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
296 (SELECT a.segment_mapping_column,
297 DECODE(a.segment_mapping_column, NULL, null, 'P'),
298 DECODE(a.segment_mapping_column, NULL, qipa.ATTRIBUTE_STATUS||'003',qipa.ATTRIBUTE_STATUS)
299 FROM qp_segments_v a, qp_prc_contexts_b b, qp_interface_pricing_attribs c
300 WHERE b.prc_context_id = a.prc_context_id
301 AND b.prc_context_code = c.PRODUCT_ATTRIBUTE_CONTEXT
302 AND a.segment_code = c.PRODUCT_ATTR_CODE
303 AND a.segment_mapping_column like 'PRICING%'
304 AND qipa.rowid = c.rowid)
305 WHERE qipa.REQUEST_ID = P_REQUEST_ID
306 AND qipa.process_status_flag = 'P' --IS NOT NULL
307 AND qipa.PRODUCT_ATTRIBUTE IS NULL
308 --Bug# 5456164
309 --Don't do conversion if Value is G_NULL_CHAR
310 AND qipa.PRODUCT_ATTR_CODE <> QP_BULK_LOADER_PUB.G_NULL_CHAR
311 AND qipa.PRODUCT_ATTR_CODE IS NOT NULL
312 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
313
314 -- 5.Product Precedence.
315 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Precedence');
316 UPDATE QP_INTERFACE_LIST_LINES qill
317 SET PRODUCT_PRECEDENCE=
318 (SELECT NVL(a.USER_PRECEDENCE, a.SEEDED_PRECEDENCE)
319 FROM qp_segments_v a, qp_prc_contexts_b b, qp_interface_pricing_attribs c
320 WHERE b.prc_context_id = a.prc_context_id
321 AND b.prc_context_code = c.PRODUCT_ATTRIBUTE_CONTEXT
322 AND a.segment_code = c.PRODUCT_ATTR_CODE
323 AND a.segment_mapping_column like 'PRICING%'
324 AND qill.orig_sys_line_ref = c.orig_sys_line_ref
325 AND c.PRICING_ATTRIBUTE_CONTEXT is NULL
326 AND c.PRICING_ATTRIBUTE is NULL
327 AND c.PROCESS_STATUS_FLAG = 'P'
328 AND c.request_id = p_request_id ) --Bug No 6235177
329 WHERE qill.REQUEST_ID = P_REQUEST_ID
330 AND qill.process_status_flag = 'P' --IS NOT NULL
331 AND qill.PRODUCT_PRECEDENCE IS NULL
332 AND qill.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
333
334
335 -- 4.Pricing Attribute
336 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Pricing Attribute');
337 UPDATE QP_INTERFACE_PRICING_ATTRIBS qipa
338 SET (PRICING_ATTRIBUTE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
339 (SELECT a.segment_mapping_column,
340 DECODE(a.segment_mapping_column, NULL, null, 'P'),
341 DECODE(a.segment_mapping_column, NULL, qipa.ATTRIBUTE_STATUS||'005',qipa.ATTRIBUTE_STATUS)
342 FROM qp_segments_v a, qp_prc_contexts_b b, qp_interface_pricing_attribs c
343 WHERE b.prc_context_id = a.prc_context_id
344 AND b.prc_context_code = c.PRICING_ATTRIBUTE_CONTEXT
345 AND a.segment_code = c.PRICING_ATTR_CODE
346 AND a.segment_mapping_column like 'PRICING%'
347 AND qipa.rowid = c.rowid)
348 WHERE qipa.REQUEST_ID = P_REQUEST_ID
349 AND qipa.process_status_flag = 'P' --IS NOT NULL
350 AND qipa.PRICING_ATTRIBUTE IS NULL
351 --Bug# 5456164
352 --Don't do conversion if Value is G_NULL_CHAR
353 AND qipa.PRICING_ATTR_CODE <> QP_BULK_LOADER_PUB.G_NULL_CHAR
357 --5. Product_Attr_Value
354 AND qipa.PRICING_ATTR_CODE IS NOT NULL
355 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
356
358 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Attribute Value1');
359 UPDATE /*+ index(qipa QP_INTERFACE_PRCNG_ATTRIBS_N1) */ QP_INTERFACE_PRICING_ATTRIBS qipa --7323577
360 SET (PRODUCT_ATTR_VALUE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
361 (SELECT a.inventory_item_id,
362 DECODE(a.inventory_item_id, NULL, null, 'P'),
363 DECODE(a.inventory_item_id, NULL, qipa.ATTRIBUTE_STATUS||'004',qipa.ATTRIBUTE_STATUS)
364 FROM mtl_system_items_vl a, qp_interface_pricing_attribs c
365 WHERE (c.product_attr_value IS NULL and
366 a.concatenated_segments= c.product_attr_val_disp
367 OR c.product_attr_value IS NOT NULL and
368 a.inventory_item_id = c.product_attr_value)
369 AND qipa.rowid = c.rowid
370 AND rownum <2)
371 WHERE qipa.REQUEST_ID = P_REQUEST_ID
372 AND qipa.process_status_flag = 'P' --IS NOT NULL
373 AND qipa.product_attribute_context='ITEM'
374 AND qipa.product_attribute = 'PRICING_ATTRIBUTE1'
375 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
376
377 --6. Product_Attr_Value
378 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Attribute Value2');
379 UPDATE QP_INTERFACE_PRICING_ATTRIBS qipa
380 SET (PRODUCT_ATTR_VALUE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
381 (SELECT a.category_id,
382 DECODE(a.category_id, NULL, null, 'P'),
383 DECODE(a.category_id, NULL, qipa.ATTRIBUTE_STATUS||'004',qipa.ATTRIBUTE_STATUS)
384 FROM qp_item_categories_v a, qp_interface_pricing_attribs c
385 WHERE (c.product_attr_value IS NULL and
386 a.category_name = c.product_attr_val_disp
387 OR c.product_attr_value IS NOT NULL and
388 a.category_id = c.product_attr_value)
389 AND qipa.rowid = c.rowid
390 AND rownum <2)
391 WHERE qipa.REQUEST_ID = P_REQUEST_ID
392 AND qipa.process_status_flag = 'P' --IS NOT NULL
393 AND qipa.product_attribute_context='ITEM'
394 AND qipa.product_attribute = 'PRICING_ATTRIBUTE2'
395 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
396
397 FOR rec IN c_price_attr
398 LOOP
399 qp_bulk_loader_pub.write_log('In the Pricing attr value to id loop');
400 qp_bulk_loader_pub.write_log('product_attr_code: '|| rec.product_attr_code);
401 qp_bulk_loader_pub.write_log('product_attribute: '|| rec.product_attribute);
402 --PRODUCT_ATTRIBUTE
403 --PRODUCT_ATTR_VALUE
404
405 l_product_attr_value := null; --bug7315184
406
407 qp_bulk_loader_pub.write_log('Product_attribute_context/product attribute'||rec.product_attribute_context||l_product_attribute);
408 IF rec.product_attribute_context='ITEM'
409 and rec.product_attribute='PRICING_ATTRIBUTE1' THEN
410 NULL;
411 ELSIF rec.product_attribute_context='ITEM'
412 and rec.product_attribute='PRICING_ATTRIBUTE2' THEN
413 NULL;
414 ELSE
415 qp_bulk_loader_pub.write_log('Product context/attribute is not Item nor category');
416 if rec.product_attr_val_disp is not NULL and
417 rec.product_attr_value is NULL then
418 BEGIN
419 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
420 ('QP_ATTR_DEFNS_PRICING',
421 rec.product_attribute_context,
422 rec.product_attribute);
423
424 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
425 /*
426 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
427 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
428 p_context => rec.product_attribute_context,
429 p_segment => l_segment_name,
430 p_meaning => rec.product_attr_val_disp,
431 x_value => x_value,
432 x_id => x_id,
433 x_format_type => x_format_type);
434 */
435 QP_UTIL.Get_Valueset_Id('QP_ATTR_DEFNS_PRICING',
436 rec.product_attribute_context,
437 l_segment_name,
438 x_vsid, x_format_type, x_validation_type);
439 qp_bulk_loader_pub.write_log('vsid: '||to_char(x_vsid));
440 FND_VSET.get_valueset(x_vsid, vset, fmt);
441 FND_VSET.get_value_init(vset, TRUE);
442 FND_VSET.get_value(vset, row, found, value);
443
444 WHILE (found) LOOP
445 qp_bulk_loader_pub.write_log('meaning: '||value.meaning);
446 IF (fmt.has_meaning AND
447 ltrim(rtrim(value.meaning))=ltrim(rtrim(rec.product_attr_val_disp))
448 ) OR
449 ltrim(rtrim(value.value)) = ltrim(rtrim(rec.product_attr_val_disp))
450 THEN
451 qp_bulk_loader_pub.write_log('success: ');
452
453 IF fmt.has_id THEN
454 qp_bulk_loader_pub.write_log('fmt.has_id');
455 x_id := value.id;
456 x_value := value.value;
457 ELSE
458 x_value := value.value;
459 END IF;
460
461 EXIT;
462
463 END IF; -- If value.meaning or value.value matches with p_meaning
464
465 FND_VSET.get_value(vset, row, found, value);
466 END LOOP;
467
468 FND_VSET.get_value_end(vset);
469
470 qp_bulk_loader_pub.write_log('x_value/x_id: ' || x_value || '/' || x_id);
471 if x_value is NULL then
472 x_value := rec.product_attr_val_disp;
473 --If a match is not found in the valueset.
474 end if;
475
479 IF x_id IS NOT NULL THEN
476 qp_bulk_loader_pub.write_log('getting product_attr_value using flex_meaning_to_value_id');
477 qp_bulk_loader_pub.write_log('product_attr_val_disp '||rec.product_attr_val_disp);
478
480 l_product_attr_value := x_id;
481 ELSE
482 l_product_attr_value := x_value;
483 END IF;
484
485 qp_bulk_loader_pub.write_log('product_attr_value is '||l_product_attr_value);
486 IF l_product_attr_value IS NULL THEN
487 qp_bulk_loader_pub.write_log('product_attr_value is NULL - ERROR');
488 UPDATE QP_INTERFACE_PRICING_ATTRIBS
489 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
490 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
491 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
492 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
493 ELSE
494 UPDATE QP_INTERFACE_PRICING_ATTRIBS
495 SET PRODUCT_ATTR_VALUE = l_product_attr_value
496 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
497 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
498 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
499 END IF;
500 EXCEPTION
501 WHEN OTHERS THEN
502 UPDATE QP_INTERFACE_PRICING_ATTRIBS
503 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
504 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
505 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
506 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
507 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
508 END;
509 END IF; -- product_attr_val is NULL
510 END IF;
511
512 if rec.product_attribute_context='ITEM' and
513 (rec.product_attribute='PRICING_ATTRIBUTE1' or
514 rec.product_attribute='PRICING_ATTRIBUTE2') THEN
515 -- Precedence is already determined.
516 NULL;
517 else
518 if rec.product_attr_value is not null
519 and l_product_attr_value is NULL then
520 l_product_attr_value := rec.product_attr_value;
521 end if;
522 if l_orig_sys_line_ref IS NULL or
523 l_orig_sys_line_ref <> REC.orig_sys_line_ref then
524 l_orig_sys_line_ref := REC.ORIG_SYS_LINE_REF;
525 qp_bulk_loader_pub.write_log('default line precedence once for a line');
526 qp_bulk_loader_pub.write_log('Context/Attribute/Value'||rec.product_attribute_context||'/'||l_product_attribute||'/'||l_product_attr_value);
527 QP_UTIL.validate_qp_flexfield(
528 flexfield_name=> 'QP_ATTR_DEFNS_PRICING'
529 ,context =>rec.product_attribute_context
530 ,attribute =>rec.product_attribute
531 ,value =>l_product_attr_value
532 ,application_short_name => 'QP'
533 ,context_flag =>l_context_error
534 ,attribute_flag =>l_attribute_error
535 ,value_flag =>l_value_error
536 ,datatype =>l_datatype
537 ,precedence =>l_precedence
538 ,error_code =>l_error_code
539 );
540
541 qp_bulk_loader_pub.write_log('line ref'||rec.orig_sys_line_ref||'error_code'||to_char(l_error_code));
542 If l_error_code = 0 Then
543 UPDATE QP_INTERFACE_LIST_LINES
544 SET PRODUCT_PRECEDENCE = l_precedence
545 WHERE ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF
546 AND PROCESS_STATUS_FLAG = 'P'
547 AND PRODUCT_PRECEDENCE IS NULL;
548 else
549 UPDATE QP_INTERFACE_LIST_LINES
550 SET PROCESS_STATUS_FLAG = NULL,
551 ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
552 WHERE ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF
553 AND PROCESS_STATUS_FLAG = 'P'; --IS NULL;
554 end if;
555 end if;
556 end if;
557 --PRICING_ATTRIBUTE
558 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attribute');
559 qp_bulk_loader_pub.write_log('Pricing_attr_code: '|| rec.pricing_attr_code);
560 qp_bulk_loader_pub.write_log('Pricing_attribute: '|| rec.pricing_attribute);
561 --PRICING_ATTR_VALUE_FROM
562 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attr value from');
563 qp_bulk_loader_pub.write_log('Pricing_attr_value_from : '|| rec.pricing_attr_value_from);
564 qp_bulk_loader_pub.write_log('Pricing_attr_value_from_disp: '|| rec.pricing_attr_value_from_disp);
565 IF rec.pricing_attr_value_from IS NULL and
566 rec.pricing_attr_value_from_disp IS NOT NULL THEN
567 BEGIN
568 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
569 ('QP_ATTR_DEFNS_PRICING',
570 rec.pricing_attribute_context,
571 rec.pricing_attribute);
572 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
573
574 qp_bulk_loader_pub.write_log('getting pricing_attr_value_from_disp using flex_meaning_to_value_id');
575 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
576 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
577 p_context => rec.pricing_attribute_context,
578 p_segment => l_segment_name,
579 p_meaning => rec.pricing_attr_value_from_disp,
580 x_value => x_value,
581 x_id => x_id,
582 x_format_type => x_format_type);
583
584
585 IF x_id IS NOT NULL THEN
589 END IF;
586 l_pricing_attr_value_from := x_id;
587 ELSE
588 l_pricing_attr_value_from := x_value;
590
591 qp_bulk_loader_pub.write_log('pricing_attr_value_from is '||l_pricing_attr_value_from);
592 UPDATE QP_INTERFACE_PRICING_ATTRIBS
593 SET PRICING_ATTR_VALUE_FROM = l_pricing_attr_value_from
594 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
595 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
596 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 UPDATE QP_INTERFACE_PRICING_ATTRIBS
601 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'006'
602 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
603 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
604 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
605 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
606 END;
607 END IF;
608
609 --PRICING_ATTR_VALUE_TO
610 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attr value to');
611 qp_bulk_loader_pub.write_log('Pricing_attr_value_to : '|| rec.pricing_attr_value_to);
612 qp_bulk_loader_pub.write_log('Pricing_attr_value_to_disp: '|| rec.pricing_attr_value_to_disp);
613 IF rec.pricing_attr_value_to IS NULL and
614 rec.pricing_attr_value_to_disp IS NOT NULL THEN
615 BEGIN
616 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
617 ('QP_ATTR_DEFNS_PRICING',
618 rec.pricing_attribute_context,
619 rec.pricing_attribute);
620
621 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
622
623 qp_bulk_loader_pub.write_log('getting pricing_attr_value_to_disp using flex_meaning_to_value_id');
624 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
625 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
626 p_context => rec.pricing_attribute_context,
627 p_segment => l_segment_name,
628 p_meaning => rec.pricing_attr_value_to_disp,
629 x_value => x_value,
630 x_id => x_id,
631 x_format_type => x_format_type);
632
633 IF x_id IS NOT NULL THEN
634 l_pricing_attr_value_to := x_id;
635 ELSE
636 l_pricing_attr_value_to := x_value;
637 END IF;
638
639 qp_bulk_loader_pub.write_log('pricing_attr_value_to is '||l_pricing_attr_value_to);
640 UPDATE QP_INTERFACE_PRICING_ATTRIBS
641 SET PRICING_ATTR_VALUE_TO = l_pricing_attr_value_to
642 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
643 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
644 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 UPDATE QP_INTERFACE_PRICING_ATTRIBS
649 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'007'
650 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
651 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
652 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
653 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
654 END;
655 END IF;
656
657 END LOOP;
658 qp_bulk_loader_pub.write_log( 'Leaving Value to ID Lines');
659
660 EXCEPTION
661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
662 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.LINE:'||sqlerrm);
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 WHEN OTHERS THEN
665 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.LINE:'||sqlerrm);
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END LINE;
668
669
670 PROCEDURE INSERT_HEADER_ERROR_MESSAGES
671 (p_request_id NUMBER)
672 IS
673 CURSOR c_error_records IS
674 SELECT orig_sys_header_ref,
675 attribute_status
676 FROM qp_interface_list_headers
677 WHERE request_id = p_request_id
678 AND process_status_flag is null
679 AND attribute_status IS NOT NULL;
680
681 l_counter NUMBER:=0;
682 l_first NUMBER:=0;
683 l_attribute VARCHAR2(30);
684 l_attr_code VARCHAR2(3);
685 l_msg_data VARCHAR2(2000);
686 l_msg_txt VARCHAR2(2000);
687
688 BEGIN
689
690 qp_bulk_loader_pub.write_log( 'Entering Insert Header Error Messages');
691
692 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
693
694 FOR l_err in c_error_records LOOP
695
696 l_counter := length(l_err.attribute_status)/3;
697 IF l_counter < 1
698 THEN
699 GOTO END_OF_LOOP;
700 END IF;
701
702 l_first:=1;
703
704 FOR i IN 1..l_counter LOOP
705
706 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
707
708 IF l_attr_code = '001' THEN
709 l_attribute := 'CURRENCY HEADER';
710 ELSIF l_attr_code = '002' THEN
711 l_attribute := 'FREIGHT TERMS';
712 ELSIF l_attr_code = '003' THEN
713 l_attribute := 'SHIP METHOD';
714 ELSIF l_attr_code = '004' THEN
715 l_attribute := 'TERMS';
716 END IF;
720 INSERT INTO QP_INTERFACE_ERRORS
717
718 l_msg_txt:=l_msg_data||' '||l_attribute;
719
721 (error_id,last_update_date, last_updated_by, creation_date,
722 created_by, last_update_login, request_id, program_application_id,
723 program_id, program_update_date, entity_type, table_name, column_name,
724 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
725 orig_sys_pricing_attr_ref,error_message)
726 VALUES
727 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
728 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
729 NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', NULL,
730 l_err.orig_sys_header_ref,null,null,null,l_msg_txt);
731
732 l_first:=l_first+3;
733
734 END LOOP;
735 <<END_OF_LOOP>>
736 NULL;
737 END LOOP;
738
739 qp_bulk_loader_pub.write_log( 'Leaving Insert Header Error Messages');
740
741 EXCEPTION
742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
743 qp_bulk_loader_pub.write_log(
744 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_HEADER_ERROR_MESSAGES:'||sqlerrm);
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 WHEN OTHERS THEN
747 qp_bulk_loader_pub.write_log(
748 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_HEADER_ERROR_MESSAGES:'||sqlerrm);
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
750
751 END INSERT_HEADER_ERROR_MESSAGES;
752
753 PROCEDURE INSERT_QUAL_ERROR_MESSAGE
754 (p_request_id NUMBER)
755 IS
756 CURSOR c_error_records IS
757 SELECT orig_sys_qualifier_ref,
758 orig_sys_header_ref,
759 attribute_status
760 FROM qp_interface_qualifiers
761 WHERE request_id = p_request_id
762 AND process_status_flag is null;
763 -- AND attribute_status IS NOT NULL;
764
765 l_counter NUMBER:=0;
766 l_first NUMBER:=0;
767 l_attribute VARCHAR2(30);
768 l_attr_code VARCHAR2(3);
769 l_msg_data VARCHAR2(2000);
770 l_msg_txt VARCHAR2(2000);
771
772 BEGIN
773
774 qp_bulk_loader_pub.write_log( 'Entering Insert Qualifier Error Messages');
775
776 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
777
778 FOR l_err in c_error_records LOOP
779
780 IF l_err.attribute_status is not NULL then
781 l_counter := length(l_err.attribute_status)/3;
782 ELSE
783 l_counter := 0;
784 END if;
785 IF l_counter < 1
786 THEN
787 GOTO END_OF_LOOP;
788 END IF;
789
790 l_first:=1;
791
792 FOR i IN 1..l_counter LOOP
793
794 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
795
796 IF l_attr_code = '001' THEN
797 l_attribute := 'CREATED_FROM_RULE';
798 ELSIF l_attr_code = '002' THEN
799 l_attribute := 'QUALIFIER_RULE';
800 END IF;
801
802 l_msg_txt:=l_msg_data||' '||l_attribute;
803
804 INSERT INTO QP_INTERFACE_ERRORS
805 (error_id,last_update_date, last_updated_by, creation_date,
806 created_by, last_update_login, request_id, program_application_id,
807 program_id, program_update_date, entity_type, table_name, column_name,
808 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
809 orig_sys_pricing_attr_ref, error_message)
810 VALUES
811 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
812 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
813 NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', NULL,
814 l_err.orig_sys_header_ref,null,l_err.orig_sys_qualifier_ref,null,l_msg_txt);
815
816 l_first:=l_first+3;
817
818 END LOOP;
819 <<END_OF_LOOP>>
820 IF l_err.attribute_status is NULL then
821 l_attribute := 'QUALIFIER_ATTRIBUTE';
822 l_msg_txt:=l_msg_data||' '||l_attribute;
823
824 INSERT INTO QP_INTERFACE_ERRORS
825 (error_id,last_update_date, last_updated_by, creation_date,
826 created_by, last_update_login, request_id, program_application_id,
827 program_id, program_update_date, entity_type, table_name, column_name,
828 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
829 orig_sys_pricing_attr_ref, error_message)
830 VALUES
831 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
832 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
833 NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', NULL,
834 l_err.orig_sys_header_ref,null,l_err.orig_sys_qualifier_ref,null,l_msg_txt);
835 END IF;
836 NULL;
837 END LOOP;
838
839 qp_bulk_loader_pub.write_log( 'Leaving Insert Qualifier Error Messages');
840
841 EXCEPTION
842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
843 qp_bulk_loader_pub.write_log(
844 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_QUAL_ERROR_MESSAGES:'||sqlerrm);
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 WHEN OTHERS THEN
847 qp_bulk_loader_pub.write_log(
848 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_QUAL_ERROR_MESSAGES:'||sqlerrm);
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850
851 END INSERT_QUAL_ERROR_MESSAGE;
852
853
857 CURSOR c_error_records IS
854 PROCEDURE INSERT_LINE_ERROR_MESSAGE
855 (p_request_id NUMBER)
856 IS
858 SELECT orig_sys_header_ref orig_header_ref,
859 orig_sys_line_ref orig_line_ref,
860 null orig_attr_ref,
861 attribute_status, null pac, null pa, null pcac, null pca, null pav, null pavd,
862 'QP_INTERFACE_LIST_LINES' table_name
863 FROM qp_interface_list_lines
864 WHERE request_id = p_request_id
865 AND process_status_flag is null
866 AND attribute_status IS NOT NULL
867 UNION
868 SELECT orig_sys_header_ref orig_header_ref,
869 orig_sys_line_ref orig_line_ref,
870 orig_sys_pricing_attr_ref orig_attr_ref,
871 attribute_status, product_attr_code pac, product_attribute pa,
872 pricing_attr_code pcac, pricing_attribute pca,
873 product_attr_value pav, product_attr_val_disp pavd,
874 'QP_INTERFACE_PRICING_ATTRIBS' table_name
875 FROM qp_interface_pricing_attribs
876 WHERE request_id = p_request_id
877 AND process_status_flag is null;
878 -- AND attribute_status IS NOT NULL;
879
880 l_counter NUMBER:=0;
881 l_first NUMBER:=0;
882 l_attribute VARCHAR2(30);
883 l_attr_code VARCHAR2(3);
884 l_msg_data VARCHAR2(2000);
885 l_msg_txt VARCHAR2(2000);
886
887 BEGIN
888 qp_bulk_loader_pub.write_log( 'Entering Insert Line Error Messages');
889
890 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
891
892 FOR l_err in c_error_records LOOP
893 IF l_err.attribute_status is NULL then
894 if l_err.pac is not NULL and l_err.pa is NULL then
895 l_attribute := 'PRODUCT_ATTRIBUTE';
896 l_msg_txt:=l_msg_data||' '||l_attribute;
897 INSERT INTO QP_INTERFACE_ERRORS
898 (error_id,last_update_date, last_updated_by, creation_date,
899 created_by, last_update_login, request_id, program_application_id,
900 program_id, program_update_date, entity_type, table_name, column_name,
901 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
902 orig_sys_pricing_attr_ref,error_message)
903 VALUES
904 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
905 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
906 NULL,NULL, 'PRL',l_err.table_name, NULL,
907 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
908 end if;
909 if l_err.pcac is not NULL and l_err.pca is NULL then
910 l_attribute := 'PRICING_ATTRIBUTE';
911 l_msg_txt:=l_msg_data||' '||l_attribute;
912 INSERT INTO QP_INTERFACE_ERRORS
913 (error_id,last_update_date, last_updated_by, creation_date,
914 created_by, last_update_login, request_id, program_application_id,
915 program_id, program_update_date, entity_type, table_name, column_name,
916 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
917 orig_sys_pricing_attr_ref,error_message)
918 VALUES
919 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
920 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
921 NULL,NULL, 'PRL',l_err.table_name, NULL,
922 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
923 end if;
924 if l_err.pavd is not NULL and l_err.pav is NULL then
925 l_attribute := 'PRODUCT_ATTRIBUTE_VALUE';
926 l_msg_txt:=l_msg_data||' '||l_attribute;
927 INSERT INTO QP_INTERFACE_ERRORS
928 (error_id,last_update_date, last_updated_by, creation_date,
929 created_by, last_update_login, request_id, program_application_id,
930 program_id, program_update_date, entity_type, table_name, column_name,
931 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
932 orig_sys_pricing_attr_ref,error_message)
933 VALUES
934 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
935 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
936 NULL,NULL, 'PRL',l_err.table_name, NULL,
937 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
938 end if;
939 if l_err.pavd is NULL and l_err.pav is NULL then
940 l_msg_txt := FND_MESSAGE.GET_STRING('QP','QP_INVALID_PROD_VALUE');
941 INSERT INTO QP_INTERFACE_ERRORS
942 (error_id,last_update_date, last_updated_by, creation_date,
943 created_by, last_update_login, request_id, program_application_id,
944 program_id, program_update_date, entity_type, table_name, column_name,
945 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
946 orig_sys_pricing_attr_ref,error_message)
947 VALUES
948 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
949 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
950 NULL,NULL, 'PRL',l_err.table_name, NULL,
951 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
952 end if;
953 ELSE
954 l_counter := length(l_err.attribute_status)/3;
955 IF l_counter < 1
956 THEN
957 GOTO END_OF_LOOP;
958 END IF;
959
960 l_first:=1;
961
962 FOR i IN 1..l_counter LOOP
963
964 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
965
966 IF l_attr_code = '001' THEN
967 l_attribute := 'PRICE_BY_FORMULA';
968 ELSIF l_attr_code = '002' THEN
969 l_attribute := 'GENERATE_USING_FORMULA';
970 ELSIF l_attr_code = '003' THEN
971 l_attribute := 'PRODUCT_ATTRIBUTE';
972 ELSIF l_attr_code = '004' THEN
973 l_attribute := 'PRODUCT_ATTRIBUTE_VALUE';
974 ELSIF l_attr_code = '005' THEN
975 l_attribute := 'PRICING_ATTRIBUTE';
976 ELSIF l_attr_code = '006' THEN
977 l_attribute := 'PRICING_ATTRIBUTE_VALUE_FROM';
978 ELSIF l_attr_code = '007' THEN
979 l_attribute := 'PRICING_ATTRIBUTE_VALUE_TO';
980
981 END IF;
982 IF l_attr_code = '008' THEN
983 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_INVALID_PROD_VALUE');
984 l_attribute := ' ';
985 END IF;
986
987 l_msg_txt:=l_msg_data||' '||l_attribute;
988
989 INSERT INTO QP_INTERFACE_ERRORS
990 (error_id,last_update_date, last_updated_by, creation_date,
991 created_by, last_update_login, request_id, program_application_id,
992 program_id, program_update_date, entity_type, table_name, column_name,
993 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
994 orig_sys_pricing_attr_ref,error_message)
995 VALUES
996 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
997 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
998 NULL,NULL, 'PRL',l_err.table_name, NULL,
999 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
1000
1001 l_first:=l_first+3;
1002
1003 END LOOP;
1004 END IF;
1005 <<END_OF_LOOP>>
1006 NULL;
1007 END LOOP;
1008
1009 qp_bulk_loader_pub.write_log( 'Leaving Insert Line Error Messages');
1010
1011 EXCEPTION
1012 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1013 qp_bulk_loader_pub.write_log(
1014 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_LINE_ERROR_MESSAGES:'||sqlerrm);
1015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1016 WHEN OTHERS THEN
1017 qp_bulk_loader_pub.write_log(
1018 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_LINE_ERROR_MESSAGES:'||sqlerrm);
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020
1021 END INSERT_LINE_ERROR_MESSAGE;
1022
1023 END QP_BULK_VALUE_TO_ID;