[Home] [Help]
PACKAGE BODY: APPS.QP_MODIFIERS_ISETUP_IMP
Source
1 PACKAGE BODY QP_MODIFIERS_ISETUP_IMP AS
2 /* $Header: QPMODIMB.pls 120.8 2006/07/07 19:30:07 rbagri 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
70 item_id varchar2(240) := null;
71 c_name_id varchar2(240) := null;
72 begin
73 if p_product_attr_context = 'ITEM' THEN
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;
205 l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
206
207 CURSOR qp_modifier_list_CUR IS
208 SELECT B.attribute1
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
369 , LL.includeonreturnsflag
370 , null returnstatus
371 , null dbflag
372 , 'CREATE' operation
373 , FND_API.G_MISS_NUM modifierparentindex
374 , LL.qualificationind
375 , LL.netamountflag
376 , LL.accumattribute
377 , LL.continuouspricebreakflag
378 FROM QP_LIST_LINES_TEMP LL,
379 QP_PRICE_FORMULAS_TL TL2,
383 QP_PRICE_FORMULAS_TL TL3,
380 (SELECT concatenated_segments, inventory_item_id
381 FROM MTL_SYSTEM_ITEMS_KFV KFV group by concatenated_segments, inventory_item_id) KFV1,
382 MTL_PARAMETERS MTL,
384 (SELECT concatenated_segments, inventory_item_id
385 FROM MTL_SYSTEM_ITEMS_KFV KFV group by concatenated_segments, inventory_item_id) KFV2
386 WHERE LL.LISTHEADERID = mListHeaderId
387 AND LL.PRICEBYFORMULA = TL2.NAME(+)
388 AND LL.LANGUAGE = TL2.LANGUAGE(+)
389 AND LL.ORGANIZATIONCODE = MTL.ORGANIZATION_CODE(+)
390 AND LL.INVENTORYITEMCODE = KFV1.CONCATENATED_SEGMENTS(+)
391 AND LL.relateditemcode = KFV2.CONCATENATED_SEGMENTS(+)
392 AND LL.GENERATEUSINGFORMULA = TL3.NAME(+)
393 AND LL.LANGUAGE = TL3.LANGUAGE(+);
394
395
396 CURSOR qp_pricing_attributes_CUR (mListLineId Number) IS
397 select Q.accumulateflag
398 , Q.attribute1
399 , Q.attribute10
400 , Q.attribute11
401 , Q.attribute12
402 , Q.attribute13
403 , Q.attribute14
404 , Q.attribute15
405 , Q.attribute2
406 , Q.attribute3
407 , Q.attribute4
408 , Q.attribute5
409 , Q.attribute6
410 , Q.attribute7
411 , Q.attribute8
412 , Q.attribute9
413 , TO_NUMBER(Q.attributegroupingno)
414 , Q.context
415 , Q.createdby
416 , null creationdate
417 , Q.excluderflag
418 , Q.lastupdatedby
419 , null lastupdatedate
420 , Q.lastupdatelogin
421 , FND_API.G_MISS_NUM listlineid
422 , Q.pricingattribute
423 , Q.pricingattributecontext
424 , FND_API.G_MISS_NUM pricingattributeid
425 , Q.pricingattrvaluefrom
426 , Q.pricingattrvalueto
427 , Q.productattribute
428 , Q.productattributecontext
429 , Q.productattrvalue
430 , Q.productuomcode
431 , null programapplicationid
432 , null programid
433 , null programupdatedate
434 , Q.productattributedatatype
435 , Q.pricingattributedatatype
436 , Q.comparisonoperatorcode
437 , FND_API.G_MISS_NUM listheaderid
438 --, Q.pricingphaseid
439 , PP.PRICING_PHASE_ID
440 , null requestid
441 , TO_NUMBER(Q.pricingattrvaluefromnumber)
442 , TO_NUMBER(Q.pricingattrvaluetonumber)
443 , Q.qualificationind
444 , null return_status
445 , null db_flag
446 , 'CREATE' operation
447 , 1 modifiers_index
448 FROM
449 QP_PRICING_ATTRIBUTES_TEMP Q,
450 QP_PRICING_PHASES PP
451 WHERE Q.PRICINGPHASENAME = PP.NAME(+)
452 AND Q.LISTLINEID = mListLineId;
453
454
455 CURSOR qp_qualifiers_CUR (mListHeaderId Number) IS
456 SELECT Q.attribute1
457 , Q.attribute10
458 , Q.attribute11
459 , Q.attribute12
460 , Q.attribute13
461 , Q.attribute14
462 , Q.attribute15
463 , Q.attribute2
464 , Q.attribute3
465 , Q.attribute4
466 , Q.attribute5
467 , Q.attribute6
468 , Q.attribute7
469 , Q.attribute8
470 , Q.attribute9
471 , Q.comparisonoperatorcode
472 , Q.context
473 , Q.createdby
474 --, createdfromruleid
475 , QR1.QUALIFIER_RULE_ID
476 , null creationdate
477 --, TO_DATE(Q.enddateactive,'YYYY-MM-DD HH24:MI:SS') bug no 5298343
478 , Q.enddateactive
479 , Q.excluderflag
480 , Q.lastupdatedby
481 , null lastupdatedate
482 , Q.lastupdatelogin
483 , FND_API.G_MISS_NUM listheaderid
484 , Q.listlineid
485 , Q.programapplicationid
486 , Q.programid
487 , null programupdatedate
488 , Q.qualifierattribute
489 , Q.qualifierattrvalue
490 , Q.qualifierattrvalueto
491 , Q.qualifiercontext
492 , Q.qualifierdatatype
493 , TO_NUMBER(Q.qualifiergroupingno)
494 , FND_API.G_MISS_NUM qualifierid
495 , TO_NUMBER(Q.qualifierprecedence)
496 --, qualifierruleid
497 , QR2.QUALIFIER_RULE_ID
498 , NULL requestid
499 --, TO_DATE(Q.startdateactive,'YYYY-MM-DD HH24:MI:SS') bug no 5298343
500 , Q.startdateactive
501 , Q.listtypecode
502 , TO_NUMBER(Q.qualattrvaluefromnumber)
503 , TO_NUMBER(Q.qualattrvaluetonumber)
504 , Q.activeflag
505 , TO_NUMBER(Q.searchind)
506 , TO_NUMBER(Q.qualifiergroupcnt)
507 , Q.headerqualsexistflag
508 , TO_NUMBER(Q.distinctrowcount)
509 , null return_status
510 , NULL db_flag
511 , 'CREATE' operation
512 , FND_API.G_MISS_CHAR qualify_hier_descendents_flag
513 FROM
514 QP_QUALIFIERS_TEMP Q,
515 QP_QUALIFIER_RULES QR1,
516 QP_QUALIFIER_RULES QR2
517
518 WHERE Q.CREATEDFROMRULENAME = QR1.NAME(+)
519 AND Q.QUALIFIERFROMRULENAME = QR2.NAME(+)
520 AND Q.LISTHEADERID = mListHeaderId;
521
522
523
524 BEGIN
525 IF (P_modifier_list_XML IS NOT NULL) THEN
526 -- Modifier Header Record
527 -- get the context handle
528
529 insCtx := Dbms_Xmlsave.newContext('QP_LIST_HEADER_TEMP');
530 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
531 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
532 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersListHeadersVO');
533 -- this inserts the document
534 rows := Dbms_Xmlsave.insertXML(insCtx, P_modifier_list_XML);
535 -- this closes the handle
536 Dbms_Xmlsave.closeContext(insCtx);
537
538 END IF;
539
540
541
542 IF (P_modifier_list_lines_XML IS NOT NULL) THEN
543 -- Modifier List Line Record
544 -- get the context handle
545 insCtx := Dbms_Xmlsave.newContext('QP_LIST_LINES_TEMP');
546 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
547 Dbms_Xmlsave.setDateFormat(insCtx, 'YYYY-MM-dd HH:mm:ss');
548 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersListLinesVO');
549 -- this inserts the document
550 rows := Dbms_Xmlsave.insertXML(insCtx, P_modifier_list_lines_XML);
551 -- this closes the handle
552 Dbms_Xmlsave.closeContext(insCtx);
553 END IF;
554
555
556 IF (P_pricing_attributes_XML IS NOT NULL) THEN
557 -- PricingAttributes Record
558 -- get the context handle
559 insCtx := Dbms_Xmlsave.newContext('QP_PRICING_ATTRIBUTES_TEMP');
560 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
561 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
562 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersPricingAttributesVO');
563 -- this inserts the document
564 rows := Dbms_Xmlsave.insertXML(insCtx, P_pricing_attributes_XML);
565 -- this closes the handle
566 Dbms_Xmlsave.closeContext(insCtx);
567 END IF;
568
569
570 IF (P_Qualifiers_XML IS NOT NULL) THEN
571 -- Qualifiers Record
572 -- get the context handle
573 insCtx := Dbms_Xmlsave.newContext('QP_QUALIFIERS_TEMP');
574 Dbms_Xmlsave.setIgnoreCase(insCtx, 1);
575 Dbms_Xmlsave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
576 Dbms_Xmlsave.setRowTag(insCtx , 'ModifiersQualifiersVO');
577 -- this inserts the document
578 rows := Dbms_Xmlsave.insertXML(insCtx, P_Qualifiers_XML);
579 -- this closes the handle
580 Dbms_Xmlsave.closeContext(insCtx);
581 END IF;
582
583
584
585 i := 1;
586 OPEN qp_modifier_list_CUR;
587 LOOP
588
589 l_MODIFIER_LIST_rec := null;
590
591 FETCH qp_modifier_list_CUR INTO l_MODIFIER_LIST_rec;
592 IF (qp_modifier_list_CUR%NOTFOUND) THEN
593 EXIT;
594 END IF;
595 mListHeaderId := l_MODIFIER_LIST_rec.list_header_id;
596
597 j := 1;
598 OPEN qp_modifier_list_line_CUR(mListHeaderId);
599 LOOP
600 FETCH qp_modifier_list_line_CUR INTO l_MODIFIERS_tbl(j);
601
602 IF (qp_modifier_list_line_CUR%NOTFOUND) THEN
603 EXIT;
604 END IF;
605
606 mListLineId := l_MODIFIERS_tbl(j).list_line_id;
607
608 -- l := 1;
609 OPEN qp_pricing_attributes_CUR(mListLineId);
610 LOOP
611 FETCH qp_pricing_attributes_CUR INTO l_PRICING_ATTR_tbl(l);
612
613 IF (qp_pricing_attributes_CUR%NOTFOUND) THEN
614 EXIT;
615 END IF;
616 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);
617 l_PRICING_ATTR_tbl(l).product_attr_value := p_value;
618 l_PRICING_ATTR_tbl(l).modifiers_index := j;
619
620 l := l + 1;
621 END LOOP;
622 CLOSE qp_pricing_attributes_CUR;
623
624 l_MODIFIERS_tbl(j).list_line_id := FND_API.G_MISS_NUM;
625
626 j := j + 1;
627 END LOOP;
628 CLOSE qp_modifier_list_line_CUR;
629
630 m := 1;
631 OPEN qp_qualifiers_CUR(mListHeaderId);
632 LOOP
633 FETCH qp_qualifiers_CUR INTO l_QUALIFIERS_tbl(m);
634
635 IF (qp_qualifiers_CUR%NOTFOUND) THEN
636 EXIT;
637 END IF;
638
639 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);
640 l_QUALIFIERS_tbl(m).qualifier_attr_value := q_value;
641
642 m := m + 1;
643 END LOOP;
644 CLOSE qp_qualifiers_CUR;
645
646
647
648 l_MODIFIER_LIST_rec.list_header_id := FND_API.G_MISS_NUM;
649
650 i := i + 1;
651
652 BEGIN
653
654 l_x_MODIFIER_LIST_rec := l_MODIFIER_LIST_rec;
655 l_x_MODIFIERS_tbl := l_MODIFIERS_tbl;
656 l_x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
657 l_x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
658
659 QP_Modifiers_PUB.Process_Modifiers(
660 p_api_version_number=> 1.0
661 , p_init_msg_list=> FND_API.G_FALSE
662 , p_return_values=> FND_API.G_FALSE
663 , p_commit=> FND_API.G_FALSE
664 , x_return_status=> x_return_status
665 , x_msg_count=>x_msg_count
666 , x_msg_data=>x_msg_data
667 ,p_MODIFIER_LIST_rec=> l_MODIFIER_LIST_rec
668 ,p_MODIFIERS_tbl=> l_MODIFIERS_tbl
669 ,p_QUALIFIERS_tbl=> l_QUALIFIERS_tbl
670 ,p_PRICING_ATTR_tbl=> l_PRICING_ATTR_tbl
671 ,x_MODIFIER_LIST_rec=> l_x_MODIFIER_LIST_rec
672 ,x_MODIFIER_LIST_val_rec=> l_x_MODIFIER_LIST_val_rec
673 ,x_MODIFIERS_tbl=> l_x_MODIFIERS_tbl
674 ,x_MODIFIERS_val_tbl=> l_x_MODIFIERS_val_tbl
675 ,x_QUALIFIERS_tbl=> l_x_QUALIFIERS_tbl
676 ,x_QUALIFIERS_val_tbl=> l_x_QUALIFIERS_val_tbl
677 ,x_PRICING_ATTR_tbl=> l_x_PRICING_ATTR_tbl
678 ,x_PRICING_ATTR_val_tbl=> l_x_PRICING_ATTR_val_tbl
679 );
680
681
682 FOR t in 1..x_msg_count LOOP
683 x_msg_data := oe_msg_pub.get( p_msg_index => t, p_encoded => 'F');
684 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);
685 END LOOP;
686
687 EXCEPTION
688 WHEN FND_API.G_EXC_ERROR THEN
689
690 X_return_status := FND_API.G_RET_STS_ERROR;
691 --Get message count and data
692 --dbms_output.put_line('err msg 1 is : ' || x_msg_data);
696
693 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;
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
697 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
698 --dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
699 for k in 1 .. x_msg_count loop
700 x_msg_data := oe_msg_pub.get( p_msg_index => k,
701 p_encoded => 'F'
702 );
703 --Get message count and data
704 --dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
705 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;
706 end loop;
707
708
709 WHEN OTHERS THEN
710
711 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
712 --dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
713 for k in 1 .. x_msg_count loop
714 x_msg_data := oe_msg_pub.get( p_msg_index => k,
715 p_encoded => 'F'
716 );
717 --Get message count and data
718 --dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
719 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;
720 end loop;
721
722 END;
723
724
725 END LOOP;
726
727 CLOSE qp_modifier_list_CUR;
728
729 END Import_Modifiers;
730
731 END QP_MODIFIERS_ISETUP_IMP;