eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_INT_LOADER_PUB

Source


1 PACKAGE BODY QP_INT_LOADER_PUB AS
2 /* $Header: QPXILDRB.pls 120.1 2005/06/14 05:36:31 appldev  $ */
3 
4 -- When List_Type_Code is 'PRL' Process_Price_List is invoked
5 -- When List_Type Code is 'PLL' Process_Mofidiers is invoked
6 
7 PROCEDURE Load_Int_List
8 (	p_process_id		IN	NUMBER,
9 	x_status		OUT NOCOPY /* file.sql.39 change */  	VARCHAR2,
10 	x_errors		OUT NOCOPY /* file.sql.39 change */	VARCHAR2
11 )
12 IS
13 
14  l_request_type_code varchar2(3):= NULL;
15  gpr_return_status varchar2(30) := NULL;
16  gpr_msg_count number := 0;
17  gpr_msg_data varchar2(2000);
18  l_operation VARCHAR2(30);
19  l_process_type VARCHAR2(30);
20  i number := 1;
21  length number := 0;
22 
23  l_interface_action_code VARCHAR2(30);
24  l_list_type_code VARCHAR2(30);
25  l_name VARCHAR2(30);
26  l_err_buffer VARCHAR2(240) :=NULL;
27  l_list_header_id NUMBER := 0;
28  l_list_line_id NUMBER := 0;
29  l_count NUMBER :=0;
30 
31 -- for fte_batch_jobs and fte_job_errors tables
32  l_party_id NUMBER:=-1;
33  l_party_name VARCHAR2(30);
34  l_job_start_date DATE := SYSDATE;
35  l_job_completion_date DATE := NULL;
36  -- job status 0 = completed with success, 1 = completed with error, 2 = in process
37  l_job_status VARCHAR2(30) := 2;
38 
39  l_region_id NUMBER :=NULL;
40  l_is_prclst_exists BOOLEAN := NULL;
41 
42  null_interface_action_code EXCEPTION;
43  invalid_interface_action_code EXCEPTION;
44  null_list_type_code EXCEPTION;
45  invalid_list_type_code EXCEPTION;
46  boi_failed_exception EXCEPTION;
47  origin_rid_failed EXCEPTION;
48  destination_rid_failed EXCEPTION;
49  prclst_not_exist EXCEPTION;
50  party_id_failed EXCEPTION;
51  qualifier_prclst_not_exist EXCEPTION;
52 
53 
54  -- PRL, for Process_Price_List
55  gpr_price_list_rec 		QP_PRICE_LIST_PUB.Price_List_Rec_Type;
56  gpr_price_list_val_rec 	QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
57  gpr_price_list_line_tbl 	QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
58  gpr_price_list_line_val_tbl 	QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
59 
60  gpr_qualifiers_tbl 		QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
61  gpr_qualifiers_val_tbl 	QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
62  gpr_pricing_attr_tbl 		QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
63  gpr_pricing_attr_val_tbl 	QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
64 
65  ppr_price_list_rec 		QP_PRICE_LIST_PUB.Price_List_Rec_Type;
66  ppr_price_list_val_rec 	QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
67  ppr_price_list_line_tbl 	QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
68  ppr_price_list_line_val_tbl 	QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
69 
70  ppr_qualifiers_tbl 		QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
71  ppr_qualifiers_val_tbl 	QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
72  ppr_pricing_attr_tbl 		QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
73  ppr_pricing_attr_val_tbl 	QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
74 
75 -- SLT, for Process_Modifiers
76 
77  gpr_modifier_list_rec             QP_Modifiers_PUB.Modifier_List_Rec_Type;
78  gpr_modifier_list_val_rec         QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
79  gpr_modifiers_tbl                 QP_Modifiers_PUB.Modifiers_Tbl_Type;
80  gpr_modifiers_val_tbl             QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
81 
82  --gpr_qualifiers_tbl                QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
83  --gpr_qualifiers_val_tbl            QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
84  gpr_pricing_mod_attr_tbl          QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
85  gpr_pricing_mod_attr_val_tbl      QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
86 
87  ppr_modifier_list_rec             QP_Modifiers_PUB.Modifier_List_Rec_Type;
88  ppr_modifier_list_val_rec         QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
89  ppr_modifiers_tbl                 QP_Modifiers_PUB.Modifiers_Tbl_Type;
90  ppr_modifiers_val_tbl             QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
91 
92 --ppr_qualifiers_tbl                QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
93 --ppr_qualifiers_val_tbl            QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
94  ppr_pricing_mod_attr_tbl           QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
95  ppr_pricing_mod_attr_val_tbl       QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
96 
97 
98 -- Getting the data from the interface tables
99 
100  CURSOR l_qualifiers IS
101  SELECT
102     qual.interface_action_code,
103     qual.excluder_flag,
104     qual.comparison_operator_code,
105     qual.qualifier_context,
106     qual.qualifier_attribute,
107     qual.qualifier_attr_value,
108     qual.qualifier_grouping_no
109  FROM
110     qp_interface_qualifiers qual
111  WHERE
112     qual.process_id = p_process_id;
113 
114  CURSOR l_lines IS
115  SELECT
116     line.interface_action_code,
117     line.list_header_id,
118     line.list_line_id,
119     line.list_line_type_code,
120     line.automatic_flag,
121     line.override_flag,
122     line.modifier_level_code,
123     line.operand,
124     line.arithmetic_operator,
125     line.product_precedence,
126     line.comments,
127     line.price_break_type_code,
128     line.list_line_no,
129     line.price_break_header_index
130  FROM
131     qp_interface_list_lines line
132  WHERE
133     line.process_id = p_process_id
134  ORDER BY
135     line.list_line_no;
136 
137 -- SLT
138  CURSOR l_mod_lines IS
139  SELECT
140     line.interface_action_code,
141     line.list_header_id,
142     line.list_line_id,
143     line.list_line_type_code,
144     line.automatic_flag,
145     line.override_flag,
146     line.modifier_level_code,
147     line.operand,
148     line.arithmetic_operator,
149     line.product_precedence,
150     line.pricing_group_sequence,
151     line.pricing_phase_id,
152     line.comments,
153     line.price_break_type_code,
154     line.list_line_no,
155     line.charge_type_code,
156     line.charge_subtype_code,
157     line.price_break_header_index
158  FROM
159     qp_interface_list_lines line
160  WHERE
161     line.process_id = p_process_id
162  ORDER BY
163     line.list_line_no;
164 
165  -- PRL
166  CURSOR l_pricing_attribs IS
167  SELECT
168     pa.interface_action_code,
169     pa.list_line_id,
170     pa.excluder_flag,
171     pa.product_attribute_context,
172     pa.product_attribute,
173     pa.product_attr_value,
174     pa.product_uom_code,
175     pa.product_attribute_datatype,
176     pa.pricing_attribute_datatype,
177     pa.pricing_attribute_context,
178     pa.pricing_attribute,
179     pa.pricing_attr_value_from,
180     pa.pricing_attr_value_to,
181     pa.attribute_grouping_no,
182     pa.comparison_operator_code,
183 --    pa.price_list_line_index,
184     pa.list_line_no
185  FROM
186     qp_interface_pricing_attribs pa
187  WHERE
188     pa.process_id = p_process_id;
189 
190 --SLT
191  CURSOR l_pricing_mod_attribs IS
192  SELECT
193     pa.interface_action_code,
194     pa.list_line_id,
195     pa.excluder_flag,
196     pa.product_attribute_context,
197     pa.product_attribute,
198     pa.product_attr_value,
199     pa.product_uom_code,
200     pa.product_attribute_datatype,
201     pa.pricing_attribute_datatype,
202     pa.pricing_attribute_context,
203     pa.pricing_attribute,
204     pa.pricing_attr_value_from,
205     pa.pricing_attr_value_to,
206     pa.attribute_grouping_no,
207     pa.comparison_operator_code,
208 --    pa.price_list_line_index,
209     pa.list_line_no
210  FROM
211     qp_interface_pricing_attribs pa
212  WHERE
213     pa.process_id = p_process_id;
214 
215 
216 BEGIN
217 
218 --commit the interface table data
219 commit;
220 
221 -- Transferring data into pl/sql tables used as input to the PriceList BOI
222  x_status := 1;
223 
224  Get_Party_Id (p_process_id, l_party_id, l_err_buffer);
225  	IF (l_party_id = -1) THEN
226  		raise party_id_failed;
227  	END IF;
228 
229  -- obtain values for validations
230  SELECT process_type, list_type_code, interface_action_code, name, source_system_code
231  INTO   l_process_type, l_list_type_code, l_interface_action_code, l_name, l_request_type_code
232  FROM   qp_interface_list_headers
233  WHERE  process_id = p_process_id;
234 
235  QP_INT_LOADER_PUB.G_PROCESS_LST_REQ_TYPE := nvl(l_request_type_code, 'FTE'); --shulin, used in BOI
236 
237  IF (l_interface_action_code IS NULL) THEN
238 	RAISE null_interface_action_code;
239 
240  ELSIF (l_interface_action_code <> 'C') AND (l_interface_action_code <> 'D') THEN
241  	RAISE invalid_interface_action_code;
242 
243  ELSIF (l_list_type_code IS NULL) THEN
244  	RAISE null_list_type_code;
245 
246  ELSIF (l_list_type_code <> 'PRL') AND (l_list_type_code <> 'SLT') THEN
247  	RAISE invalid_list_type_code;
248 
249  END IF;
250 
251  IF (l_interface_action_code = 'D') THEN
252 
253  	-- see if data exists
254  	SELECT count(1) INTO  l_count FROM qp_list_headers_tl qp_lhdr_tl where qp_lhdr_tl.name = l_name;
255  	IF l_count > 0 THEN
256 
257  		-- obtain list_header_id
258  		SELECT qp_lhdr_tl.list_header_id INTO l_list_header_id FROM qp_list_headers_tl qp_lhdr_tl WHERE qp_lhdr_tl.name = l_name AND qp_lhdr_tl.language='US' ;
259 
260  		delete from qp_pricing_attributes qp_prc_att where qp_prc_att.list_header_id  = l_list_header_id;
261  		delete from qp_list_lines qp_ll where qp_ll.list_header_id  = l_list_header_id;
262  		delete from qp_qualifiers qp_qual where qp_qual.list_header_id  = l_list_header_id;
263  		delete from qp_list_headers_b qp_lhdr_b where qp_lhdr_b.list_header_id = l_list_header_id;
264  		delete from qp_list_headers_tl qp_lhdr_tl where qp_lhdr_tl.list_header_id  = l_list_header_id;
265 
266  		/*
267  		update fte_lanes set fte_lanes.pricelist_view_flag = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;
268  		update fte_lanes set fte_lanes.pricelist_name = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;
269  		update fte_lanes set fte_lanes.pricelist_id = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;
270  		*/
271 		commit;
272 
273 		--extra for modifier
274 		IF (l_list_type_code = 'SLT') THEN
275 			select qp_ll.list_line_id INTO l_list_line_id from qp_list_lines qp_ll where qp_ll.list_header_id = l_list_header_id;
276 			delete from qp_rltd_modifiers where qp_rltd_modifiers.from_rltd_modifier_id = l_list_line_id;
277 			commit;
278 		END IF;
279 
280 		/*
281  		Insert_Job_Status  (p_process_id, -1, -1, -1, 1, -1, x_status, l_process_type, l_name, SYSDATE, SYSDATE);
282  		*/
283  	ELSE
284  		raise prclst_not_exist;
285  	END IF;
286  END IF;
287 
288  IF (l_interface_action_code = 'C') THEN
289 
290  IF (l_process_type = 'XML') AND (l_list_type_code = 'PRL') THEN
291 
292   	SELECT
293     		lhdr.list_header_id,
294     		lhdr.name,
295     		lhdr.description,
296     		lhdr.interface_action_code,
297     		lhdr.list_type_code,
298     		lhdr.currency_code,
299     		TO_DATE(lhdr.start_date_active,'YYYYMMDD HH24MISS'),
300     		TO_DATE(lhdr.end_date_active,'YYYYMMDD HH24MISS'),
301     		lhdr.automatic_flag
302  	INTO
303     		gpr_price_list_rec.list_header_id,
304     		gpr_price_list_rec.name,
305     		gpr_price_list_rec.description,
306     		l_operation,
307     		gpr_price_list_rec.list_type_code,
308     		gpr_price_list_rec.currency_code,
309     		gpr_price_list_rec.start_date_active,
310     		gpr_price_list_rec.end_date_active,
311     		gpr_price_list_rec.automatic_flag
312  	FROM
313     		qp_interface_list_headers lhdr
314  	WHERE
315     		lhdr.process_id = p_process_id
316  	AND
317     		rownum < 2;
318 
319 
320  ELSIF (l_process_type = 'XML') AND (l_list_type_code = 'SLT') THEN
321 
322  	SELECT
323     		lhdr.list_header_id,
324     		lhdr.name,
325     		lhdr.description,
326     		lhdr.interface_action_code,
327     		lhdr.list_type_code,
328     		lhdr.currency_code,
329     		TO_DATE(lhdr.start_date_active,'YYYYMMDD HH24MISS'),
330     		TO_DATE(lhdr.end_date_active,'YYYYMMDD HH24MISS'),
331     		lhdr.automatic_flag
332  	INTO
333     		gpr_modifier_list_rec.list_header_id,
334     		gpr_modifier_list_rec.name,
335     		gpr_modifier_list_rec.description,
336     		l_operation,
337     		gpr_modifier_list_rec.list_type_code,
338     		gpr_modifier_list_rec.currency_code,
339     		gpr_modifier_list_rec.start_date_active,
340     		gpr_modifier_list_rec.end_date_active,
341     		gpr_modifier_list_rec.automatic_flag
342  	FROM
343     		qp_interface_list_headers lhdr
344  	WHERE
345     		lhdr.process_id = p_process_id
346  	AND
347     		rownum < 2;
348 
349 ELSIF (l_process_type = 'SSH') AND (l_list_type_code = 'PRL') THEN
350 
351  	SELECT
352     		lhdr.list_header_id,
353     		lhdr.name,
354     		lhdr.description,
355     		lhdr.interface_action_code,
356     		lhdr.list_type_code,
357     		lhdr.currency_code,
358     		lhdr.start_date_active,
359     		lhdr.end_date_active,
360     		lhdr.automatic_flag
361  	INTO
362     		gpr_price_list_rec.list_header_id,
363     		gpr_price_list_rec.name,
364     		gpr_price_list_rec.description,
365     		l_operation,
366     		gpr_price_list_rec.list_type_code,
367     		gpr_price_list_rec.currency_code,
368     		gpr_price_list_rec.start_date_active,
369     		gpr_price_list_rec.end_date_active,
370     		gpr_price_list_rec.automatic_flag
371  	FROM
372     		qp_interface_list_headers lhdr
373  	WHERE
374     		lhdr.process_id = p_process_id
375  	AND
376     		rownum < 2;
377 
378 
379  ELSIF (l_process_type = 'SSH') AND (l_list_type_code = 'SLT') THEN
380 
381 	SELECT
382     		lhdr.list_header_id,
383     		lhdr.name,
384     		lhdr.description,
385     		lhdr.interface_action_code,
386     		lhdr.list_type_code,
387     		lhdr.currency_code,
388     		lhdr.start_date_active,
389     		lhdr.end_date_active,
390    		lhdr.automatic_flag
391 
392  	INTO
393     		gpr_modifier_list_rec.list_header_id,
394     		gpr_modifier_list_rec.name,
395     		gpr_modifier_list_rec.description,
396     		l_operation,
397     		gpr_modifier_list_rec.list_type_code,
398     		gpr_modifier_list_rec.currency_code,
399     		gpr_modifier_list_rec.start_date_active,
400     		gpr_modifier_list_rec.end_date_active,
401     		gpr_modifier_list_rec.automatic_flag
402  	FROM
403     		qp_interface_list_headers lhdr
404  	WHERE
405     		lhdr.process_id = p_process_id
406  	AND
407     		rownum < 2;
408 
409 -- ELSE
410 -- 	dbms_output.put_line('Wrong process_type and list_type_code');
411 
412  END IF;
413 
414  IF (l_operation = 'C') AND (l_list_type_code = 'PRL') THEN
415  	gpr_price_list_rec.list_header_id := FND_API.G_MISS_NUM;
416 	gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_CREATE;
417 
418 	IF (gpr_price_list_rec.automatic_flag IS NULL) THEN
419 		gpr_price_list_rec.automatic_flag:= 'Y';
420 	END IF;
421 
422  ELSIF (l_operation = 'C') AND (l_list_type_code = 'SLT') THEN
423 	gpr_modifier_list_rec.list_header_id := FND_API.G_MISS_NUM;
424 	gpr_modifier_list_rec.operation := QP_GLOBALS.G_OPR_CREATE;
425 
426 	IF (gpr_modifier_list_rec.automatic_flag IS NULL) THEN
427 		gpr_modifier_list_rec.automatic_flag:= 'Y';
428 	END IF;
429 
430 -- dbms_output.put_line('Header_id: ' || gpr_modifier_list_rec.list_header_id);
431 -- dbms_output.put_line('Name: ' || gpr_modifier_list_rec.name);
432 -- dbms_output.put_line('Description: ' || gpr_modifier_list_rec.description);
433 -- dbms_output.put_line('Operation: ' || gpr_modifier_list_rec.operation);
434 -- dbms_output.put_line('List_Type_Code: ' || gpr_modifier_list_rec.list_type_code);
435 -- dbms_output.put_line('Currency_Code: ' || gpr_modifier_list_rec.currency_code);
436 -- dbms_output.put_line('Start_Date_Active: ' || gpr_modifier_list_rec.start_date_active);
437 -- dbms_output.put_line('End_Date_Active: ' || gpr_modifier_list_rec.end_date_active);
438 -- dbms_output.put_line('Automatic_Flag: ' || gpr_modifier_list_rec.automatic_flag);
439 
440  END IF;
441 
442 
443  i := 1;
444  OPEN l_qualifiers;
445  LOOP
446 	FETCH l_qualifiers INTO
447         l_operation,
448     	gpr_qualifiers_tbl(i).excluder_flag,
449     	gpr_qualifiers_tbl(i).comparison_operator_code,
450     	gpr_qualifiers_tbl(i).qualifier_context,
451     	gpr_qualifiers_val_tbl(i).qualifier_attribute_desc,
452     	gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc,
453     	gpr_qualifiers_tbl(i).qualifier_grouping_no;
454 	EXIT WHEN l_qualifiers%NOTFOUND;
455 
456 	gpr_qualifiers_tbl(i).qualifier_attribute := FND_API.G_MISS_CHAR;
457 	gpr_qualifiers_tbl(i).qualifier_attr_value := FND_API.G_MISS_CHAR;
458 
459 	IF (l_operation = 'C') THEN
460 
461 		gpr_qualifiers_tbl(i).list_header_id := FND_API.G_MISS_NUM;
462 		gpr_qualifiers_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
463 
464 		IF (gpr_qualifiers_tbl(i).excluder_flag IS NULL) THEN
465 			IF (l_list_type_code = 'PRL') THEN
466 				gpr_qualifiers_tbl(i).excluder_flag:= FND_API.G_MISS_CHAR;
467 			ELSIF (l_list_type_code = 'SLT') THEN
468 				gpr_qualifiers_tbl(i).excluder_flag:= 'N';
469 			END IF;
470 		END IF;
471 
472 		IF (gpr_qualifiers_tbl(i).qualifier_grouping_no IS NULL) THEN
473 			gpr_qualifiers_tbl(i).qualifier_grouping_no:= FND_API.G_MISS_NUM;
474 		END IF;
475 
476 		/*
477 		-- region to id conversion
478 		IF (UPPER (gpr_qualifiers_val_tbl(i).qualifier_attribute_desc) = 'ORIGIN') THEN
479 			GetRegionId (gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc, l_region_id, l_err_buffer);
480 			IF (l_region_id = -1) THEN
481 				l_err_buffer := 'Origin region id conversion failed. ' || l_err_buffer;
482 				raise origin_rid_failed;
483 			ELSE
484 				gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc := l_region_id;
485 			END IF;
486 
487 		ELSIF (UPPER (gpr_qualifiers_val_tbl(i).qualifier_attribute_desc) = 'DESTINATION') THEN
488 			GetRegionId (gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc, l_region_id, l_err_buffer);
489 			IF (l_region_id = -1) THEN
490 				l_err_buffer := 'Destination region id conversion failed. ' || l_err_buffer;
491 				raise destination_rid_failed;
492 			ELSE
493 				gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc := l_region_id;
494 			END IF;
495 		END IF;
496 		*/
497 
498 		--check if specified price list to be a qualifier exists
499 		IF (UPPER (gpr_qualifiers_tbl(i).qualifier_context) = 'MODLIST' AND UPPER (gpr_qualifiers_val_tbl(i).qualifier_attribute_desc) = 'PRICE_LIST' ) THEN
500 			Is_Qualifier_Prclst_Exist (gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc, l_is_prclst_exists, l_err_buffer);
501 			IF l_is_prclst_exists = FALSE THEN
502 				raise qualifier_prclst_not_exist;
503 			END IF;
504 		END IF;
505  	END IF;
506 
507 
508 --	dbms_output.put_line('*** Qualifier ***');
509 --	dbms_output.put_line('excluder_flag: ' || gpr_qualifiers_tbl(i).excluder_flag);
510 --	dbms_output.put_line('comparison_operator_code: ' || gpr_qualifiers_tbl(i).comparison_operator_code);
511 --	dbms_output.put_line('qualifier_context: ' || gpr_qualifiers_tbl(i).qualifier_context);
512 --	dbms_output.put_line('qualifier_attribute_desc: ' || gpr_qualifiers_val_tbl(i).qualifier_attribute_desc);
513 --	dbms_output.put_line('qualifier_attr_value_desc: ' || gpr_qualifiers_val_tbl(i).qualifier_attr_value_desc);
514 --	dbms_output.put_line('qualifier_grouping_no: ' || gpr_qualifiers_tbl(i).qualifier_grouping_no);
515 
516 	i := i + 1;
517 
518  END LOOP;
519  CLOSE l_qualifiers;
520 
521 IF (l_list_type_code = 'PRL') THEN
522 
523 	i := 1;
524  	OPEN l_lines;
525  	LOOP
526 		FETCH l_lines INTO
527     		l_operation,
528     		gpr_price_list_line_tbl(i).list_header_id,
529     		gpr_price_list_line_tbl(i).list_line_id,
530     		gpr_price_list_line_tbl(i).list_line_type_code,
531     		gpr_price_list_line_tbl(i).automatic_flag,
532     		gpr_price_list_line_tbl(i).override_flag,
533     		gpr_price_list_line_tbl(i).modifier_level_code,
534     		gpr_price_list_line_tbl(i).operand,
535      		gpr_price_list_line_tbl(i).arithmetic_operator,
536     		gpr_price_list_line_tbl(i).product_precedence,
537     		gpr_price_list_line_tbl(i).comments,
538     		gpr_price_list_line_tbl(i).price_break_type_code,
539     		gpr_price_list_line_tbl(i).list_line_no,
540     		gpr_price_list_line_tbl(i).price_break_header_index;
541 		EXIT WHEN l_lines%NOTFOUND;
542 
543 		IF (l_operation = 'C') THEN
544 			gpr_price_list_line_tbl(i).list_header_id := FND_API.G_MISS_NUM;
545 			gpr_price_list_line_tbl(i).list_line_id := FND_API.G_MISS_NUM;
546 			gpr_price_list_line_tbl(i).rltd_modifier_id := FND_API.G_MISS_NUM;
547 			gpr_price_list_line_tbl(i).from_rltd_modifier_id := FND_API.G_MISS_NUM;
548 			gpr_price_list_line_tbl(i).to_rltd_modifier_id := FND_API.G_MISS_NUM;
549 			gpr_price_list_line_tbl(i).rltd_modifier_group_no := FND_API.G_MISS_NUM;
550 			gpr_price_list_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
551 
552 			IF (gpr_price_list_line_tbl(i).automatic_flag IS NULL) THEN
553 				gpr_price_list_line_tbl(i).automatic_flag := FND_API.G_MISS_CHAR;
554 			END IF;
555 
556 			IF (gpr_price_list_line_tbl(i).modifier_level_code IS NULL) THEN
557 				gpr_price_list_line_tbl(i).modifier_level_code := FND_API.G_MISS_CHAR;
558 			END IF;
559  		END IF;
560 
561 --	dbms_output.put_line('*** List Line ***');
562 --	dbms_output.put_line('operation: ' || gpr_price_list_line_tbl(i).operation);
563 --	dbms_output.put_line('Header_id: ' || gpr_price_list_line_tbl(i).list_header_id);
564 --	dbms_output.put_line('List_line_id: ' || gpr_price_list_line_tbl(i).list_line_id);
565 --	dbms_output.put_line('list_line_type_code: ' || gpr_price_list_line_tbl(i).list_line_type_code);
566 --	dbms_output.put_line('automatic_flag: ' || gpr_price_list_line_tbl(i).automatic_flag);
567 --	dbms_output.put_line('override_flag: ' || gpr_price_list_line_tbl(i).override_flag);
568 --	dbms_output.put_line('modifier_level_code: ' || gpr_price_list_line_tbl(i).modifier_level_code);
569 --	dbms_output.put_line('operand: ' || gpr_price_list_line_tbl(i).operand);
570 --	dbms_output.put_line('arithmetic_operator: ' || gpr_price_list_line_tbl(i).arithmetic_operator);
571 --	dbms_output.put_line('product_precedence: ' || gpr_price_list_line_tbl(i).product_precedence);
572 --	dbms_output.put_line('comments: ' || gpr_price_list_line_tbl(i).comments);
573 --	dbms_output.put_line('price_break_type_code: ' || gpr_price_list_line_tbl(i).price_break_type_code);
574 --	dbms_output.put_line('list_line_no: ' || gpr_price_list_line_tbl(i).list_line_no);
575 --	dbms_output.put_line('price_break_header_index: ' || gpr_price_list_line_tbl(i).price_break_header_index);
576 
577 	i := i + 1;
578 	END LOOP;
579  	CLOSE l_lines;
580 
581 ELSIF (l_list_type_code = 'SLT') THEN
582 
583  	i := 1;
584  	OPEN l_mod_lines;
585  	LOOP
586 		FETCH l_mod_lines INTO
587     		l_operation,
588     		gpr_modifiers_tbl(i).list_header_id,
589     		gpr_modifiers_tbl(i).list_line_id,
590     		gpr_modifiers_tbl(i).list_line_type_code,
591     		gpr_modifiers_tbl(i).automatic_flag,
592     		gpr_modifiers_tbl(i).override_flag,
593     		gpr_modifiers_tbl(i).modifier_level_code,
594     		gpr_modifiers_tbl(i).operand,
595         	gpr_modifiers_tbl(i).arithmetic_operator,
596     		gpr_modifiers_tbl(i).product_precedence,
597     		gpr_modifiers_tbl(i).pricing_group_sequence,
598     		gpr_modifiers_tbl(i).pricing_phase_id,
599     		gpr_modifiers_tbl(i).comments,
600     		gpr_modifiers_tbl(i).price_break_type_code,
601     		gpr_modifiers_tbl(i).list_line_no,
602     		gpr_modifiers_tbl(i).charge_type_code,
603     		gpr_modifiers_tbl(i).charge_subtype_code,
604     		gpr_modifiers_tbl(i).modifier_parent_index;
605 		EXIT WHEN l_mod_lines%NOTFOUND;
606 
607 		IF (l_operation = 'C') THEN
608 			gpr_modifiers_tbl(i).list_header_id := FND_API.G_MISS_NUM;
609 			gpr_modifiers_tbl(i).list_line_id := FND_API.G_MISS_NUM;
610 			gpr_modifiers_tbl(i).rltd_modifier_id := FND_API.G_MISS_NUM;
611 			gpr_modifiers_tbl(i).from_rltd_modifier_id := FND_API.G_MISS_NUM;
612 			gpr_modifiers_tbl(i).to_rltd_modifier_id := FND_API.G_MISS_NUM;
613 			gpr_modifiers_tbl(i).rltd_modifier_grp_no := FND_API.G_MISS_NUM;
614 			gpr_modifiers_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
615 
616 			IF (gpr_modifiers_tbl(i).automatic_flag IS NULL) THEN
617 				gpr_modifiers_tbl(i).automatic_flag := 'Y';
618 			END IF;
619 
620 			IF (gpr_modifiers_tbl(i).override_flag IS NULL) THEN
621 				gpr_modifiers_tbl(i).override_flag := 'N';
622 			END IF;
623 
624 			-- BUG FIX, phase_id should be 2 (line level adjustment)
625 			-- other values are in qp_pricing_phases table
626 			IF (gpr_modifiers_tbl(i).pricing_phase_id IS NULL) THEN
627 				gpr_modifiers_tbl(i).pricing_phase_id := 2;
628 			END IF;
629 
630 			IF (gpr_modifiers_tbl(i).modifier_level_code IS NULL) THEN
631 				gpr_modifiers_tbl(i).modifier_level_code := FND_API.G_MISS_CHAR;
632 			END IF;
633 
634 			IF (gpr_modifiers_tbl(i).pricing_group_sequence IS NULL) THEN
635 				gpr_modifiers_tbl(i).pricing_group_sequence := FND_API.G_MISS_NUM;
636 			END IF;
637 
638 			-- add this 4/20/2001
639 			IF (gpr_modifiers_tbl(i).product_precedence IS NULL) THEN
640 				gpr_modifiers_tbl(i).product_precedence := 220;
641 			END IF;
642  		END IF;
643 
644 
645 --	dbms_output.put_line('*** List Line ***');
646 --	dbms_output.put_line('operation: ' || gpr_modifiers_tbl(i).operation);
647 --	dbms_output.put_line('Header_id: ' || gpr_modifiers_tbl(i).list_header_id);
648 --	dbms_output.put_line('List_line_id: ' || gpr_modifiers_tbl(i).list_line_id);
649 --	dbms_output.put_line('list_line_type_code: ' || gpr_modifiers_tbl(i).list_line_type_code);
650 --	dbms_output.put_line('automatic_flag: ' || gpr_modifiers_tbl(i).automatic_flag);
651 --	dbms_output.put_line('override_flag: ' || gpr_modifiers_tbl(i).override_flag);
652 --	dbms_output.put_line('modifier_level_code: ' || gpr_modifiers_tbl(i).modifier_level_code);
653 --	dbms_output.put_line('operand: ' || gpr_modifiers_tbl(i).operand);
654 --	dbms_output.put_line('arithmetic_operator: ' || gpr_modifiers_tbl(i).arithmetic_operator);
655 --	dbms_output.put_line('product_precedence: ' || gpr_modifiers_tbl(i).product_precedence);
656 --	dbms_output.put_line('pricing_group_sequence: ' || gpr_modifiers_tbl(i).pricing_group_sequence);
657 --	dbms_output.put_line('pricing_phase_id: ' || gpr_modifiers_tbl(i).pricing_phase_id);
658 --	dbms_output.put_line('comments: ' || gpr_modifiers_tbl(i).comments);
659 --	dbms_output.put_line('price_break_type_code: ' || gpr_modifiers_tbl(i).price_break_type_code);
660 --	dbms_output.put_line('list_line_no: ' || gpr_modifiers_tbl(i).list_line_no);
661 --	dbms_output.put_line('modifier_parent_index: ' || gpr_modifiers_tbl(i).modifier_parent_index);
662 --      dbms_output.put_line('charge_type_code: ' || gpr_modifiers_tbl(i).charge_type_code);
663 --    	dbms_output.put_line('charge_subtype_code: ' ||	gpr_modifiers_tbl(i).charge_subtype_code);
664 	i := i + 1;
665 
666  	END LOOP;
667  	CLOSE l_mod_lines;
668 
669  END IF;
670 
671 
672  IF (l_list_type_code = 'PRL') THEN
673 
674 	 i := 1;
675  	OPEN l_pricing_attribs;
676 	LOOP
677 		FETCH l_pricing_attribs INTO
678     		l_operation,
679     		gpr_pricing_attr_tbl(i).list_line_id,
680     		gpr_pricing_attr_tbl(i).excluder_flag,
681     		gpr_pricing_attr_tbl(i).product_attribute_context,
682     		gpr_pricing_attr_tbl(i).product_attribute,
683     		gpr_pricing_attr_tbl(i).product_attr_value,
684     		gpr_pricing_attr_tbl(i).product_uom_code,
685     		gpr_pricing_attr_tbl(i).product_attribute_datatype,
686     		gpr_pricing_attr_tbl(i).pricing_attribute_datatype,
687     		gpr_pricing_attr_tbl(i).pricing_attribute_context,
688     		gpr_pricing_attr_val_tbl(i).pricing_attribute_desc,
689    		gpr_pricing_attr_val_tbl(i).pricing_attr_value_from_desc,
690     		gpr_pricing_attr_val_tbl(i).pricing_attr_value_to_desc,
691     		gpr_pricing_attr_tbl(i).attribute_grouping_no,
692     		gpr_pricing_attr_tbl(i).comparison_operator_code,
693     		gpr_pricing_attr_tbl(i).price_list_line_index;
694 		EXIT WHEN l_pricing_attribs%NOTFOUND;
695 
696 		gpr_pricing_attr_tbl(i).pricing_attribute := FND_API.G_MISS_CHAR;
697    		gpr_pricing_attr_tbl(i).pricing_attr_value_from := FND_API.G_MISS_CHAR;
698     		gpr_pricing_attr_tbl(i).pricing_attr_value_to := FND_API.G_MISS_CHAR;
699 
700 		IF (l_operation = 'C') THEN
701 			gpr_pricing_attr_tbl(i).list_line_id := FND_API.G_MISS_NUM;
702 			gpr_pricing_attr_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
703 
704 			IF (gpr_pricing_attr_tbl(i).excluder_flag IS NULL) THEN
705 				gpr_pricing_attr_tbl(i).excluder_flag := FND_API.G_MISS_CHAR;
706 			END IF;
707 
708 			IF (gpr_pricing_attr_tbl(i).attribute_grouping_no IS NULL) THEN
709 				gpr_pricing_attr_tbl(i).attribute_grouping_no := FND_API.G_MISS_NUM;
710 			END IF;
711 
712 
713 			IF (gpr_pricing_attr_tbl(i).product_attribute_datatype IS NULL) THEN
714 				--gpr_pricing_attr_tbl(i).product_attribute_datatype := FND_API.G_MISS_CHAR;
715 				gpr_pricing_attr_tbl(i).product_attribute_datatype := 'C';
716 			END IF;
717 
718 			IF (gpr_pricing_attr_tbl(i).pricing_attribute_datatype IS NULL) THEN
719 				gpr_pricing_attr_tbl(i).pricing_attribute_datatype := FND_API.G_MISS_CHAR;
720 				--gpr_pricing_attr_tbl(i).pricing_attribute_datatype := 'C';
721 			END IF;
722 
723 			-- the first pricing attribute (i.e. ALL, Pricingattribute3), comparison_operator_code should be '='
724 			-- since BOI default it to 'BETWEEN' when null, it causes problem.
725 			-- gpr_pricing_attr_tbl(1).comparison_operator_code := '=';
726 			IF (gpr_pricing_attr_val_tbl(i).pricing_attribute_desc IS NULL AND
727 			gpr_pricing_attr_val_tbl(i).pricing_attr_value_from_desc IS NULL) THEN
728 					gpr_pricing_attr_tbl(i).comparison_operator_code := '=';
729 			END IF;
730  		END IF;
731 
732 --	dbms_output.put_line('***Pricing Attribute***');
733 -- 	dbms_output.put_line('operation: ' || gpr_pricing_attr_tbl(i).operation);
734 -- 	dbms_output.put_line('excluder_flag: ' || gpr_pricing_attr_tbl(i).excluder_flag);
735 -- 	dbms_output.put_line('product_attribute_context: ' || gpr_pricing_attr_tbl(i).product_attribute_context);
736 -- 	dbms_output.put_line('product_attribute: ' || gpr_pricing_attr_tbl(i).product_attribute);
737 -- 	dbms_output.put_line('product_attr_value: ' || gpr_pricing_attr_tbl(i).product_attr_value);
738 --	dbms_output.put_line('product_uom_code: ' || gpr_pricing_attr_tbl(i).product_uom_code);
739 -- 	dbms_output.put_line('product_attribute_datatype: ' || gpr_pricing_attr_tbl(i).product_attribute_datatype);
740 -- 	dbms_output.put_line('pricing_attribute_datatype: ' || gpr_pricing_attr_tbl(i).pricing_attribute_datatype);
741 -- 	dbms_output.put_line('pricing_attribute_context: ' || gpr_pricing_attr_tbl(i).pricing_attribute_context);
742 -- 	dbms_output.put_line('pricing_attribute: ' || gpr_pricing_attr_val_tbl(i).pricing_attribute_desc);
743 -- 	dbms_output.put_line('pricing_attr_value_from: ' || gpr_pricing_attr_val_tbl(i).pricing_attr_value_from_desc);
744 -- 	dbms_output.put_line('pricing_attr_value_to: ' || gpr_pricing_attr_val_tbl(i).pricing_attr_value_to_desc);
745 -- 	dbms_output.put_line('attribute_grouping_no: ' || gpr_pricing_attr_tbl(i).attribute_grouping_no);
746 -- 	dbms_output.put_line('comparison_operator_code: ' || gpr_pricing_attr_tbl(i).comparison_operator_code);
747 -- 	dbms_output.put_line('price_list_line_index: ' || gpr_pricing_attr_tbl(i).price_list_line_index);
748 
749 	i := i + 1;
750  	END LOOP;
751 
752  	-- since only pricing_attr_value_from was inserted at the xml gateway level for price breaks,
753  	-- therefore we need to calculate and insert the pricing_attr_value_to here when context is VOLUME
754  	length := i-1;
755 -- 	dbms_output.put_line ('length: ' || length);
756 
757  	-- no need to calculate the last one since it does not have the next record to calculate
758  	FOR i IN 1 .. length-1 LOOP
759 -- 		dbms_output.put_line('pricing_attr_value_from: ' || gpr_pricing_attr_val_tbl(i).pricing_attr_value_from_desc);
760  		IF (gpr_pricing_attr_tbl(i).comparison_operator_code = 'BETWEEN' AND gpr_pricing_attr_tbl(i).pricing_attribute_context = 'VOLUME') THEN
761  			-- take next record's from value minus one to be current record's to value
762  		gpr_pricing_attr_val_tbl(i).pricing_attr_value_to_desc := TO_CHAR(TO_NUMBER(gpr_pricing_attr_val_tbl(i+1).pricing_attr_value_from_desc) - 1);
763  		END IF;
764 -- 		dbms_output.put_line('pricing_attr_value_to: ' || gpr_pricing_attr_val_tbl(i).pricing_attr_value_to_desc);
765  	END LOOP;
766  	--default the last pricing_attr_value_to_desc to 999,999
767  	gpr_pricing_attr_val_tbl(length).pricing_attr_value_to_desc := '999999';
768 
769  	CLOSE l_pricing_attribs;
770 
771  ELSIF (l_list_type_code = 'SLT') THEN
772 
773  	i := 1;
774  	OPEN l_pricing_mod_attribs;
775  	LOOP
776 		FETCH l_pricing_mod_attribs INTO
777     		l_operation,
778     		gpr_pricing_mod_attr_tbl(i).list_line_id,
779     		gpr_pricing_mod_attr_tbl(i).excluder_flag,
780     		gpr_pricing_mod_attr_tbl(i).product_attribute_context,
781     		gpr_pricing_mod_attr_tbl(i).product_attribute,
782     		gpr_pricing_mod_attr_tbl(i).product_attr_value,
783     		gpr_pricing_mod_attr_tbl(i).product_uom_code,
784     		gpr_pricing_mod_attr_tbl(i).product_attribute_datatype,
785     		gpr_pricing_mod_attr_tbl(i).pricing_attribute_datatype,
786     		gpr_pricing_mod_attr_tbl(i).pricing_attribute_context,
787     		gpr_pricing_mod_attr_val_tbl(i).pricing_attribute_desc,
788    		gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_from_desc,
789     		gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_to_desc,
790     		gpr_pricing_mod_attr_tbl(i).attribute_grouping_no,
791     		gpr_pricing_mod_attr_tbl(i).comparison_operator_code,
792     		gpr_pricing_mod_attr_tbl(i).modifiers_index;
793 		EXIT WHEN l_pricing_mod_attribs%NOTFOUND;
794 
795 		gpr_pricing_mod_attr_tbl(i).pricing_attribute := FND_API.G_MISS_CHAR;
796    		gpr_pricing_mod_attr_tbl(i).pricing_attr_value_from := FND_API.G_MISS_CHAR;
797     		gpr_pricing_mod_attr_tbl(i).pricing_attr_value_to := FND_API.G_MISS_CHAR;
798 
799 		IF (l_operation = 'C') THEN
800 
801 			gpr_pricing_mod_attr_tbl(i).list_line_id := FND_API.G_MISS_NUM;
802 			gpr_pricing_mod_attr_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
803 
804  			IF (gpr_pricing_mod_attr_tbl(i).excluder_flag IS NULL) THEN
805 				--gpr_pricing_mod_attr_tbl(i).excluder_flag := FND_API.G_MISS_CHAR;
806 				gpr_pricing_mod_attr_tbl(i).excluder_flag := 'N';
807 			END IF;
808 
809 			IF (gpr_pricing_mod_attr_tbl(i).attribute_grouping_no IS NULL) THEN
810 				gpr_pricing_mod_attr_tbl(i).attribute_grouping_no := FND_API.G_MISS_NUM;
811 			END IF;
812 
813 
814 			IF (gpr_pricing_mod_attr_tbl(i).product_attribute_datatype IS NULL) THEN
815 				--gpr_pricing_mod_attr_tbl(i).product_attribute_datatype := FND_API.G_MISS_CHAR;
816 				gpr_pricing_mod_attr_tbl(i).product_attribute_datatype := 'C';
817 			END IF;
818 
819 			IF (gpr_pricing_mod_attr_tbl(i).pricing_attribute_datatype IS NULL) THEN
820 				--gpr_pricing_mod_attr_tbl(i).pricing_attribute_datatype := FND_API.G_MISS_CHAR;
821 				gpr_pricing_mod_attr_tbl(i).pricing_attribute_datatype := 'C';
822 			END IF;
823 
824 			-- the first pricing attribute for each modifier line, comparison_operator_code should be null
825 			IF (gpr_pricing_mod_attr_val_tbl(i).pricing_attribute_desc IS NULL AND
826 				gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_from_desc IS NULL) THEN
827 					gpr_pricing_mod_attr_tbl(i).comparison_operator_code := NULL;
828 			END IF;
829 
830  		END IF;
831 
832 --	dbms_output.put_line('***Pricing Mod Attribute***');
833 -- 	dbms_output.put_line('operation: ' || gpr_pricing_mod_attr_tbl(i).operation);
834 -- 	dbms_output.put_line('excluder_flag: ' || gpr_pricing_mod_attr_tbl(i).excluder_flag);
835 -- 	dbms_output.put_line('product_attribute_context: ' || gpr_pricing_mod_attr_tbl(i).product_attribute_context);
836 -- 	dbms_output.put_line('product_attribute: ' || gpr_pricing_mod_attr_tbl(i).product_attribute);
837 -- 	dbms_output.put_line('product_attr_value: ' || gpr_pricing_mod_attr_tbl(i).product_attr_value);
838 -- 	dbms_output.put_line('product_uom_code: ' || gpr_pricing_mod_attr_tbl(i).product_uom_code);
839 --	dbms_output.put_line('product_attribute_datatype: ' || gpr_pricing_mod_attr_tbl(i).product_attribute_datatype);
840 -- 	dbms_output.put_line('pricing_attribute_datatype: ' || gpr_pricing_mod_attr_tbl(i).pricing_attribute_datatype);
841 -- 	dbms_output.put_line('pricing_attribute_context: ' || gpr_pricing_mod_attr_tbl(i).pricing_attribute_context);
842 -- 	dbms_output.put_line('pricing_attribute: ' || gpr_pricing_mod_attr_val_tbl(i).pricing_attribute_desc);
843 -- 	dbms_output.put_line('pricing_attr_value_from: ' || gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_from_desc);
844 -- 	dbms_output.put_line('pricing_attr_value_to: ' || gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_to_desc);
845 -- 	dbms_output.put_line('attribute_grouping_no: ' || gpr_pricing_mod_attr_tbl(i).attribute_grouping_no);
846 -- 	dbms_output.put_line('comparison_operator_code: ' || gpr_pricing_mod_attr_tbl(i).comparison_operator_code);
847 -- 	dbms_output.put_line('modifiers_index: ' || gpr_pricing_mod_attr_tbl(i).modifiers_index);
848 
849 	i := i + 1;
850  	END LOOP;
851 
852  	-- since only pricing_attr_value_from was inserted at the xml gateway level,
853  	-- therefore we need to calculate and insert the pricing_attr_value_to here
854  	length := i-1;
855 -- 	dbms_output.put_line ('length: ' || length);
856 
857  	-- no need to calculate the last one since it does not have the next record to calculate
858  	FOR i IN 1 .. length-1 LOOP
859 -- 		dbms_output.put_line('pricing_attr_value_from: ' || gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_from_desc);
860  		IF (gpr_pricing_mod_attr_tbl(i).comparison_operator_code = 'BETWEEN') THEN
861  			-- take next record's from value minus one to be current record's to value
862  		gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_to_desc := TO_CHAR(TO_NUMBER(gpr_pricing_mod_attr_val_tbl(i+1).pricing_attr_value_from_desc) - 1);
863  		END IF;
864 -- 		dbms_output.put_line('pricing_attr_value_to: ' || gpr_pricing_mod_attr_val_tbl(i).pricing_attr_value_to_desc);
865  	END LOOP;
866 
867  	-- default the last pricing_attr_value_to to a large number
868  	gpr_pricing_mod_attr_val_tbl(length).pricing_attr_value_to_desc := '999999';
869 
870  	CLOSE l_pricing_mod_attribs;
871 
872 END IF;
873 
874 --commit data to interface table
875 commit;
876 
877 IF (l_list_type_code = 'PRL') THEN
878 
879 -- process price list
880 
881 QP_PRICE_LIST_PUB.Process_Price_List
882  (   p_api_version_number            => 1
883  ,   p_init_msg_list                 => FND_API.G_FALSE
884  ,   p_return_values                 => FND_API.G_FALSE
885  ,   p_commit                        => FND_API.G_FALSE
886  ,   x_return_status                 => gpr_return_status
887  ,   x_msg_count                     => gpr_msg_count
888  ,   x_msg_data                      => gpr_msg_data
889  ,   p_PRICE_LIST_rec                => gpr_price_list_rec
890  ,   p_PRICE_LIST_LINE_tbl           => gpr_price_list_line_tbl
891  ,   p_QUALIFIERS_tbl                => gpr_qualifiers_tbl
892  ,   p_QUALIFIERS_val_tbl            => gpr_qualifiers_val_tbl
893  ,   p_PRICING_ATTR_tbl              => gpr_pricing_attr_tbl
894  ,   p_PRICING_ATTR_val_tbl          => gpr_pricing_attr_val_tbl
895  ,   x_PRICE_LIST_rec                => ppr_price_list_rec
896  ,   x_PRICE_LIST_val_rec            => ppr_price_list_val_rec
897  ,   x_PRICE_LIST_LINE_tbl           => ppr_price_list_line_tbl
898  ,   x_PRICE_LIST_LINE_val_tbl       => ppr_price_list_line_val_tbl
899  ,   x_QUALIFIERS_tbl                => ppr_qualifiers_tbl
900  ,   x_QUALIFIERS_val_tbl            => ppr_qualifiers_val_tbl
901  ,   x_PRICING_ATTR_tbl              => ppr_pricing_attr_tbl
902  ,   x_PRICING_ATTR_val_tbl          => ppr_pricing_attr_val_tbl
903  );
904 
905 ELSIF (l_list_type_code = 'SLT') THEN
906 
907 -- process modifier list
908 
909  QP_MODIFIERS_PUB.Process_Modifiers
910  (   p_api_version_number            => 1
911  ,   p_init_msg_list                 => FND_API.G_FALSE
912  ,   p_return_values                 => FND_API.G_FALSE
913  ,   p_commit                        => FND_API.G_FALSE
914  ,   x_return_status                 => gpr_return_status
915  ,   x_msg_count                     => gpr_msg_count
916  ,   x_msg_data                      => gpr_msg_data
917  ,   p_MODIFIER_LIST_rec             => gpr_modifier_list_rec
918  ,   p_MODIFIER_LIST_val_rec         => gpr_modifier_list_val_rec
919  ,   p_MODIFIERS_tbl           	     => gpr_modifiers_tbl
920  ,   p_MODIFIERS_val_tbl             => gpr_modifiers_val_tbl
921  ,   p_QUALIFIERS_tbl                => gpr_qualifiers_tbl
922  ,   p_QUALIFIERS_val_tbl            => gpr_qualifiers_val_tbl
923  ,   p_PRICING_ATTR_tbl              => gpr_pricing_mod_attr_tbl
924  ,   p_PRICING_ATTR_val_tbl          => gpr_pricing_mod_attr_val_tbl
925  ,   x_MODIFIER_LIST_rec             => ppr_modifier_list_rec
926  ,   x_MODIFIER_LIST_val_rec         => ppr_modifier_list_val_rec
927  ,   x_MODIFIERS_tbl                 => ppr_modifiers_tbl
928  ,   x_MODIFIERS_val_tbl             => ppr_modifiers_val_tbl
929  ,   x_QUALIFIERS_tbl                => ppr_qualifiers_tbl
930  ,   x_QUALIFIERS_val_tbl            => ppr_qualifiers_val_tbl
931  ,   x_PRICING_ATTR_tbl              => ppr_pricing_mod_attr_tbl
932  ,   x_PRICING_ATTR_val_tbl          => ppr_pricing_mod_attr_val_tbl
933  );
934 
935 END IF;
936 
937 -- get err msg from BOI
938 for k in 1 .. gpr_msg_count loop
939         gpr_msg_data := oe_msg_pub.get( p_msg_index => k,
940                         p_encoded => 'F'
941                         );
942 
943            -- max length for err_msg is 240 char in fte_job_errors table
944 	   x_errors := x_errors || substr(gpr_msg_data,1,239) || ' , ';
945 --         dbms_output.put_line('Error msg: '|| x_errors);
946 end loop;
947 
948 IF gpr_return_status = FND_API.G_RET_STS_SUCCESS THEN
949 	 x_status := 'COMPLETED';
950 	 l_job_status := 0;
951 	 l_job_completion_date := SYSDATE;
952 
953 	 /*
954 	 -- job status 0 is success
955 	 --(JOB_ID, LINES_PROCESSED, LINES_FAILED, LINES_SUBMITTED, TOTAL_ERROR_NUMBER, SUPPLIER_ID, JOB_STATUS, JOB_TYPE, FILENAME, START_DATE, COMPLETION_DATE)
956 	 Insert_Job_Status  (p_process_id, 1, 0, 1, 0, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
957 	 */
958 --	 dbms_output.put_line('Successfully completed Pricelist BOI');
959 END IF;
960 
961 IF gpr_return_status <> FND_API.G_RET_STS_SUCCESS THEN
962 
963 	 x_status := 'FAILED';
964 --	 dbms_output.put_line('Error in BOI');
965 --	 dbms_output.put_line('X_STATUS : ' ||  x_status);
966 --	 dbms_output.put_line('X_ERRORS : ' ||  x_errors);
967 
968 	 -- name must be unique for price list
969 	 IF ( substr(x_errors,1,22) = 'SO_NT_NOTE_NAME_IN_USE') THEN
970 		l_err_buffer := 'Duplicate Price List.';
971 
972 	 -- combination of (name, version no, language) must be unique for modifier list
973 	 -- 4/25/2001, match BOI msg change
974 	 ELSIF ( substr(x_errors,1,51) = 'The Modifier Number that you entered already exists') THEN
975 	 	l_err_buffer := 'Duplicate Modifier List.';
976 
977 	 ELSE
978 	 	-- 4/25/2001, give all BOI msg instead of 1st msg only
979 	 	l_err_buffer := SUBSTR(x_errors, 1,239);
980 --	 	dbms_output.put_line('ERRORS INSERT: ' ||  l_err_buffer);
981 
982 	 END IF;
983 	 rollback;	-- do not insert to qp tables
984 
985 	 l_job_status := 1;
986 	 l_job_completion_date := SYSDATE;
987 
988 	 /*
989 	 Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
990 	 -- job status 1 is completed with error
991 	 --(JOB_ID, LINES_PROCESSED, LINES_FAILED, LINES_SUBMITTED, TOTAL_ERROR_NUMBER, SUPPLIER_ID, JOB_STATUS, JOB_TYPE, FILENAME, START_DATE, COMPLETION_DATE)
992 	 Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY:'||l_name, l_job_start_date, l_job_completion_date);
993 	 */
994 	 RAISE boi_failed_exception;
995 
996 
997 END IF;
998 
999 END IF;  -- END IF (l_interface_action_code = 'C')
1000 
1001 EXCEPTION
1002 
1003 	WHEN party_id_failed THEN
1004 		x_status := 'FAILED';
1005 		l_job_status := 1;
1006 	 	l_job_completion_date := SYSDATE;
1007 	 	/*
1008 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1009 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1010 		*/
1011 	WHEN null_list_type_code THEN
1012 		--rollback;
1013 		x_status := 'FAILED';
1014 		l_err_buffer := 'PRICELSTTYPE cannot be NULL. Please specify PRICELSTTYPE data as PRL for pricelist or SLT for modifier. ';
1015 		l_job_status := 1;
1016 	 	l_job_completion_date := SYSDATE;
1017 
1018 	 	/*
1019 	 	-- (JOB_ID, LINE_NUMBER, FIELD_NAME, CREATION_DATE, ERROR_MESSAGE, LAST_UPDATE_DATE)
1020 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1021 
1022 	 	-- (JOB_ID, LINES_PROCESSED, LINES_FAILED, LINES_SUBMITTED, TOTAL_ERROR_NUMBER, SUPPLIER_ID, JOB_STATUS, JOB_TYPE, FILENAME, START_DATE, COMPLETION_DATE)
1023 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1024 	 	*/
1025 
1026 	WHEN invalid_list_type_code THEN
1027 		--rollback;
1028 		x_status := 'FAILED';
1029 		l_err_buffer := 'PRICELSTTYPE must be PRL or SLT. Please specify PRICELSTTYPE data as PRL for pricelist or SLT for modifier. ';
1030 		l_job_status := 1;
1031 	 	l_job_completion_date := SYSDATE;
1032 
1033 	 	/*
1034 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1035 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1036 	 	*/
1037 	WHEN null_interface_action_code THEN
1038 		--rollback;
1039 		x_status := 'FAILED';
1040 		l_err_buffer := 'SYNCIND cannot be NULL. Please specify SYNCIND data as C for create or D for delete. ';
1041 		l_job_status := 1;
1042 	 	l_job_completion_date := SYSDATE;
1043 
1044 	 	/*
1045 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1046 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1047 	 	*/
1048 	WHEN invalid_interface_action_code THEN
1049 		--rollback;
1050 		x_status := 'FAILED';
1051 		l_err_buffer := 'SYNCIND must be C or D. Please specify SYNCIND data as C for create or D for delete. ';
1052 		l_job_status := 1;
1053 	 	l_job_completion_date := SYSDATE;
1054 
1055 	 	/*
1056 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1057 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1058 	 	*/
1059 	WHEN origin_rid_failed THEN
1060 		--rollback;
1061 		x_status := 'FAILED';
1062 		l_job_status := 1;
1063 	 	l_job_completion_date := SYSDATE;
1064 	 	/*
1065 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1066 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1067 	 	*/
1068 
1069 	WHEN destination_rid_failed THEN
1070 		--rollback;
1071 		x_status := 'FAILED';
1072 		l_job_status := 1;
1073 	 	l_job_completion_date := SYSDATE;
1074 	 	/*
1075 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1076 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1077 		*/
1078 	WHEN prclst_not_exist THEN
1079 		--rollback;
1080 		x_status := 'FAILED';
1081 		l_err_buffer := 'Price list to be deleted does not exist. Please correct data for PRICELSTID. ';
1082 		l_job_status := 1;
1083 	 	l_job_completion_date := SYSDATE;
1084 	 	/*
1085 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1086 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1087 	 	*/
1088 	WHEN qualifier_prclst_not_exist THEN
1089 		x_status := 'FAILED';
1090 		l_job_status := 1;
1091 	 	l_job_completion_date := SYSDATE;
1092 	 	/*
1093 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1094 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1095 	 	*/
1096 	WHEN  boi_failed_exception THEN
1097 		x_status := 'FAILED';
1098 
1099 	WHEN others THEN
1100 		rollback;
1101 		x_status := 'FAILED';
1102 		-- error_message column in fte_job_errors only has width 240
1103 		l_err_buffer := SUBSTR('Unexpected error occurred. '||SQLERRM , 1, 239);
1104 		l_job_status := 1;
1105 	 	l_job_completion_date := SYSDATE;
1106 	 	/*
1107 	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);
1108 	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);
1109 		*/
1110 END Load_Int_List;
1111 
1112 --overload procedure
1113 PROCEDURE LOAD_INT_LIST
1114 (	p_process_id	IN	NUMBER,
1115 	p_action_code	IN	VARCHAR2
1116 )
1117 IS
1118 
1119 BEGIN
1120 
1121 	Load_INT_List(p_process_id, G_temp_status, G_temp_errors);
1122 
1123 END LOAD_INT_LIST;
1124 
1125 /*
1126 -- procedure to insert err message to FTE_JOB_ERRORS
1127 	PROCEDURE Insert_Err_Msg
1128 	(
1129 		p_job_id		IN	NUMBER,
1130 		p_line_num		IN	NUMBER,
1131 		p_field_name		IN 	VARCHAR2,
1132 		p_creation_date		IN 	DATE,
1133 		p_err_msg		IN 	VARCHAR2,
1134 		p_last_update_date	IN 	DATE
1135 	)
1136 	IS
1137 	BEGIN
1138 		INSERT INTO FTE_JOB_ERRORS (JOB_ID, LINE_NUMBER, FIELD_NAME, CREATION_DATE, ERROR_MESSAGE, LAST_UPDATE_DATE)
1139 	 	VALUES (p_job_id, p_line_num, p_field_name, p_creation_date, p_err_msg, p_last_update_date);
1140 	 	commit;
1141 
1142 	END Insert_Err_Msg;
1143 
1144 	PROCEDURE Insert_Job_Status
1145 	(
1146 		p_job_id		IN	NUMBER,
1147 		p_lines_processed	IN	NUMBER,
1148 		p_lines_failed		IN 	NUMBER,
1149 		p_lines_submitted	IN 	NUMBER,
1150 		p_total_error_number	IN 	NUMBER,
1151 		p_supplier_id		IN 	NUMBER,
1152 		p_job_status		IN	VARCHAR2,
1153 		p_job_type		IN	VARCHAR2,
1154 		p_file_name		IN	VARCHAR2,
1155 		p_start_date		IN 	DATE,
1156 		p_completion_date	IN	DATE
1157 	)
1158 	IS
1159 	BEGIN
1160 		-- job status 0 = completed with success, 1 = completed with error
1161 		INSERT INTO FTE_BATCH_JOBS (JOB_ID, LINES_PROCESSED, LINES_FAILED, LINES_SUBMITTED, TOTAL_ERROR_NUMBER, SUPPLIER_ID, JOB_STATUS, JOB_TYPE, FILENAME, START_DATE, COMPLETION_DATE)
1162 	 	VALUES (p_job_id, p_lines_processed, p_lines_failed, p_lines_submitted, p_total_error_number, p_supplier_id, p_job_status, p_job_type, p_file_name, p_start_date, p_completion_date);
1163 	 	commit;
1164 	END Insert_Job_Status;
1165 
1166 
1167 	PROCEDURE GetRegionId (
1168 		p_region_str IN VARCHAR2,
1169 		x_region_id OUT NOCOPY NUMBER,
1170 		x_rid_err_msg OUT NOCOPY VARCHAR2
1171 	)
1172 	IS
1173 
1174  	l_occurance NUMBER := 1;
1175  	l_width NUMBER:= 1;
1176  	l_token VARCHAR2(100) := NULL;
1177  	l_prev_width NUMBER:= 0;
1178  	l_tk_prev_width NUMBER := 0;
1179  	l_parse_str VARCHAR2(100) := NULL;
1180  	l_tk_str VARCHAR2(100) := NULL;
1181  	l_tk_attr VARCHAR2(50) := NULL;
1182  	l_tk_attr_val VARCHAR2(50) := NULL;
1183  	l_city VARCHAR2(100) := NULL;
1184  	l_state VARCHAR2(100) := NULL;
1185  	l_country VARCHAR2(100) := NULL;
1186  	l_zip VARCHAR2(100) := NULL;
1187  	l_zone VARCHAR2(100) := NULL;
1188  	invalid_key EXCEPTION;
1189  	no_regionid_found EXCEPTION;
1190 
1191 	BEGIN
1192 
1193 	-- append ',' to end city_state_country_str for tokenization reason
1194 	l_parse_str := p_region_str || SUBSTR (',' , 1, 1);
1195 
1196 	LOOP
1197 		l_width := INSTR (l_parse_str, ',', 1,l_occurance);
1198 
1199 		-- when no more tokens
1200 		EXIT WHEN l_width = 0;
1201 
1202 		-- trim off the white space at left, ',' at right
1203 		l_token := LTRIM (SUBSTR (l_parse_str, l_prev_width + 1, l_width - l_prev_width -1));
1204 
1205 		-- append '-' to end string for tokenization reason
1206 		l_tk_str := l_token || SUBSTR ('-' , 1, 1);
1207 		l_tk_prev_width := INSTR (l_tk_str, '-', 1, 1);		-- up to first '-'
1208 
1209 		-- get key-value pair
1210 		l_tk_attr := LTRIM (SUBSTR (l_tk_str, 1, l_tk_prev_width -1));		-- key
1211 		l_tk_attr_val :=  LTRIM (SUBSTR (l_tk_str, l_tk_prev_width + 1, INSTR (l_tk_str, '-', 1, 2) - l_tk_prev_width-1)); --value
1212 		-- dbms_output.put_line ('tk_attr ' || l_occurance || ': ' || l_tk_attr);
1213 		-- dbms_output.put_line ('tk_attr_val ' || l_occurance || ': ' || l_tk_attr_val);
1214 
1215 		IF UPPER(l_tk_attr) = 'CITY' THEN
1216 			l_city := l_tk_attr_val;
1217 		ELSIF UPPER(l_tk_attr) = 'STATE' THEN
1218 			l_state := l_tk_attr_val;
1219 		ELSIF UPPER(l_tk_attr) = 'COUNTRY' THEN
1220 			l_country := l_tk_attr_val;
1221 		ELSIF UPPER(l_tk_attr) = 'ZONE' THEN
1222 			l_zone := l_tk_attr_val;
1223 		ELSIF UPPER(l_tk_attr) = 'POSTAL CODE' THEN
1224 			l_zip := l_tk_attr_val;
1225 		ELSE
1226 			raise invalid_key;
1227 		END IF;
1228 
1229 		l_prev_width := l_width;
1230 		l_occurance := l_occurance + 1;
1231 
1232 	END LOOP;
1233 
1234 
1235 	-- use FTE_REGIONS_PKG.Get_Region_Id_TL (parent_region_type, country, country_region, state, city, postal_code, zone, lang_code, interface_flag, x_region_id);
1236 	FTE_REGIONS_PKG.Get_Region_Id_TL(NULL, l_country, NULL, l_state, l_city, l_zip, l_zone, NULL, 'N', x_region_id);
1237 
1238 	-- dbms_output.put_line('region id: ' || x_region_id);
1239 	IF x_region_id = -1 THEN
1240 		raise no_regionid_found;
1241 	END IF;
1242 
1243 	EXCEPTION
1244 		WHEN invalid_key THEN
1245 			x_region_id := -1;
1246 			x_rid_err_msg := 'Please use City, State, Country, Zone, Postal Code to define the region. Please correct data for ATTRIBUTE_VALUE. ';
1247 		WHEN no_regionid_found THEN
1248 			x_region_id := -1;
1249 			x_rid_err_msg := 'No region id found. Please correct data for ATTRIBUTE_VALUE. ';
1250 		WHEN others THEN
1251 			x_region_id := -1;
1252 			x_rid_err_msg := SUBSTR('Upnexpected error occurred while obtaining region id. Please correct data for ATTRIBUTE_VALUE. '||SQLERRM , 1, 239);
1253 
1254 	END GetRegionId;
1255 */
1256 
1257 	PROCEDURE Get_Party_Id
1258 	(
1259 		p_process_id IN NUMBER,
1260 		x_party_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1261 		x_pid_err_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1262 	)
1263 	IS
1264 		l_party_name VARCHAR2(360):= NULL;
1265 	BEGIN
1266 
1267  		-- obtain party_name
1268  		SELECT q.qualifier_attr_value
1269  		INTO l_party_name
1270  		FROM qp_interface_qualifiers q
1271  		WHERE q.process_id = p_process_id AND q.qualifier_context = 'PARTY';
1272 
1273  		--obtain the party_id
1274  		SELECT hz_parties.party_id
1275  		INTO x_party_id
1276  		FROM hz_parties
1277  		WHERE hz_parties.party_name = l_party_name;
1278 
1279  		Exception
1280 
1281  		WHEN NO_DATA_FOUND THEN
1282  			x_party_id := -1;
1283  			x_pid_err_msg := 'No party id found for the specified party name. Please correct data for SUPPLIERID. ';
1284 		WHEN others THEN
1285 			x_party_id := -1;
1286 			x_pid_err_msg := SUBSTR('Upnexpected error occurred while obtaining party id. Please correct data for SUPPLIERID. '||SQLERRM , 1, 239);
1287 
1288 
1289 	END Get_Party_Id;
1290 
1291 	PROCEDURE Is_Qualifier_Prclst_Exist
1292 	(
1293 		p_prclst_name IN VARCHAR2,
1294 		x_prclst_exists OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
1295 		x_prclst_exists_err_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1296 	)
1297 	IS
1298 		l_count NUMBER := NULL;
1299 	BEGIN
1300 		SELECT count(1) into l_count from qp_list_headers_tl where name = p_prclst_name;
1301 		IF l_count = 0 THEN
1302 			x_prclst_exists := FALSE;
1303 			x_prclst_exists_err_msg := SUBSTR('Prclst specified as a qualifier does not exists. Please correct data for ATTRIBUTE_VALUE. ', 1,239);
1304 		ELSE
1305 			x_prclst_exists := TRUE;
1306 		END IF;
1307 
1308 	EXCEPTION
1309 
1310 	WHEN others THEN
1311 		x_prclst_exists := FALSE;
1312 		x_prclst_exists_err_msg := SUBSTR('Upnexpected error occurred while obtaining the prclst specified as a qualifier. '||SQLERRM , 1, 239);
1313 	END Is_Qualifier_Prclst_Exist;
1314 
1315 
1316 END QP_INT_LOADER_PUB;