[Home] [Help]
PACKAGE BODY: APPS.QP_DATA_COMPARE_PVT
Source
1 PACKAGE BODY QP_Data_Compare_PVT AS
2 /* $Header: QPXVDATB.pls 120.0 2005/06/02 00:04:14 appldev noship $ */
3
4 PROCEDURE List_Header_Data(p_html_list_line_id NUMBER,
5 p_forms_list_line_id NUMBER,
6 p_file_dir VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2) IS
8
9 l_filename VARCHAR2(60);
10 l_file UTL_FILE.file_type;
11 l_html_list_header_id NUMBER;
12 l_forms_list_header_id NUMBER;
13 l_header_string VARCHAR2(2000);
14 l_header_string1 VARCHAR2(255);
15 l_header_string2 VARCHAR2(255);
16 l_header_string3 VARCHAR2(255);
17 l_header_string4 VARCHAR2(255);
18 l_data_rec VARCHAR2(700);
19 l_header_rec LIST_HEADER_REC_TYPE;
20 l_reset_header_rec LIST_HEADER_REC_TYPE;
21
22 CURSOR csr_get_list_headers(p_list_header_id NUMBER) IS
23 SELECT a.list_header_id,
24 a.request_id,
25 a.list_type_code,
26 a.start_date_active,
27 a.end_date_active,
28 a.automatic_flag,
29 a.currency_code,
30 a.rounding_factor,
31 a.ship_method_code,
32 a.freight_terms_code,
33 a.terms_id,
34 a.comments,
35 a.discount_lines_flag,
36 a.gsa_indicator,
37 a.prorate_flag,
38 a.source_system_code,
39 a.ask_for_flag,
40 a.active_flag,
41 a.parent_list_header_id,
42 a.start_date_active_first,
43 a.end_date_active_first,
44 a.active_date_first_type,
45 a.start_date_active_second,
46 a.end_date_active_second,
47 a.active_date_second_type,
48 a.limit_exists_flag,
49 a.mobile_download,
50 a.currency_header_id,
51 a.pte_code,
52 a.list_source_code,
53 a.orig_system_header_ref,
54 a.orig_org_id,
55 a.global_flag,
56 a.sold_to_org_id,
57 a.shareable_flag,
58 a.locked_from_list_header_id,
59 b.language,
60 b.source_lang,
61 b.name,
62 b.description,
63 b.version_no
64 FROM qp_list_headers_b a ,
65 qp_list_headers_tl b
66 WHERE a.list_header_id = b.list_header_id
67 AND a.list_header_id = p_list_header_id;
68
69 CURSOR csr_get_list_header_id(p_list_line_id NUMBER) IS
70 SELECT list_header_id
71 FROM qp_list_lines
72 WHERE list_line_id = p_list_line_id;
73
74 BEGIN
75
76 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
77
78 BEGIN
79 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'w');
80 EXCEPTION
81 WHEN UTL_FILE.INVALID_PATH THEN
82 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
83 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
84 RETURN;
85 END;
86
87 utl_file.put(l_file,'LIST_HEADERS');
88 utl_file.new_line(l_file);
89
90 OPEN csr_get_list_header_id(p_html_list_line_id);
91 FETCH csr_get_list_header_id INTO l_html_list_header_id;
92 CLOSE csr_get_list_header_id;
93
94 OPEN csr_get_list_header_id(p_forms_list_line_id);
95 FETCH csr_get_list_header_id INTO l_forms_list_header_id;
96 CLOSE csr_get_list_header_id;
97
98 OPEN csr_get_list_headers(l_forms_list_header_id);
99 FETCH csr_get_list_headers INTO l_header_rec;
100 CLOSE csr_get_list_headers;
101
102 l_header_string1 := 'DataSource,List_Header_Id,Request_Id,List_Yype_Code,Start_Date_Active,End_Date_Active,Automatic_Flag, Currency_Code,Rounding_Factor,Ship_Method_Code,Freight_Terms_Code,Terms_Id,Comments,Discount_Lines_Flag, Gsa_Indicator,';
103 l_header_string2 := 'Prorate_Flag,Source_System_Code,Ask_For_Flag,Active_Flag,Parent_List_Header_Id,Start_Date_Active_First, End_Date_Active_First,Active_Date_First_Type,Start_Date_Active_Second,End_Date_Active_Second,';
104 l_header_string3 := 'Active_Date_Second_Type, Limit_Exists_Flag,Mobile_Download,Currency_Header_Id,Pte_Code,List_Source_Code,Orig_System_Header_Ref,Orig_Org_Id, Global_Flag,Sold_To_Org_Id,Shareable_Flag,';
105 l_header_string4 := 'Locked_From_List_Header_Id,Language,Source_Lang,Name,Description,Version_No';
106 l_header_string := l_header_string1 || l_header_string2 || l_header_string3 || l_header_string4;
107
108
109 utl_file.put(l_file,l_header_string);
110 utl_file.new_line(l_file);
111
112 l_data_rec := 'FORMS' || ',';
113 l_data_rec := l_data_rec || l_header_rec.list_header_id || ',';
114 l_data_rec := l_data_rec || l_header_rec.request_id || ',';
115 l_data_rec := l_data_rec || l_header_rec.list_type_code || ',';
116 l_data_rec := l_data_rec || l_header_rec.start_date_active || ',';
117 l_data_rec := l_data_rec || l_header_rec.end_date_active || ',';
118 l_data_rec := l_data_rec || l_header_rec.automatic_flag || ',';
119 l_data_rec := l_data_rec || l_header_rec.currency_code || ',';
120 l_data_rec := l_data_rec || l_header_rec.rounding_factor || ',';
121 l_data_rec := l_data_rec || l_header_rec.ship_method_code || ',';
122 l_data_rec := l_data_rec || l_header_rec.freight_terms_code || ',';
123 l_data_rec := l_data_rec || l_header_rec.terms_id || ',';
124 l_data_rec := l_data_rec || l_header_rec.comments || ',';
125 l_data_rec := l_data_rec || l_header_rec.discount_lines_flag || ',';
126 l_data_rec := l_data_rec || l_header_rec.gsa_indicator || ',';
127 l_data_rec := l_data_rec || l_header_rec.prorate_flag || ',';
128 l_data_rec := l_data_rec || l_header_rec.source_system_code || ',';
129 l_data_rec := l_data_rec || l_header_rec.ask_for_flag || ',';
130 l_data_rec := l_data_rec || l_header_rec.active_flag || ',';
131 l_data_rec := l_data_rec || l_header_rec.parent_list_header_id || ',';
132 l_data_rec := l_data_rec || l_header_rec.start_date_active_first || ',';
133 l_data_rec := l_data_rec || l_header_rec.end_date_active_first || ',';
134 l_data_rec := l_data_rec || l_header_rec.active_date_first_type || ',';
135 l_data_rec := l_data_rec || l_header_rec.start_date_active_second || ',';
136 l_data_rec := l_data_rec || l_header_rec.end_date_active_second || ',';
137 l_data_rec := l_data_rec || l_header_rec.active_date_second_type || ',';
138 l_data_rec := l_data_rec || l_header_rec.limit_exists_flag || ',';
139 l_data_rec := l_data_rec || l_header_rec.mobile_download || ',';
140 l_data_rec := l_data_rec || l_header_rec.currency_header_id || ',';
141 l_data_rec := l_data_rec || l_header_rec.pte_code || ',';
142 l_data_rec := l_data_rec || l_header_rec.list_source_code || ',';
143 l_data_rec := l_data_rec || l_header_rec.orig_system_header_ref || ',';
144 l_data_rec := l_data_rec || l_header_rec.orig_org_id || ',';
145 l_data_rec := l_data_rec || l_header_rec.global_flag || ',';
146 l_data_rec := l_data_rec || l_header_rec.sold_to_org_id || ',';
147 l_data_rec := l_data_rec || l_header_rec.shareable_flag || ',';
148 l_data_rec := l_data_rec || l_header_rec.locked_from_list_header_id || ',';
149 l_data_rec := l_data_rec || l_header_rec.language || ',';
150 l_data_rec := l_data_rec || l_header_rec.source_lang || ',';
151 l_data_rec := l_data_rec || l_header_rec.name || ',';
152 l_data_rec := l_data_rec || l_header_rec.description || ',';
153 l_data_rec := l_data_rec || l_header_rec.version_no;
154
155 utl_file.put(l_file,l_data_rec);
156 utl_file.new_line(l_file);
157
158 l_header_rec := l_reset_header_rec;
159
160 OPEN csr_get_list_headers(l_html_list_header_id);
161 FETCH csr_get_list_headers INTO l_header_rec;
162 CLOSE csr_get_list_headers;
163
164 l_data_rec := 'HTML' || ',';
165 l_data_rec := l_data_rec || l_header_rec.list_header_id || ',';
166 l_data_rec := l_data_rec || l_header_rec.request_id || ',';
167 l_data_rec := l_data_rec || l_header_rec.list_type_code || ',';
168 l_data_rec := l_data_rec || l_header_rec.start_date_active || ',';
169 l_data_rec := l_data_rec || l_header_rec.end_date_active || ',';
170 l_data_rec := l_data_rec || l_header_rec.automatic_flag || ',';
171 l_data_rec := l_data_rec || l_header_rec.currency_code || ',';
172 l_data_rec := l_data_rec || l_header_rec.rounding_factor || ',';
173 l_data_rec := l_data_rec || l_header_rec.ship_method_code || ',';
174 l_data_rec := l_data_rec || l_header_rec.freight_terms_code || ',';
175 l_data_rec := l_data_rec || l_header_rec.terms_id || ',';
176 l_data_rec := l_data_rec || l_header_rec.comments || ',';
177 l_data_rec := l_data_rec || l_header_rec.discount_lines_flag || ',';
178 l_data_rec := l_data_rec || l_header_rec.gsa_indicator || ',';
179 l_data_rec := l_data_rec || l_header_rec.prorate_flag || ',';
180 l_data_rec := l_data_rec || l_header_rec.source_system_code || ',';
181 l_data_rec := l_data_rec || l_header_rec.ask_for_flag || ',';
182 l_data_rec := l_data_rec || l_header_rec.active_flag || ',';
183 l_data_rec := l_data_rec || l_header_rec.parent_list_header_id || ',';
184 l_data_rec := l_data_rec || l_header_rec.start_date_active_first || ',';
185 l_data_rec := l_data_rec || l_header_rec.end_date_active_first || ',';
186 l_data_rec := l_data_rec || l_header_rec.active_date_first_type || ',';
187 l_data_rec := l_data_rec || l_header_rec.start_date_active_second || ',';
188 l_data_rec := l_data_rec || l_header_rec.end_date_active_second || ',';
189 l_data_rec := l_data_rec || l_header_rec.active_date_second_type || ',';
190 l_data_rec := l_data_rec || l_header_rec.limit_exists_flag || ',';
191 l_data_rec := l_data_rec || l_header_rec.mobile_download || ',';
192 l_data_rec := l_data_rec || l_header_rec.currency_header_id || ',';
193 l_data_rec := l_data_rec || l_header_rec.pte_code || ',';
194 l_data_rec := l_data_rec || l_header_rec.list_source_code || ',';
195 l_data_rec := l_data_rec || l_header_rec.orig_system_header_ref || ',';
196 l_data_rec := l_data_rec || l_header_rec.orig_org_id || ',';
197 l_data_rec := l_data_rec || l_header_rec.global_flag || ',';
198 l_data_rec := l_data_rec || l_header_rec.sold_to_org_id || ',';
199 l_data_rec := l_data_rec || l_header_rec.shareable_flag || ',';
200 l_data_rec := l_data_rec || l_header_rec.locked_from_list_header_id || ',';
201 l_data_rec := l_data_rec || l_header_rec.language || ',';
202 l_data_rec := l_data_rec || l_header_rec.source_lang || ',';
203 l_data_rec := l_data_rec || l_header_rec.name || ',';
204 l_data_rec := l_data_rec || l_header_rec.description || ',';
205 l_data_rec := l_data_rec || l_header_rec.version_no;
206
207 utl_file.put(l_file,l_data_rec);
208
209 utl_file.fclose(l_file);
210
211 x_return_status := 'S';
212
213 END List_Header_Data;
214
215 PROCEDURE Qualifier_Data(p_html_list_line_id NUMBER,
216 p_forms_list_line_id NUMBER,
217 p_file_dir VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2) IS
219
220 l_filename VARCHAR2(60);
221 l_file UTL_FILE.file_type;
222 l_html_list_header_id NUMBER;
223 l_forms_list_header_id NUMBER;
224 l_qual_string VARCHAR2(2000);
225 l_qual_string1 VARCHAR2(255);
226 l_qual_string2 VARCHAR2(255);
227 l_qual_string3 VARCHAR2(255);
228 l_data_rec VARCHAR2(700);
229 l_qualifier_rec QUALIFIER_REC_TYPE;
230 l_reset_qualifier_rec QUALIFIER_REC_TYPE;
231 l_used_in_setup VARCHAR2(1);
232
233 CURSOR csr_get_list_qualifiers(p_list_header_id NUMBER) IS
234 SELECT qualifier_id,
235 qualifier_grouping_no,
236 qualifier_context,
237 qualifier_attribute,
238 qualifier_attr_value,
239 comparison_operator_code,
240 excluder_flag,
241 qualifier_rule_id,
242 start_date_active,
243 end_date_active,
244 created_from_rule_id,
245 qualifier_precedence,
246 list_header_id,
247 list_line_id,
248 qualifier_datatype,
249 qualifier_attr_value_to,
250 active_flag,
251 list_type_code,
252 qual_attr_value_from_number,
253 qual_attr_value_to_number,
254 search_ind,
255 qualifier_group_cnt,
256 header_quals_exist_flag,
257 distinct_row_count,
258 others_group_cnt,
259 orig_sys_qualifier_ref,
260 orig_sys_header_ref,
261 orig_sys_line_ref,
262 segment_id
263 FROM qp_qualifiers
264 WHERE list_header_id = p_list_header_id
265 ORDER BY qualifier_context,qualifier_attribute;
266
267 CURSOR csr_get_list_header_id(p_list_line_id NUMBER) IS
268 SELECT list_header_id
269 FROM qp_list_lines
270 WHERE list_line_id = p_list_line_id;
271
272
273 CURSOR csr_get_used_in_setup_flag(p_context VARCHAR2, p_attribute VARCHAR2, p_context_type VARCHAR2) IS
274 SELECT used_in_setup
275 FROM qp_pte_segments
276 WHERE segment_id IN
277 (SELECT a.segment_id
278 FROM qp_segments_b a,qp_prc_contexts_b b
279 WHERE a.segment_mapping_column=p_attribute
280 AND a.prc_context_id=b.prc_context_id
281 AND b.prc_context_type=p_context_type
282 AND b.prc_context_code=p_context);
283
284 BEGIN
285
286 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
287
288 BEGIN
289 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
290 EXCEPTION
291 WHEN UTL_FILE.INVALID_PATH THEN
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
294 RETURN;
295 END;
296
297 utl_file.new_line(l_file);
298 utl_file.put(l_file,'QUALIFIERS');
299 utl_file.new_line(l_file);
300
301 OPEN csr_get_list_header_id(p_html_list_line_id);
302 FETCH csr_get_list_header_id INTO l_html_list_header_id;
303 CLOSE csr_get_list_header_id;
304
305 OPEN csr_get_list_header_id(p_forms_list_line_id);
306 FETCH csr_get_list_header_id INTO l_forms_list_header_id;
307 CLOSE csr_get_list_header_id;
308
309 l_qual_string1 := 'DataSource, Qualifier_id, Qualifier_grouping_no, Qualifier_context, Qualifier_attribute, Qualifier_attr_value,Comparison_operator_code, Excluder_flag, Qualifier_rule_id, Start_date_active, End_date_active, Created_from_rule_id,';
310 l_qual_string2 := 'Qualifier_precedence,List_header_id, List_line_id, Qualifier_datatype, Qualifier_attr_value_to, Active_flag, List_type_code, Qual_attr_value_from_number, Qual_attr_value_to_number, Search_ind, Qualifier_group_cnt,';
311 l_qual_string3 := 'Header_quals_exist_flag, Distinct_row_count,Others_group_cnt, Orig_sys_qualifier_ref, Orig_sys_header_ref, Orig_sys_line_ref, Segment_id,Used_In_Setup';
312 l_qual_string := l_qual_string1 || l_qual_string2 || l_qual_string3;
313
314
315 utl_file.put(l_file,l_qual_string);
316
317 OPEN csr_get_list_qualifiers(l_forms_list_header_id);
318 LOOP
319
323 OPEN csr_get_used_in_setup_flag(l_qualifier_rec.qualifier_context , l_qualifier_rec.qualifier_attribute,'QUALIFIER');
320 FETCH csr_get_list_qualifiers INTO l_qualifier_rec;
321 EXIT WHEN csr_get_list_qualifiers%NOTFOUND;
322
324 FETCH csr_get_used_in_setup_flag INTO l_used_in_setup;
325 CLOSE csr_get_used_in_setup_flag;
326
327 utl_file.new_line(l_file);
328 l_data_rec := 'FORMS' || ',';
329
330 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_id||',' ;
331 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_grouping_no||',' ;
332 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_context||',' ;
333 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attribute||',' ;
334 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attr_value||',' ;
335 l_data_rec := l_data_rec || l_qualifier_rec.comparison_operator_code||',' ;
336 l_data_rec := l_data_rec || l_qualifier_rec.excluder_flag||',' ;
337 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_rule_id||',' ;
338 l_data_rec := l_data_rec || l_qualifier_rec.start_date_active||',' ;
339 l_data_rec := l_data_rec || l_qualifier_rec.end_date_active||',' ;
340 l_data_rec := l_data_rec || l_qualifier_rec.created_from_rule_id||',' ;
341 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_precedence||',' ;
342 l_data_rec := l_data_rec || l_qualifier_rec.list_header_id||',' ;
343 l_data_rec := l_data_rec || l_qualifier_rec.list_line_id||',' ;
344 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_datatype||',' ;
345 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attr_value_to||',' ;
346 l_data_rec := l_data_rec || l_qualifier_rec.active_flag||',' ;
347 l_data_rec := l_data_rec || l_qualifier_rec.list_type_code||',' ;
348 l_data_rec := l_data_rec || l_qualifier_rec.qual_attr_value_from_number||',' ;
349 l_data_rec := l_data_rec || l_qualifier_rec.qual_attr_value_to_number||',' ;
350 l_data_rec := l_data_rec || l_qualifier_rec.search_ind||',' ;
351 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_group_cnt||',' ;
352 l_data_rec := l_data_rec || l_qualifier_rec.header_quals_exist_flag||',' ;
353 l_data_rec := l_data_rec || l_qualifier_rec.distinct_row_count||',' ;
354 l_data_rec := l_data_rec || l_qualifier_rec.others_group_cnt||',' ;
355 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_qualifier_ref||',' ;
356 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_header_ref||',' ;
357 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_line_ref||',' ;
358 l_data_rec := l_data_rec || l_qualifier_rec.segment_id || ',';
359 l_data_rec := l_data_rec || l_used_in_setup ;
360
361 utl_file.put(l_file,l_data_rec);
362
363 END LOOP;
364
365 CLOSE csr_get_list_qualifiers;
366
367 l_qualifier_rec := l_reset_qualifier_rec;
368
369 OPEN csr_get_list_qualifiers(l_html_list_header_id);
370 LOOP
371
372 FETCH csr_get_list_qualifiers INTO l_qualifier_rec;
373 EXIT WHEN csr_get_list_qualifiers%NOTFOUND;
374
375 OPEN csr_get_used_in_setup_flag(l_qualifier_rec.qualifier_context , l_qualifier_rec.qualifier_attribute,'QUALIFIER');
376 FETCH csr_get_used_in_setup_flag INTO l_used_in_setup;
377 CLOSE csr_get_used_in_setup_flag;
378
379 utl_file.new_line(l_file);
380 l_data_rec := 'HTML' || ',';
381
382 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_id||',' ;
383 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_grouping_no||',' ;
384 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_context||',' ;
385 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attribute||',' ;
386 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attr_value||',' ;
387 l_data_rec := l_data_rec || l_qualifier_rec.comparison_operator_code||',' ;
388 l_data_rec := l_data_rec || l_qualifier_rec.excluder_flag||',' ;
389 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_rule_id||',' ;
390 l_data_rec := l_data_rec || l_qualifier_rec.start_date_active||',' ;
391 l_data_rec := l_data_rec || l_qualifier_rec.end_date_active||',' ;
392 l_data_rec := l_data_rec || l_qualifier_rec.created_from_rule_id||',' ;
393 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_precedence||',' ;
394 l_data_rec := l_data_rec || l_qualifier_rec.list_header_id||',' ;
395 l_data_rec := l_data_rec || l_qualifier_rec.list_line_id||',' ;
396 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_datatype||',' ;
397 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_attr_value_to||',' ;
398 l_data_rec := l_data_rec || l_qualifier_rec.active_flag||',' ;
399 l_data_rec := l_data_rec || l_qualifier_rec.list_type_code||',' ;
400 l_data_rec := l_data_rec || l_qualifier_rec.qual_attr_value_from_number||',' ;
401 l_data_rec := l_data_rec || l_qualifier_rec.qual_attr_value_to_number||',' ;
402 l_data_rec := l_data_rec || l_qualifier_rec.search_ind||',' ;
403 l_data_rec := l_data_rec || l_qualifier_rec.qualifier_group_cnt||',' ;
404 l_data_rec := l_data_rec || l_qualifier_rec.header_quals_exist_flag||',' ;
405 l_data_rec := l_data_rec || l_qualifier_rec.distinct_row_count||',' ;
406 l_data_rec := l_data_rec || l_qualifier_rec.others_group_cnt||',' ;
407 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_qualifier_ref||',' ;
408 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_header_ref||',' ;
409 l_data_rec := l_data_rec || l_qualifier_rec.orig_sys_line_ref||',' ;
410 l_data_rec := l_data_rec || l_qualifier_rec.segment_id || ',';
411 l_data_rec := l_data_rec || l_used_in_setup ;
412
413 utl_file.put(l_file,l_data_rec);
414
415 END LOOP;
416
417 CLOSE csr_get_list_qualifiers;
418
419 utl_file.fclose(l_file);
420 x_return_status := 'S';
421
422 END Qualifier_Data;
423
424 PROCEDURE List_Line_Data(p_html_list_line_id NUMBER,
425 p_forms_list_line_id NUMBER,
426 p_file_dir VARCHAR2,
427 x_return_status OUT NOCOPY VARCHAR2) IS
428
429 l_filename VARCHAR2(60);
433 l_list_line_string VARCHAR2(2000);
430 l_file UTL_FILE.file_type;
431 l_html_list_header_id NUMBER;
432 l_forms_list_header_id NUMBER;
434 l_list_line_string1 VARCHAR2(255);
435 l_list_line_string2 VARCHAR2(255);
436 l_list_line_string3 VARCHAR2(255);
437 l_list_line_string4 VARCHAR2(255);
438 l_list_line_string5 VARCHAR2(255);
439 l_data_rec VARCHAR2(700);
440 l_list_line_rec LIST_LINE_REC_TYPE;
441 l_reset_list_line_rec LIST_LINE_REC_TYPE;
442
443 CURSOR csr_get_list_lines(p_list_line_id NUMBER) IS
444 SELECT list_line_id,
445 list_header_id,
446 list_line_type_code,
447 start_date_active,
448 end_date_active,
449 automatic_flag,
450 modifier_level_code,
451 price_by_formula_id,
452 primary_uom_flag,
453 price_break_type_code,
454 arithmetic_operator,
455 operand,
456 override_flag,
457 accrual_qty,
458 accrual_uom_code,
459 estim_accrual_rate,
460 generate_using_formula_id,
461 list_line_no,
462 estim_gl_value,
463 benefit_price_list_line_id,
464 expiration_period_start_date,
465 number_expiration_periods,
466 expiration_period_uom,
467 expiration_date,
468 accrual_flag,
469 pricing_phase_id,
470 pricing_group_sequence,
471 incompatibility_grp_code,
472 product_precedence,
473 proration_type_code,
474 accrual_conversion_rate,
475 benefit_qty,
476 benefit_uom_code,
477 qualification_ind,
478 limit_exists_flag,
479 group_count,
480 net_amount_flag,
481 recurring_value,
482 accum_context,
483 accum_attribute,
484 accum_attr_run_src_flag,
485 break_uom_code,
486 break_uom_context,
487 break_uom_attribute,
488 pattern_id,
489 product_uom_code,
490 pricing_attribute_count,
491 hash_key,
492 cache_key
493 FROM qp_list_lines
494 WHERE list_line_id = p_list_line_id
495 UNION
496 SELECT list_line_id,
497 list_header_id,
498 list_line_type_code,
499 start_date_active,
500 end_date_active,
501 automatic_flag,
502 modifier_level_code,
503 price_by_formula_id,
504 primary_uom_flag,
505 price_break_type_code,
506 arithmetic_operator,
507 operand,
508 override_flag,
509 accrual_qty,
510 accrual_uom_code,
511 estim_accrual_rate,
512 generate_using_formula_id,
513 list_line_no,
514 estim_gl_value,
515 benefit_price_list_line_id,
516 expiration_period_start_date,
517 number_expiration_periods,
518 expiration_period_uom,
519 expiration_date,
520 accrual_flag,
521 pricing_phase_id,
522 pricing_group_sequence,
523 incompatibility_grp_code,
524 product_precedence,
525 proration_type_code,
526 accrual_conversion_rate,
527 benefit_qty,
528 benefit_uom_code,
529 qualification_ind,
530 limit_exists_flag,
531 group_count,
532 net_amount_flag,
533 recurring_value,
534 accum_context,
535 accum_attribute,
536 accum_attr_run_src_flag,
537 break_uom_code,
538 break_uom_context,
539 break_uom_attribute,
540 pattern_id,
541 product_uom_code,
542 pricing_attribute_count,
543 hash_key,
544 cache_key
545 FROM qp_list_lines
546 WHERE list_line_id IN (SELECT to_rltd_modifier_id
547 FROM qp_rltd_modifiers
548 WHERE from_rltd_modifier_id = p_list_line_id);
549 BEGIN
550
551 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
552
553 BEGIN
554 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
555 EXCEPTION
556 WHEN UTL_FILE.INVALID_PATH THEN
557 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
558 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
559 RETURN;
560 END;
561
562 utl_file.new_line(l_file);
563 utl_file.put(l_file,'LIST_LINES');
564 utl_file.new_line(l_file);
565
566 l_list_line_string1 := 'DataSource,List_Line_Id, List_Header_Id, List_Line_Type_Code, Start_Date_Active, End_Date_Active, Automatic_Flag, Modifier_Level_Code, Price_By_Formula_Id, Primary_Uom_Flag, Price_Break_Type_Code, Arithmetic_Operator, Operand,';
567 l_list_line_string2 := 'Override_Flag,Accrual_Qty, Accrual_Uom_Code, Estim_Accrual_Rate, Generate_Using_Formula_Id, List_Line_No, Estim_Gl_Value, Benefit_Price_List_Line_Id, Expiration_Period_Start_Date, Number_Expiration_Periods,';
568 l_list_line_string3 := 'Expiration_Period_Uom,Expiration_Date,Accrual_Flag, Pricing_Phase_Id, Pricing_Group_Sequence, Incompatibility_Grp_Code, Product_Precedence, Proration_Type_Code, Accrual_Conversion_Rate, Benefit_Qty, Benefit_Uom_Code,';
569 l_list_line_string4 := 'Qualification_Ind,Limit_Exists_Flag, Group_Count,Net_Amount_Flag, Recurring_Value, Accum_Context, Accum_Attribute, Accum_Attr_Run_Src_Flag, Break_Uom_Code, Break_Uom_Context, Break_Uom_Attribute, Pattern_Id,Product_Uom_Code,';
570 l_list_line_string5 := 'Pricing_Attribute_Count,Hash_Key, Cache_Key';
571 l_list_line_string := l_list_line_string1 || l_list_line_string2 || l_list_line_string3 || l_list_line_string4 || l_list_line_string5 ;
572
573 utl_file.put(l_file,l_list_line_string);
574
578 FETCH csr_get_list_lines INTO l_list_line_rec;
575 OPEN csr_get_list_lines(p_forms_list_line_id);
576 LOOP
577
579 EXIT WHEN csr_get_list_lines%NOTFOUND;
580
581 utl_file.new_line(l_file);
582
583 l_data_rec := 'FORMS' || ',';
584
585 l_data_rec := l_data_rec || l_list_line_rec.list_line_id|| ',';
586 l_data_rec := l_data_rec || l_list_line_rec.list_header_id|| ',';
587 l_data_rec := l_data_rec || l_list_line_rec.list_line_type_code|| ',';
588 l_data_rec := l_data_rec || l_list_line_rec.start_date_active|| ',';
589 l_data_rec := l_data_rec || l_list_line_rec.end_date_active|| ',';
590 l_data_rec := l_data_rec || l_list_line_rec.automatic_flag|| ',';
591 l_data_rec := l_data_rec || l_list_line_rec.modifier_level_code|| ',';
592 l_data_rec := l_data_rec || l_list_line_rec.price_by_formula_id|| ',';
593 l_data_rec := l_data_rec || l_list_line_rec.primary_uom_flag|| ',';
594 l_data_rec := l_data_rec || l_list_line_rec.price_break_type_code|| ',';
595 l_data_rec := l_data_rec || l_list_line_rec.arithmetic_operator|| ',';
596 l_data_rec := l_data_rec || l_list_line_rec.operand|| ',';
597 l_data_rec := l_data_rec || l_list_line_rec.override_flag|| ',';
598 l_data_rec := l_data_rec || l_list_line_rec.accrual_qty|| ',';
599 l_data_rec := l_data_rec || l_list_line_rec.accrual_uom_code|| ',';
600 l_data_rec := l_data_rec || l_list_line_rec.estim_accrual_rate|| ',';
601 l_data_rec := l_data_rec || l_list_line_rec.generate_using_formula_id|| ',';
602 l_data_rec := l_data_rec || l_list_line_rec.list_line_no|| ',';
603 l_data_rec := l_data_rec || l_list_line_rec.estim_gl_value|| ',';
604 l_data_rec := l_data_rec || l_list_line_rec.benefit_price_list_line_id|| ',';
605 l_data_rec := l_data_rec || l_list_line_rec.expiration_period_start_date|| ',';
606 l_data_rec := l_data_rec || l_list_line_rec.number_expiration_periods|| ',';
607 l_data_rec := l_data_rec || l_list_line_rec.expiration_period_uom|| ',';
608 l_data_rec := l_data_rec || l_list_line_rec.expiration_date|| ',';
609 l_data_rec := l_data_rec || l_list_line_rec.accrual_flag|| ',';
610 l_data_rec := l_data_rec || l_list_line_rec.pricing_phase_id|| ',';
611 l_data_rec := l_data_rec || l_list_line_rec.pricing_group_sequence|| ',';
612 l_data_rec := l_data_rec || l_list_line_rec.incompatibility_grp_code|| ',';
613 l_data_rec := l_data_rec || l_list_line_rec.product_precedence|| ',';
614 l_data_rec := l_data_rec || l_list_line_rec.proration_type_code|| ',';
615 l_data_rec := l_data_rec || l_list_line_rec.accrual_conversion_rate|| ',';
616 l_data_rec := l_data_rec || l_list_line_rec.benefit_qty|| ',';
617 l_data_rec := l_data_rec || l_list_line_rec.benefit_uom_code|| ',';
618 l_data_rec := l_data_rec || l_list_line_rec.qualification_ind|| ',';
619 l_data_rec := l_data_rec || l_list_line_rec.limit_exists_flag|| ',';
620 l_data_rec := l_data_rec || l_list_line_rec.group_count|| ',';
621 l_data_rec := l_data_rec || l_list_line_rec.net_amount_flag|| ',';
622 l_data_rec := l_data_rec || l_list_line_rec.recurring_value|| ',';
623 l_data_rec := l_data_rec || l_list_line_rec.accum_context|| ',';
624 l_data_rec := l_data_rec || l_list_line_rec.accum_attribute|| ',';
625 l_data_rec := l_data_rec || l_list_line_rec.accum_attr_run_src_flag|| ',';
626 l_data_rec := l_data_rec || l_list_line_rec.break_uom_code|| ',';
627 l_data_rec := l_data_rec || l_list_line_rec.break_uom_context|| ',';
628 l_data_rec := l_data_rec || l_list_line_rec.break_uom_attribute|| ',';
629 l_data_rec := l_data_rec || l_list_line_rec.pattern_id|| ',';
630 l_data_rec := l_data_rec || l_list_line_rec.product_uom_code|| ',';
631 l_data_rec := l_data_rec || l_list_line_rec.pricing_attribute_count|| ',';
632 l_data_rec := l_data_rec || l_list_line_rec.hash_key|| ',';
633 l_data_rec := l_data_rec || l_list_line_rec.cache_key;
634
635 utl_file.put(l_file,l_data_rec);
636
637 END LOOP;
638
639 CLOSE csr_get_list_lines;
640
641 l_list_line_rec := l_reset_list_line_rec;
642
643 OPEN csr_get_list_lines(p_html_list_line_id);
644 LOOP
645
646 FETCH csr_get_list_lines INTO l_list_line_rec;
647 EXIT WHEN csr_get_list_lines%NOTFOUND;
648
649 utl_file.new_line(l_file);
650 l_data_rec := 'HTML' || ',';
651
652 l_data_rec := l_data_rec || l_list_line_rec.list_line_id|| ',';
653 l_data_rec := l_data_rec || l_list_line_rec.list_header_id|| ',';
654 l_data_rec := l_data_rec || l_list_line_rec.list_line_type_code|| ',';
655 l_data_rec := l_data_rec || l_list_line_rec.start_date_active|| ',';
656 l_data_rec := l_data_rec || l_list_line_rec.end_date_active|| ',';
657 l_data_rec := l_data_rec || l_list_line_rec.automatic_flag|| ',';
658 l_data_rec := l_data_rec || l_list_line_rec.modifier_level_code|| ',';
659 l_data_rec := l_data_rec || l_list_line_rec.price_by_formula_id|| ',';
660 l_data_rec := l_data_rec || l_list_line_rec.primary_uom_flag|| ',';
661 l_data_rec := l_data_rec || l_list_line_rec.price_break_type_code|| ',';
662 l_data_rec := l_data_rec || l_list_line_rec.arithmetic_operator|| ',';
663 l_data_rec := l_data_rec || l_list_line_rec.operand|| ',';
664 l_data_rec := l_data_rec || l_list_line_rec.override_flag|| ',';
665 l_data_rec := l_data_rec || l_list_line_rec.accrual_qty|| ',';
666 l_data_rec := l_data_rec || l_list_line_rec.accrual_uom_code|| ',';
667 l_data_rec := l_data_rec || l_list_line_rec.estim_accrual_rate|| ',';
668 l_data_rec := l_data_rec || l_list_line_rec.generate_using_formula_id|| ',';
669 l_data_rec := l_data_rec || l_list_line_rec.list_line_no|| ',';
670 l_data_rec := l_data_rec || l_list_line_rec.estim_gl_value|| ',';
671 l_data_rec := l_data_rec || l_list_line_rec.benefit_price_list_line_id|| ',';
672 l_data_rec := l_data_rec || l_list_line_rec.expiration_period_start_date|| ',';
673 l_data_rec := l_data_rec || l_list_line_rec.number_expiration_periods|| ',';
674 l_data_rec := l_data_rec || l_list_line_rec.expiration_period_uom|| ',';
675 l_data_rec := l_data_rec || l_list_line_rec.expiration_date|| ',';
679 l_data_rec := l_data_rec || l_list_line_rec.incompatibility_grp_code|| ',';
676 l_data_rec := l_data_rec || l_list_line_rec.accrual_flag|| ',';
677 l_data_rec := l_data_rec || l_list_line_rec.pricing_phase_id|| ',';
678 l_data_rec := l_data_rec || l_list_line_rec.pricing_group_sequence|| ',';
680 l_data_rec := l_data_rec || l_list_line_rec.product_precedence|| ',';
681 l_data_rec := l_data_rec || l_list_line_rec.proration_type_code|| ',';
682 l_data_rec := l_data_rec || l_list_line_rec.accrual_conversion_rate|| ',';
683 l_data_rec := l_data_rec || l_list_line_rec.benefit_qty|| ',';
684 l_data_rec := l_data_rec || l_list_line_rec.benefit_uom_code|| ',';
685 l_data_rec := l_data_rec || l_list_line_rec.qualification_ind|| ',';
686 l_data_rec := l_data_rec || l_list_line_rec.limit_exists_flag|| ',';
687 l_data_rec := l_data_rec || l_list_line_rec.group_count|| ',';
688 l_data_rec := l_data_rec || l_list_line_rec.net_amount_flag|| ',';
689 l_data_rec := l_data_rec || l_list_line_rec.recurring_value|| ',';
690 l_data_rec := l_data_rec || l_list_line_rec.accum_context|| ',';
691 l_data_rec := l_data_rec || l_list_line_rec.accum_attribute|| ',';
692 l_data_rec := l_data_rec || l_list_line_rec.accum_attr_run_src_flag|| ',';
693 l_data_rec := l_data_rec || l_list_line_rec.break_uom_code|| ',';
694 l_data_rec := l_data_rec || l_list_line_rec.break_uom_context|| ',';
695 l_data_rec := l_data_rec || l_list_line_rec.break_uom_attribute|| ',';
696 l_data_rec := l_data_rec || l_list_line_rec.pattern_id|| ',';
697 l_data_rec := l_data_rec || l_list_line_rec.product_uom_code|| ',';
698 l_data_rec := l_data_rec || l_list_line_rec.pricing_attribute_count|| ',';
699 l_data_rec := l_data_rec || l_list_line_rec.hash_key|| ',';
700 l_data_rec := l_data_rec || l_list_line_rec.cache_key;
701
702 utl_file.put(l_file,l_data_rec);
703
704 END LOOP;
705
706 CLOSE csr_get_list_lines;
707
708 utl_file.fclose(l_file);
709 x_return_status := 'S';
710
711 END List_Line_Data;
712
713 PROCEDURE Pricing_Attribute_Data(p_html_list_line_id NUMBER,
714 p_forms_list_line_id NUMBER,
715 p_file_dir VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2) IS
717
718 l_filename VARCHAR2(60);
719 l_file UTL_FILE.file_type;
720 l_pricing_attr_string VARCHAR2(2000);
721 l_pricing_attr_string1 VARCHAR2(255);
722 l_pricing_attr_string2 VARCHAR2(255);
723 l_pricing_attr_string3 VARCHAR2(255);
724 l_pricing_attr_string4 VARCHAR2(255);
725 l_data_rec VARCHAR2(700);
726 l_pricing_attribute_rec PRICING_ATTRIBUTE_REC_TYPE;
727 l_reset_pricing_attr_rec PRICING_ATTRIBUTE_REC_TYPE;
728 l_product_used_in_setup VARCHAR2(1);
729 l_pricing_attr_used_in_setup VARCHAR2(1);
730
731 CURSOR csr_get_pricing_attributes(p_list_line_id NUMBER) IS
732 SELECT pricing_attribute_id,
733 list_line_id,
734 excluder_flag,
735 accumulate_flag,
736 product_attribute_context,
737 product_attribute,
738 product_attr_value,
739 product_uom_code,
740 pricing_attribute_context,
741 pricing_attribute,
742 pricing_attr_value_from,
743 pricing_attr_value_to,
744 attribute_grouping_no,
745 product_attribute_datatype,
746 pricing_attribute_datatype,
747 comparison_operator_code,
748 list_header_id,
749 pricing_phase_id,
750 qualification_ind,
751 pricing_attr_value_from_number,
752 pricing_attr_value_to_number,
753 distinct_row_count,
754 search_ind,
755 pattern_value_from_positive,
756 pattern_value_to_positive,
757 pattern_value_from_negative,
758 pattern_value_to_negative,
759 product_segment_id,
760 pricing_segment_id
761 FROM qp_pricing_attributes
762 WHERE list_line_id = p_list_line_id
763 UNION
764 SELECT pricing_attribute_id,
765 list_line_id,
766 excluder_flag,
767 accumulate_flag,
768 product_attribute_context,
769 product_attribute,
770 product_attr_value,
771 product_uom_code,
772 pricing_attribute_context,
773 pricing_attribute,
774 pricing_attr_value_from,
775 pricing_attr_value_to,
776 attribute_grouping_no,
777 product_attribute_datatype,
778 pricing_attribute_datatype,
779 comparison_operator_code,
780 list_header_id,
781 pricing_phase_id,
782 qualification_ind,
783 pricing_attr_value_from_number,
784 pricing_attr_value_to_number,
785 distinct_row_count,
786 search_ind,
787 pattern_value_from_positive,
788 pattern_value_to_positive,
789 pattern_value_from_negative,
790 pattern_value_to_negative,
791 product_segment_id,
792 pricing_segment_id
793 FROM qp_pricing_attributes
794 WHERE list_line_id IN (SELECT to_rltd_modifier_id
795 FROM qp_rltd_modifiers
796 WHERE from_rltd_modifier_id = p_list_line_id)
797 ORDER BY product_attribute_context,product_attribute;
798
799 CURSOR csr_get_used_in_setup_flag(p_context VARCHAR2, p_attribute VARCHAR2, p_context_type VARCHAR2) IS
800 SELECT used_in_setup
801 FROM qp_pte_segments
802 WHERE segment_id IN
803 (SELECT a.segment_id
804 FROM qp_segments_b a,qp_prc_contexts_b b
805 WHERE a.segment_mapping_column=p_attribute
806 AND a.prc_context_id=b.prc_context_id
807 AND b.prc_context_type=p_context_type
808 AND b.prc_context_code=p_context);
809
810 BEGIN
811
815 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
812 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
813
814 BEGIN
816 EXCEPTION
817 WHEN UTL_FILE.INVALID_PATH THEN
818 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
819 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
820 RETURN;
821 END;
822
823 utl_file.new_line(l_file);
824 utl_file.put(l_file,'PRICING_ATTRIBUTES');
825 utl_file.new_line(l_file);
826
827 l_pricing_attr_string1 := 'DataSource, Pricing_Attribute_Id, List_Line_Id, Excluder_Flag, Accumulate_Flag, Product_Attribute_Context, Product_Attribute, Product_Attr_Value, Product_Uom_Code, Pricing_Attribute_Context, Pricing_Attribute,';
828 l_pricing_attr_string2 := 'Pricing_Attr_Value_From,Pricing_Attr_Value_To, Attribute_Grouping_No, Product_Attribute_Datatype, Pricing_Attribute_Datatype, Comparison_Operator_Code, List_Header_Id, Pricing_Phase_Id, Qualification_Ind,';
829 l_pricing_attr_string3 := 'Pricing_Attr_Value_From_Number, Pricing_Attr_Value_To_Number,Distinct_Row_Count, Search_Ind, Pattern_Value_From_Positive, Pattern_Value_To_Positive, Pattern_Value_From_Negative, Pattern_Value_To_Negative, Product_Segment_Id,';
830 l_pricing_attr_string4 := 'Product_Used_In_Setup, Pricing_Segment_Id,Pricing_Attr_Used_In_Setup';
831 l_pricing_attr_string := l_pricing_attr_string1 || l_pricing_attr_string2 || l_pricing_attr_string3 || l_pricing_attr_string4 ;
832
833
834 utl_file.put(l_file,l_pricing_attr_string);
835
836 OPEN csr_get_pricing_attributes(p_forms_list_line_id);
837 LOOP
838
839 FETCH csr_get_pricing_attributes INTO l_pricing_attribute_rec;
840 EXIT WHEN csr_get_pricing_attributes%NOTFOUND;
841
842 OPEN csr_get_used_in_setup_flag(l_pricing_attribute_rec.product_attribute_context , l_pricing_attribute_rec.product_attribute,'PRODUCT');
843 FETCH csr_get_used_in_setup_flag INTO l_product_used_in_setup;
844 CLOSE csr_get_used_in_setup_flag;
845
846 OPEN csr_get_used_in_setup_flag(l_pricing_attribute_rec.pricing_attribute_context , l_pricing_attribute_rec.pricing_attribute,'PRICING');
847 FETCH csr_get_used_in_setup_flag INTO l_pricing_attr_used_in_setup;
848 CLOSE csr_get_used_in_setup_flag;
849
850 utl_file.new_line(l_file);
851
852 l_data_rec := 'FORMS' || ',';
853
854 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_id|| ',';
855 l_data_rec := l_data_rec || l_pricing_attribute_rec.list_line_id|| ',';
856 l_data_rec := l_data_rec || l_pricing_attribute_rec.excluder_flag|| ',';
857 l_data_rec := l_data_rec || l_pricing_attribute_rec.accumulate_flag|| ',';
858 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute_context|| ',';
859 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute|| ',';
860 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attr_value|| ',';
861 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_uom_code|| ',';
862 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_context|| ',';
863 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute|| ',';
864 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_from|| ',';
865 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_to|| ',';
866 l_data_rec := l_data_rec || l_pricing_attribute_rec.attribute_grouping_no|| ',';
867 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute_datatype|| ',';
868 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_datatype|| ',';
869 l_data_rec := l_data_rec || l_pricing_attribute_rec.comparison_operator_code|| ',';
870 l_data_rec := l_data_rec || l_pricing_attribute_rec.list_header_id|| ',';
871 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_phase_id|| ',';
872 l_data_rec := l_data_rec || l_pricing_attribute_rec.qualification_ind|| ',';
873 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_from_number|| ',';
874 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_to_number|| ',';
875 l_data_rec := l_data_rec || l_pricing_attribute_rec.distinct_row_count|| ',';
876 l_data_rec := l_data_rec || l_pricing_attribute_rec.search_ind|| ',';
877 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_from_positive|| ',';
878 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_to_positive|| ',';
879 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_from_negative|| ',';
880 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_to_negative|| ',';
881 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_segment_id|| ',';
882 l_data_rec := l_data_rec || l_product_used_in_setup || ',';
883 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_segment_id || ',';
884 l_data_rec := l_data_rec || l_pricing_attr_used_in_setup ;
885
886 utl_file.put(l_file,l_data_rec);
887
888 END LOOP;
889
890 CLOSE csr_get_pricing_attributes;
891
892 l_pricing_attribute_rec := l_reset_pricing_attr_rec;
893
894 OPEN csr_get_pricing_attributes(p_html_list_line_id);
895 LOOP
896
897 FETCH csr_get_pricing_attributes INTO l_pricing_attribute_rec;
898 EXIT WHEN csr_get_pricing_attributes%NOTFOUND;
899
900 utl_file.new_line(l_file);
901 l_data_rec := 'HTML' || ',';
902
903 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_id|| ',';
904 l_data_rec := l_data_rec || l_pricing_attribute_rec.list_line_id|| ',';
905 l_data_rec := l_data_rec || l_pricing_attribute_rec.excluder_flag|| ',';
906 l_data_rec := l_data_rec || l_pricing_attribute_rec.accumulate_flag|| ',';
907 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute_context|| ',';
908 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute|| ',';
909 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attr_value|| ',';
910 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_uom_code|| ',';
914 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_to|| ',';
911 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_context|| ',';
912 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute|| ',';
913 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_from|| ',';
915 l_data_rec := l_data_rec || l_pricing_attribute_rec.attribute_grouping_no|| ',';
916 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_attribute_datatype|| ',';
917 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attribute_datatype|| ',';
918 l_data_rec := l_data_rec || l_pricing_attribute_rec.comparison_operator_code|| ',';
919 l_data_rec := l_data_rec || l_pricing_attribute_rec.list_header_id|| ',';
920 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_phase_id|| ',';
921 l_data_rec := l_data_rec || l_pricing_attribute_rec.qualification_ind|| ',';
922 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_from_number|| ',';
923 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_attr_value_to_number|| ',';
924 l_data_rec := l_data_rec || l_pricing_attribute_rec.distinct_row_count|| ',';
925 l_data_rec := l_data_rec || l_pricing_attribute_rec.search_ind|| ',';
926 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_from_positive|| ',';
927 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_to_positive|| ',';
928 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_from_negative|| ',';
929 l_data_rec := l_data_rec || l_pricing_attribute_rec.pattern_value_to_negative|| ',';
930 l_data_rec := l_data_rec || l_pricing_attribute_rec.product_segment_id|| ',';
931 l_data_rec := l_data_rec || l_pricing_attribute_rec.pricing_segment_id;
932
933 utl_file.put(l_file,l_data_rec);
934
935 END LOOP;
936
937 CLOSE csr_get_pricing_attributes;
938
939 utl_file.fclose(l_file);
940 x_return_status := 'S';
941
942 END Pricing_Attribute_Data;
943
944 PROCEDURE Rltd_Modifier_Data(p_html_list_line_id NUMBER,
945 p_forms_list_line_id NUMBER,
946 p_file_dir VARCHAR2,
947 x_return_status OUT NOCOPY VARCHAR2) IS
948
949 l_filename VARCHAR2(60);
950 l_file UTL_FILE.file_type;
951 l_rltd_modifier_string VARCHAR2(2000);
952 l_data_rec VARCHAR2(700);
953 l_rltd_modifier_rec RLTD_MODIFIER_REC_TYPE;
954 l_reset_rltd_modifier_rec RLTD_MODIFIER_REC_TYPE;
955
956 CURSOR csr_get_rltd_modifiers(p_list_line_id NUMBER) IS
957 SELECT rltd_modifier_id,
958 rltd_modifier_grp_no,
959 from_rltd_modifier_id,
960 to_rltd_modifier_id,
961 rltd_modifier_grp_type
962 FROM qp_rltd_modifiers
963 WHERE from_rltd_modifier_id = p_list_line_id;
964
965 BEGIN
966
967 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
968
969 BEGIN
970 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
971 EXCEPTION
972 WHEN UTL_FILE.INVALID_PATH THEN
973 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
974 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
975 RETURN;
976 END;
977
978 utl_file.new_line(l_file);
979 utl_file.put(l_file,'RLTD_MODIFIERS');
980 utl_file.new_line(l_file);
981
982 l_rltd_modifier_string := 'DataSource, Rltd_Modifier_Id, Rltd_Modifier_Grp_No, From_Rltd_Modifier_Id, To_Rltd_Modifier_Id, Rltd_Modifier_Grp_Type';
983
984 utl_file.put(l_file,l_rltd_modifier_string);
985
986 OPEN csr_get_rltd_modifiers(p_forms_list_line_id);
987 LOOP
988
989 FETCH csr_get_rltd_modifiers INTO l_rltd_modifier_rec;
990 EXIT WHEN csr_get_rltd_modifiers%NOTFOUND;
991
992 utl_file.new_line(l_file);
993
994 l_data_rec := 'FORMS' || ',';
995
996 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_id|| ',';
997 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_grp_no|| ',';
998 l_data_rec := l_data_rec || l_rltd_modifier_rec.from_rltd_modifier_id|| ',';
999 l_data_rec := l_data_rec || l_rltd_modifier_rec.to_rltd_modifier_id|| ',';
1000 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_grp_type;
1001
1002 utl_file.put(l_file,l_data_rec);
1003
1004 END LOOP;
1005
1006 CLOSE csr_get_rltd_modifiers;
1007
1008 l_rltd_modifier_rec := l_reset_rltd_modifier_rec;
1009
1010 OPEN csr_get_rltd_modifiers(p_html_list_line_id);
1011 LOOP
1012
1013 FETCH csr_get_rltd_modifiers INTO l_rltd_modifier_rec;
1014 EXIT WHEN csr_get_rltd_modifiers%NOTFOUND;
1015
1016 utl_file.new_line(l_file);
1017 l_data_rec := 'HTML' || ',';
1018
1019 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_id|| ',';
1020 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_grp_no|| ',';
1021 l_data_rec := l_data_rec || l_rltd_modifier_rec.from_rltd_modifier_id|| ',';
1022 l_data_rec := l_data_rec || l_rltd_modifier_rec.to_rltd_modifier_id|| ',';
1023 l_data_rec := l_data_rec || l_rltd_modifier_rec.rltd_modifier_grp_type;
1024
1025 utl_file.put(l_file,l_data_rec);
1026
1027 END LOOP;
1028
1029 CLOSE csr_get_rltd_modifiers;
1030
1031 utl_file.fclose(l_file);
1032 x_return_status := 'S';
1033
1034 END Rltd_Modifier_Data;
1035
1036 PROCEDURE List_Header_Phases_Data(p_html_list_line_id NUMBER,
1037 p_forms_list_line_id NUMBER,
1038 p_file_dir VARCHAR2,
1039 x_return_status OUT NOCOPY VARCHAR2) IS
1040
1041 l_filename VARCHAR2(60);
1042 l_file UTL_FILE.file_type;
1046 l_data_rec VARCHAR2(700);
1043 l_html_list_header_id NUMBER;
1044 l_forms_list_header_id NUMBER;
1045 l_list_header_phases_string VARCHAR2(2000);
1047 l_list_header_phases_rec LIST_HEADER_PHASES_REC_TYPE;
1048 l_reset_list_header_phases_rec LIST_HEADER_PHASES_REC_TYPE;
1049
1050 CURSOR csr_get_list_header_phases(p_list_header_id NUMBER) IS
1051 SELECT list_header_id,
1052 pricing_phase_id,
1053 qualifier_flag
1054 FROM qp_list_header_phases
1055 WHERE list_header_id = p_list_header_id;
1056
1057 CURSOR csr_get_list_header_id(p_list_line_id NUMBER) IS
1058 SELECT list_header_id
1059 FROM qp_list_lines
1060 WHERE list_line_id = p_list_line_id;
1061
1062 BEGIN
1063
1064 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
1065
1066 BEGIN
1067 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1068 EXCEPTION
1069 WHEN UTL_FILE.INVALID_PATH THEN
1070 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1071 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1072 RETURN;
1073 END;
1074
1075 utl_file.new_line(l_file);
1076 utl_file.put(l_file,'LIST_HEADER_PHASES');
1077 utl_file.new_line(l_file);
1078
1079 l_list_header_phases_string := 'DataSource, List_Header_Id, Pricing_Phase_Id, Qualifier_Flag';
1080
1081 utl_file.put(l_file,l_list_header_phases_string);
1082
1083 OPEN csr_get_list_header_id(p_html_list_line_id);
1084 FETCH csr_get_list_header_id INTO l_html_list_header_id;
1085 CLOSE csr_get_list_header_id;
1086
1087 OPEN csr_get_list_header_id(p_forms_list_line_id);
1088 FETCH csr_get_list_header_id INTO l_forms_list_header_id;
1089 CLOSE csr_get_list_header_id;
1090
1091 OPEN csr_get_list_header_phases(l_forms_list_header_id);
1092 LOOP
1093
1094 FETCH csr_get_list_header_phases INTO l_list_header_phases_rec;
1095 EXIT WHEN csr_get_list_header_phases%NOTFOUND;
1096
1097 utl_file.new_line(l_file);
1098
1099 l_data_rec := 'FORMS' || ',';
1100
1101 l_data_rec := l_data_rec || l_list_header_phases_rec.List_Header_Id || ',' ;
1102 l_data_rec := l_data_rec || l_list_header_phases_rec.Pricing_Phase_Id || ',' ;
1103 l_data_rec := l_data_rec || l_list_header_phases_rec.Qualifier_Flag ;
1104
1105 utl_file.put(l_file,l_data_rec);
1106
1107 END LOOP;
1108
1109 CLOSE csr_get_list_header_phases;
1110
1111 l_list_header_phases_rec := l_reset_list_header_phases_rec;
1112
1113 OPEN csr_get_list_header_phases(l_html_list_header_id);
1114 LOOP
1115
1116 FETCH csr_get_list_header_phases INTO l_list_header_phases_rec;
1117 EXIT WHEN csr_get_list_header_phases%NOTFOUND;
1118
1119 utl_file.new_line(l_file);
1120 l_data_rec := 'HTML' || ',';
1121
1122 l_data_rec := l_data_rec || l_list_header_phases_rec.List_Header_Id || ',' ;
1123 l_data_rec := l_data_rec || l_list_header_phases_rec.Pricing_Phase_Id || ',' ;
1124 l_data_rec := l_data_rec || l_list_header_phases_rec.Qualifier_Flag ;
1125
1126 utl_file.put(l_file,l_data_rec);
1127
1128 END LOOP;
1129
1130 CLOSE csr_get_list_header_phases;
1131
1132 utl_file.fclose(l_file);
1133 x_return_status := 'S';
1134
1135 END List_Header_Phases_Data;
1136
1137 PROCEDURE Pricing_Phases_Data(p_data_creation_method VARCHAR2,
1138 p_file_dir VARCHAR2,
1139 x_return_status OUT NOCOPY VARCHAR2) IS
1140
1141 l_filename VARCHAR2(60);
1142 l_file UTL_FILE.file_type;
1143 l_pricing_phases_string VARCHAR2(2000);
1144 l_pricing_phases_string1 VARCHAR2(255);
1145 l_pricing_phases_string2 VARCHAR2(255);
1146 l_data_rec VARCHAR2(700);
1147 l_pricing_phases_rec PRICING_PHASES_REC_TYPE;
1148 l_reset_pricing_phases_rec PRICING_PHASES_REC_TYPE;
1149
1150 CURSOR csr_get_pricing_phases IS
1151 SELECT modifier_level_code,
1152 phase_sequence,
1153 pricing_phase_id,
1154 incompat_resolve_code,
1155 name,
1156 seeded_flag,
1157 freeze_override_flag,
1158 user_freeze_override_flag,
1159 user_incompat_resolve_code,
1160 line_group_exists,
1161 oid_exists,
1162 rltd_exists,
1163 freight_exists,
1164 manual_modifier_flag
1165 FROM qp_pricing_phases
1166 ORDER BY phase_sequence;
1167
1168 BEGIN
1169
1170 l_filename := 'QP_' || p_data_creation_method || '_OTHERS.dat' ;
1171
1172 BEGIN
1173 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'w');
1174 EXCEPTION
1175 WHEN UTL_FILE.INVALID_PATH THEN
1176 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1177 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1178 RETURN;
1179 END;
1180
1181 utl_file.new_line(l_file);
1182 utl_file.put(l_file,'PRICING_PHASES');
1183 utl_file.new_line(l_file);
1184
1185 l_pricing_phases_string1 := 'DataSource, Modifier_Level_Code, Phase_Sequence, Pricing_Phase_Id, Incompat_Resolve_Code, Name, Seeded_Flag, Freeze_Override_Flag, User_Freeze_Override_Flag, User_Incompat_Resolve_Code, Line_Group_Exists, Oid_Exists,';
1186 l_pricing_phases_string2 := 'Rltd_Exists, Freight_Exists,Manual_Modifier_Flag';
1187 l_pricing_phases_string := l_pricing_phases_string1 || l_pricing_phases_string2 ;
1188
1189
1190 utl_file.put(l_file,l_pricing_phases_string);
1191
1192 OPEN csr_get_pricing_phases;
1193 LOOP
1194
1195 FETCH csr_get_pricing_phases INTO l_pricing_phases_rec;
1199
1196 EXIT WHEN csr_get_pricing_phases%NOTFOUND;
1197
1198 utl_file.new_line(l_file);
1200 l_data_rec := p_data_creation_method || ',';
1201
1202 l_data_rec := l_data_rec || l_pricing_phases_rec.modifier_level_code|| ',';
1203 l_data_rec := l_data_rec || l_pricing_phases_rec.phase_sequence|| ',';
1204 l_data_rec := l_data_rec || l_pricing_phases_rec.pricing_phase_id|| ',';
1205 l_data_rec := l_data_rec || l_pricing_phases_rec.incompat_resolve_code|| ',';
1206 l_data_rec := l_data_rec || l_pricing_phases_rec.name|| ',';
1207 l_data_rec := l_data_rec || l_pricing_phases_rec.seeded_flag|| ',';
1208 l_data_rec := l_data_rec || l_pricing_phases_rec.freeze_override_flag|| ',';
1209 l_data_rec := l_data_rec || l_pricing_phases_rec.user_freeze_override_flag|| ',';
1210 l_data_rec := l_data_rec || l_pricing_phases_rec.user_incompat_resolve_code|| ',';
1211 l_data_rec := l_data_rec || l_pricing_phases_rec.line_group_exists|| ',';
1212 l_data_rec := l_data_rec || l_pricing_phases_rec.oid_exists|| ',';
1213 l_data_rec := l_data_rec || l_pricing_phases_rec.rltd_exists|| ',';
1214 l_data_rec := l_data_rec || l_pricing_phases_rec.freight_exists|| ',';
1215 l_data_rec := l_data_rec || l_pricing_phases_rec.manual_modifier_flag ;
1216
1217 utl_file.put(l_file,l_data_rec);
1218
1219 END LOOP;
1220
1221 CLOSE csr_get_pricing_phases;
1222
1223 utl_file.fclose(l_file);
1224 x_return_status := 'S';
1225
1226 END Pricing_Phases_Data;
1227
1228 PROCEDURE Adv_Mod_Products_Data(p_list_line_id NUMBER ,
1229 p_data_creation_method VARCHAR2,
1230 p_file_dir VARCHAR2,
1231 x_return_status OUT NOCOPY VARCHAR2) IS
1232
1233 l_filename VARCHAR2(60);
1234 l_file UTL_FILE.file_type;
1235 l_adv_mod_products_string VARCHAR2(2000);
1236 l_data_rec VARCHAR2(700);
1237 l_adv_mod_product_rec ADV_MOD_PRODUCTS_REC_TYPE;
1238 l_reset_adv_mod_products_rec ADV_MOD_PRODUCTS_REC_TYPE;
1239 l_pricing_phase_id NUMBER;
1240
1241 CURSOR csr_get_adv_mod_products(p_pricing_phase_id NUMBER) IS
1242 SELECT pricing_phase_id,
1243 product_attribute,
1244 product_attr_value
1245 FROM qp_adv_mod_products
1246 WHERE pricing_phase_id = p_pricing_phase_id;
1247
1248 CURSOR csr_get_pricing_phase_id(p_list_line_id NUMBER) IS
1249 SELECT pricing_phase_id
1250 FROM qp_list_lines
1251 WHERE list_line_id = p_list_line_id;
1252
1253
1254 BEGIN
1255
1256 l_filename := 'QP_' || p_data_creation_method || '_OTHERS.dat';
1257
1258 BEGIN
1259 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1260 EXCEPTION
1261 WHEN UTL_FILE.INVALID_PATH THEN
1262 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1263 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1264 RETURN;
1265 END;
1266
1267 OPEN csr_get_pricing_phase_id(p_list_line_id);
1268 FETCH csr_get_pricing_phase_id INTO l_pricing_phase_id;
1269 CLOSE csr_get_pricing_phase_id;
1270
1271 utl_file.new_line(l_file);
1272 utl_file.put(l_file,'ADV_MOD_PRODUCTS ' || l_pricing_phase_id);
1273 utl_file.new_line(l_file);
1274
1275 l_adv_mod_products_string := 'DataSource, Pricing_Phase_Id, Product_Attribute, Product_Attr_Value';
1276
1277 utl_file.put(l_file,l_adv_mod_products_string);
1278
1279
1280 OPEN csr_get_adv_mod_products(l_pricing_phase_id);
1281 LOOP
1282
1283 FETCH csr_get_adv_mod_products INTO l_adv_mod_product_rec;
1284 EXIT WHEN csr_get_adv_mod_products%NOTFOUND;
1285
1286 utl_file.new_line(l_file);
1287
1288 l_data_rec := p_data_creation_method || ',';
1289
1290 l_data_rec := l_data_rec || l_adv_mod_product_rec.pricing_phase_id|| ',' ;
1291 l_data_rec := l_data_rec || l_adv_mod_product_rec.product_attribute|| ',' ;
1292 l_data_rec := l_data_rec || l_adv_mod_product_rec.product_attr_value;
1293
1294 utl_file.put(l_file,l_data_rec);
1295
1296 END LOOP;
1297
1298 CLOSE csr_get_adv_mod_products;
1299
1300 utl_file.fclose(l_file);
1301 x_return_status := 'S';
1302
1303 END Adv_Mod_Products_Data;
1304
1305 PROCEDURE Attribute_Groups_Data(p_html_list_line_id NUMBER,
1306 p_forms_list_line_id NUMBER,
1307 p_file_dir VARCHAR2,
1308 x_return_status OUT NOCOPY VARCHAR2) IS
1309
1310 l_filename VARCHAR2(60);
1311 l_file UTL_FILE.file_type;
1312 l_html_list_header_id NUMBER;
1313 l_forms_list_header_id NUMBER;
1314 l_attribute_group_string VARCHAR2(2000);
1315 l_attribute_group_string1 VARCHAR2(255);
1316 l_attribute_group_string2 VARCHAR2(255);
1317 l_data_rec VARCHAR2(700);
1318 l_attribute_groups_rec ATTRIBUTE_GROUPS_REC_TYPE;
1319 l_reset_attribute_groups_rec ATTRIBUTE_GROUPS_REC_TYPE;
1320
1321 CURSOR csr_get_attribute_groups(p_list_header_id NUMBER) IS
1322 SELECT list_header_id,
1323 list_line_id,
1324 active_flag,
1325 list_type_code,
1326 start_date_active_q,
1327 end_date_active_q,
1328 pattern_id,
1329 currency_code ,
1330 ask_for_flag,
1331 limit_exists,
1332 source_system_code,
1333 effective_precedence,
1334 grouping_no,
1335 pricing_phase_id,
1336 modifier_level_code,
1337 hash_key,
1338 cache_key
1339 FROM qp_attribute_groups
1340 WHERE list_header_id = p_list_header_id;
1341
1342 CURSOR csr_get_list_header_id(p_list_line_id NUMBER) IS
1343 SELECT list_header_id
1347 BEGIN
1344 FROM qp_list_lines
1345 WHERE list_line_id = p_list_line_id;
1346
1348
1349 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
1350
1351 BEGIN
1352 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1353 EXCEPTION
1354 WHEN UTL_FILE.INVALID_PATH THEN
1355 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1356 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1357 RETURN;
1358 END;
1359
1360 utl_file.new_line(l_file);
1361 utl_file.put(l_file,'ATTRIBUTE_GROUPS');
1362 utl_file.new_line(l_file);
1363
1364 l_attribute_group_string1 := 'DataSource, List_Header_Id, List_Line_Id, Active_Flag, List_Type_Code, Start_Date_Active_Q, End_Date_Active_Q, Pattern_Id, Currency_Code , Ask_For_Flag, Limit_Exists, Source_System_Code, Effective_Precedence, Grouping_No,';
1365 l_attribute_group_string2 := 'Pricing_Phase_Id,Modifier_Level_Code, Hash_Key, Cache_Key';
1366 l_attribute_group_string := l_attribute_group_string1 || l_attribute_group_string2;
1367
1368
1369 OPEN csr_get_list_header_id(p_html_list_line_id);
1370 FETCH csr_get_list_header_id INTO l_html_list_header_id;
1371 CLOSE csr_get_list_header_id;
1372
1373 OPEN csr_get_list_header_id(p_forms_list_line_id);
1374 FETCH csr_get_list_header_id INTO l_forms_list_header_id;
1375 CLOSE csr_get_list_header_id;
1376
1377 utl_file.put(l_file,l_attribute_group_string);
1378
1379 OPEN csr_get_attribute_groups(l_forms_list_header_id);
1380 LOOP
1381
1382 FETCH csr_get_attribute_groups INTO l_attribute_groups_rec;
1383 EXIT WHEN csr_get_attribute_groups%NOTFOUND;
1384
1385 utl_file.new_line(l_file);
1386
1387 l_data_rec := 'FORMS' || ',';
1388
1389 l_data_rec := l_data_rec || l_attribute_groups_rec.list_header_id|| ',';
1390 l_data_rec := l_data_rec || l_attribute_groups_rec.list_line_id|| ',';
1391 l_data_rec := l_data_rec || l_attribute_groups_rec.active_flag|| ',';
1392 l_data_rec := l_data_rec || l_attribute_groups_rec.list_type_code|| ',';
1393 l_data_rec := l_data_rec || l_attribute_groups_rec.start_date_active_q|| ',';
1394 l_data_rec := l_data_rec || l_attribute_groups_rec.end_date_active_q|| ',';
1395 l_data_rec := l_data_rec || l_attribute_groups_rec.pattern_id|| ',';
1396 l_data_rec := l_data_rec || l_attribute_groups_rec.currency_code || ',';
1397 l_data_rec := l_data_rec || l_attribute_groups_rec.ask_for_flag|| ',';
1398 l_data_rec := l_data_rec || l_attribute_groups_rec.limit_exists|| ',';
1399 l_data_rec := l_data_rec || l_attribute_groups_rec.source_system_code|| ',';
1400 l_data_rec := l_data_rec || l_attribute_groups_rec.effective_precedence|| ',';
1401 l_data_rec := l_data_rec || l_attribute_groups_rec.grouping_no|| ',';
1402 l_data_rec := l_data_rec || l_attribute_groups_rec.pricing_phase_id|| ',';
1403 l_data_rec := l_data_rec || l_attribute_groups_rec.modifier_level_code|| ',';
1404 l_data_rec := l_data_rec || l_attribute_groups_rec.hash_key|| ',';
1405 l_data_rec := l_data_rec || l_attribute_groups_rec.cache_key;
1406
1407 utl_file.put(l_file,l_data_rec);
1408
1409 END LOOP;
1410
1411 CLOSE csr_get_attribute_groups;
1412
1413 l_attribute_groups_rec := l_reset_attribute_groups_rec;
1414
1415 OPEN csr_get_attribute_groups(l_html_list_header_id);
1416 LOOP
1417
1418 FETCH csr_get_attribute_groups INTO l_attribute_groups_rec;
1419 EXIT WHEN csr_get_attribute_groups%NOTFOUND;
1420
1421 utl_file.new_line(l_file);
1422 l_data_rec := 'HTML' || ',';
1423
1424 l_data_rec := l_data_rec || l_attribute_groups_rec.list_header_id|| ',';
1425 l_data_rec := l_data_rec || l_attribute_groups_rec.list_line_id|| ',';
1426 l_data_rec := l_data_rec || l_attribute_groups_rec.active_flag|| ',';
1427 l_data_rec := l_data_rec || l_attribute_groups_rec.list_type_code|| ',';
1428 l_data_rec := l_data_rec || l_attribute_groups_rec.start_date_active_q|| ',';
1429 l_data_rec := l_data_rec || l_attribute_groups_rec.end_date_active_q|| ',';
1430 l_data_rec := l_data_rec || l_attribute_groups_rec.pattern_id|| ',';
1431 l_data_rec := l_data_rec || l_attribute_groups_rec.currency_code || ',';
1432 l_data_rec := l_data_rec || l_attribute_groups_rec.ask_for_flag|| ',';
1433 l_data_rec := l_data_rec || l_attribute_groups_rec.limit_exists|| ',';
1434 l_data_rec := l_data_rec || l_attribute_groups_rec.source_system_code|| ',';
1435 l_data_rec := l_data_rec || l_attribute_groups_rec.effective_precedence|| ',';
1436 l_data_rec := l_data_rec || l_attribute_groups_rec.grouping_no|| ',';
1437 l_data_rec := l_data_rec || l_attribute_groups_rec.pricing_phase_id|| ',';
1438 l_data_rec := l_data_rec || l_attribute_groups_rec.modifier_level_code|| ',';
1439 l_data_rec := l_data_rec || l_attribute_groups_rec.hash_key|| ',';
1440 l_data_rec := l_data_rec || l_attribute_groups_rec.cache_key;
1441
1442 utl_file.put(l_file,l_data_rec);
1443
1444 END LOOP;
1445
1446 CLOSE csr_get_attribute_groups;
1447
1448 utl_file.fclose(l_file);
1449 x_return_status := 'S';
1450
1451 END Attribute_Groups_Data;
1452
1453 PROCEDURE Patterns_Data(p_html_list_line_id NUMBER,
1454 p_forms_list_line_id NUMBER,
1455 p_pattern_type VARCHAR2,
1456 p_file_dir VARCHAR2,
1457 x_return_status OUT NOCOPY VARCHAR2) IS
1458
1459 l_filename VARCHAR2(60);
1460 l_file UTL_FILE.file_type;
1461 l_patterns_string VARCHAR2(2000);
1462 l_data_rec VARCHAR2(700);
1463 l_patterns_rec PATTERNS_REC_TYPE;
1464 l_reset_patterns_rec PATTERNS_REC_TYPE;
1465 i PLS_INTEGER;
1466 l_pattern_id NUMBER;
1467
1468 CURSOR csr_get_patterns(p_pattern_id NUMBER) IS
1469 SELECT pattern_id,
1470 segment_id,
1471 pattern_type,
1475 ORDER BY pattern_id,segment_id;
1472 pattern_string
1473 FROM qp_patterns
1474 WHERE pattern_id = p_pattern_id
1476
1477 CURSOR csr_get_prod_pattern_id(p_list_line_id NUMBER) IS
1478 SELECT pattern_id
1479 FROM qp_list_lines
1480 WHERE list_line_id = p_list_line_id;
1481
1482 CURSOR csr_get_qual_pattern_id(p_list_line_id NUMBER) IS
1483 SELECT pattern_id,list_line_id,list_header_id
1484 FROM qp_attribute_groups
1485 WHERE list_header_id IN (SELECT list_header_id FROM qp_list_lines WHERE list_line_id = p_list_line_id)
1486 AND list_line_id IN (p_list_line_id , -1);
1487
1488 BEGIN
1489
1490 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
1491
1492 BEGIN
1493 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1494 EXCEPTION
1495 WHEN UTL_FILE.INVALID_PATH THEN
1496 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1497 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1498 RETURN;
1499 END;
1500
1501 IF (p_pattern_type = 'PRODUCT') THEN
1502
1503 OPEN csr_get_prod_pattern_id(p_forms_list_line_id);
1504 FETCH csr_get_prod_pattern_id INTO l_pattern_id;
1505 CLOSE csr_get_prod_pattern_id;
1506
1507 utl_file.new_line(l_file);
1508 utl_file.put(l_file,'PRODUCT PATTERNS');
1509 utl_file.new_line(l_file);
1510
1511 l_patterns_string := 'DataSource, List_Line_Id, Pattern_Id, Segment_Id, Pattern_Type, Pattern_String';
1512
1513 utl_file.put(l_file,l_patterns_string);
1514
1515 OPEN csr_get_patterns(l_pattern_id);
1516 LOOP
1517
1518 FETCH csr_get_patterns INTO l_patterns_rec;
1519 EXIT WHEN csr_get_patterns%NOTFOUND;
1520
1521 utl_file.new_line(l_file);
1522
1523 l_data_rec := 'FORMS' || ',';
1524
1525 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1526 l_data_rec := l_data_rec || l_patterns_rec.pattern_id|| ',';
1527 l_data_rec := l_data_rec || l_patterns_rec.segment_id|| ',';
1528 l_data_rec := l_data_rec || l_patterns_rec.pattern_type|| ',';
1529 l_data_rec := l_data_rec || l_patterns_rec.pattern_string;
1530
1531 utl_file.put(l_file,l_data_rec);
1532
1533 END LOOP;
1534
1535 CLOSE csr_get_patterns;
1536
1537 l_patterns_rec := l_reset_patterns_rec;
1538
1539 OPEN csr_get_prod_pattern_id(p_html_list_line_id);
1540 FETCH csr_get_prod_pattern_id INTO l_pattern_id;
1541 CLOSE csr_get_prod_pattern_id;
1542
1543 OPEN csr_get_patterns(l_pattern_id);
1544 LOOP
1545
1546 FETCH csr_get_patterns INTO l_patterns_rec;
1547 EXIT WHEN csr_get_patterns%NOTFOUND;
1548
1549 utl_file.new_line(l_file);
1550
1551 l_data_rec := 'HTML' || ',';
1552
1553 l_data_rec := l_data_rec || p_html_list_line_id || ',';
1554 l_data_rec := l_data_rec || l_patterns_rec.pattern_id|| ',';
1555 l_data_rec := l_data_rec || l_patterns_rec.segment_id|| ',';
1556 l_data_rec := l_data_rec || l_patterns_rec.pattern_type|| ',';
1557 l_data_rec := l_data_rec || l_patterns_rec.pattern_string;
1558
1559 utl_file.put(l_file,l_data_rec);
1560
1561 END LOOP;
1562
1563 CLOSE csr_get_patterns;
1564
1565 END IF;
1566
1567 IF (p_pattern_type = 'QUALIFIER') THEN
1568
1569 FOR i IN csr_get_qual_pattern_id(p_forms_list_line_id)
1570 LOOP
1571
1572 utl_file.new_line(l_file);
1573 utl_file.put(l_file,'QUALIFIER PATTERNS');
1574 utl_file.new_line(l_file);
1575
1576 l_patterns_string := 'DataSource, List_Line_Id, Pattern_Id, Segment_Id, Pattern_Type, Pattern_String';
1577
1578 utl_file.put(l_file,l_patterns_string);
1579
1580 OPEN csr_get_patterns(i.pattern_id);
1581 LOOP
1582
1583 FETCH csr_get_patterns INTO l_patterns_rec;
1584 EXIT WHEN csr_get_patterns%NOTFOUND;
1585
1586 utl_file.new_line(l_file);
1587
1588 l_data_rec := 'FORMS' || ',';
1589
1590 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1591 l_data_rec := l_data_rec || l_patterns_rec.pattern_id|| ',';
1592 l_data_rec := l_data_rec || l_patterns_rec.segment_id|| ',';
1593 l_data_rec := l_data_rec || l_patterns_rec.pattern_type|| ',';
1594 l_data_rec := l_data_rec || l_patterns_rec.pattern_string;
1595
1596 utl_file.put(l_file,l_data_rec);
1597
1598 END LOOP;
1599 CLOSE csr_get_patterns;
1600
1601 END LOOP;
1602
1603 l_patterns_rec := l_reset_patterns_rec;
1604
1605 FOR i IN csr_get_qual_pattern_id(p_html_list_line_id)
1606 LOOP
1607
1608 OPEN csr_get_patterns(i.pattern_id);
1609 LOOP
1610
1611 FETCH csr_get_patterns INTO l_patterns_rec;
1612 EXIT WHEN csr_get_patterns%NOTFOUND;
1613
1614 utl_file.new_line(l_file);
1615
1616 l_data_rec := 'HTML' || ',';
1617
1618 l_data_rec := l_data_rec || p_html_list_line_id || ',';
1619 l_data_rec := l_data_rec || l_patterns_rec.pattern_id|| ',';
1620 l_data_rec := l_data_rec || l_patterns_rec.segment_id|| ',';
1621 l_data_rec := l_data_rec || l_patterns_rec.pattern_type|| ',';
1622 l_data_rec := l_data_rec || l_patterns_rec.pattern_string;
1623
1624 utl_file.put(l_file,l_data_rec);
1625
1626 END LOOP;
1627 CLOSE csr_get_patterns;
1628
1629 END LOOP;
1630
1631 END IF;
1632 utl_file.fclose(l_file);
1633 x_return_status := 'S';
1634
1635 END Patterns_Data;
1636
1637 PROCEDURE Pattern_Phases_Data(p_html_list_line_id NUMBER ,
1638 p_forms_list_line_id NUMBER,
1639 p_pattern_type VARCHAR2,
1640 p_file_dir VARCHAR2,
1644 l_file UTL_FILE.file_type;
1641 x_return_status OUT NOCOPY VARCHAR2) IS
1642
1643 l_filename VARCHAR2(60);
1645 l_pattern_phases_string VARCHAR2(2000);
1646 l_data_rec VARCHAR2(700);
1647 l_pattern_phases_rec PATTERN_PHASES_REC_TYPE;
1648 l_reset_pattern_phases_rec PATTERN_PHASES_REC_TYPE;
1649 l_pricing_phase_id NUMBER;
1650 l_pattern_id NUMBER;
1651 l_list_header_id NUMBER;
1652
1653 CURSOR csr_get_pattern_phases(p_pricing_phase_id NUMBER,p_pattern_id NUMBER) IS
1654 SELECT pattern_id,
1655 pricing_phase_id
1656 FROM qp_pattern_phases
1657 WHERE pricing_phase_id = p_pricing_phase_id
1658 AND pattern_id = p_pattern_id;
1659
1660 CURSOR csr_get_prod_pattern_phases(p_list_line_id NUMBER) IS
1661 SELECT a.list_header_id,a.pattern_id,a.pricing_phase_id
1662 FROM qp_list_lines a , qp_pattern_phases b
1663 WHERE a.list_line_id = p_list_line_id
1664 AND a.pattern_id = b.pattern_id
1665 AND a.pricing_phase_id = b.pricing_phase_id;
1666
1667 CURSOR csr_get_hqual_pattern_phases(p_list_line_id NUMBER) IS
1668 SELECT a.list_header_id,a.pattern_id,c.pricing_phase_id
1669 FROM qp_attribute_groups a ,
1670 (SELECT distinct list_header_id, pricing_phase_id
1671 FROM qp_list_lines WHERE list_line_id = p_list_line_id) b ,
1672 qp_pattern_phases c
1673 WHERE a.list_header_id = b.list_header_id
1674 AND a.pattern_id = c.pattern_id
1675 AND a.list_line_id = -1;
1676
1677 CURSOR csr_get_lqual_pattern_phases(p_list_line_id NUMBER) IS
1678 SELECT a.list_header_id,a.pattern_id,a.pricing_phase_id
1679 FROM qp_attribute_groups a, qp_pattern_phases b
1680 WHERE a.list_line_id = p_list_line_id
1681 AND a.pattern_id = b.pattern_id
1682 AND a.pricing_phase_id = b.pricing_phase_id;
1683
1684 BEGIN
1685
1686 l_filename := 'QP_HTML_' || p_html_list_line_id||'_FORMS_'||p_forms_list_line_id || '.dat' ;
1687
1688 BEGIN
1689 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1690 EXCEPTION
1691 WHEN UTL_FILE.INVALID_PATH THEN
1692 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1693 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1694 RETURN;
1695 END;
1696
1697 IF (p_pattern_type = 'PRODUCT') THEN
1698
1699 utl_file.new_line(l_file);
1700 utl_file.put(l_file,'PRODUCT PATTERN PHASES');
1701 utl_file.new_line(l_file);
1702
1703 l_pattern_phases_string := 'DataSource, List_Header_Id, List_Line_Id, Pattern_Id, Pricing_Phase_Id';
1704
1705 utl_file.put(l_file,l_pattern_phases_string);
1706
1707 OPEN csr_get_prod_pattern_phases(p_forms_list_line_id);
1708 LOOP
1709
1710 FETCH csr_get_prod_pattern_phases INTO l_pattern_phases_rec;
1711 EXIT WHEN csr_get_prod_pattern_phases%NOTFOUND;
1712
1713 utl_file.new_line(l_file);
1714
1715 l_data_rec := 'FORMS' || ',';
1716
1717 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1718 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1719 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1720 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1721
1722 utl_file.put(l_file,l_data_rec);
1723
1724 END LOOP;
1725
1726 CLOSE csr_get_prod_pattern_phases;
1727
1728 l_pattern_phases_rec := l_reset_pattern_phases_rec;
1729
1730 OPEN csr_get_prod_pattern_phases(p_html_list_line_id);
1731 LOOP
1732
1733 FETCH csr_get_prod_pattern_phases INTO l_pattern_phases_rec;
1734 EXIT WHEN csr_get_prod_pattern_phases%NOTFOUND;
1735
1736 utl_file.new_line(l_file);
1737
1738 l_data_rec := 'HTML' || ',';
1739
1740 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1741 l_data_rec := l_data_rec || p_html_list_line_id || ',';
1742 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1743 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1744
1745 utl_file.put(l_file,l_data_rec);
1746
1747 END LOOP;
1748
1749 CLOSE csr_get_prod_pattern_phases;
1750
1751 END IF;
1752
1753 IF (p_pattern_type = 'QUALIFIER') THEN
1754
1755 utl_file.new_line(l_file);
1756 utl_file.put(l_file,'QUALIFIER HEADER PATTERN PHASES' );
1757 utl_file.new_line(l_file);
1758
1759 l_pattern_phases_string := 'DataSource, List_Header_Id, List_Line_Id , Pattern_Id, Pricing_Phase_Id';
1760
1761 utl_file.put(l_file,l_pattern_phases_string);
1762
1763 OPEN csr_get_hqual_pattern_phases(p_forms_list_line_id);
1764 LOOP
1765
1766 FETCH csr_get_hqual_pattern_phases INTO l_pattern_phases_rec;
1767 EXIT WHEN csr_get_hqual_pattern_phases%NOTFOUND;
1768
1769 utl_file.new_line(l_file);
1770
1771 l_data_rec := 'FORMS' || ',';
1772
1773 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1774 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1775 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1776 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1777
1778 utl_file.put(l_file,l_data_rec);
1779
1780 END LOOP;
1781
1782 CLOSE csr_get_hqual_pattern_phases;
1783
1784 l_pattern_phases_rec := l_reset_pattern_phases_rec;
1785
1786 OPEN csr_get_hqual_pattern_phases(p_html_list_line_id);
1787 LOOP
1788
1789 FETCH csr_get_hqual_pattern_phases INTO l_pattern_phases_rec;
1790 EXIT WHEN csr_get_hqual_pattern_phases%NOTFOUND;
1791
1792 utl_file.new_line(l_file);
1793
1794 l_data_rec := 'HTML' || ',';
1795
1799 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1796 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1797 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1798 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1800
1801 utl_file.put(l_file,l_data_rec);
1802
1803 END LOOP;
1804
1805 CLOSE csr_get_hqual_pattern_phases;
1806
1807 utl_file.new_line(l_file);
1808 utl_file.put(l_file,'QUALIFIER LINE PATTERN PHASES' );
1809 utl_file.new_line(l_file);
1810
1811 l_pattern_phases_string := 'DataSource, List_Header_Id, List_Line_Id , Pattern_Id, Pricing_Phase_Id';
1812
1813 utl_file.put(l_file,l_pattern_phases_string);
1814
1815 OPEN csr_get_lqual_pattern_phases(p_forms_list_line_id);
1816 LOOP
1817
1818 FETCH csr_get_lqual_pattern_phases INTO l_pattern_phases_rec;
1819 EXIT WHEN csr_get_lqual_pattern_phases%NOTFOUND;
1820
1821 utl_file.new_line(l_file);
1822
1823 l_data_rec := 'FORMS' || ',';
1824
1825 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1826 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1827 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1828 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1829
1830 utl_file.put(l_file,l_data_rec);
1831
1832 END LOOP;
1833
1834 CLOSE csr_get_lqual_pattern_phases;
1835
1836 l_pattern_phases_rec := l_reset_pattern_phases_rec;
1837
1838 OPEN csr_get_lqual_pattern_phases(p_html_list_line_id);
1839 LOOP
1840
1841 FETCH csr_get_lqual_pattern_phases INTO l_pattern_phases_rec;
1842 EXIT WHEN csr_get_lqual_pattern_phases%NOTFOUND;
1843
1844 utl_file.new_line(l_file);
1845
1846 l_data_rec := 'HTML' || ',';
1847
1848 l_data_rec := l_data_rec || l_pattern_phases_rec.list_header_id || ',';
1849 l_data_rec := l_data_rec || p_forms_list_line_id || ',';
1850 l_data_rec := l_data_rec || l_pattern_phases_rec.pattern_id || ',';
1851 l_data_rec := l_data_rec || l_pattern_phases_rec.pricing_phase_id;
1852
1853 utl_file.put(l_file,l_data_rec);
1854
1855 END LOOP;
1856
1857 CLOSE csr_get_lqual_pattern_phases;
1858
1859 END IF;
1860
1861 utl_file.fclose(l_file);
1862 x_return_status := 'S';
1863
1864 END Pattern_Phases_Data;
1865
1866 PROCEDURE Profiles_Data(p_list_line_id NUMBER ,
1867 p_data_creation_method VARCHAR2,
1868 p_file_dir VARCHAR2,
1869 x_return_status OUT NOCOPY VARCHAR2) IS
1870
1871 l_filename VARCHAR2(60);
1872 l_file UTL_FILE.file_type;
1873 l_profiles_string VARCHAR2(2000);
1874 l_data_rec VARCHAR2(700);
1875 l_profile_value VARCHAR2(30);
1876
1877 CURSOR csr_get_profile_value(profile_name VARCHAR2) IS
1878 SELECT fnd_profile.value(profile_name)
1879 FROM dual;
1880
1881 BEGIN
1882
1883 l_filename := 'QP_' || p_data_creation_method || '_OTHERS.dat';
1884
1885 BEGIN
1886 l_file := UTL_FILE.fopen(p_file_dir, l_filename, 'a');
1887 EXCEPTION
1888 WHEN UTL_FILE.INVALID_PATH THEN
1889 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Log directory, '||p_file_dir||' is not accessible');
1890 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please select a directory included in the utl_file_dir parameter for this database');
1891 RETURN;
1892 END;
1893
1894 OPEN csr_get_profile_value('QP_HVOP_PRICING_SETUP');
1895 FETCH csr_get_profile_value INTO l_profile_value;
1896 CLOSE csr_get_profile_value;
1897
1898 utl_file.new_line(l_file);
1899 utl_file.put(l_file,'PROFILE VALUES ' );
1900 utl_file.new_line(l_file);
1901
1902 l_profiles_string := 'DataSource, List_Line_Id , Profile_Name , Profile_Value';
1903
1904 utl_file.put(l_file,l_profiles_string);
1905 utl_file.new_line(l_file);
1906
1907 l_data_rec := p_data_creation_method || ',';
1908
1909 l_data_rec := l_data_rec || p_list_line_id || ',' ;
1910 l_data_rec := l_data_rec || 'QP_HVOP_PRICING_SETUP ' || ',' ;
1911 l_data_rec := l_data_rec || l_profile_value ;
1912
1913 utl_file.put(l_file,l_data_rec);
1914
1915 utl_file.fclose(l_file);
1916 x_return_status := 'S';
1917
1918 END Profiles_Data;
1919
1920 END QP_Data_Compare_PVT;