[Home] [Help]
PACKAGE BODY: APPS.QP_MODIFIERS_ISETUP_IMP
Source
1 PACKAGE BODY QP_MODIFIERS_ISETUP_IMP AS
2 /* $Header: QPMODIMB.pls 120.8.12020000.2 2013/02/22 07:12:13 jputta ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- QPMODIMB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package QP_MODIFIERS_ISETUP_IMP
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 31-DEC-02 Anupam Jain Initial Creation
21 -- 12-Feb-03 M V M P Tilak Modifications for bug#2797778
22 -- 07-MAR-03 Anupam Jain, removed NULL defaulted for global_flag
23 -- and net_amount_flag, Bug# 2798830
24 -- 31-MAR-03 Anupam Jain, Added NULL check for input XML Clobs
25 -- 04/21/2003 Anupam Jain, Modified to change DATE and NUMBER
26 -- fields to VARCHAR2(19) and VARCHAR2(150).
27 -- Bug# 2915610
28 ***************************************************************************/
29 FUNCTION get_product_code (p_product_attr_context varchar2,
30 p_product_attr varchar2,
31 p_product_attr_val varchar2)
32 RETURN VARCHAR2
33 is
34 item_id varchar2(240) := null;
35 c_name varchar2(240) := null;
36 l_org_id number;
37 begin
38 if p_product_attr_context = 'ITEM' THEN
39 IF p_product_attr = 'PRICING_ATTRIBUTE1' THEN
40 begin
41 SELECT KFV.concatenated_segments into item_id
42 FROM MTL_SYSTEM_ITEMS_KFV KFV
43 WHERE inventory_item_id = p_product_attr_val
44 AND rownum =1;
45 RETURN item_id;
46 EXCEPTION WHEN OTHERS THEN
47 RETURN NULL;
48 End;
49 ELSIF (p_product_attr = 'PRICING_ATTRIBUTE2') THEN
50 begin
51 select category_name into c_name
52 from qp_item_categories_v
53 where category_id = p_product_attr_val
54 and rownum = 1;
55 return c_name;
56 EXCEPTION WHEN OTHERS THEN
57 RETURN NULL;
58 end;
59 ELSE
60 RETURN p_product_attr_val;
61 End IF;
62 END IF;
63 END get_product_code;
64
65 FUNCTION get_product_value (p_product_attr_context varchar2,
66 p_product_attr varchar2,
67 p_product_attr_val varchar2)
68 RETURN VARCHAR2
69 is
73 if p_product_attr_context = 'ITEM' THEN
70 item_id varchar2(240) := null;
71 c_name_id varchar2(240) := null;
72 begin
74 IF (p_product_attr = 'PRICING_ATTRIBUTE1') THEN
75 begin
76 SELECT inventory_item_id into item_id
77 FROM MTL_SYSTEM_ITEMS_KFV KFV
78 WHERE concatenated_segments = p_product_attr_val
79 AND rownum =1;
80 RETURN item_id;
81 EXCEPTION WHEN OTHERS THEN
82 RETURN NULL;
83 end;
84 ELSIF (p_product_attr = 'PRICING_ATTRIBUTE2') THEN
85 begin
86 select category_id into c_name_id
87 from qp_item_categories_v
88 where category_name = p_product_attr_val
89 and rownum = 1;
90 return c_name_id;
91 EXCEPTION WHEN OTHERS THEN
92 RETURN NULL;
93 end;
94 ELSE
95 RETURN p_product_attr_val;
96 End IF;
97 END IF;
98 END get_product_value;
99
100
101 FUNCTION get_qualifier_code (p_qualifier_attr_context varchar2,
102 p_qualifier_attr varchar2,
103 p_qualifier_attr_val varchar2)
104 RETURN VARCHAR2
105 is
106 item_id varchar2(240) := null;
107 begin
108
109 IF p_qualifier_attr_context = 'MODLIST' THEN
110 IF p_qualifier_attr = 'QUALIFIER_ATTRIBUTE4' THEN
111 begin
112 SELECT name into item_id
113 FROM QP_LIST_HEADERS_TL
114 WHERE list_header_id=p_qualifier_attr_val and language='US';
115 RETURN item_id;--Conversion to code in case of pricelist.
116 EXCEPTION WHEN OTHERS THEN
117 RETURN NULL;
118 End;
119 ELSE
120 begin
121 RETURN p_qualifier_attr_val;
122 EXCEPTION WHEN OTHERS THEN
123 RETURN NULL;
124 End;
125 End If;
126 ELSE
127 RETURN p_qualifier_attr_val;--else return the value qp_qualifier_attr_val unchanged
128 END IF;
129 END get_qualifier_code;
130
131 FUNCTION get_qualifier_value (p_qualifier_attr_context varchar2,
132 p_qualifier_attr varchar2,
133 p_qualifier_attr_val varchar2)
134 RETURN VARCHAR2
135 is
136 item_id varchar2(240) := null;
137 begin
138
139
140 IF p_qualifier_attr_context = 'MODLIST' THEN
141 IF p_qualifier_attr = 'QUALIFIER_ATTRIBUTE4' THEN
142 begin
143 SELECT list_header_id into item_id
144 FROM QP_LIST_HEADERS_TL
145 WHERE name=p_qualifier_attr_val and rownum=1;
146 RETURN item_id;--Conversion to value in case of pricelist.
147 EXCEPTION WHEN OTHERS THEN
148 RETURN NULL;
149 End;
150 ELSE
151 begin
152 RETURN p_qualifier_attr_val;
153 EXCEPTION WHEN OTHERS THEN
154 RETURN NULL;
155 End;
156 End If;
157 ELSE
158 RETURN p_qualifier_attr_val;--else return the value qp_qualifier_attr_val unchanged
159 END IF;
160 END get_qualifier_value;
161
162
163 PROCEDURE Import_Modifiers
164 (P_debug IN VARCHAR2 := 'N',
165 P_output_dir IN VARCHAR2 := NULL,
166 P_debug_filename IN VARCHAR2 := 'QP_Modifiers_debug.log',
167 P_modifier_list_XML IN CLOB,
168 P_modifier_list_lines_XML IN CLOB,
169 P_pricing_attributes_XML IN CLOB,
170 P_Qualifiers_XML IN CLOB,
171 X_return_status OUT NOCOPY VARCHAR2,
172 X_msg_count OUT NOCOPY NUMBER,
173 X_G_MSG_DATA OUT NOCOPY Long ) IS
174
175 insCtx DBMS_XMLSave.ctxType;
176 rows NUMBER;
177 i NUMBER;
178 j NUMBER;
179 l NUMBER :=1;
180 m NUMBER;
181
182 mListHeaderId NUMBER;
183 mListLineId NUMBER;
184
185 x_msg_data Varchar2(2000);
186 p_value Varchar2(2000);
187 q_value Varchar2(2000);
188 x_msg_index number;
189
190 l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
191 l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
192 l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
193 l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
194 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type := QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_TBL;
195 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
196 l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
197 l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
198 l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
199 l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
200 l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
201 l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
202 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
203 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
204 l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
208 SELECT B.attribute1
205 l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
206
207 CURSOR qp_modifier_list_CUR IS
209 , B.attribute10
210 , B.attribute11
211 , B.attribute12
212 , B.attribute13
213 , B.attribute14
214 , B.attribute15
215 , B.attribute2
216 , B.attribute3
217 , B.attribute4
218 , B.attribute5
219 , B.attribute6
220 , B.attribute7
221 , B.attribute8
222 , B.attribute9
223 , B.automaticflag
224 , B.comments
225 , B.context
226 , B.createdby
227 , NULL creationdate
228 , B.currencycode
229 , B.discountlinesflag
230 , TO_DATE(B.enddateactive,'YYYY-MM-DD HH24:MI:SS')
231 , B.freighttermscode
232 , B.gsaindicator
233 , NULL lastupdatedby
234 , NULL lastupdatedate
235 , NULL lastupdatelogin
236 , B.listheaderid
237 , B.listtypecode
238 , NULL programapplicationid
239 , NULL programid
240 , NULL programupdatedate
241 , B.prorateflag
242 , NULL requestid
243 , TO_NUMBER(B.roundingfactor)
244 , B.shipmethodcode
245 , TO_DATE(B.startdateactive,'YYYY-MM-DD HH24:MI:SS')
246 , RA.term_id
247 --, B.TERMSID
248 , B.sourcesystemcode
249 , B.activeflag
250 , FND_API.G_MISS_NUM parentlistheaderid
251 , TO_DATE(B.startdateactivefirst,'YYYY-MM-DD HH24:MI:SS')
252 , TO_DATE(B.enddateactivefirst,'YYYY-MM-DD HH24:MI:SS')
253 , B.activedatefirsttype
254 , TO_DATE(B.startdateactivesecond,'YYYY-MM-DD HH24:MI:SS')
255 , B.globalflag
256 , TO_DATE(B.enddateactivesecond,'YYYY-MM-DD HH24:MI:SS')
257 , B.activedatesecondtype
258 , B.askforflag
259 , B.name
260 , B.description
261 , B.versionno
262 , NULL returnstatus
263 , NULL dbflag
264 , 'CREATE' operation
265 , B.ptecode
266 , B.listsourcecode
267 , B.origsystemheaderref
268 , B.shareableflag
269 , FND_API.G_MISS_NUM org_id -- added org_id for moac
270 FROM QP_LIST_HEADER_TEMP B ,
271 RA_TERMS_TL RA
272 WHERE B.TERMSNAME = RA.NAME(+)
273 AND B.LANGUAGE = RA.LANGUAGE(+)
274 AND B.VERSIONNO IS NULL
275 AND
276 NOT EXISTS (SELECT 1
277 FROM QP_LIST_HEADERS_TL TL3
278 WHERE B.NAME = TL3.NAME
279 AND TL3.VERSION_NO IS NULL);
280
281 CURSOR qp_modifier_list_line_CUR (mListHeaderId Number) IS
282 select LL.arithmeticoperator
283 , LL.attribute1
284 , LL.attribute10
285 , LL.attribute11
286 , LL.attribute12
287 , LL.attribute13
288 , LL.attribute14
289 , LL.attribute15
290 , LL.attribute2
291 , LL.attribute3
292 , LL.attribute4
293 , LL.attribute5
294 , LL.attribute6
295 , LL.attribute7
296 , LL.attribute8
297 , LL.attribute9
298 , LL.automaticflag
299 , LL.comments
300 , LL.context
301 , LL.createdby
302 , null creationdate
303 , LL.effectiveperioduom
304 , TO_DATE(LL.enddateactive,'YYYY-MM-DD HH24:MI:SS')
305 , TO_NUMBER(LL.estimaccrualrate)
306 --, LL.generateusingformulaid
307 , TL3.PRICE_FORMULA_ID
308 --, FND_API.G_MISS_NUM inventoryitemid
309 , KFV1.INVENTORY_ITEM_ID
310 , LL.lastupdatedby
311 , null lastupdatedate
312 , LL.lastupdatelogin
313 , FND_API.G_MISS_NUM listheaderid
314 , LL.listlineid
315 , LL.listlinetypecode
316 , TO_NUMBER(LL.listprice)
317 , LL.modifierlevelcode
318 , TO_NUMBER(LL.numbereffectiveperiods)
319 , TO_NUMBER(LL.operand)
320 --, LL.organizationid
321 , MTL.ORGANIZATION_ID
322 , LL.overrideflag
323 , TO_NUMBER(LL.percentprice)
324 , LL.pricebreaktypecode
325 --, LL.pricebyformulaid
326 , TL2.PRICE_FORMULA_ID
327 , LL.primaryuomflag
328 , LL.printoninvoiceflag
329 , NULL programapplicationid
330 , NULL programid
331 , null programupdatedate
332 , LL.rebatetransactiontypecode
333 -- , FND_API.G_MISS_NUM relateditemid
334 , KFV2.INVENTORY_ITEM_ID
335 , relationshiptypeid
336 , LL.repriceflag
337 , NULL requestid
338 , LL.revision
339 , TO_DATE(LL.revisiondate,'YYYY-MM-DD HH24:MI:SS')
340 , LL.revisionreasoncode
341 , TO_DATE(LL.startdateactive,'YYYY-MM-DD HH24:MI:SS')
342 , LL.substitutionattribute
343 , LL.substitutioncontext
344 , LL.substitutionvalue
345 , LL.accrualflag
346 , TO_NUMBER(LL.pricinggroupsequence)
347 , LL.incompatibilitygrpcode
348 , LL.listlineno
349 , FND_API.G_MISS_NUM RltdModifierId
350 , FND_API.G_MISS_NUM FromRltdModifierId
351 , FND_API.G_MISS_NUM ToRltdModifierId
352 , FND_API.G_MISS_NUM RltdModifierGrpNo
353 , FND_API.G_MISS_CHAR RltdModifierGrpType
354 , LL.pricingphaseid
355 , TO_NUMBER(LL.productprecedence)
356 , TO_DATE(LL.expirationperiodstartdate,'YYYY-MM-DD HH24:MI:SS')
357 , TO_NUMBER(LL.numberexpirationperiods)
358 , LL.expirationperioduom
359 , TO_DATE(LL.expirationdate,'YYYY-MM-DD HH24:MI:SS')
360 , TO_NUMBER(LL.estimglvalue)
361 , FND_API.G_MISS_NUM benefitpricelistlineid
362 , TO_NUMBER(LL.benefitlimit)
363 , LL.chargetypecode
364 , LL.chargesubtypecode
365 , TO_NUMBER(LL.benefitqty)
366 , LL.benefituomcode
367 , TO_NUMBER(LL.accrualconversionrate)
368 , LL.prorationtypecode
372 , 'CREATE' operation
369 , LL.includeonreturnsflag
370 , null returnstatus
371 , null dbflag
373 , FND_API.G_MISS_NUM modifierparentindex
374 , LL.qualificationind
375 , LL.netamountflag
376 , LL.accumattribute
377 , LL.continuouspricebreakflag
378 , NULL service_duration --service project
379 , NULL service_period -- service project
380 FROM QP_LIST_LINES_TEMP LL,
381 QP_PRICE_FORMULAS_TL TL2,
382 (SELECT concatenated_segments, inventory_item_id
383 FROM MTL_SYSTEM_ITEMS_KFV KFV group by concatenated_segments, inventory_item_id) KFV1,
384 MTL_PARAMETERS MTL,
385 QP_PRICE_FORMULAS_TL TL3,
386 (SELECT concatenated_segments, inventory_item_id
387 FROM MTL_SYSTEM_ITEMS_KFV KFV group by concatenated_segments, inventory_item_id) KFV2
388 WHERE LL.LISTHEADERID = mListHeaderId
389 AND LL.PRICEBYFORMULA = TL2.NAME(+)
390 AND LL.LANGUAGE = TL2.LANGUAGE(+)
391 AND LL.ORGANIZATIONCODE = MTL.ORGANIZATION_CODE(+)
392 AND LL.INVENTORYITEMCODE = KFV1.CONCATENATED_SEGMENTS(+)
393 AND LL.relateditemcode = KFV2.CONCATENATED_SEGMENTS(+)
394 AND LL.GENERATEUSINGFORMULA = TL3.NAME(+)
395 AND LL.LANGUAGE = TL3.LANGUAGE(+);
396
397
398 CURSOR qp_pricing_attributes_CUR (mListLineId Number) IS
399 select Q.accumulateflag
400 , Q.attribute1
401 , Q.attribute10
402 , Q.attribute11
403 , Q.attribute12
404 , Q.attribute13
405 , Q.attribute14
406 , Q.attribute15
407 , Q.attribute2
408 , Q.attribute3
409 , Q.attribute4
410 , Q.attribute5
411 , Q.attribute6
412 , Q.attribute7
413 , Q.attribute8
414 , Q.attribute9
415 , TO_NUMBER(Q.attributegroupingno)
416 , Q.context
417 , Q.createdby
418 , null creationdate
419 , Q.excluderflag
420 , Q.lastupdatedby
421 , null lastupdatedate
422 , Q.lastupdatelogin
423 , FND_API.G_MISS_NUM listlineid
424 , Q.pricingattribute
425 , Q.pricingattributecontext
426 , FND_API.G_MISS_NUM pricingattributeid
427 , Q.pricingattrvaluefrom
428 , Q.pricingattrvalueto
429 , Q.productattribute
430 , Q.productattributecontext
431 , Q.productattrvalue
432 , Q.productuomcode
433 , null programapplicationid
434 , null programid
435 , null programupdatedate
436 , Q.productattributedatatype
437 , Q.pricingattributedatatype
438 , Q.comparisonoperatorcode
439 , FND_API.G_MISS_NUM listheaderid
440 --, Q.pricingphaseid
441 , PP.PRICING_PHASE_ID
442 , null requestid
443 , TO_NUMBER(Q.pricingattrvaluefromnumber)
444 , TO_NUMBER(Q.pricingattrvaluetonumber)
445 , Q.qualificationind
446 , null return_status
447 , null db_flag
448 , 'CREATE' operation
449 , 1 modifiers_index
450 FROM
451 QP_PRICING_ATTRIBUTES_TEMP Q,
452 QP_PRICING_PHASES PP
453 WHERE Q.PRICINGPHASENAME = PP.NAME(+)
454 AND Q.LISTLINEID = mListLineId;
455
456
457 CURSOR qp_qualifiers_CUR (mListHeaderId Number) IS
458 SELECT Q.attribute1
459 , Q.attribute10
460 , Q.attribute11
461 , Q.attribute12
462 , Q.attribute13
463 , Q.attribute14
464 , Q.attribute15
465 , Q.attribute2
466 , Q.attribute3
467 , Q.attribute4
468 , Q.attribute5
469 , Q.attribute6
470 , Q.attribute7
471 , Q.attribute8
472 , Q.attribute9
473 , Q.comparisonoperatorcode
474 , Q.context
475 , Q.createdby
476 --, createdfromruleid
477 , QR1.QUALIFIER_RULE_ID
478 , null creationdate
479 --, TO_DATE(Q.enddateactive,'YYYY-MM-DD HH24:MI:SS') bug no 5298343
480 , Q.enddateactive
481 , Q.excluderflag
482 , Q.lastupdatedby
483 , null lastupdatedate
484 , Q.lastupdatelogin
485 , FND_API.G_MISS_NUM listheaderid
486 , Q.listlineid
487 , Q.programapplicationid
488 , Q.programid
489 , null programupdatedate
490 , Q.qualifierattribute
491 , Q.qualifierattrvalue
492 , Q.qualifierattrvalueto
493 , Q.qualifiercontext
494 , Q.qualifierdatatype
495 , TO_NUMBER(Q.qualifiergroupingno)
496 , FND_API.G_MISS_NUM qualifierid
497 , TO_NUMBER(Q.qualifierprecedence)
498 --, qualifierruleid
499 , QR2.QUALIFIER_RULE_ID
500 , NULL requestid
501 --, TO_DATE(Q.startdateactive,'YYYY-MM-DD HH24:MI:SS') bug no 5298343
502 , Q.startdateactive
503 , Q.listtypecode
504 , TO_NUMBER(Q.qualattrvaluefromnumber)
505 , TO_NUMBER(Q.qualattrvaluetonumber)
506 , Q.activeflag
507 , TO_NUMBER(Q.searchind)
508 , TO_NUMBER(Q.qualifiergroupcnt)
509 , Q.headerqualsexistflag
510 , TO_NUMBER(Q.distinctrowcount)
511 , null return_status
512 , NULL db_flag
513 , 'CREATE' operation
514 , FND_API.G_MISS_CHAR qualify_hier_descendents_flag
515 FROM
516 QP_QUALIFIERS_TEMP Q,
517 QP_QUALIFIER_RULES QR1,
518 QP_QUALIFIER_RULES QR2
519
520 WHERE Q.CREATEDFROMRULENAME = QR1.NAME(+)
521 AND Q.QUALIFIERFROMRULENAME = QR2.NAME(+)
522 AND Q.LISTHEADERID = mListHeaderId;
523
524
525
526 BEGIN
527 IF (P_modifier_list_XML IS NOT NULL) THEN
528 -- Modifier Header Record
529 -- get the context handle
530
534 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersListHeadersVO');
531 insCtx := Dbms_Xmlsave.newContext('QP_LIST_HEADER_TEMP');
532 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
533 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
535 -- this inserts the document
536 rows := Dbms_Xmlsave.insertXML(insCtx, P_modifier_list_XML);
537 -- this closes the handle
538 Dbms_Xmlsave.closeContext(insCtx);
539
540 END IF;
541
542
543
544 IF (P_modifier_list_lines_XML IS NOT NULL) THEN
545 -- Modifier List Line Record
546 -- get the context handle
547 insCtx := Dbms_Xmlsave.newContext('QP_LIST_LINES_TEMP');
548 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
549 Dbms_Xmlsave.setDateFormat(insCtx, 'YYYY-MM-dd HH:mm:ss');
550 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersListLinesVO');
551 -- this inserts the document
552 rows := Dbms_Xmlsave.insertXML(insCtx, P_modifier_list_lines_XML);
553 -- this closes the handle
554 Dbms_Xmlsave.closeContext(insCtx);
555 END IF;
556
557
558 IF (P_pricing_attributes_XML IS NOT NULL) THEN
559 -- PricingAttributes Record
560 -- get the context handle
561 insCtx := Dbms_Xmlsave.newContext('QP_PRICING_ATTRIBUTES_TEMP');
562 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
563 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
564 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersPricingAttributesVO');
565 -- this inserts the document
566 rows := Dbms_Xmlsave.insertXML(insCtx, P_pricing_attributes_XML);
567 -- this closes the handle
568 Dbms_Xmlsave.closeContext(insCtx);
569 END IF;
570
571
572 IF (P_Qualifiers_XML IS NOT NULL) THEN
573 -- Qualifiers Record
574 -- get the context handle
575 insCtx := Dbms_Xmlsave.newContext('QP_QUALIFIERS_TEMP');
576 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
577 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
578 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersQualifiersVO');
579 -- this inserts the document
580 rows := Dbms_Xmlsave.insertXML(insCtx, P_Qualifiers_XML);
581 -- this closes the handle
582 Dbms_Xmlsave.closeContext(insCtx);
583 END IF;
584
585
586
587 i := 1;
588 OPEN qp_modifier_list_CUR;
589 LOOP
590
591 l_MODIFIER_LIST_rec := null;
592
593 FETCH qp_modifier_list_CUR INTO l_MODIFIER_LIST_rec;
594 IF (qp_modifier_list_CUR%NOTFOUND) THEN
595 EXIT;
596 END IF;
597 mListHeaderId := l_MODIFIER_LIST_rec.list_header_id;
598
599 j := 1;
600 OPEN qp_modifier_list_line_CUR(mListHeaderId);
601 LOOP
602 FETCH qp_modifier_list_line_CUR INTO l_MODIFIERS_tbl(j);
603
604 IF (qp_modifier_list_line_CUR%NOTFOUND) THEN
605 EXIT;
606 END IF;
607
608 mListLineId := l_MODIFIERS_tbl(j).list_line_id;
609
610 -- l := 1;
611 OPEN qp_pricing_attributes_CUR(mListLineId);
612 LOOP
613 FETCH qp_pricing_attributes_CUR INTO l_PRICING_ATTR_tbl(l);
614
615 IF (qp_pricing_attributes_CUR%NOTFOUND) THEN
616 EXIT;
617 END IF;
618 p_value := QP_MODIFIERS_ISETUP_IMP.get_product_value(l_PRICING_ATTR_tbl(l).product_attribute_context,l_PRICING_ATTR_tbl(l).product_attribute,l_PRICING_ATTR_tbl(l).product_attr_value);
619 l_PRICING_ATTR_tbl(l).product_attr_value := p_value;
620 l_PRICING_ATTR_tbl(l).modifiers_index := j;
621
622 l := l + 1;
623 END LOOP;
624 CLOSE qp_pricing_attributes_CUR;
625
626 l_MODIFIERS_tbl(j).list_line_id := FND_API.G_MISS_NUM;
627
628 j := j + 1;
629 END LOOP;
630 CLOSE qp_modifier_list_line_CUR;
631
632 m := 1;
633 OPEN qp_qualifiers_CUR(mListHeaderId);
634 LOOP
635 FETCH qp_qualifiers_CUR INTO l_QUALIFIERS_tbl(m);
636
637 IF (qp_qualifiers_CUR%NOTFOUND) THEN
638 EXIT;
639 END IF;
640
641 q_value := QP_MODIFIERS_ISETUP_IMP.get_qualifier_value(l_QUALIFIERS_tbl(m).qualifier_context,l_QUALIFIERS_tbl(m).qualifier_attribute,l_QUALIFIERS_tbl(m).qualifier_attr_value);
642 l_QUALIFIERS_tbl(m).qualifier_attr_value := q_value;
643
644 m := m + 1;
645 END LOOP;
646 CLOSE qp_qualifiers_CUR;
647
648
649
650 l_MODIFIER_LIST_rec.list_header_id := FND_API.G_MISS_NUM;
651
652 i := i + 1;
653
654 BEGIN
655
656 l_x_MODIFIER_LIST_rec := l_MODIFIER_LIST_rec;
657 l_x_MODIFIERS_tbl := l_MODIFIERS_tbl;
658 l_x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
659 l_x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
660
661 QP_Modifiers_PUB.Process_Modifiers(
662 p_api_version_number=> 1.0
663 , p_init_msg_list=> FND_API.G_FALSE
664 , p_return_values=> FND_API.G_FALSE
665 , p_commit=> FND_API.G_FALSE
666 , x_return_status=> x_return_status
667 , x_msg_count=>x_msg_count
668 , x_msg_data=>x_msg_data
669 ,p_MODIFIER_LIST_rec=> l_MODIFIER_LIST_rec
670 ,p_MODIFIERS_tbl=> l_MODIFIERS_tbl
671 ,p_QUALIFIERS_tbl=> l_QUALIFIERS_tbl
672 ,p_PRICING_ATTR_tbl=> l_PRICING_ATTR_tbl
673 ,x_MODIFIER_LIST_rec=> l_x_MODIFIER_LIST_rec
674 ,x_MODIFIER_LIST_val_rec=> l_x_MODIFIER_LIST_val_rec
675 ,x_MODIFIERS_tbl=> l_x_MODIFIERS_tbl
676 ,x_MODIFIERS_val_tbl=> l_x_MODIFIERS_val_tbl
677 ,x_QUALIFIERS_tbl=> l_x_QUALIFIERS_tbl
678 ,x_QUALIFIERS_val_tbl=> l_x_QUALIFIERS_val_tbl
679 ,x_PRICING_ATTR_tbl=> l_x_PRICING_ATTR_tbl
680 ,x_PRICING_ATTR_val_tbl=> l_x_PRICING_ATTR_val_tbl
681 );
682
686 X_G_MSG_DATA := X_G_MSG_DATA || FND_GLOBAL.NewLine || FND_GLOBAL.NewLine || x_msg_data || ' :: ListHeaderId in the Input XML file is : ' || to_char(mListHeaderId);
683
684 FOR t in 1..x_msg_count LOOP
685 x_msg_data := oe_msg_pub.get( p_msg_index => t, p_encoded => 'F');
687 END LOOP;
688
689 EXCEPTION
690 WHEN FND_API.G_EXC_ERROR THEN
691
692 X_return_status := FND_API.G_RET_STS_ERROR;
693 --Get message count and data
694 --dbms_output.put_line('err msg 1 is : ' || x_msg_data);
695 X_G_MSG_DATA := X_G_MSG_DATA || FND_GLOBAL.NewLine || FND_GLOBAL.NewLine || x_msg_data || ' :: ListHeaderId in the Input XML file is : ' || mListHeaderId;
696
697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
698
699 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
700 --dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
701 for k in 1 .. x_msg_count loop
702 x_msg_data := oe_msg_pub.get( p_msg_index => k,
703 p_encoded => 'F'
704 );
705 --Get message count and data
706 --dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
707 X_G_MSG_DATA := X_G_MSG_DATA || FND_GLOBAL.NewLine || FND_GLOBAL.NewLine || x_msg_data || ' :: ListHeaderId in the Input XML file is : ' || mListHeaderId;
708 end loop;
709
710
711 WHEN OTHERS THEN
712
713 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
714 --dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
715 for k in 1 .. x_msg_count loop
716 x_msg_data := oe_msg_pub.get( p_msg_index => k,
717 p_encoded => 'F'
718 );
719 --Get message count and data
720 --dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
721 X_G_MSG_DATA := X_G_MSG_DATA || FND_GLOBAL.NewLine || FND_GLOBAL.NewLine || x_msg_data || ' :: ListHeaderId in the Input XML file is : ' || mListHeaderId;
722 end loop;
723
724 END;
725
726
727 END LOOP;
728
729 CLOSE qp_modifier_list_CUR;
730
731 END Import_Modifiers;
732
733 END QP_MODIFIERS_ISETUP_IMP;