[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.8 2010/08/27 12:05:17 smbalara 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
354 AND qipa.PRICING_ATTR_CODE IS NOT NULL
355 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
356
357 --5. Product_Attr_Value
358 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Attribute Value1');
359 --Query is commented for bug 10065970 smbalara
360
361 /* UPDATE /*+ index(qipa QP_INTERFACE_PRCNG_ATTRIBS_N1) */ /*QP_INTERFACE_PRICING_ATTRIBS qipa --7323577
362 SET (PRODUCT_ATTR_VALUE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
363 (SELECT a.inventory_item_id,
364 DECODE(a.inventory_item_id, NULL, null, 'P'),
365 DECODE(a.inventory_item_id, NULL, qipa.ATTRIBUTE_STATUS||'004',qipa.ATTRIBUTE_STATUS)
366 FROM mtl_system_items_vl a, qp_interface_pricing_attribs c
367 WHERE (c.product_attr_value IS NULL and
368 a.concatenated_segments= c.product_attr_val_disp
369 OR c.product_attr_value IS NOT NULL and
370 a.inventory_item_id = c.product_attr_value)
371 AND a.ORGANIZATION_ID = FND_PROFILE.VALUE('QP_ORGANIZATION_ID') --8402384
372 AND qipa.rowid = c.rowid
373 AND rownum <2)
374 WHERE qipa.REQUEST_ID = P_REQUEST_ID
375 AND qipa.process_status_flag = 'P' --IS NOT NULL
376 AND qipa.product_attribute_context='ITEM'
377 AND qipa.product_attribute = 'PRICING_ATTRIBUTE1'
378 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');*/
379
380 UPDATE /*+ index(qipa QP_INTERFACE_PRCNG_ATTRIBS_N1) */ QP_INTERFACE_PRICING_ATTRIBS QIPA
381 SET (PRODUCT_ATTR_VALUE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS) =
382 (
383 SELECT A.INVENTORY_ITEM_ID, DECODE(A.INVENTORY_ITEM_ID, NULL, NULL, 'P'),
384 DECODE(INVENTORY_ITEM_ID, NULL,QIPA.ATTRIBUTE_STATUS||'004',QIPA.ATTRIBUTE_STATUS)
385 FROM MTL_SYSTEM_ITEMS_VL A
386 WHERE QIPA.PRODUCT_ATTR_VALUE IS NOT NULL
387 AND A.INVENTORY_ITEM_ID = QIPA.PRODUCT_ATTR_VALUE
388 AND A.ORGANIZATION_ID = FND_PROFILE.VALUE('QP_ORGANIZATION_ID')
389 AND ROWNUM < 2
390 UNION ALL
391 SELECT A.INVENTORY_ITEM_ID, DECODE(A.INVENTORY_ITEM_ID, NULL, NULL, 'P'),
392 DECODE(INVENTORY_ITEM_ID, NULL, QIPA.ATTRIBUTE_STATUS||'004',QIPA.ATTRIBUTE_STATUS)
393 FROM MTL_SYSTEM_ITEMS_VL A
394 WHERE QIPA.PRODUCT_ATTR_VALUE IS NULL
395 AND A.CONCATENATED_SEGMENTS=QIPA.PRODUCT_ATTR_VAL_DISP
396 AND A.ORGANIZATION_ID = FND_PROFILE.VALUE('QP_ORGANIZATION_ID')
397 AND ROWNUM < 2
398 )
399 WHERE QIPA.REQUEST_ID = P_REQUEST_ID
400 AND QIPA.PROCESS_STATUS_FLAG = 'P'
401 AND QIPA.PRODUCT_ATTRIBUTE_CONTEXT='ITEM'
402 AND QIPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
403 AND QIPA.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
404
405 --bug 10065970 smbalara end
406
407
408 --6. Product_Attr_Value
409 qp_bulk_loader_pub.write_log( 'Entering Value to ID Lines Product Attribute Value2');
410 UPDATE QP_INTERFACE_PRICING_ATTRIBS qipa
411 SET (PRODUCT_ATTR_VALUE, PROCESS_STATUS_FLAG, ATTRIBUTE_STATUS)=
412 (SELECT a.category_id,
413 DECODE(a.category_id, NULL, null, 'P'),
414 DECODE(a.category_id, NULL, qipa.ATTRIBUTE_STATUS||'004',qipa.ATTRIBUTE_STATUS)
415 FROM qp_item_categories_v a, qp_interface_pricing_attribs c
416 WHERE (c.product_attr_value IS NULL and
417 a.category_name = c.product_attr_val_disp
418 OR c.product_attr_value IS NOT NULL and
419 a.category_id = c.product_attr_value)
420 AND qipa.rowid = c.rowid
421 AND rownum <2)
422 WHERE qipa.REQUEST_ID = P_REQUEST_ID
423 AND qipa.process_status_flag = 'P' --IS NOT NULL
424 AND qipa.product_attribute_context='ITEM'
425 AND qipa.product_attribute = 'PRICING_ATTRIBUTE2'
426 AND qipa.INTERFACE_ACTION_CODE IN ('INSERT','UPDATE');
427
428 FOR rec IN c_price_attr
429 LOOP
430 qp_bulk_loader_pub.write_log('In the Pricing attr value to id loop');
431 qp_bulk_loader_pub.write_log('product_attr_code: '|| rec.product_attr_code);
432 qp_bulk_loader_pub.write_log('product_attribute: '|| rec.product_attribute);
433 --PRODUCT_ATTRIBUTE
434 --PRODUCT_ATTR_VALUE
435
436 l_product_attr_value := null; --bug7315184
437
438 qp_bulk_loader_pub.write_log('Product_attribute_context/product attribute'||rec.product_attribute_context||l_product_attribute);
439 IF rec.product_attribute_context='ITEM'
440 and rec.product_attribute='PRICING_ATTRIBUTE1' THEN
441 NULL;
442 ELSIF rec.product_attribute_context='ITEM'
443 and rec.product_attribute='PRICING_ATTRIBUTE2' THEN
444 NULL;
445 ELSE
446 qp_bulk_loader_pub.write_log('Product context/attribute is not Item nor category');
447 if rec.product_attr_val_disp is not NULL and
448 rec.product_attr_value is NULL then
449 BEGIN
450 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
451 ('QP_ATTR_DEFNS_PRICING',
452 rec.product_attribute_context,
453 rec.product_attribute);
454
455 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
456 /*
457 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
458 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
459 p_context => rec.product_attribute_context,
460 p_segment => l_segment_name,
461 p_meaning => rec.product_attr_val_disp,
462 x_value => x_value,
463 x_id => x_id,
464 x_format_type => x_format_type);
465 */
466 QP_UTIL.Get_Valueset_Id('QP_ATTR_DEFNS_PRICING',
467 rec.product_attribute_context,
468 l_segment_name,
469 x_vsid, x_format_type, x_validation_type);
470 qp_bulk_loader_pub.write_log('vsid: '||to_char(x_vsid));
471 FND_VSET.get_valueset(x_vsid, vset, fmt);
472 FND_VSET.get_value_init(vset, TRUE);
473 FND_VSET.get_value(vset, row, found, value);
474
475 WHILE (found) LOOP
476 qp_bulk_loader_pub.write_log('meaning: '||value.meaning);
477 IF (fmt.has_meaning AND
478 ltrim(rtrim(value.meaning))=ltrim(rtrim(rec.product_attr_val_disp))
479 ) OR
480 ltrim(rtrim(value.value)) = ltrim(rtrim(rec.product_attr_val_disp))
481 THEN
482 qp_bulk_loader_pub.write_log('success: ');
483
484 IF fmt.has_id THEN
485 qp_bulk_loader_pub.write_log('fmt.has_id');
486 x_id := value.id;
487 x_value := value.value;
488 ELSE
489 x_value := value.value;
490 END IF;
491
492 EXIT;
493
494 END IF; -- If value.meaning or value.value matches with p_meaning
495
496 FND_VSET.get_value(vset, row, found, value);
497 END LOOP;
498
499 FND_VSET.get_value_end(vset);
500
501 qp_bulk_loader_pub.write_log('x_value/x_id: ' || x_value || '/' || x_id);
502 if x_value is NULL then
503 x_value := rec.product_attr_val_disp;
504 --If a match is not found in the valueset.
505 end if;
506
507 qp_bulk_loader_pub.write_log('getting product_attr_value using flex_meaning_to_value_id');
508 qp_bulk_loader_pub.write_log('product_attr_val_disp '||rec.product_attr_val_disp);
509
510 IF x_id IS NOT NULL THEN
511 l_product_attr_value := x_id;
512 ELSE
513 l_product_attr_value := x_value;
514 END IF;
515
516 qp_bulk_loader_pub.write_log('product_attr_value is '||l_product_attr_value);
517 IF l_product_attr_value IS NULL THEN
518 qp_bulk_loader_pub.write_log('product_attr_value is NULL - ERROR');
519 UPDATE QP_INTERFACE_PRICING_ATTRIBS
520 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
521 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
522 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
523 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
524 ELSE
525 UPDATE QP_INTERFACE_PRICING_ATTRIBS
526 SET PRODUCT_ATTR_VALUE = l_product_attr_value
527 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
528 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
529 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
530 END IF;
531 EXCEPTION
532 WHEN OTHERS THEN
533 UPDATE QP_INTERFACE_PRICING_ATTRIBS
534 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
535 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
536 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
537 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
538 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
539 END;
540 END IF; -- product_attr_val is NULL
541 END IF;
542
543 if rec.product_attribute_context='ITEM' and
544 (rec.product_attribute='PRICING_ATTRIBUTE1' or
545 rec.product_attribute='PRICING_ATTRIBUTE2') THEN
546 -- Precedence is already determined.
547 NULL;
548 else
549 if rec.product_attr_value is not null
550 and l_product_attr_value is NULL then
551 l_product_attr_value := rec.product_attr_value;
552 end if;
553 if l_orig_sys_line_ref IS NULL or
554 l_orig_sys_line_ref <> REC.orig_sys_line_ref then
555 l_orig_sys_line_ref := REC.ORIG_SYS_LINE_REF;
556 qp_bulk_loader_pub.write_log('default line precedence once for a line');
557 qp_bulk_loader_pub.write_log('Context/Attribute/Value'||rec.product_attribute_context||'/'||l_product_attribute||'/'||l_product_attr_value);
558 QP_UTIL.validate_qp_flexfield(
559 flexfield_name=> 'QP_ATTR_DEFNS_PRICING'
560 ,context =>rec.product_attribute_context
561 ,attribute =>rec.product_attribute
562 ,value =>l_product_attr_value
563 ,application_short_name => 'QP'
564 ,context_flag =>l_context_error
565 ,attribute_flag =>l_attribute_error
566 ,value_flag =>l_value_error
567 ,datatype =>l_datatype
568 ,precedence =>l_precedence
569 ,error_code =>l_error_code
570 );
571
572 qp_bulk_loader_pub.write_log('line ref'||rec.orig_sys_line_ref||'error_code'||to_char(l_error_code));
573 If l_error_code = 0 Then
574 UPDATE QP_INTERFACE_LIST_LINES
575 SET PRODUCT_PRECEDENCE = l_precedence
576 WHERE ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF
577 AND PROCESS_STATUS_FLAG = 'P'
578 AND PRODUCT_PRECEDENCE IS NULL;
579 else
580 UPDATE QP_INTERFACE_LIST_LINES
581 SET PROCESS_STATUS_FLAG = NULL,
582 ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'004'
583 WHERE ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF
584 AND PROCESS_STATUS_FLAG = 'P'; --IS NULL;
585 end if;
586 end if;
587 end if;
588 --PRICING_ATTRIBUTE
589 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attribute');
590 qp_bulk_loader_pub.write_log('Pricing_attr_code: '|| rec.pricing_attr_code);
591 qp_bulk_loader_pub.write_log('Pricing_attribute: '|| rec.pricing_attribute);
592 --PRICING_ATTR_VALUE_FROM
596 IF rec.pricing_attr_value_from IS NULL and
593 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attr value from');
594 qp_bulk_loader_pub.write_log('Pricing_attr_value_from : '|| rec.pricing_attr_value_from);
595 qp_bulk_loader_pub.write_log('Pricing_attr_value_from_disp: '|| rec.pricing_attr_value_from_disp);
597 rec.pricing_attr_value_from_disp IS NOT NULL THEN
598 BEGIN
599 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
600 ('QP_ATTR_DEFNS_PRICING',
601 rec.pricing_attribute_context,
602 rec.pricing_attribute);
603 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
604
605 qp_bulk_loader_pub.write_log('getting pricing_attr_value_from_disp using flex_meaning_to_value_id');
606 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
607 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
608 p_context => rec.pricing_attribute_context,
609 p_segment => l_segment_name,
610 p_meaning => rec.pricing_attr_value_from_disp,
611 x_value => x_value,
612 x_id => x_id,
613 x_format_type => x_format_type);
614
615
616 IF x_id IS NOT NULL THEN
617 l_pricing_attr_value_from := x_id;
618 ELSE
619 l_pricing_attr_value_from := x_value;
620 END IF;
621
622 qp_bulk_loader_pub.write_log('pricing_attr_value_from is '||l_pricing_attr_value_from);
623 UPDATE QP_INTERFACE_PRICING_ATTRIBS
624 SET PRICING_ATTR_VALUE_FROM = l_pricing_attr_value_from
625 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
626 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
627 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 UPDATE QP_INTERFACE_PRICING_ATTRIBS
632 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'006'
633 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
634 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
635 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
636 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
637 END;
638 END IF;
639
640 --PRICING_ATTR_VALUE_TO
641 qp_bulk_loader_pub.write_log('Value to ID for Pricing Attr value to');
642 qp_bulk_loader_pub.write_log('Pricing_attr_value_to : '|| rec.pricing_attr_value_to);
643 qp_bulk_loader_pub.write_log('Pricing_attr_value_to_disp: '|| rec.pricing_attr_value_to_disp);
644 IF rec.pricing_attr_value_to IS NULL and
645 rec.pricing_attr_value_to_disp IS NOT NULL THEN
646 BEGIN
647 l_segment_name :=QP_PRICE_LIST_LINE_UTIL.Get_Segment_Name
648 ('QP_ATTR_DEFNS_PRICING',
649 rec.pricing_attribute_context,
650 rec.pricing_attribute);
651
652 qp_bulk_loader_pub.write_log('getting segment name'||l_segment_name);
653
654 qp_bulk_loader_pub.write_log('getting pricing_attr_value_to_disp using flex_meaning_to_value_id');
655 QP_Value_To_ID.Flex_Meaning_To_Value_Id(
656 p_flexfield_name => 'QP_ATTR_DEFNS_PRICING',
657 p_context => rec.pricing_attribute_context,
658 p_segment => l_segment_name,
659 p_meaning => rec.pricing_attr_value_to_disp,
660 x_value => x_value,
661 x_id => x_id,
662 x_format_type => x_format_type);
663
664 IF x_id IS NOT NULL THEN
665 l_pricing_attr_value_to := x_id;
666 ELSE
667 l_pricing_attr_value_to := x_value;
668 END IF;
669
670 qp_bulk_loader_pub.write_log('pricing_attr_value_to is '||l_pricing_attr_value_to);
671 UPDATE QP_INTERFACE_PRICING_ATTRIBS
672 SET PRICING_ATTR_VALUE_TO = l_pricing_attr_value_to
673 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
674 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
675 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 UPDATE QP_INTERFACE_PRICING_ATTRIBS
680 SET PROCESS_STATUS_FLAG=NULL, ATTRIBUTE_STATUS=ATTRIBUTE_STATUS||'007'
681 WHERE ORIG_SYS_PRICING_ATTR_REF = REC.ORIG_SYS_PRICING_ATTR_REF
682 AND ORIG_SYS_HEADER_REF = REC.ORIG_SYS_HEADER_REF
683 AND ORIG_SYS_LINE_REF = REC.ORIG_SYS_LINE_REF;
684 qp_bulk_loader_pub.write_log('Exception Error caused'||sqlerrm);
685 END;
686 END IF;
687
688 END LOOP;
689 qp_bulk_loader_pub.write_log( 'Leaving Value to ID Lines');
690
691 EXCEPTION
692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
693 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.LINE:'||sqlerrm);
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 WHEN OTHERS THEN
696 qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.LINE:'||sqlerrm);
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 END LINE;
699
700
701 PROCEDURE INSERT_HEADER_ERROR_MESSAGES
702 (p_request_id NUMBER)
703 IS
704 CURSOR c_error_records IS
705 SELECT orig_sys_header_ref,
706 attribute_status
707 FROM qp_interface_list_headers
708 WHERE request_id = p_request_id
709 AND process_status_flag is null
710 AND attribute_status IS NOT NULL;
711
712 l_counter NUMBER:=0;
713 l_first NUMBER:=0;
714 l_attribute VARCHAR2(30);
715 l_attr_code VARCHAR2(3);
716 l_msg_data VARCHAR2(2000);
717 l_msg_txt VARCHAR2(2000);
718
719 BEGIN
720
721 qp_bulk_loader_pub.write_log( 'Entering Insert Header Error Messages');
722
723 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
724
725 FOR l_err in c_error_records LOOP
726
727 l_counter := length(l_err.attribute_status)/3;
728 IF l_counter < 1
729 THEN
730 GOTO END_OF_LOOP;
731 END IF;
732
733 l_first:=1;
734
735 FOR i IN 1..l_counter LOOP
736
737 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
738
739 IF l_attr_code = '001' THEN
740 l_attribute := 'CURRENCY HEADER';
741 ELSIF l_attr_code = '002' THEN
742 l_attribute := 'FREIGHT TERMS';
743 ELSIF l_attr_code = '003' THEN
744 l_attribute := 'SHIP METHOD';
745 ELSIF l_attr_code = '004' THEN
746 l_attribute := 'TERMS';
747 END IF;
748
749 l_msg_txt:=l_msg_data||' '||l_attribute;
750
751 INSERT INTO QP_INTERFACE_ERRORS
752 (error_id,last_update_date, last_updated_by, creation_date,
753 created_by, last_update_login, request_id, program_application_id,
754 program_id, program_update_date, entity_type, table_name, column_name,
755 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
756 orig_sys_pricing_attr_ref,error_message)
757 VALUES
758 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
759 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
760 NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', NULL,
761 l_err.orig_sys_header_ref,null,null,null,l_msg_txt);
762
763 l_first:=l_first+3;
764
765 END LOOP;
766 <<END_OF_LOOP>>
767 NULL;
768 END LOOP;
769
770 qp_bulk_loader_pub.write_log( 'Leaving Insert Header Error Messages');
771
772 EXCEPTION
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774 qp_bulk_loader_pub.write_log(
775 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_HEADER_ERROR_MESSAGES:'||sqlerrm);
776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777 WHEN OTHERS THEN
778 qp_bulk_loader_pub.write_log(
779 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_HEADER_ERROR_MESSAGES:'||sqlerrm);
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781
782 END INSERT_HEADER_ERROR_MESSAGES;
783
784 PROCEDURE INSERT_QUAL_ERROR_MESSAGE
785 (p_request_id NUMBER)
786 IS
787 CURSOR c_error_records IS
788 SELECT orig_sys_qualifier_ref,
789 orig_sys_header_ref,
790 attribute_status
791 FROM qp_interface_qualifiers
792 WHERE request_id = p_request_id
793 AND process_status_flag is null;
794 -- AND attribute_status IS NOT NULL;
795
796 l_counter NUMBER:=0;
797 l_first NUMBER:=0;
798 l_attribute VARCHAR2(30);
799 l_attr_code VARCHAR2(3);
800 l_msg_data VARCHAR2(2000);
801 l_msg_txt VARCHAR2(2000);
802
803 BEGIN
804
805 qp_bulk_loader_pub.write_log( 'Entering Insert Qualifier Error Messages');
806
807 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
808
809 FOR l_err in c_error_records LOOP
810
811 IF l_err.attribute_status is not NULL then
812 l_counter := length(l_err.attribute_status)/3;
813 ELSE
814 l_counter := 0;
815 END if;
816 IF l_counter < 1
817 THEN
818 GOTO END_OF_LOOP;
819 END IF;
820
821 l_first:=1;
822
823 FOR i IN 1..l_counter LOOP
824
825 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
826
827 IF l_attr_code = '001' THEN
828 l_attribute := 'CREATED_FROM_RULE';
829 ELSIF l_attr_code = '002' THEN
830 l_attribute := 'QUALIFIER_RULE';
831 END IF;
832
833 l_msg_txt:=l_msg_data||' '||l_attribute;
834
835 INSERT INTO QP_INTERFACE_ERRORS
836 (error_id,last_update_date, last_updated_by, creation_date,
837 created_by, last_update_login, request_id, program_application_id,
838 program_id, program_update_date, entity_type, table_name, column_name,
839 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
840 orig_sys_pricing_attr_ref, error_message)
841 VALUES
842 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
843 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
844 NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', NULL,
845 l_err.orig_sys_header_ref,null,l_err.orig_sys_qualifier_ref,null,l_msg_txt);
846
847 l_first:=l_first+3;
848
849 END LOOP;
850 <<END_OF_LOOP>>
851 IF l_err.attribute_status is NULL then
852 l_attribute := 'QUALIFIER_ATTRIBUTE';
853 l_msg_txt:=l_msg_data||' '||l_attribute;
854
855 INSERT INTO QP_INTERFACE_ERRORS
856 (error_id,last_update_date, last_updated_by, creation_date,
857 created_by, last_update_login, request_id, program_application_id,
858 program_id, program_update_date, entity_type, table_name, column_name,
859 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
860 orig_sys_pricing_attr_ref, error_message)
861 VALUES
862 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
863 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
864 NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', NULL,
865 l_err.orig_sys_header_ref,null,l_err.orig_sys_qualifier_ref,null,l_msg_txt);
866 END IF;
867 NULL;
868 END LOOP;
869
870 qp_bulk_loader_pub.write_log( 'Leaving Insert Qualifier Error Messages');
871
872 EXCEPTION
873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
874 qp_bulk_loader_pub.write_log(
875 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_QUAL_ERROR_MESSAGES:'||sqlerrm);
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 WHEN OTHERS THEN
878 qp_bulk_loader_pub.write_log(
879 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_QUAL_ERROR_MESSAGES:'||sqlerrm);
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881
882 END INSERT_QUAL_ERROR_MESSAGE;
883
884
885 PROCEDURE INSERT_LINE_ERROR_MESSAGE
886 (p_request_id NUMBER)
887 IS
888 CURSOR c_error_records IS
889 SELECT orig_sys_header_ref orig_header_ref,
890 orig_sys_line_ref orig_line_ref,
891 null orig_attr_ref,
892 attribute_status, null pac, null pa, null pcac, null pca, null pav, null pavd,
893 'QP_INTERFACE_LIST_LINES' table_name
894 FROM qp_interface_list_lines
895 WHERE request_id = p_request_id
896 AND process_status_flag is null
897 AND attribute_status IS NOT NULL
898 UNION
899 SELECT orig_sys_header_ref orig_header_ref,
900 orig_sys_line_ref orig_line_ref,
901 orig_sys_pricing_attr_ref orig_attr_ref,
902 attribute_status, product_attr_code pac, product_attribute pa,
903 pricing_attr_code pcac, pricing_attribute pca,
904 product_attr_value pav, product_attr_val_disp pavd,
905 'QP_INTERFACE_PRICING_ATTRIBS' table_name
906 FROM qp_interface_pricing_attribs
907 WHERE request_id = p_request_id
908 AND process_status_flag is null;
909 -- AND attribute_status IS NOT NULL;
910
911 l_counter NUMBER:=0;
912 l_first NUMBER:=0;
913 l_attribute VARCHAR2(30);
914 l_attr_code VARCHAR2(3);
915 l_msg_data VARCHAR2(2000);
916 l_msg_txt VARCHAR2(2000);
917
918 BEGIN
919 qp_bulk_loader_pub.write_log( 'Entering Insert Line Error Messages');
920
921 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_BULK_VALUE_TO_ID_ERROR');
922
923 FOR l_err in c_error_records LOOP
924 IF l_err.attribute_status is NULL then
925 if l_err.pac is not NULL and l_err.pa is NULL then
926 l_attribute := 'PRODUCT_ATTRIBUTE';
927 l_msg_txt:=l_msg_data||' '||l_attribute;
928 INSERT INTO QP_INTERFACE_ERRORS
929 (error_id,last_update_date, last_updated_by, creation_date,
930 created_by, last_update_login, request_id, program_application_id,
931 program_id, program_update_date, entity_type, table_name, column_name,
932 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
933 orig_sys_pricing_attr_ref,error_message)
934 VALUES
935 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
936 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
937 NULL,NULL, 'PRL',l_err.table_name, NULL,
938 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
939 end if;
940 if l_err.pcac is not NULL and l_err.pca is NULL then
941 l_attribute := 'PRICING_ATTRIBUTE';
942 l_msg_txt:=l_msg_data||' '||l_attribute;
943 INSERT INTO QP_INTERFACE_ERRORS
944 (error_id,last_update_date, last_updated_by, creation_date,
945 created_by, last_update_login, request_id, program_application_id,
946 program_id, program_update_date, entity_type, table_name, column_name,
947 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
948 orig_sys_pricing_attr_ref,error_message)
949 VALUES
950 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
951 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
952 NULL,NULL, 'PRL',l_err.table_name, NULL,
953 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
954 end if;
955 if l_err.pavd is not NULL and l_err.pav is NULL then
956 l_attribute := 'PRODUCT_ATTRIBUTE_VALUE';
957 l_msg_txt:=l_msg_data||' '||l_attribute;
958 INSERT INTO QP_INTERFACE_ERRORS
959 (error_id,last_update_date, last_updated_by, creation_date,
960 created_by, last_update_login, request_id, program_application_id,
961 program_id, program_update_date, entity_type, table_name, column_name,
962 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
963 orig_sys_pricing_attr_ref,error_message)
964 VALUES
965 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
966 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
967 NULL,NULL, 'PRL',l_err.table_name, NULL,
968 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
969 end if;
970 if l_err.pavd is NULL and l_err.pav is NULL then
971 l_msg_txt := FND_MESSAGE.GET_STRING('QP','QP_INVALID_PROD_VALUE');
972 INSERT INTO QP_INTERFACE_ERRORS
973 (error_id,last_update_date, last_updated_by, creation_date,
974 created_by, last_update_login, request_id, program_application_id,
975 program_id, program_update_date, entity_type, table_name, column_name,
976 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
977 orig_sys_pricing_attr_ref,error_message)
978 VALUES
979 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
980 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
981 NULL,NULL, 'PRL',l_err.table_name, NULL,
982 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
983 end if;
984 ELSE
985 l_counter := length(l_err.attribute_status)/3;
986 IF l_counter < 1
987 THEN
988 GOTO END_OF_LOOP;
989 END IF;
990
991 l_first:=1;
992
993 FOR i IN 1..l_counter LOOP
994
995 l_attr_code := SUBSTR(l_err.attribute_status,l_first,3);
996
997 IF l_attr_code = '001' THEN
998 l_attribute := 'PRICE_BY_FORMULA';
999 ELSIF l_attr_code = '002' THEN
1000 l_attribute := 'GENERATE_USING_FORMULA';
1001 ELSIF l_attr_code = '003' THEN
1002 l_attribute := 'PRODUCT_ATTRIBUTE';
1003 ELSIF l_attr_code = '004' THEN
1004 l_attribute := 'PRODUCT_ATTRIBUTE_VALUE';
1005 ELSIF l_attr_code = '005' THEN
1006 l_attribute := 'PRICING_ATTRIBUTE';
1007 ELSIF l_attr_code = '006' THEN
1008 l_attribute := 'PRICING_ATTRIBUTE_VALUE_FROM';
1009 ELSIF l_attr_code = '007' THEN
1010 l_attribute := 'PRICING_ATTRIBUTE_VALUE_TO';
1011
1012 END IF;
1013 IF l_attr_code = '008' THEN
1014 l_msg_data := FND_MESSAGE.GET_STRING('QP','QP_INVALID_PROD_VALUE');
1015 l_attribute := ' ';
1016 END IF;
1017
1018 l_msg_txt:=l_msg_data||' '||l_attribute;
1019
1020 INSERT INTO QP_INTERFACE_ERRORS
1021 (error_id,last_update_date, last_updated_by, creation_date,
1022 created_by, last_update_login, request_id, program_application_id,
1023 program_id, program_update_date, entity_type, table_name, column_name,
1024 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1025 orig_sys_pricing_attr_ref,error_message)
1026 VALUES
1027 (qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1028 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1029 NULL,NULL, 'PRL',l_err.table_name, NULL,
1030 l_err.orig_header_ref,l_err.orig_line_ref,null,l_err.orig_attr_ref,l_msg_txt);
1031
1032 l_first:=l_first+3;
1033
1034 END LOOP;
1035 END IF;
1036 <<END_OF_LOOP>>
1037 NULL;
1038 END LOOP;
1039
1040 qp_bulk_loader_pub.write_log( 'Leaving Insert Line Error Messages');
1041
1042 EXCEPTION
1043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1044 qp_bulk_loader_pub.write_log(
1045 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_LINE_ERROR_MESSAGES:'||sqlerrm);
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047 WHEN OTHERS THEN
1048 qp_bulk_loader_pub.write_log(
1049 'UNEXCPECTED ERROR IN QP_BULK_VALUE_TO_ID.INSERT_LINE_ERROR_MESSAGES:'||sqlerrm);
1050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1051
1052 END INSERT_LINE_ERROR_MESSAGE;
1053
1054 END QP_BULK_VALUE_TO_ID;