[Home] [Help]
PACKAGE BODY: APPS.PON_FORMS_UTIL_PVT
Source
1 PACKAGE BODY PON_FORMS_UTIL_PVT as
2 /* $Header: PONFMUTB.pls 120.9 2006/03/21 04:43:19 ukottama noship $ */
3
4 g_jrad_rgn_pkg_name CONSTANT VARCHAR2(50) := '/oracle/apps/pon/forms/jrad/webui/';
5
6 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_FORMS_UTIL_PVT';
8 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
9
10
11 g_newline Varchar2(10);
12 g_alias_counter number :=0;
13 g_bind_char Varchar2(10) := ':';
14 g_mode Varchar2(10):='XML';
15 g_dummy_char Varchar2(10) := 'Y#X';
16 g_dummy_num Number := 1025;
17 g_dummy_PK Varchar2(10) := '-9998';
18
19 g_date_sequence_number NUMBER;
20
21 g_number_sequence_number NUMBER;
22
23 g_text_sequence_number NUMBER;
24
25 g_internal_sequence_number NUMBER;
26
27
28
29 -------------------------- HELPER FUNCTIONS ----------------------------------
30
31 PROCEDURE printClobOut(result IN CLOB);
32 PROCEDURE printLong(result IN Varchar2);
33
34 PROCEDURE INSERT_LEVEL1_SECTION_IN_FORM(p_form_id IN NUMBER,
35 p_level1_section_id IN NUMBER);
36
37 PROCEDURE INSERT_LEVEL2_SECTION_IN_FORM(p_form_id IN NUMBER,
38 p_level1_section_id IN NUMBER,
39 p_level2_section_id IN NUMBER);
40
41 Procedure GetValSetTBLQuery(
42 p_value_set_name in Varchar2,
43 p_query_stmt in out NOCOPY Varchar2,
44 p_orderby in out NOCOPY Varchar2,
45 p_id_column_exists OUT NOCOPY Varchar2,
46 p_error OUT NOCOPY VARCHAR2,
47 p_result OUT NOCOPY number -- 0: Success, 1: failure
48 );
49
50
51
52 Procedure CreateDummyRowForXML(
53 p_value_pk_id Number,
54 p_form_id number,
55 p_section_id Number,
56 p_parent_fk_id Number,
57 p_level1_section_id Number,
58 p_level2_section_id Number,
59 p_error IN OUT NOCOPY VARCHAR2,
60 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
61 );
62 -------------------------- END HELPER FUNCTIONS ------------------------------
63
64
65
66 /*======================================================================
67 FUNCTON: getDataEntryRegionName PRIVATE
68 PARAMETERS:
69 COMMENT :
70 ======================================================================*/
71
72 FUNCTION getDataEntryRegionName(p_form_id IN NUMBER) RETURN VARCHAR2 IS
73
74 v_form_code PON_FORMS_SECTIONS.FORM_CODE%TYPE;
75 v_form_version PON_FORMS_SECTIONS.FORM_VERSION%TYPE;
76 v_type PON_FORMS_SECTIONS.TYPE%TYPE;
77
78 l_api_name CONSTANT VARCHAR2(30) := 'getDataEntryRegionName';
79
80 BEGIN
81
82 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
83
84 select form_code , form_version , type
85 into v_form_code, v_form_version, v_type
86 from pon_forms_sections where form_id = p_form_id;
87
88 print_debug_log(l_api_name, 'END- DataEntryRegionName = '|| g_jrad_rgn_pkg_name || 'pon_'|| v_form_code || '_V' || to_char(v_form_version) || '_RG');
89
90 return g_jrad_rgn_pkg_name || 'pon_'|| v_form_code || '_V' || to_char(v_form_version) || '_RG';
91
92 END getDataEntryRegionName;
93
94 /*======================================================================
95 FUNCTON: getReadOnlyRegionName PRIVATE
96 PARAMETERS:
97 COMMENT :
98 ======================================================================*/
99
100 FUNCTION getReadOnlyRegionName(p_form_id IN NUMBER) RETURN VARCHAR2 IS
101
102 v_form_code PON_FORMS_SECTIONS.FORM_CODE%TYPE;
103 v_form_version PON_FORMS_SECTIONS.FORM_VERSION%TYPE;
104 v_type PON_FORMS_SECTIONS.TYPE%TYPE;
105
106 l_api_name CONSTANT VARCHAR2(30) := 'getReadOnlyRegionName';
107
108 BEGIN
109
110 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
111
112 select form_code , form_version , type
113 into v_form_code, v_form_version, v_type
114 from pon_forms_sections where form_id = p_form_id;
115
116 print_debug_log(l_api_name, 'END- ReadOnlyRegionName = '||g_jrad_rgn_pkg_name || 'pon_'|| v_form_code || '_V' || to_char(v_form_version) || '_DSP_RG');
117
118 return g_jrad_rgn_pkg_name || 'pon_'|| v_form_code || '_V' || to_char(v_form_version) || '_DSP_RG';
119
120 END getReadOnlyRegionName;
121
122
123 /*======================================================================
124 FUNCTON: getFlexValuePVT PRIVATE
125 PARAMETERS:
126 COMMENT :
127 ======================================================================*/
128
129
130 FUNCTION getFlexValuePVT(p_field_code IN VARCHAR2, p_field_value IN VARCHAR2) RETURN VARCHAR2 IS
131
132 l_validation_type VARCHAR2(1);
133 l_ret_val VARCHAR2(2000);
134 l_vset_name PON_FIELDS.VALUE_SET_NAME%TYPE;
135 l_err_num NUMBER;
136 l_err_msg VARCHAR2(200);
137 l_api_name CONSTANT VARCHAR2(30) := 'getFlexValuePVT';
138 BEGIN
139
140 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||' p_field_value = '||p_field_value);
141
142 -- just in case something bad happens
143 l_ret_val := p_field_value;
144
145 SELECT fnd_flex_value_sets.VALIDATION_TYPE, fnd_flex_value_sets.flex_value_set_name
146 INTO l_validation_type, l_vset_name
147 FROM fnd_flex_value_sets, pon_fields
148 WHERE fnd_flex_value_sets.FLEX_VALUE_SET_NAME = pon_fields.VALUE_SET_NAME
149 AND pon_fields.FIELD_CODE = P_FIELD_CODE;
150
151 IF(l_validation_type = 'F') THEN
152 l_ret_val := PON_FORMS_UTIL_PVT.getFlexTblValue(p_field_code, p_field_value);
153 ELSIF ((l_validation_type = 'X') or (l_validation_type = 'I')) THEN
154 l_ret_val := PON_FORMS_UTIL_PVT.GetFLEXINDENDENTVALUE(l_vset_name, p_field_value);
155 END IF;
156
157 print_debug_log(l_api_name, 'END l_ret_val = '||l_ret_val);
158
159 return l_ret_val;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 l_err_num := SQLCODE;
164 l_err_msg := SUBSTR(SQLERRM, 1, 200);
165 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_field_value = '||p_field_value||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
166
167 RETURN P_FIELD_VALUE;
168 END getFlexValuePvt;
169
170 /*======================================================================
171 FUNCTON: getFlexValue PUBLIC
172 PARAMETERS:
173 COMMENT :
174 ======================================================================*/
175
176
177 FUNCTION getFlexValue(p_field_code IN VARCHAR2, p_mapping_column IN VARCHAR2, p_form_field_id IN NUMBER) RETURN VARCHAR2 IS
178
179 l_query_stmt VARCHAR2(1000);
180 l_field_value VARCHAR2(2000);
181 l_display_value VARCHAR2(2000);
182 l_err_num NUMBER;
183 l_err_msg VARCHAR2(200);
184 l_api_name CONSTANT VARCHAR2(30) := 'getFlexValue';
185
186 BEGIN
187
188 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||' p_mapping_column = '||p_mapping_column);
189
190 l_display_value := to_char(null);
191 l_query_stmt := 'select ' || upper(p_mapping_column) || ' from pon_form_field_values where form_field_value_id = :1' ;
192
193 EXECUTE IMMEDIATE l_query_stmt INTO l_field_value USING p_form_field_id;
194
195 --dbms_output.put_line(l_api_name || ' ' || l_field_value);
196
197 IF(nvl(l_field_value, 'xYz') <> 'xYz') THEN
198 l_display_value := getFlexValuePvt(p_field_code, l_field_value);
199 END IF;
200
201 print_debug_log(l_api_name, 'END l_display_value = '||l_display_value);
202
203 RETURN l_display_value;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207
208 l_err_num := SQLCODE;
209 l_err_msg := SUBSTR(SQLERRM, 1, 200);
210 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_mapping_column = '||p_mapping_column||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
211
212 RETURN L_FIELD_VALUE;
213 END getFlexValue;
214
215
216
217 /*======================================================================
218 FUNCTON: GetMappingColumn PUBLIC
219 PARAMETERS:
220 COMMENT :
221 ======================================================================*/
222 Procedure GetMappingColumn(p_datatype in Varchar2,
223 p_mapping_column in out NOCOPY Varchar2) is
224
225 l_api_name CONSTANT VARCHAR2(30) := 'GetMappingColumn';
226
227 Begin
228
229 print_debug_log(l_api_name, 'BEGIN- p_datatype = '||p_datatype||' p_mapping_column = '||p_mapping_column);
230
231 if p_datatype = 'TEXT' then
232
233 g_text_sequence_number := g_text_sequence_number + 1;
234 p_mapping_column := 'Textcol'||g_text_sequence_number;
235
236 elsif p_datatype = 'DATE' or p_datatype = 'DATETIME' then
237
238 g_date_sequence_number := g_date_sequence_number + 1;
239 p_mapping_column := 'Datecol'||g_date_sequence_number;
240
241 else
242
243 g_number_sequence_number := g_number_sequence_number + 1;
244 p_mapping_column := 'Numbercol'||g_number_sequence_number;
245
246 end if;
247
248 print_debug_log(l_api_name, 'END- p_datatype = '||p_datatype||' p_mapping_column = '||p_mapping_column);
249
250 end GetMappingColumn;
251
252
253
254 /*======================================================================
255 FUNCTON: InsertCompiledRow PRIVATE
256 PARAMETERS:
257 COMMENT :
258 ======================================================================*/
259 Procedure InsertCompiledRow( p_FORM_SECTION_FIELD_ID Number,
260 p_form_id Number,
261 p_type Varchar2,
262 p_field_code Varchar2,
263 p_INTERNAL_SEQUENCE_NUMBER Number,
264 p_MAPPING_FIELD_VALUE_COLUMN Varchar2,
265 p_REQUIRED Varchar2,
266 p_LEVEL1_SECTION_ID Number,
267 p_LEVEL2_SECTION_ID Number,
268 p_REPEATING_SECTION_ID Number,
269 p_DISPLAY_ON_MAIN_PAGE Varchar2,
270 p_ENABLED Varchar2) is
271
272 l_api_name CONSTANT VARCHAR2(30) := 'InsertCompiledRow';
273
274 begin
275
276 print_debug_log(l_api_name, 'BEGIN- p_FORM_SECTION_FIELD_ID = '||p_FORM_SECTION_FIELD_ID||'
277 p_form_id = '||p_form_id||'
278 p_type = '||p_type||'
279 p_field_code = '||p_field_code||'
280 p_INTERNAL_SEQUENCE_NUMBER = '||p_INTERNAL_SEQUENCE_NUMBER||'
281 p_REQUIRED = '||p_REQUIRED||'
282 p_MAPPING_FIELD_VALUE_COLUMN = '||p_MAPPING_FIELD_VALUE_COLUMN||'
283 p_LEVEL1_SECTION_ID = '||p_LEVEL1_SECTION_ID||'
284 p_LEVEL2_SECTION_ID = '||p_LEVEL2_SECTION_ID||'
285 p_REPEATING_SECTION_ID = '||p_REPEATING_SECTION_ID||'
286 p_DISPLAY_ON_MAIN_PAGE = '||p_DISPLAY_ON_MAIN_PAGE||'
287 p_ENABLED = '||p_ENABLED);
288
289 insert into pon_form_section_compiled
290 (FORM_SECTION_FIELD_ID,
291 FORM_ID,
292 TYPE,
293 FIELD_CODE,
294 INTERNAL_SEQUENCE_NUMBER,
295 MAPPING_FIELD_VALUE_COLUMN,
296 REQUIRED,
297 LEVEL1_SECTION_ID,
298 LEVEL2_SECTION_ID,
299 REPEATING_SECTION_ID,
300 DISPLAY_ON_MAIN_PAGE,
301 ENABLED,
302 CREATION_DATE,
303 CREATED_BY,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 LAST_UPDATE_LOGIN)
307 values
308 ( p_FORM_SECTION_FIELD_ID ,
309 p_form_id ,
310 p_type,
311 p_field_code ,
312 p_INTERNAL_SEQUENCE_NUMBER ,
313 p_MAPPING_FIELD_VALUE_COLUMN ,
314 p_REQUIRED ,
315 p_LEVEL1_SECTION_ID ,
316 p_LEVEL2_SECTION_ID ,
317 p_REPEATING_SECTION_ID ,
318 p_DISPLAY_ON_MAIN_PAGE,
319 p_ENABLED,
320 sysdate,
321 fnd_global.user_id,
322 sysdate,
323 fnd_global.user_id,
324 fnd_global.user_id
325 );
326
327 print_debug_log(l_api_name, 'END ');
328
329 end InsertCompiledRow;
330
331 PROCEDURE printClobOut(result IN CLOB) is
332 xmlstr varchar2(32767);
333 line varchar2(2000);
334 l_index Number;
335 begin
336 g_newline := fnd_global.newline();
337 xmlstr := dbms_lob.SUBSTR(result,32767);
338 loop
339 exit when xmlstr is null;
340 l_index := instr(xmlstr,g_newline);
341 line := substr(xmlstr,1,l_index-1);
342 --dbms_output.put_line(line);
343 xmlstr := substr(xmlstr,l_index+1);
344 end loop;
345 End;
346
347 PROCEDURE printLong(result IN Varchar2) is
348 xmlstr Varchar2(31500);
349 line varchar2(2000);
350 l_index Number;
351 begin
352 xmlstr := result;
353 g_newline := fnd_global.newline();
354 loop
355 exit when xmlstr is null;
356 l_index := instr(xmlstr,g_newline);
357 line := substr(xmlstr,1,l_index-1);
358 --dbms_output.put_line(line);
359 xmlstr := substr(xmlstr,l_index+1);
360 end loop;
361 End;
362
363
364 /*======================================================================
365 FUNCTON: Get_Freight PRIVATE
366 PARAMETERS:
367 COMMENT :
368 ======================================================================*/
369 Function Get_Freight(p_carrier_code IN Varchar2,
370 p_inventory_organization_id IN Number)
371 return Varchar2 is
372 rt_value Varchar2(80);
373 l_err_num NUMBER;
374 l_err_msg VARCHAR2(200);
375
376 l_api_name CONSTANT VARCHAR2(30) := 'Get_Freight';
377
378 Begin
379
380 print_debug_log(l_api_name, 'BEGIN- p_carrier_code = '||p_carrier_code||'
381 p_inventory_organization_id = '||p_inventory_organization_id);
382
383 select description
384 into rt_value
385 from org_freight_tl
386 where LANGUAGE = userenv('LANG')
387 and ORGANIZATION_ID = p_inventory_organization_id
388 and FREIGHT_CODE = p_carrier_code;
389
390 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
391
392 return rt_value;
393
394 exception when others then
395 l_err_num := SQLCODE;
396 l_err_msg := SUBSTR(SQLERRM, 1, 200);
397 print_error_log(l_api_name ,'EXCEPTION for p_carrier_code = '||p_carrier_code||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
398 return p_carrier_code;
399 End;
400
401
402
403 /*======================================================================
404 FUNCTON: GetSYSTEMDate PUBLIC
405 PARAMETERS:
406 COMMENT :
407 ======================================================================*/
408 Function GetSYSTEMDate(p_field_code in Varchar2,
409 p_id in Varchar2) return Date is
410 rt_date Date;
411 l_stmt Varchar2(250);
412 l_err_num NUMBER;
413 l_err_msg VARCHAR2(200);
414 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMDate';
415
416 begin
417
418 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
419 p_id = '||p_id);
420
421 if p_id=g_dummy_pk then
422
423 print_debug_log(l_api_name, 'END rt_date (g_dummy_pk)= '||sysdate);
424
425 return(sysdate);
426
427 end if;
428
429 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
430
431 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
432
433
434 if p_field_code = 'AWARD_DATE' then
435
436 select AWARD_BY_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
437
438 elsif p_field_code = 'CLOSE_DATE' then
439
440 select CLOSE_BIDDING_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
441
442 elsif p_field_code = 'OPEN_DATE' then
443
444 select OPEN_BIDDING_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
445
446 elsif p_field_code = 'PREVIEW_DATE' then
447
448 select VIEW_BY_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
449
450 end if;
451
452 print_debug_log(l_api_name, 'END rt_date = '||rt_date);
453
454 return rt_date;
455 exception when others then
456
457 l_err_num := SQLCODE;
458 l_err_msg := SUBSTR(SQLERRM, 1, 200);
459 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_id = '||p_id||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
460
461 return TO_DATE(NULL) ;
462 end;
463
464
465 /*======================================================================
466 FUNCTON: GetSYSTEMNumber PUBLIC
467 PARAMETERS:
468 COMMENT :
469 ======================================================================*/
470 Function GetSYSTEMNumber(p_field_code in Varchar2,
471 p_id in Varchar2) return Number is
472 rt_value Number;
473 l_stmt Varchar2(250);
474
475 l_err_num NUMBER;
476 l_err_msg VARCHAR2(200);
477 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMNumber';
478
479 begin
480
481 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
482 p_id = '||p_id);
483
484 if p_id=g_dummy_pk then
485
486 print_debug_log(l_api_name, 'END rt_value (g_dummy_num)= '||g_dummy_num);
487 return g_dummy_num;
488
489 end if;
490 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
491
492 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
493
494 if p_field_code = 'PAYMENT_TERMS' then
495
496 select PAYMENT_TERMS_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
497
498 elsif p_field_code = 'ORGANIZATION' then
499
500 select ORG_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
501
502 elsif p_field_code = 'AUCTION_HEADER_ID' then
503
504 select AUCTION_HEADER_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
505
506 end if;
507
508 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
509
510 return rt_value;
511
512 exception when others then
513
514 l_err_num := SQLCODE;
515 l_err_msg := SUBSTR(SQLERRM, 1, 200);
516 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_id = '||p_id||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
517 return TO_NUMBER(NULL) ;
518
519 end;
520
521
522 /*======================================================================
523 FUNCTON: GetSYSTEMChar PUBLIC
524 PARAMETERS:
525 COMMENT :
526 ======================================================================*/
527 Function GetSYSTEMChar(p_field_code in Varchar2,
528 p_id in Varchar2) return Varchar2 is
529 rt_value Varchar2(500);
530 rt_large_value Varchar2(4000);
531 l_stmt Varchar2(250);
532
533 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMChar';
534 l_err_num NUMBER;
535 l_err_msg VARCHAR2(200);
536 begin
537
538 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
539 p_id = '||p_id);
540
541 if p_id=g_dummy_pk then
542
543 print_debug_log(l_api_name, 'END rt_value (g_dummy_pk)= Dummy data');
544
545 return('Dummy data');
546
547 end if;
548
549 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
550
551 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
552
553 if p_field_code = 'ABSTRACT_STATUS' then
554
555 select ABSTRACT_STATUS into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
556
557 elsif p_field_code = 'AMENDMENT_DESCRIPTION' then
558
559 select AMENDMENT_DESCRIPTION into rt_large_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
560
561 elsif p_field_code = 'TITLE' then
562
563 select AUCTION_TITLE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
564
565 elsif p_field_code = 'STYLE' then
566
567 select BID_VISIBILITY_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
568
569 elsif p_field_code = 'CARRIER' then
570
571 select CARRIER_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
572
573 elsif p_field_code = 'NEGOTIATION_CURR' then
574
575 select CURRENCY_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
576
577 elsif p_field_code = 'NEGOTIATION_NUM' then
578
579 select DOCUMENT_NUMBER into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
580
581 elsif p_field_code = 'EVENT' then
582
583 select EVENT_TITLE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
584
585 elsif p_field_code = 'FOB' then
586
587 select FOB_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
588
589 elsif p_field_code = 'FREIGHT_TERMS' then
590
591 select FREIGHT_TERMS_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
592
593 elsif p_field_code = 'BUYER' then
594
595 select DOCUMENT_NUMBER into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
596
597 elsif p_field_code = 'PAYMENT_TERMS_NAME' then
598
599 select atvl_1.NAME
600 into rt_value
601 from
602 PON_AUCTION_HEADERS_ALL ah,
603 AP_TERMS_VL atvl_1
604 where
605 ah.auction_header_id = to_number(p_id)
606 and atvl_1.TERM_ID(+) = ah.PAYMENT_TERMS_ID;
607
608 elsif p_field_code = 'CARRIER_NAME' then
609
610 select
611 PON_FORMS_UTIL_PVT.GET_FREIGHT(ah.carrier_code, fsp.inventory_organization_id)
612 into rt_large_value
613 from pon_auction_headers_all ah,
614 financials_system_params_all fsp
615 where ah.auction_header_id = to_number(p_id)
616 and fsp.org_id(+) = ah.org_id;
617
618 elsif p_field_code = 'DISPLAY_PDF_FLAG' then
619
620 select
621 decode( nvl(ah.INCLUDE_PDF_IN_EXTERNAL_PAGE, 'N'), 'Y', 'SHOW_PDF', 'HIDE_PDF') DISPLAY_PDF_FLAG
622 into rt_value
623 from pon_auction_headers_all ah
624 where ah.auction_header_id = to_number(p_id);
625
626 elsif p_field_code = 'NEGOTIATION_TYPE' then
627
628 select doc.internal_name
629 into rt_value
630 from
631 pon_auction_headers_all ah,
632 PON_AUC_DOCTYPES doc
633 where
634 ah.auction_header_id = to_number(p_id)
635 and ah.DOCTYPE_ID = doc.DOCTYPE_ID;
636
637 elsif p_field_code = 'NEGOTIATION_TYPE_NAME' then
638
639 select doctl.NAME
640 into rt_value
641 from pon_auction_headers_all ah,
642 PON_AUC_DOCTYPES_TL doctl
643 where
644 ah.auction_header_id = to_number(p_id)
645 and ah.DOCTYPE_ID = doctl.DOCTYPE_ID
646 and doctl.LANGUAGE = userenv('LANG');
647
648 elsif p_field_code = 'STYLE_NAME' then
649
650 select
651 lookup_1.MEANING
652 into
653 rt_value
654 from pon_auction_headers_all ah,
655 FND_LOOKUP_VALUES lookup_1
656 where
657 ah.auction_header_id = to_number(p_id)
658 and lookup_1.LOOKUP_CODE(+) = ah.BID_VISIBILITY_CODE
659 and lookup_1.VIEW_APPLICATION_ID (+) = 0
660 and lookup_1.SECURITY_GROUP_ID (+) = 0
661 and lookup_1.LOOKUP_TYPE(+) = 'PON_BID_VISIBILITY_CODE'
662 and lookup_1.LANGUAGE(+) = userenv('LANG');
663
664 elsif p_field_code = 'FOB_NAME' then
665
666 select
667 lookup_2.MEANING
668 into rt_value
669 from pon_auction_headers_all ah,
670 FND_LOOKUP_VALUES lookup_2
671 where ah.auction_header_id = to_number(p_id)
672 and lookup_2.lookup_code(+) = ah.fob_code
673 and lookup_2.LOOKUP_TYPE(+) = 'FOB'
674 and lookup_2.LANGUAGE(+) = userenv('LANG')
675 and lookup_2.SECURITY_GROUP_ID (+) = 0
676 and lookup_2.VIEW_APPLICATION_ID(+) = 201;
677
678 elsif p_field_code = 'FREIGHT_TERMS_NAME' then
679
680 select
681 lookup_3.MEANING
682 into
683 rt_value
684 from pon_auction_headers_all ah,
685 FND_LOOKUP_VALUES lookup_3
686 where ah.auction_header_id = to_number(p_id)
687 and lookup_3.LOOKUP_CODE(+) = ah.FREIGHT_TERMS_CODE
688 and lookup_3.lookup_type ='FREIGHT TERMS'
689 and lookup_3.LANGUAGE(+) = userenv('LANG')
690 and lookup_3.SECURITY_GROUP_ID (+) = 0
691 and lookup_3.VIEW_APPLICATION_ID(+) = 201;
692
693 elsif p_field_code = 'ORGANIZATION_NAME' then
694
695 select
696 org.NAME
697 into
698 rt_value
699 from pon_auction_headers_all ah,
700 HR_ALL_ORGANIZATION_UNITS_TL org
701 where ah.auction_header_id = to_number(p_id)
702 and org.ORGANIZATION_ID = ah.ORG_ID
703 and org.LANGUAGE = userenv('LANG');
704 end if;
705
706 if (p_field_code = 'CARRIER_NAME' or p_field_code = 'AMENDMENT_DESCRIPTION') then
707 print_debug_log(l_api_name, 'END rt_large_value = '||rt_large_value);
708 return rt_large_value;
709 else
710 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
711 return rt_value;
712 end if;
713
714 exception when others then
715 l_err_num := SQLCODE;
716 l_err_msg := SUBSTR(SQLERRM, 1, 200);
717 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_id = '||p_id||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
718 return NULL ;
719 end;
720
721
722 /*======================================================================
723 FUNCTON: GetValSetQueryIdOrder PUBLIC
724 PARAMETERS:
725 COMMENT :
726 ======================================================================*/
727 Procedure GetValSetQueryIdOrder (
728 p_value_set_name IN VARCHAR2,
729 p_query_stmt OUT NOCOPY Varchar2,
730 p_orderby OUT NOCOPY Varchar2,
731 p_id_column_exists OUT NOCOPY Varchar2,
732 p_is_table_based OUT NOCOPY VARCHAR2,
733 p_error OUT NOCOPY Varchar2,
734 p_result OUT NOCOPY number
735 ) IS
736 l_value_set_type Varchar2(10);
737 l_err_num NUMBER;
738 l_err_msg VARCHAR2(200);
739 l_api_name CONSTANT VARCHAR2(30) := 'GetValSetQueryIdOrder';
740
741 Begin
742
743 p_result := 0;
744
745 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name);
746
747 select validation_type
748 into l_value_set_type
749 from fnd_flex_value_sets
750 where FLEX_VALUE_SET_NAME = p_value_set_name;
751
752 if (l_value_set_type = 'F') then
753 GetValSetTBLQuery (p_value_set_name =>p_value_set_name,
754 p_query_stmt => p_query_stmt,
755 p_orderby => p_orderby,
756 p_id_column_exists=> p_id_column_exists,
757 p_error => p_error,
758 p_result => p_result);
759 p_is_table_based := 'Y';
760 else
761 p_is_table_based := 'N';
762 end if;
763
764 print_debug_log(l_api_name, 'END p_query_stmt = '||p_query_stmt ||'
765 p_orderby = '|| p_orderby ||'
766 p_id_column_exists = '|| p_id_column_exists ||'
767 p_is_table_based = '|| p_is_table_based ||'
768 p_error = '|| p_error ||'
769 p_result = '|| p_result);
770
771 EXCEPTION when others then
772 p_result := 1;
773 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
774 l_err_num := SQLCODE;
775 l_err_msg := SUBSTR(SQLERRM, 1, 200);
776 print_error_log(l_api_name ,'EXCEPTION for GetValSetQueryIdOrder p_value_set_name= '|| p_value_set_name ||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
777 End GetValSetQueryIdOrder;
778
779
780 /*======================================================================
781 FUNCTON: GetValSetTBLQuery PRIVATE
782 PARAMETERS:
783 COMMENT :
784 ======================================================================*/
785 Procedure GetValSetTBLQuery(
786 p_value_set_name in Varchar2,
787 p_query_stmt in out NOCOPY Varchar2,
788 p_orderby in out NOCOPY Varchar2,
789 p_id_column_exists OUT NOCOPY Varchar2,
790 p_error OUT NOCOPY VARCHAR2,
791 p_result OUT NOCOPY number -- 0: Success, 1: failure
792 ) IS
793 l_query_stmt Varchar2(4000);
794 l_success Number;
795 l_id_column_name Varchar2(240) := NULL;
796 l_value_column_name Varchar2(240) := NULL;
797 l_mapping_code Varchar2(80);
798 l_from_index Number;
799 l_orderby_index Number;
800 l_where_index NUMBER;
801 l_where_clause VARCHAR2(4000);
802
803 l_api_name CONSTANT VARCHAR2(30) := 'GetValSetTBLQuery';
804 PON_INVALID_VALSET_EXCEPTION EXCEPTION;
805
806 Begin
807
808 p_result := 0;
809
810 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name||'
811 p_query_stmt = '||p_query_stmt||'
812 p_orderby = '||p_orderby);
813
814 select VALUE_COLUMN_NAME, ID_COLUMN_NAME
815 into l_value_column_name,l_id_column_name
816 from fnd_flex_validation_tables tbl, fnd_flex_value_sets val
817 where tbl.flex_value_set_id =val.flex_value_set_id
818 and val.flex_value_set_name = p_value_set_name;
819
820 print_debug_log(l_api_name, ' l_value_column_name = '||l_value_column_name||'
821 l_id_column_name = '||l_id_column_name);
822
823 fnd_flex_val_api.get_table_vset_select( p_value_set_name =>p_value_set_name,
824 x_select => l_query_stmt,
825 x_mapping_code => l_mapping_code,
826 x_success => l_success);
827
828 print_debug_log(l_api_name, ' l_query_stmt = '||l_query_stmt);
829
830 l_query_stmt := upper(l_query_stmt);
831 l_from_index := instr(l_query_stmt,'FROM');
832 l_where_index := instr (l_query_stmt, 'WHERE');
833 l_orderby_index := instr(l_query_stmt,'ORDER BY') ;
834
835 print_debug_log(l_api_name, ' l_query_stmt = '||l_query_stmt||' l_from_index = '||l_from_index||' l_orderby_index = '||l_orderby_index);
836
837 if l_where_index > l_from_index then
838 if (l_orderby_index > l_where_index) then
839 l_where_clause := substr (l_query_stmt, l_where_index, l_orderby_index - l_where_index -1);
840 else
841 l_where_clause := substr (l_query_stmt, l_where_index);
842 end if;
843
844 print_debug_log(l_api_name, 'index of $ = ' || instr (l_where_clause, '$'));
845
846 if (instr (l_where_clause, '$') <> 0 OR instr (l_where_index, ':') <> 0) then
847 RAISE PON_INVALID_VALSET_EXCEPTION;
848 end if;
849 end if;
850
851 if L_ID_COLUMN_NAME is not null then
852 p_id_column_exists := 'Y';
853 p_query_stmt := 'Select ' || L_ID_COLUMN_NAME || ' AS ID_COLUMN ,'
854 || g_newline || substr(l_value_column_name,1,500) || ' AS VALUE_COLUMN ';
855 else
856 p_id_column_exists := 'N';
857 -- if the ID column does not exist, just return the same column twice
858 p_query_stmt := 'Select ' || l_value_column_name || ' AS ID_COLUMN ,'
859 || g_newline || l_value_column_name || ' AS VALUE_COLUMN ';
860 end if;
861
862 print_debug_log(l_api_name, 'p_id_column_exists = '||p_id_column_exists ||' p_query_stmt = '||p_query_stmt);
863
864 if l_orderby_index >l_from_index then
865 p_query_stmt := p_query_stmt || g_newline ||
866 substr(l_query_stmt,l_from_index,l_orderby_index-l_from_index - 1);
867 p_orderby := substr(l_query_stmt,l_orderby_index);
868 else
869 p_query_stmt := p_query_stmt || g_newline ||
870 substr(l_query_stmt,l_from_index);
871 p_orderby := NULL;
872 end if;
873
874 print_debug_log(l_api_name, ' END =
875 p_query_stmt = '|| p_query_stmt ||'
876 p_orderby = '|| p_orderby ||'
877 p_id_column_exists = '|| p_id_column_exists ||'
878 p_error = '|| p_error ||'
879 p_result = '|| p_result);
880
881 EXCEPTION when PON_INVALID_VALSET_EXCEPTION then
882 p_result := 1;
883 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
884 End GetValSetTBLQuery;
885
886
887
888 /*======================================================================
889 FUNCTON: GetFLEXINDENDENTVALUE PUBLIC
890 PARAMETERS:
891 COMMENT :
892 ======================================================================*/
893 Function GetFLEXINDENDENTVALUE(p_value_set_name in varchar2,
894 p_id_value in Varchar2) Return Varchar2 IS
895 l_err_num NUMBER;
896 l_err_msg VARCHAR2(200);
897 rt_value Varchar2(150);
898 cursor get_value is
899 select tl.FLEX_VALUE_MEANING
900 from fnd_flex_values_tl tl,
901 fnd_flex_values val,
902 fnd_flex_value_sets valset
903 where tl.flex_value_id = val.flex_value_id
904 and tl.language = USERENV('LANG')
905 and val.flex_value_set_id = valset.flex_value_set_id
906 and valset.flex_value_set_name = p_value_set_name
907 and val.FLEX_VALUE = p_id_value;
908
909 l_api_name CONSTANT VARCHAR2(30) := 'GetFLEXINDENDENTVALUE';
910
911 Begin
912
913 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name||'
914 p_id_value = '||p_id_value);
915
916 if p_id_value = g_dummy_char then
917 rt_value := p_id_value;
918 else
919 open get_value;
920 fetch get_value into rt_value;
921 close get_value;
922 end if;
923
924 print_debug_log(l_api_name, ' END rt_value = '|| rt_value );
925
926 return rt_value;
927
928 exception when others then
929 l_err_num := SQLCODE;
930 l_err_msg := SUBSTR(SQLERRM, 1, 200);
931 print_error_log(l_api_name ,'EXCEPTION for p_value_set_name = '||p_value_set_name||' p_id_value = '||p_id_value||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
932
933 return p_id_value;
934
935 End GetFLEXINDENDENTVALUE;
936
937
938
939 /*======================================================================
940 FUNCTON: GetFLEXTBLVALUE PUBLIC
941 PARAMETERS:
942 COMMENT :
943 ======================================================================*/
944 Function GetFLEXTBLVALUE(p_field_code in varchar2,
945 p_id_value in Varchar2) Return Varchar2 IS
946
947 l_err_num NUMBER;
948 l_err_msg VARCHAR2(200);
949 rt_value Varchar2(500);
950 l_query_stmt Varchar2(4000);
951 l_success Number;
952 l_value_column_name VARCHAR2(240) := NULL;
953 l_id_column_name Varchar2(240) := NULL;
954 l_id_column_exists Varchar2(10) := NULL;
955 l_mapping_code Varchar2(80);
956 l_id_value Varchar2(500);
957
958 l_api_name CONSTANT VARCHAR2(30) := 'GetFLEXTBLVALUE';
959
960 Begin
961
962 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
963 p_id_value = '||p_id_value);
964
965 if p_id_value = g_dummy_char then
966 rt_value := p_id_value;
967 else
968 select tbl.ID_COLUMN_NAME, tbl.VALUE_COLUMN_NAME, fld.VALUE_SET_QUERY, fld.VALUE_SET_ID_EXISTS
969 into l_id_column_name, l_value_column_name, l_query_stmt, l_id_column_exists
970 from fnd_flex_validation_tables tbl,
971 fnd_flex_value_sets val,
972 pon_fields fld
973 where tbl.flex_value_set_id = val.flex_value_set_id
974 and val.flex_value_set_name = fld.value_set_name
975 and fld.field_code = p_field_code;
976
977 print_debug_log(l_api_name, 'l_id_column_name = '|| l_id_column_name ||'
978 l_query_stmt = '||l_query_stmt ||'
979 l_id_column_exists = '||l_id_column_exists);
980
981 if L_ID_COLUMN_NAME is null then
982 rt_value := p_id_value;
983 elsif L_ID_COLUMN_NAME is not null and l_id_column_exists ='N' then
984 rt_value := p_id_value;
985 else
986 if instr(l_query_stmt,'WHERE') >0 then
987 l_query_stmt := l_query_stmt || g_newline || ' AND ' || l_value_column_name || ' = :1';
988 else
989 l_query_stmt := l_query_stmt || g_newline || ' WHERE ' || l_value_column_name || ' = :1';
990 end if;
991
992 l_query_stmt := l_query_stmt || ' AND ROWNUM = 1';
993
994 print_debug_log(l_api_name, 'l_query_stmt = '|| l_query_stmt );
995
996 EXECUTE IMMEDIATE l_query_stmt INTO rt_value, l_id_value USING p_id_value;
997 end if;
998 end if;
999
1000 print_debug_log(l_api_name, ' END rt_value = '|| rt_value );
1001
1002 return rt_value;
1003
1004 exception when others then
1005
1006 l_err_num := SQLCODE;
1007 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1008 print_error_log(l_api_name ,'EXCEPTION for p_field_code = '||p_field_code||' p_id_value = '||p_id_value||' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1009
1010 return p_id_value;
1011
1012 End GetFLEXTBLVALUE;
1013
1014
1015 /*======================================================================
1016 PROCEDURE: ADDSTMTVALUESET PUBLIC
1017 PARAMETERS:
1018 COMMENT :
1019 ======================================================================*/
1020 Procedure ADDSTMTVALUESET(p_field_code in Varchar2,
1021 p_value_alias in Varchar2,
1022 p_value_column in Varchar2,
1023 p_value_set_name in Varchar2,
1024 p_query_stmt in out nocopy Varchar2,
1025 p_error IN OUT NOCOPY VARCHAR2,
1026 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1027 ) IS
1028 l_err_num NUMBER;
1029 l_err_msg VARCHAR2(200);
1030 l_value_set_type Varchar2(10);
1031 l_id_column_name Varchar2(240);
1032 l_value_column_name Varchar2(240);
1033
1034 l_api_name CONSTANT VARCHAR2(30) := 'ADDSTMTVALUESET';
1035
1036 Begin
1037
1038 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
1039 p_value_alias = '||p_value_alias||'
1040 p_value_column = '||p_value_column||'
1041 p_value_set_name = '||p_value_set_name||'
1042 p_query_stmt = '||p_query_stmt);
1043
1044 select validation_type
1045 into l_value_set_type
1046 from fnd_flex_value_sets
1047 where FLEX_VALUE_SET_NAME = p_value_set_name;
1048
1049 print_debug_log(l_api_name, 'l_value_set_type = '||l_value_set_type);
1050
1051 if l_value_set_type in ('I','X') then
1052 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias ||'.' ||p_value_column || ' AS ' || p_field_code
1053 || g_newline || ',PON_FORMS_UTIL_PVT.GetFLEXINDENDENTVALUE('''|| p_value_set_name || ''','
1054 || g_newline || ' ' || p_value_alias ||'.'|| p_value_column || ') AS ' || p_field_code || '_NM';
1055 /* ==============================================================================
1056 p_query_stmt := p_query_stmt || g_newline || ',xmlelement("FIELDVALUE"'
1057 || ' ,xmlattributes(' || p_value_alias ||'.' ||p_value_column || ' AS "CODE"'
1058 || g_newline || ' ,PON_FORMS_UTIL_PVT.GetFLEXINDENDENTVALUE('''
1059 || p_value_set_name || ''',' || p_value_alias ||'.'
1060 || p_value_column || ') AS "DESCRIPTION"'
1061 || ')) AS ' || p_field_code;
1062 ============================================================================== */
1063 elsif l_value_set_type = 'F' then
1064
1065 select VALUE_COLUMN_NAME, ID_COLUMN_NAME
1066 into l_value_column_name,l_id_column_name
1067 from fnd_flex_validation_tables tbl, fnd_flex_value_sets val
1068 where tbl.flex_value_set_id =val.flex_value_set_id
1069 and val.flex_value_set_name = p_value_set_name;
1070
1071 print_debug_log(l_api_name, 'l_value_column_name = '||l_value_column_name ||' l_id_column_name = '||l_id_column_name);
1072
1073 if l_id_column_name is null then -- this is a single column value set
1074
1075 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.'
1076 || p_value_column || ' AS ' || p_field_code;
1077
1078 else
1079
1080 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias ||'.' ||p_value_column || ' AS ' || p_field_code
1081 || g_newline || ',PON_FORMS_UTIL_PVT.GetFLEXTBLVALUE('''|| p_field_code || ''','
1082 || g_newline || ' ' || p_value_alias ||'.'|| p_value_column || ') AS ' || p_field_code || '_NM';
1083 /* ==============================================================================
1084 p_query_stmt := p_query_stmt || g_newline || ',xmlelement("FIELDVALUE"'
1085 || ' ,xmlattributes(' || p_value_alias ||'.' ||p_value_column || ' AS "CODE"'
1086 || g_newline || ' ,PON_FORMS_UTIL_PVT.GetFLEXTBLVALUE('''
1087 || p_field_code || ''',' || p_value_alias ||'.' ||p_value_column
1088 || ') AS "DESCRIPTION"'
1089 || ')) AS ' || p_field_code;
1090 ============================================================================== */
1091
1092 end if;
1093
1094 else
1095
1096 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1097 p_value_column || ' AS ' || p_field_code;
1098
1099 end if;
1100
1101 print_debug_log(l_api_name, 'END- p_query_stmt = '||p_query_stmt);
1102
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 p_result := 1;
1106 l_err_num := SQLCODE;
1107 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1108 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1109 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
1110
1111 End ADDSTMTVALUESET;
1112
1113
1114
1115 /*======================================================================
1116 PROCEDURE: ADDSTMTFIELD PUBLIC
1117 PARAMETERS:
1118 COMMENT :
1119 ======================================================================*/
1120 Procedure ADDSTMTFIELD(p_field_code in Varchar2,
1121 p_datatype in Varchar2,
1122 p_value_alias in Varchar2,
1123 p_value_column in Varchar2,
1124 p_value_set_name in Varchar2,
1125 p_query_stmt in out nocopy Varchar2,
1126 p_error IN OUT NOCOPY VARCHAR2,
1127 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1128 ) IS
1129
1130 l_err_num NUMBER;
1131 l_err_msg VARCHAR2(200);
1132 l_date_value_column Varchar2(60);
1133
1134 l_api_name CONSTANT VARCHAR2(30) := 'ADDSTMTFIELD';
1135
1136 Begin
1137
1138 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
1139 p_datatype = '||p_datatype||'
1140 p_value_alias = '||p_value_alias||'
1141 p_value_column = '||p_value_column||'
1142 p_value_set_name = '||p_value_set_name||'
1143 p_query_stmt = '||p_query_stmt);
1144
1145 if p_datatype = 'NUMBER' then
1146
1147 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1148 p_value_column || ' AS ' || p_field_code;
1149 elsif p_datatype = 'AMOUNT' then
1150
1151 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1152 p_value_column || ' AS ' || p_field_code;
1153
1154 elsif p_datatype = 'DATE' then
1155
1156 l_date_value_column := p_value_alias || '.' ||p_value_column;
1157
1158 p_query_stmt := p_query_stmt || g_newline || ',' || l_date_value_column || ' AS ' || p_field_code;
1159
1160 p_query_stmt := p_query_stmt || g_newline || ',cursor (select to_char(' || l_date_value_column || ',''YYYY'') AS "YYYY"'
1161 || g_newline || ' ,to_char(' || l_date_value_column || ',''MM'') AS "MM"'
1162 || g_newline || ' ,to_char(' || l_date_value_column || ',''DD'') AS "DD"'
1163 || g_newline || ' from dual) AS ' || p_field_code ||'_NM';
1164
1165
1166 elsif p_datatype = 'DATETIME' then
1167 l_date_value_column := p_value_alias || '.' ||p_value_column;
1168
1169 p_query_stmt := p_query_stmt || g_newline || ',' || l_date_value_column || ' AS ' || p_field_code;
1170
1171 p_query_stmt := p_query_stmt || g_newline || ',cursor (select to_char(' || l_date_value_column || ',''YYYY'') AS "YYYY"'
1172 || g_newline || ' ,to_char(' || l_date_value_column || ',''MM'') AS "MM"'
1173 || g_newline || ' ,to_char(' || l_date_value_column || ',''DD'') AS "DD"'
1174 || g_newline || ' ,to_char(' || l_date_value_column || ',''HH'') AS "HH"'
1175 || g_newline || ' ,to_char(' || l_date_value_column || ',''MI'') AS "MI"'
1176 || g_newline || ' ,to_char(' || l_date_value_column || ',''SS'') AS "SS"'
1177 || g_newline || ' from dual) AS ' || p_field_code ||'_NM';
1178 elsif p_datatype = 'TEXT' then
1179
1180 if p_value_set_name is null then
1181
1182 print_debug_log(l_api_name, ' p_field_code = '||p_field_code||' value set name is null');
1183
1184 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1185 p_value_column || ' AS ' || p_field_code;
1186
1187 else
1188
1189 print_debug_log(l_api_name, ' p_field_code = '||p_field_code||' calling ADDSTMTVALUESET');
1190
1191 ADDSTMTVALUESET(p_field_code => p_field_code,
1192 p_value_alias =>p_value_alias,
1193 p_value_column => p_value_column,
1194 p_value_set_name =>p_value_set_name,
1195 p_query_stmt => p_query_stmt,
1196 p_error => p_error,
1197 p_result => p_result);
1198
1199 if p_result = 1 then
1200 return;
1201 end if;
1202
1203 end if;
1204
1205 end if;
1206
1207 print_debug_log(l_api_name, 'END p_query_stmt = '||p_query_stmt);
1208
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 p_result := 1;
1212 l_err_num := SQLCODE;
1213 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1214 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1215 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_UNABLE_TO_ADD_FIELD');
1216
1217 end ADDSTMTFIELD;
1218
1219
1220 /*======================================================================
1221 PROCEDURE: ADDVIEWFORSECTION PUBLIC
1222 PARAMETERS:
1223 COMMENT :
1224 ======================================================================*/
1225 Procedure ADDVIEWFORSECTION(
1226 p_form_id in Number,
1227 p_section_id in Number,
1228 p_LEVEL1_SECTION_ID in Number,
1229 p_LEVEL2_SECTION_ID in Number,
1230 p_section_code in Varchar2,
1231 p_parent_alias in Varchar2,
1232 p_parent_fk_id in Number,
1233 p_query_stmt in out nocopy Varchar2,
1234 p_error IN OUT NOCOPY VARCHAR2,
1235 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1236 ) is
1237
1238 l_value_alias Varchar2(30);
1239
1240 Cursor Section_field is
1241 select rs.form_code repeating_section_code,
1242 ff.TYPE,
1243 ff.FIELD_CODE,
1244 ff.LEVEL1_SECTION_ID,
1245 ff.LEVEL2_SECTION_ID,
1246 ff.repeating_section_id repeating_section_id,
1247 ff.MAPPING_FIELD_VALUE_COLUMN,
1248 f.datatype,
1249 f.value_set_name,
1250 valset.flex_value_set_id value_set_id,
1251 valset.VALIDATION_TYPE
1252 from pon_form_section_compiled ff,
1253 pon_forms_sections rs,
1254 pon_fields f,
1255 fnd_flex_value_sets valset
1256 where ff.form_id = p_section_id
1257 and rs.form_id(+) = ff.repeating_section_id
1258 and ff.enabled ='Y'
1259 and f.field_code(+) = ff.field_code
1260 and f.value_set_name = valset.flex_value_set_name(+)
1261 order by INTERNAL_SEQUENCE_NUMBER;
1262 l_schema_pk_id Number;
1263
1264 l_api_name CONSTANT VARCHAR2(30) := 'ADDVIEWFORSECTION';
1265
1266 Begin
1267
1268 p_result := 0;
1269
1270 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1271 p_section_id = '||p_section_id||'
1272 p_LEVEL1_SECTION_ID = '||p_LEVEL1_SECTION_ID||'
1273 p_LEVEL2_SECTION_ID = '||p_LEVEL2_SECTION_ID||'
1274 p_section_code = '||p_section_code||'
1275 p_parent_alias = '||p_parent_alias||'
1276 p_parent_fk_id = '||p_parent_fk_id||'
1277 g_mode = '||g_mode);
1278
1279 l_value_alias := 'V_'|| to_char(g_alias_counter);
1280 if g_mode = 'SCHEMA' then
1281 l_schema_pk_id := g_alias_counter * -1;
1282 CreateDummyRowForXML(
1283 p_value_pk_id => l_schema_pk_id,
1284 p_form_id => p_form_id,
1285 p_section_id => p_section_id,
1286 p_parent_fk_id => p_parent_fk_id,
1287 p_level1_section_id => p_level1_section_id,
1288 p_level2_section_id => p_level2_section_id,
1289 p_error => p_error,
1290 p_result => p_result);
1291
1292 if p_result = 1 then
1293 return;
1294 end if;
1295 end if;
1296
1297 g_alias_counter := g_alias_counter + 1;
1298
1299 p_query_stmt := p_query_stmt || g_newline || ',Cursor (SELECT ' || l_value_alias || '.' || 'FORM_FIELD_VALUE_ID AS SECTION_PK_ID,'
1300 || g_newline || l_value_alias || '.' || 'PARENT_FIELD_VALUES_FK AS PARENT_FK_ID' ;
1301
1302 print_debug_log(l_api_name, ' p_query_stmt = '||p_query_stmt);
1303
1304 for r1 in Section_field loop
1305
1306 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||'
1307 r1.field_code = '||r1.field_code||'
1308 r1.repeating_section_id = '||r1.repeating_section_id);
1309
1310 if r1.field_code is not null then
1311
1312 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||'
1313 ADDSTMTFIELD : r1.field_code = '||r1.field_code);
1314
1315 ADDSTMTFIELD(p_field_code => r1.field_code,
1316 p_datatype => r1.datatype,
1317 p_value_alias => l_value_alias,
1318 p_value_column => r1.MAPPING_FIELD_VALUE_COLUMN,
1319 p_value_set_name => r1.value_set_name,
1320 p_query_stmt=> p_query_stmt,
1321 p_error => p_error,
1322 p_result => p_result);
1323
1324 if p_result = 1 then
1325 return;
1326 end if;
1327
1328 elsif r1.repeating_section_id is not null then
1329 -- recursively call the ADDVIEWFORSECTION API
1330 ADDVIEWFORSECTION( p_form_id => p_form_id,
1331 p_section_id => r1.repeating_section_id,
1332 p_LEVEL1_SECTION_ID => r1.LEVEL1_SECTION_ID,
1333 p_LEVEL2_SECTION_ID => r1.LEVEL2_SECTION_ID,
1334 p_section_code => r1.repeating_section_code,
1335 p_parent_alias => l_value_alias,
1336 p_parent_fk_id => l_schema_pk_id,
1337 p_query_stmt=> p_query_stmt,
1338 p_error => p_error,
1339 p_result => p_result);
1340
1341 if p_result = 1 then
1342 return;
1343 end if;
1344 end if;
1345 end loop;
1346
1347 p_query_stmt := p_query_stmt || ' from pon_form_field_values ' || l_value_alias
1348 || g_newline || ' where ' || l_value_alias || '.PARENT_FIELD_VALUES_FK=' ||p_parent_alias||'.' ||'FORM_FIELD_VALUE_ID'
1349 || g_newline || ' and ' || l_value_alias || '.section_id=' || to_char(p_section_id)
1350 || g_newline || ' and nvl(' || l_value_alias || '.LEVEL1_SECTION_ID,-1)= ' || to_char(nvl(p_LEVEL1_SECTION_ID,-1))
1351 || g_newline || ' and nvl(' || l_value_alias || '.LEVEL2_SECTION_ID,-1)= ' || to_char(nvl(p_LEVEL2_SECTION_ID,-1))
1352 || g_newline || ' order by ' || l_value_alias || '.FORM_FIELD_VALUE_ID'
1353 || g_newline || ' ) AS ' || p_Section_code;
1354
1355 print_debug_log(l_api_name, ' END p_query_stmt = '||p_query_stmt);
1356
1357 END ADDVIEWFORSECTION;
1358
1359
1360 /*======================================================================
1361 PROCEDURE: GENERATE_XMLQUERY PUBLIC
1362 PARAMETERS:
1363 COMMENT :
1364 ======================================================================*/
1365 Procedure GENERATE_XMLQUERY (p_form_id in Number, -- top form
1366 p_query_stmt in out NOCOPY Varchar2,
1367 p_error IN OUT NOCOPY VARCHAR2,
1368 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1369 ) IS
1370
1371 l_flex_val_alias Varchar2(30);
1372 l_flex_val_tl_alias Varchar2(30);
1373 l_value_column_size number;
1374 l_id_column_size number;
1375 l_tablebasedvalset_query Varchar2(2000);
1376 l_success NUMBER;
1377 L_ID_COLUMN_exists varchar2(10);
1378 l_value_alias Varchar2(30);
1379 l_prev_level1section_code Varchar2(30) := NULL;
1380 l_prev_section_code Varchar2(30) := NULL;
1381 l_form_code Varchar2(30);
1382 l_schema_pk_id Number;
1383
1384 cursor l_form_entry_cursor is
1385 select ff.TYPE,
1386 ts.form_code LEVEL1_SECTION_CODE,
1387 isec.form_code LEVEL2_SECTION_code,
1388 rs.form_code repeating_section_code,
1389 ff.LEVEL1_SECTION_ID LEVEL1_SECTION_ID,
1390 ff.LEVEL2_SECTION_ID LEVEL2_SECTION_ID,
1391 ff.repeating_section_id repeating_section_id,
1392 ff.FIELD_CODE,
1393 ff.MAPPING_FIELD_VALUE_COLUMN,
1394 f.datatype,
1395 f.value_set_name,
1396 f.system_flag,
1397 f.SYSTEM_FIELD_LOV_FLAG,
1398 valset.flex_value_set_id value_set_id,
1399 valset.VALIDATION_TYPE
1400 from pon_form_section_compiled ff,
1401 PON_FORMS_SECTIONS ts,
1402 PON_FORMS_SECTIONS isec,
1403 PON_FORMS_SECTIONS rs,
1404 pon_fields f,
1405 fnd_flex_value_sets valset
1406 where ts.form_id(+) = ff.LEVEL1_SECTION_ID
1407 and isec.form_id(+) = ff.LEVEL2_SECTION_ID
1408 and rs.form_id(+) = ff.repeating_section_id
1409 and ff.form_id = p_form_id
1410 and ff.enabled ='Y'
1411 and ff.field_code =f.field_code(+)
1412 and f.value_set_name = valset.flex_value_set_name(+)
1413 order by INTERNAL_SEQUENCE_NUMBER;
1414
1415 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XMLQUERY';
1416
1417 begin
1418
1419 p_result := 0;
1420
1421 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1422 p_query_stmt = '||p_query_stmt||'
1423 g_mode = '||g_mode);
1424
1425 g_newline := fnd_global.newline();
1426 g_alias_counter :=1;
1427 l_value_alias := 'V_'|| to_char(g_alias_counter);
1428
1429 print_debug_log(l_api_name, 'l_value_alias = '||l_value_alias||' g_mode = '||g_mode);
1430
1431 if g_mode = 'SCHEMA' then
1432
1433 l_schema_pk_id := g_alias_counter * -1;
1434
1435 print_debug_log(l_api_name, 'l_schema_pk_id = '||l_schema_pk_id);
1436
1437 CreateDummyRowForXML(
1438 p_value_pk_id => l_schema_pk_id,
1439 p_form_id => p_form_id,
1440 p_section_id =>-1,
1441 p_parent_fk_id => NULL,
1442 p_level1_section_id => NULL,
1443 p_level2_section_id => NULL,
1444 p_error => p_error,
1445 p_result => p_result);
1446
1447 if p_result = 1 then
1448 return;
1449 end if;
1450
1451 print_debug_log(l_api_name, 'Inserted Dummy Row l_schema_pk_id = '||l_schema_pk_id||' p_form_id = '||p_form_id||' p_section_id = -1 p_parent_fk_id = NULL');
1452
1453 end if;
1454
1455 g_alias_counter := g_alias_counter + 1;
1456
1457 select form_code
1458 into l_form_code
1459 from PON_FORMS_SECTIONS
1460 where form_id = p_form_id;
1461
1462 print_debug_log(l_api_name, 'l_form_code = '||l_form_code||' g_alias_counter = '||g_alias_counter);
1463
1464 --
1465 p_query_stmt := 'SELECT ' || l_value_alias || '.' || 'FORM_FIELD_VALUE_ID AS FORM_PK_VALUE,'
1466 || g_newline || l_value_alias || '.' || 'OWNING_ENTITY_CODE,'
1467 || g_newline || l_value_alias || '.' || 'ENTITY_PK1';
1468
1469 print_debug_log(l_api_name, 'p_query_stmt = '|| p_query_stmt);
1470
1471 for r1 in l_form_entry_cursor loop
1472
1473 print_debug_log(l_api_name, 'r1.TYPE = '||r1.TYPE ||'
1474 r1.LEVEL1_SECTION_CODE = '||r1.LEVEL1_SECTION_CODE ||'
1475 r1.LEVEL2_SECTION_code = '||r1.LEVEL2_SECTION_code ||'
1476 r1.repeating_section_code = '||r1.repeating_section_code ||'
1477 r1.LEVEL1_SECTION_ID = '||r1.LEVEL1_SECTION_ID ||'
1478 r1.LEVEL2_SECTION_ID = '||r1.LEVEL2_SECTION_ID ||'
1479 r1.repeating_section_id = '||r1.repeating_section_id ||'
1480 r1.FIELD_CODE = '||r1.FIELD_CODE ||'
1481 r1.MAPPING_FIELD_VALUE_COLUMN = '||r1.MAPPING_FIELD_VALUE_COLUMN ||'
1482 r1.datatype = '||r1.datatype ||'
1483 r1.value_set_name = '||r1.value_set_name ||'
1484 r1.system_flag = '||r1.system_flag ||'
1485 r1.SYSTEM_FIELD_LOV_FLAG = '||r1.SYSTEM_FIELD_LOV_FLAG ||'
1486 r1.value_set_id = '||r1.value_set_id ||'
1487 r1.VALIDATION_TYPE = '||r1.VALIDATION_TYPE||'
1488 l_prev_level1section_code = '||l_prev_level1section_code ||'
1489 l_prev_section_code = '||l_prev_section_code);
1490
1491
1492 if nvl(r1.LEVEL1_SECTION_CODE,'#x#') <> nvl(l_prev_level1section_code,'#x#') then
1493
1494 if nvl(r1.LEVEL2_SECTION_code,'#x#') <> nvl(l_prev_section_code,'#x#') then
1495
1496 if l_prev_section_code is not null then
1497
1498 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1499
1500 end if;
1501
1502 l_prev_section_code := r1.LEVEL2_SECTION_code;
1503
1504 end if;
1505
1506 if l_prev_level1section_code is not null then
1507 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_level1section_code || ' ';
1508 end if;
1509
1510 if r1.LEVEL1_SECTION_CODE is not null then
1511 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1512 end if;
1513
1514 if r1.LEVEL2_SECTION_code is not null then
1515 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1516 end if;
1517
1518 l_prev_level1section_code := r1.LEVEL1_SECTION_CODE;
1519
1520 print_debug_log(l_api_name, 'First if : p_query_stmt = '||p_query_stmt);
1521
1522 end if;
1523
1524 if nvl(r1.LEVEL2_SECTION_code,'#x#') <> nvl(l_prev_section_code,'#x#') then
1525
1526 print_debug_log(l_api_name, 'r1.LEVEL2_SECTION_code = '||r1.LEVEL2_SECTION_code||'
1527 l_prev_section_code = '|| l_prev_section_code);
1528
1529 if l_prev_section_code is not null then
1530
1531 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1532
1533 end if;
1534
1535 l_prev_section_code := r1.LEVEL2_SECTION_code;
1536
1537 if r1.LEVEL2_SECTION_code is not null then
1538
1539 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1540
1541 end if;
1542
1543 print_debug_log(l_api_name, 'Second if : p_query_stmt = '||p_query_stmt);
1544 end if;
1545
1546
1547
1548 if r1.repeating_section_code is not null then
1549
1550 print_debug_log(l_api_name, ' Calling ADDVIEWFORSECTION for p_form_id = '|| p_form_id ||'
1551 r1.repeating_section_id = '|| r1.repeating_section_id ||'
1552 r1.LEVEL1_SECTION_ID = '||r1.LEVEL1_SECTION_ID ||'
1553 r1.LEVEL2_SECTION_ID = '|| r1.LEVEL2_SECTION_ID ||'
1554 r1.repeating_section_code = '||r1.repeating_section_code );
1555
1556 ADDVIEWFORSECTION(p_form_id => p_form_id,
1557 p_section_id => r1.repeating_section_id,
1558 p_LEVEL1_SECTION_ID => r1.LEVEL1_SECTION_ID,
1559 p_LEVEL2_SECTION_ID => r1.LEVEL2_SECTION_ID,
1560 p_section_code => r1.repeating_section_code,
1561 p_parent_alias => l_value_alias,
1562 p_parent_fk_id => l_schema_pk_id,
1563 p_query_stmt=> p_query_stmt,
1564 p_error => p_error,
1565 p_result => p_result);
1566
1567 if p_result = 1 then
1568 return;
1569 end if;
1570
1571 print_debug_log(l_api_name, 'Third if : p_query_stmt = '||p_query_stmt);
1572
1573 end if;
1574
1575 if r1.field_code is not null then
1576
1577 if r1.system_flag = 'Y' then
1578
1579 if r1.SYSTEM_FIELD_LOV_FLAG ='Y' then
1580
1581 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || r1.field_code || '_NAME'',v_1.entity_pk1)'
1582 || ' AS ' || r1.field_code || '_NAME';
1583
1584 end if;
1585
1586 if r1.datatype in ('DATE','DATETIME') then
1587
1588 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMDate(''' || r1.field_code || ''',v_1.entity_pk1)'
1589 || ' AS ' || r1.field_code ;
1590
1591 elsif r1.datatype in ( 'NUMBER','AMOUNT') then
1592
1593 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMNumber(''' || r1.field_code || ''',v_1.entity_pk1)'
1594 || ' AS ' || r1.field_code ;
1595
1596 else
1597
1598 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || r1.field_code || ''',v_1.entity_pk1)'
1599 || ' AS ' || r1.field_code ;
1600
1601 end if;
1602
1603 print_debug_log(l_api_name, 'Fourth if : p_query_stmt = '||p_query_stmt);
1604
1605 else
1606
1607 print_debug_log(l_api_name, ' Calling ADDSTMTFIELD for r1.field_code = '|| r1.field_code);
1608
1609 ADDSTMTFIELD(p_field_code => r1.field_code,
1610 p_datatype => r1.datatype,
1611 p_value_alias => l_value_alias,
1612 p_value_column => r1.MAPPING_FIELD_VALUE_COLUMN,
1613 p_value_set_name => r1.value_set_name,
1614 p_query_stmt=> p_query_stmt,
1615 p_error => p_error,
1616 p_result => p_result);
1617
1618 if p_result = 1 then
1619 return;
1620 end if;
1621
1622 print_debug_log(l_api_name, 'Fifth if : p_query_stmt = '||p_query_stmt);
1623 end if;
1624
1625 end if; -- Field Code Not null
1626
1627 end loop;
1628
1629 print_debug_log(l_api_name, 'l_prev_section_code = '||l_prev_section_code||'
1630 l_prev_level1section_code = '||l_prev_level1section_code);
1631
1632 if l_prev_section_code is not null then
1633
1634 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1635
1636 end if;
1637
1638 if l_prev_level1section_code is not null then
1639
1640 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_level1section_code || ' ';
1641
1642 end if;
1643
1644 print_debug_log(l_api_name, 'Last if : p_query_stmt = '||p_query_stmt);
1645
1646 -- from and where clause for the form
1647 p_query_stmt := p_query_stmt || g_newline || ' from pon_form_field_values ' || l_value_alias;
1648
1649 p_query_stmt := p_query_stmt || g_newline || ' where ' || l_value_alias ||'.OWNING_ENTITY_CODE =:ENTITY_CODE'
1650 || g_newline || ' and ' || l_value_alias ||'.ENTITY_PK1 =:ENTITY_PK1'
1651 || g_newline || ' and ' || l_value_alias ||'.form_id =' || to_char(p_form_id)
1652 || g_newline || ' and ' || l_value_alias ||'.section_id =-1' || g_newline;
1653
1654 print_debug_log(l_api_name, 'END = '||p_query_stmt);
1655
1656
1657 End GENERATE_XMLQUERY;
1658
1659
1660 /*======================================================================
1661 PROCEDURE: GENERATE_XMLSCHEMA PUBLIC
1662 PARAMETERS:
1663 COMMENT :
1664 ======================================================================*/
1665 Procedure GENERATE_XMLSCHEMA (p_form_id in Number, -- top form
1666 p_schema OUT NOCOPY CLOB,
1667 p_error IN OUT NOCOPY VARCHAR2,
1668 p_result IN OUT NOCOPY number, -- 0: Success, 1: failure
1669 x_xml_query OUT NOCOPY VARCHAR2 -- The xml query
1670 ) IS
1671
1672 l_err_num NUMBER;
1673 l_err_msg VARCHAR2(200);
1674 ldoc Varchar2(4000) :='<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1675 <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
1676 <xsl:template match="/">
1677 <xsl:apply-templates select="DOCUMENT/xsd:schema"/>
1678 </xsl:template>
1679 <xsl:template match="xsd:schema">
1680 <xsl:copy-of select="."/>
1681 </xsl:template>
1682 </xsl:stylesheet>';
1683
1684 l_xml_query Varchar2(31500);
1685 l_form_code Varchar2(30);
1686 l_queryCtx DBMS_XMLquery.ctxType;
1687
1688 l_schemOffset Number;
1689 l_xmlTagOffset INTEGER;
1690 l_documentOffset BINARY_INTEGER;
1691 l_documentTagLength BINARY_INTEGER;
1692 l_blankTextForDocumentTag VARCHAR2(100);
1693 l_count NUMBER;
1694
1695 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XMLSCHEMA';
1696
1697 Begin
1698
1699 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
1700
1701 p_result := 0;
1702
1703 SAVEPOINT PON_XMLSCHEMA;
1704 g_mode := 'SCHEMA';
1705 select form_code
1706 into l_form_code
1707 from pon_forms_sections
1708 where form_id = p_form_id;
1709
1710 print_debug_log(l_api_name, 'g_mode = '||g_mode||' l_form_code = '||l_form_code);
1711
1712 GENERATE_XMLQUERY (p_form_id,
1713 l_xml_query,
1714 p_error,
1715 p_result);
1716
1717 if p_result = 1 then
1718 return;
1719 end if;
1720
1721 x_xml_query := l_xml_query;
1722
1723 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' GOT QUERY FORM SCHEMA ');
1724
1725 l_queryCtx := DBMS_XMLquery.newContext(l_xml_query);
1726 DBMS_XMLQuery.setDateFormat(l_queryCtx,'dd/mm/yyyy HH:mm:ss'); -- sets the row tag name
1727 DBMS_XMLQuery.setRowTag(l_queryCtx,l_form_code || '_ROW'); -- sets the row tag name
1728 DBMS_XMLQuery.setRowSetTag(l_queryCtx,l_form_code);
1729 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_CODE','XML_SCHEMA_GENERATION');
1730 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_PK1',g_dummy_pk);
1731 -- DBMS_XMLquery.setXSLT(l_queryCtx,ldoc );
1732
1733 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' STARTING SCHEMA GENERATION');
1734
1735 p_schema:=DBMS_XMLquery.GETXML(l_queryCtx,2);
1736
1737 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' GOT SCHEMA ');
1738
1739 g_mode := 'XML';
1740
1741 rollback to SAVEPOINT PON_XMLSCHEMA;
1742
1743 DBMS_XMLQUERY.closecontext(l_queryCtx);
1744
1745 l_schemOffset := DBMS_LOB.INSTR(p_schema,'</xsd:schema>')+12;
1746 DBMS_LOB.TRIM(p_schema,l_schemOffset);
1747
1748 --The code below removed the <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1749 -- from the xsd
1750
1751 -- Get the length of tag for <?xml version = '1.0'?>
1752 l_xmlTagOffset := DBMS_LOB.INSTR(p_schema,'>');
1753
1754 -- Get the position where <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1755 -- ends
1756 l_documentOffset := DBMS_LOB.INSTR(p_schema,'>',1,2);
1757
1758 -- length of <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1759 l_documentTagLength := l_documentOffset-l_xmlTagOffset;
1760 l_blankTextForDocumentTag := '';
1761 l_count := 0;
1762
1763 loop
1764 exit when l_count > l_documentTagLength;
1765 l_blankTextForDocumentTag := l_blankTextForDocumentTag ||' ';
1766 l_count := l_count+1;
1767 end loop;
1768
1769 dbms_lob.write(p_schema,l_documentTagLength,l_xmlTagOffset+1,l_blankTextForDocumentTag);
1770
1771 print_debug_log(l_api_name, 'END ');
1772
1773 exception
1774 when others then
1775 l_err_num := SQLCODE;
1776 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1777 print_error_log(l_api_name, 'p_form_id = '||p_form_id||' SCHEMA GENERATION FAILED l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1778 p_result := 1;
1779 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_SCHEMA_GENERATION_FAIL');
1780 DBMS_XMLquery.closecontext (l_queryCtx);
1781
1782 End GENERATE_XMLSCHEMA;
1783
1784
1785
1786 /*======================================================================
1787 PROCEDURE: GENERATE_XML PUBLIC
1788 PARAMETERS:
1789 COMMENT :
1790 ======================================================================*/
1791 Procedure GENERATE_XML(p_form_id in Number, -- top form
1792 p_entity_code Varchar2,
1793 p_entity_pk1 Varchar2,
1794 p_xml OUT NOCOPY CLOB,
1795 p_xdo_stylesheet_code OUT NOCOPY VARCHAR2,
1796 p_error OUT NOCOPY VARCHAR2,
1797 p_result OUT NOCOPY number -- 0: Success, 1: failure
1798 ) IS
1799 l_err_num NUMBER;
1800 l_err_msg VARCHAR2(200);
1801 l_xml_query Varchar2(31500);
1802 l_form_code Varchar2(30);
1803 l_queryCtx DBMS_XMLquery.ctxType;
1804
1805 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XML';
1806
1807 Begin
1808
1809 p_result := 0;
1810
1811 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1812 p_entity_code = '||p_entity_code||'
1813 p_entity_pk1 = '||p_entity_pk1);
1814
1815 g_mode := 'XML';
1816
1817 select
1818 xdo_stylesheet_code,
1819 form_code
1820 into
1821 p_xdo_stylesheet_code,
1822 l_form_code
1823 from pon_forms_sections
1824 where form_id = p_form_id;
1825
1826
1827 GENERATE_XMLQUERY(p_form_id,
1828 l_xml_query,
1829 p_error,
1830 p_result);
1831
1832 if p_result = 1 then
1833 return;
1834 end if;
1835
1836 print_debug_log(l_api_name, 'Got Xml Query for GENERATE_XML p_form_id = '||p_form_id);
1837
1838 l_queryCtx := DBMS_XMLquery.newContext(l_xml_query);
1839 DBMS_XMLQuery.setDateFormat(l_queryCtx,'dd/mm/yyyy HH:mm:ss'); -- sets the row tag name
1840 DBMS_XMLQuery.setRowTag(l_queryCtx,l_form_code || '_ROW'); -- sets the row tag name
1841 DBMS_XMLQuery.setRowSetTag(l_queryCtx,l_form_code);
1842 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_CODE',p_entity_code);
1843 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_PK1',p_entity_pk1);
1844
1845 print_debug_log(l_api_name, 'All values bound for GENERATE_XML p_form_id = '||p_form_id);
1846
1847 p_xml:=DBMS_XMLquery.GETXML(l_queryCtx, 0);
1848 DBMS_XMLQUERY.closecontext(l_queryCtx);
1849
1850 exception
1851 when others then
1852 l_err_num := SQLCODE;
1853 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1854 print_error_log(l_api_name, 'p_form_id = '||p_form_id||' GENERATE_XML FAILED l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1855 p_result := 1;
1856 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_XML_GENERATION_FAIL');
1857 DBMS_XMLquery.closecontext (l_queryCtx);
1858
1859 End GENERATE_XML;
1860
1861
1862
1863 /*======================================================================
1864 PROCEDURE: CreateDummyRowForXML PRIVATE
1865 PARAMETERS:
1866 COMMENT :
1867 ======================================================================*/
1868 Procedure CreateDummyRowForXML(
1869 p_value_pk_id Number,
1870 p_form_id number,
1871 p_section_id Number,
1872 p_parent_fk_id Number,
1873 p_level1_section_id Number,
1874 p_level2_section_id Number,
1875 p_error IN OUT NOCOPY VARCHAR2,
1876 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1877 ) is
1878 l_err_num NUMBER;
1879 l_err_msg VARCHAR2(200);
1880 l_api_name CONSTANT VARCHAR2(30) := 'CreateDummyRowForXML';
1881
1882 Begin
1883
1884 p_result := 0;
1885
1886 print_debug_log(l_api_name, 'BEGIN- p_value_pk_id = '||p_value_pk_id||'
1887 p_form_id = '||p_form_id||'
1888 p_section_id = '||p_section_id||'
1889 p_parent_fk_id = '||p_parent_fk_id||'
1890 p_level1_section_id = '||p_level1_section_id||'
1891 p_level2_section_id = '||p_level2_section_id||'
1892 g_dummy_pk = '||g_dummy_pk||'
1893 g_dummy_char = '||g_dummy_char||'
1894 g_dummy_num = '||g_dummy_num);
1895
1896 insert into pon_form_field_values
1897 (FORM_FIELD_VALUE_ID,
1898 FORM_ID,
1899 OWNING_ENTITY_CODE,
1900 ENTITY_PK1,
1901 SECTION_ID,
1902 PARENT_FIELD_VALUES_FK,
1903 TEXTCOL1,
1904 TEXTCOL2,
1905 TEXTCOL3,
1906 TEXTCOL4,
1907 TEXTCOL5,
1908 TEXTCOL6,
1909 TEXTCOL7,
1910 TEXTCOL8,
1911 TEXTCOL9,
1912 TEXTCOL10,
1913 TEXTCOL11,
1914 TEXTCOL12,
1915 TEXTCOL13,
1916 TEXTCOL14,
1917 TEXTCOL15,
1918 TEXTCOL16,
1919 TEXTCOL17,
1920 TEXTCOL18,
1921 TEXTCOL19,
1922 TEXTCOL20,
1923 TEXTCOL21,
1924 TEXTCOL22,
1925 TEXTCOL23,
1926 TEXTCOL24,
1927 TEXTCOL25,
1928 TEXTCOL26,
1929 TEXTCOL27,
1930 TEXTCOL28,
1931 TEXTCOL29,
1932 TEXTCOL30,
1933 TEXTCOL31,
1934 TEXTCOL32,
1935 TEXTCOL33,
1936 TEXTCOL34,
1937 TEXTCOL35,
1938 TEXTCOL36,
1939 TEXTCOL37,
1940 TEXTCOL38,
1941 TEXTCOL39,
1942 TEXTCOL40,
1943 TEXTCOL41,
1944 TEXTCOL42,
1945 TEXTCOL43,
1946 TEXTCOL44,
1947 TEXTCOL45,
1948 TEXTCOL46,
1949 TEXTCOL47,
1950 TEXTCOL48,
1951 TEXTCOL49,
1952 TEXTCOL50,
1953 TEXTCOL51,
1954 TEXTCOL52,
1955 TEXTCOL53,
1956 TEXTCOL54,
1957 TEXTCOL55,
1958 TEXTCOL56,
1959 TEXTCOL57,
1960 TEXTCOL58,
1961 TEXTCOL59,
1962 TEXTCOL60,
1963 TEXTCOL61,
1964 TEXTCOL62,
1965 TEXTCOL63,
1966 TEXTCOL64,
1967 TEXTCOL65,
1968 TEXTCOL66,
1969 TEXTCOL67,
1970 TEXTCOL68,
1971 TEXTCOL69,
1972 TEXTCOL70,
1973 TEXTCOL71,
1974 TEXTCOL72,
1975 TEXTCOL73,
1976 TEXTCOL74,
1977 TEXTCOL75,
1978 TEXTCOL76,
1979 TEXTCOL77,
1980 TEXTCOL78,
1981 TEXTCOL79,
1982 TEXTCOL80,
1983 TEXTCOL81,
1984 TEXTCOL82,
1985 TEXTCOL83,
1986 TEXTCOL84,
1987 TEXTCOL85,
1988 TEXTCOL86,
1989 TEXTCOL87,
1990 TEXTCOL88,
1991 TEXTCOL89,
1992 TEXTCOL90,
1993 TEXTCOL91,
1994 TEXTCOL92,
1995 TEXTCOL93,
1996 TEXTCOL94,
1997 TEXTCOL95,
1998 TEXTCOL96,
1999 TEXTCOL97,
2000 TEXTCOL98,
2001 TEXTCOL99,
2002 TEXTCOL100,
2003 TEXTCOL101,
2004 TEXTCOL102,
2005 TEXTCOL103,
2006 TEXTCOL104,
2007 TEXTCOL105,
2008 TEXTCOL106,
2009 TEXTCOL107,
2010 TEXTCOL108,
2011 TEXTCOL109,
2012 TEXTCOL110,
2013 TEXTCOL111,
2014 TEXTCOL112,
2015 TEXTCOL113,
2016 TEXTCOL114,
2017 TEXTCOL115,
2018 TEXTCOL116,
2019 TEXTCOL117,
2020 TEXTCOL118,
2021 TEXTCOL119,
2022 TEXTCOL120,
2023 TEXTCOL121,
2024 TEXTCOL122,
2025 TEXTCOL123,
2026 TEXTCOL124,
2027 TEXTCOL125,
2028 TEXTCOL126,
2029 TEXTCOL127,
2030 TEXTCOL128,
2031 TEXTCOL129,
2032 TEXTCOL130,
2033 TEXTCOL131,
2034 TEXTCOL132,
2035 TEXTCOL133,
2036 TEXTCOL134,
2037 TEXTCOL135,
2038 TEXTCOL136,
2039 TEXTCOL137,
2040 TEXTCOL138,
2041 TEXTCOL139,
2042 TEXTCOL140,
2043 TEXTCOL141,
2044 TEXTCOL142,
2045 TEXTCOL143,
2046 TEXTCOL144,
2047 TEXTCOL145,
2048 TEXTCOL146,
2049 TEXTCOL147,
2050 TEXTCOL148,
2051 TEXTCOL149,
2052 TEXTCOL150,
2053 TEXTCOL151,
2054 TEXTCOL152,
2055 TEXTCOL153,
2056 TEXTCOL154,
2057 TEXTCOL155,
2058 TEXTCOL156,
2059 TEXTCOL157,
2060 TEXTCOL158,
2061 TEXTCOL159,
2062 TEXTCOL160,
2063 TEXTCOL161,
2064 TEXTCOL162,
2065 TEXTCOL163,
2066 TEXTCOL164,
2067 TEXTCOL165,
2068 TEXTCOL166,
2069 TEXTCOL167,
2070 TEXTCOL168,
2071 TEXTCOL169,
2072 TEXTCOL170,
2073 TEXTCOL171,
2074 TEXTCOL172,
2075 TEXTCOL173,
2076 TEXTCOL174,
2077 TEXTCOL175,
2078 TEXTCOL176,
2079 TEXTCOL177,
2080 TEXTCOL178,
2081 TEXTCOL179,
2082 TEXTCOL180,
2083 TEXTCOL181,
2084 TEXTCOL182,
2085 TEXTCOL183,
2086 TEXTCOL184,
2087 TEXTCOL185,
2088 TEXTCOL186,
2089 TEXTCOL187,
2090 TEXTCOL188,
2091 TEXTCOL189,
2092 TEXTCOL190,
2093 TEXTCOL191,
2094 TEXTCOL192,
2095 TEXTCOL193,
2096 TEXTCOL194,
2097 TEXTCOL195,
2098 TEXTCOL196,
2099 TEXTCOL197,
2100 TEXTCOL198,
2101 TEXTCOL199,
2102 TEXTCOL200,
2103 TEXTCOL201,
2104 TEXTCOL202,
2105 TEXTCOL203,
2106 TEXTCOL204,
2107 TEXTCOL205,
2108 TEXTCOL206,
2109 TEXTCOL207,
2110 TEXTCOL208,
2111 TEXTCOL209,
2112 TEXTCOL210,
2113 TEXTCOL211,
2114 TEXTCOL212,
2115 TEXTCOL213,
2116 TEXTCOL214,
2117 TEXTCOL215,
2118 TEXTCOL216,
2119 TEXTCOL217,
2120 TEXTCOL218,
2121 TEXTCOL219,
2122 TEXTCOL220,
2123 TEXTCOL221,
2124 TEXTCOL222,
2125 TEXTCOL223,
2126 TEXTCOL224,
2127 TEXTCOL225,
2128 TEXTCOL226,
2129 TEXTCOL227,
2130 TEXTCOL228,
2131 TEXTCOL229,
2132 TEXTCOL230,
2133 TEXTCOL231,
2134 TEXTCOL232,
2135 TEXTCOL233,
2136 TEXTCOL234,
2137 TEXTCOL235,
2138 TEXTCOL236,
2139 TEXTCOL237,
2140 TEXTCOL238,
2141 TEXTCOL239,
2142 TEXTCOL240,
2143 TEXTCOL241,
2144 TEXTCOL242,
2145 TEXTCOL243,
2146 TEXTCOL244,
2147 TEXTCOL245,
2148 TEXTCOL246,
2149 TEXTCOL247,
2150 TEXTCOL248,
2151 TEXTCOL249,
2152 TEXTCOL250,
2153 DATECOL1,
2154 DATECOL2,
2155 DATECOL3,
2156 DATECOL4,
2157 DATECOL5,
2158 DATECOL6,
2159 DATECOL7,
2160 DATECOL8,
2161 DATECOL9,
2162 DATECOL10,
2163 DATECOL11,
2164 DATECOL12,
2165 DATECOL13,
2166 DATECOL14,
2167 DATECOL15,
2168 DATECOL16,
2169 DATECOL17,
2170 DATECOL18,
2171 DATECOL19,
2172 DATECOL20,
2173 DATECOL21,
2174 DATECOL22,
2175 DATECOL23,
2176 DATECOL24,
2177 DATECOL25,
2178 DATECOL26,
2179 DATECOL27,
2180 DATECOL28,
2181 DATECOL29,
2182 DATECOL30,
2183 DATECOL31,
2184 DATECOL32,
2185 DATECOL33,
2186 DATECOL34,
2187 DATECOL35,
2188 DATECOL36,
2189 DATECOL37,
2190 DATECOL38,
2191 DATECOL39,
2192 DATECOL40,
2193 DATECOL41,
2194 DATECOL42,
2195 DATECOL43,
2196 DATECOL44,
2197 DATECOL45,
2198 DATECOL46,
2199 DATECOL47,
2200 DATECOL48,
2201 DATECOL49,
2202 DATECOL50,
2203 NUMBERCOL1,
2204 NUMBERCOL2,
2205 NUMBERCOL3,
2206 NUMBERCOL4,
2207 NUMBERCOL5,
2208 NUMBERCOL6,
2209 NUMBERCOL7,
2210 NUMBERCOL8,
2211 NUMBERCOL9,
2212 NUMBERCOL10,
2213 NUMBERCOL11,
2214 NUMBERCOL12,
2215 NUMBERCOL13,
2216 NUMBERCOL14,
2217 NUMBERCOL15,
2218 NUMBERCOL16,
2219 NUMBERCOL17,
2220 NUMBERCOL18,
2221 NUMBERCOL19,
2222 NUMBERCOL20,
2223 NUMBERCOL21,
2224 NUMBERCOL22,
2225 NUMBERCOL23,
2226 NUMBERCOL24,
2227 NUMBERCOL25,
2228 NUMBERCOL26,
2229 NUMBERCOL27,
2230 NUMBERCOL28,
2231 NUMBERCOL29,
2232 NUMBERCOL30,
2233 NUMBERCOL31,
2234 NUMBERCOL32,
2235 NUMBERCOL33,
2236 NUMBERCOL34,
2237 NUMBERCOL35,
2238 NUMBERCOL36,
2239 NUMBERCOL37,
2240 NUMBERCOL38,
2241 NUMBERCOL39,
2242 NUMBERCOL40,
2243 NUMBERCOL41,
2244 NUMBERCOL42,
2245 NUMBERCOL43,
2246 NUMBERCOL44,
2247 NUMBERCOL45,
2248 NUMBERCOL46,
2249 NUMBERCOL47,
2250 NUMBERCOL48,
2251 NUMBERCOL49,
2252 NUMBERCOL50,
2253 CREATION_DATE,
2254 CREATED_BY,
2255 LAST_UPDATE_DATE,
2256 LAST_UPDATED_BY,
2257 LAST_UPDATE_LOGIN,
2258 LEVEL1_SECTION_ID,
2259 LEVEL2_SECTION_ID)
2260 values(
2261 p_value_pk_id
2262 ,p_FORM_ID
2263 ,'XML_SCHEMA_GENERATION'
2264 , g_dummy_pk
2265 ,p_SECTION_ID
2266 ,p_parent_fk_id
2267 ,g_dummy_char
2268 ,g_dummy_char
2269 ,g_dummy_char
2270 ,g_dummy_char
2271 ,g_dummy_char
2272 ,g_dummy_char
2273 ,g_dummy_char
2274 ,g_dummy_char
2275 ,g_dummy_char
2276 ,g_dummy_char
2277 ,g_dummy_char
2278 ,g_dummy_char
2279 ,g_dummy_char
2280 ,g_dummy_char
2281 ,g_dummy_char
2282 ,g_dummy_char
2283 ,g_dummy_char
2284 ,g_dummy_char
2285 ,g_dummy_char
2286 ,g_dummy_char
2287 ,g_dummy_char
2288 ,g_dummy_char
2289 ,g_dummy_char
2290 ,g_dummy_char
2291 ,g_dummy_char
2292 ,g_dummy_char
2293 ,g_dummy_char
2294 ,g_dummy_char
2295 ,g_dummy_char
2296 ,g_dummy_char
2297 ,g_dummy_char
2298 ,g_dummy_char
2299 ,g_dummy_char
2300 ,g_dummy_char
2301 ,g_dummy_char
2302 ,g_dummy_char
2303 ,g_dummy_char
2304 ,g_dummy_char
2305 ,g_dummy_char
2306 ,g_dummy_char
2307 ,g_dummy_char
2308 ,g_dummy_char
2309 ,g_dummy_char
2310 ,g_dummy_char
2311 ,g_dummy_char
2312 ,g_dummy_char
2313 ,g_dummy_char
2314 ,g_dummy_char
2315 ,g_dummy_char
2316 ,g_dummy_char
2317 ,g_dummy_char
2318 ,g_dummy_char
2319 ,g_dummy_char
2320 ,g_dummy_char
2321 ,g_dummy_char
2322 ,g_dummy_char
2323 ,g_dummy_char
2324 ,g_dummy_char
2325 ,g_dummy_char
2326 ,g_dummy_char
2327 ,g_dummy_char
2328 ,g_dummy_char
2329 ,g_dummy_char
2330 ,g_dummy_char
2331 ,g_dummy_char
2332 ,g_dummy_char
2333 ,g_dummy_char
2334 ,g_dummy_char
2335 ,g_dummy_char
2336 ,g_dummy_char
2337 ,g_dummy_char
2338 ,g_dummy_char
2339 ,g_dummy_char
2340 ,g_dummy_char
2341 ,g_dummy_char
2342 ,g_dummy_char
2343 ,g_dummy_char
2344 ,g_dummy_char
2345 ,g_dummy_char
2346 ,g_dummy_char
2347 ,g_dummy_char
2348 ,g_dummy_char
2349 ,g_dummy_char
2350 ,g_dummy_char
2351 ,g_dummy_char
2352 ,g_dummy_char
2353 ,g_dummy_char
2354 ,g_dummy_char
2355 ,g_dummy_char
2356 ,g_dummy_char
2357 ,g_dummy_char
2358 ,g_dummy_char
2359 ,g_dummy_char
2360 ,g_dummy_char
2361 ,g_dummy_char
2362 ,g_dummy_char
2363 ,g_dummy_char
2364 ,g_dummy_char
2365 ,g_dummy_char
2366 ,g_dummy_char
2367 ,g_dummy_char
2368 ,g_dummy_char
2369 ,g_dummy_char
2370 ,g_dummy_char
2371 ,g_dummy_char
2372 ,g_dummy_char
2373 ,g_dummy_char
2374 ,g_dummy_char
2375 ,g_dummy_char
2376 ,g_dummy_char
2377 ,g_dummy_char
2378 ,g_dummy_char
2379 ,g_dummy_char
2380 ,g_dummy_char
2381 ,g_dummy_char
2382 ,g_dummy_char
2383 ,g_dummy_char
2384 ,g_dummy_char
2385 ,g_dummy_char
2386 ,g_dummy_char
2387 ,g_dummy_char
2388 ,g_dummy_char
2389 ,g_dummy_char
2390 ,g_dummy_char
2391 ,g_dummy_char
2392 ,g_dummy_char
2393 ,g_dummy_char
2394 ,g_dummy_char
2395 ,g_dummy_char
2396 ,g_dummy_char
2397 ,g_dummy_char
2398 ,g_dummy_char
2399 ,g_dummy_char
2400 ,g_dummy_char
2401 ,g_dummy_char
2402 ,g_dummy_char
2403 ,g_dummy_char
2404 ,g_dummy_char
2405 ,g_dummy_char
2406 ,g_dummy_char
2407 ,g_dummy_char
2408 ,g_dummy_char
2409 ,g_dummy_char
2410 ,g_dummy_char
2411 ,g_dummy_char
2412 ,g_dummy_char
2413 ,g_dummy_char
2414 ,g_dummy_char
2415 ,g_dummy_char
2416 ,g_dummy_char
2417 ,g_dummy_char
2418 ,g_dummy_char
2419 ,g_dummy_char
2420 ,g_dummy_char
2421 ,g_dummy_char
2422 ,g_dummy_char
2423 ,g_dummy_char
2424 ,g_dummy_char
2425 ,g_dummy_char
2426 ,g_dummy_char
2427 ,g_dummy_char
2428 ,g_dummy_char
2429 ,g_dummy_char
2430 ,g_dummy_char
2431 ,g_dummy_char
2432 ,g_dummy_char
2433 ,g_dummy_char
2434 ,g_dummy_char
2435 ,g_dummy_char
2436 ,g_dummy_char
2437 ,g_dummy_char
2438 ,g_dummy_char
2439 ,g_dummy_char
2440 ,g_dummy_char
2441 ,g_dummy_char
2442 ,g_dummy_char
2443 ,g_dummy_char
2444 ,g_dummy_char
2445 ,g_dummy_char
2446 ,g_dummy_char
2447 ,g_dummy_char
2448 ,g_dummy_char
2449 ,g_dummy_char
2450 ,g_dummy_char
2451 ,g_dummy_char
2452 ,g_dummy_char
2453 ,g_dummy_char
2454 ,g_dummy_char
2455 ,g_dummy_char
2456 ,g_dummy_char
2457 ,g_dummy_char
2458 ,g_dummy_char
2459 ,g_dummy_char
2460 ,g_dummy_char
2461 ,g_dummy_char
2462 ,g_dummy_char
2463 ,g_dummy_char
2464 ,g_dummy_char
2465 ,g_dummy_char
2466 ,g_dummy_char
2467 ,g_dummy_char
2468 ,g_dummy_char
2469 ,g_dummy_char
2470 ,g_dummy_char
2471 ,g_dummy_char
2472 ,g_dummy_char
2473 ,g_dummy_char
2474 ,g_dummy_char
2475 ,g_dummy_char
2476 ,g_dummy_char
2477 ,g_dummy_char
2478 ,g_dummy_char
2479 ,g_dummy_char
2480 ,g_dummy_char
2481 ,g_dummy_char
2482 ,g_dummy_char
2483 ,g_dummy_char
2484 ,g_dummy_char
2485 ,g_dummy_char
2486 ,g_dummy_char
2487 ,g_dummy_char
2488 ,g_dummy_char
2489 ,g_dummy_char
2490 ,g_dummy_char
2491 ,g_dummy_char
2492 ,g_dummy_char
2493 ,g_dummy_char
2494 ,g_dummy_char
2495 ,g_dummy_char
2496 ,g_dummy_char
2497 ,g_dummy_char
2498 ,g_dummy_char
2499 ,g_dummy_char
2500 ,g_dummy_char
2501 ,g_dummy_char
2502 ,g_dummy_char
2503 ,g_dummy_char
2504 ,g_dummy_char
2505 ,g_dummy_char
2506 ,g_dummy_char
2507 ,g_dummy_char
2508 ,g_dummy_char
2509 ,g_dummy_char
2510 ,g_dummy_char
2511 ,g_dummy_char
2512 ,g_dummy_char
2513 ,g_dummy_char
2514 ,g_dummy_char
2515 ,g_dummy_char
2516 ,g_dummy_char
2517 ,sysdate -200
2518 ,sysdate -200
2519 ,sysdate -200
2520 ,sysdate -200
2521 ,sysdate -200
2522 ,sysdate -200
2523 ,sysdate -200
2524 ,sysdate -200
2525 ,sysdate -200
2526 ,sysdate -200
2527 ,sysdate -200
2528 ,sysdate -200
2529 ,sysdate -200
2530 ,sysdate -200
2531 ,sysdate -200
2532 ,sysdate -200
2533 ,sysdate -200
2534 ,sysdate -200
2535 ,sysdate -200
2536 ,sysdate -200
2537 ,sysdate -200
2538 ,sysdate -200
2539 ,sysdate -200
2540 ,sysdate -200
2541 ,sysdate -200
2542 ,sysdate -200
2543 ,sysdate -200
2544 ,sysdate -200
2545 ,sysdate -200
2546 ,sysdate -200
2547 ,sysdate -200
2548 ,sysdate -200
2549 ,sysdate -200
2550 ,sysdate -200
2551 ,sysdate -200
2552 ,sysdate -200
2553 ,sysdate -200
2554 ,sysdate -200
2555 ,sysdate -200
2556 ,sysdate -200
2557 ,sysdate -200
2558 ,sysdate -200
2559 ,sysdate -200
2560 ,sysdate -200
2561 ,sysdate -200
2562 ,sysdate -200
2563 ,sysdate -200
2564 ,sysdate -200
2565 ,sysdate -200
2566 ,sysdate -200
2567 ,g_dummy_num
2568 ,g_dummy_num
2569 ,g_dummy_num
2570 ,g_dummy_num
2571 ,g_dummy_num
2572 ,g_dummy_num
2573 ,g_dummy_num
2574 ,g_dummy_num
2575 ,g_dummy_num
2576 ,g_dummy_num
2577 ,g_dummy_num
2578 ,g_dummy_num
2579 ,g_dummy_num
2580 ,g_dummy_num
2581 ,g_dummy_num
2582 ,g_dummy_num
2583 ,g_dummy_num
2584 ,g_dummy_num
2585 ,g_dummy_num
2586 ,g_dummy_num
2587 ,g_dummy_num
2588 ,g_dummy_num
2589 ,g_dummy_num
2590 ,g_dummy_num
2591 ,g_dummy_num
2592 ,g_dummy_num
2593 ,g_dummy_num
2594 ,g_dummy_num
2595 ,g_dummy_num
2596 ,g_dummy_num
2597 ,g_dummy_num
2598 ,g_dummy_num
2599 ,g_dummy_num
2600 ,g_dummy_num
2601 ,g_dummy_num
2602 ,g_dummy_num
2603 ,g_dummy_num
2604 ,g_dummy_num
2605 ,g_dummy_num
2606 ,g_dummy_num
2607 ,g_dummy_num
2608 ,g_dummy_num
2609 ,g_dummy_num
2610 ,g_dummy_num
2611 ,g_dummy_num
2612 ,g_dummy_num
2613 ,g_dummy_num
2614 ,g_dummy_num
2615 ,g_dummy_num
2616 ,g_dummy_num
2617 ,sysdate
2618 ,0
2619 ,sysdate
2620 ,0
2621 ,0
2622 ,p_level1_section_id
2623 ,p_level2_section_id);
2624
2625 print_debug_log(l_api_name, 'END '||l_api_name);
2626
2627 exception
2628 when others then
2629 l_err_num := SQLCODE;
2630 l_err_msg := SUBSTR(SQLERRM, 1, 200);
2631 print_error_log(l_api_name, 'p_form_id = '||p_form_id||' CreateDummyRowForXML FAILED l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
2632 p_result := 1;
2633 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_CREATE_DUMMY_ROW_ERROR');
2634
2635 End CreateDummyRowForXML;
2636
2637
2638
2639 /*======================================================================
2640 PROCEDURE: COMPILE_FORM PUBLIC
2641 PARAMETERS:
2642 COMMENT :
2643 ======================================================================*/
2644 PROCEDURE COMPILE_FORM(p_form_id IN NUMBER) IS
2645
2646 x_section_id NUMBER;
2647
2648 x_is_repeating_section_flag VARCHAR2(1);
2649
2650 x_form_section_field_id NUMBER;
2651
2652 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
2653
2654 CURSOR c1_form_section_fields IS
2655 select
2656 ff.FORM_SECTION_FIELD_ID,
2657 ff.FORM_ID,
2658 ff.TYPE,
2659 ff.FIELD_CODE,
2660 ff.SEQUENCE_NUMBER,
2661 ff.REQUIRED,
2662 ff.SECTION_ID,
2663 ff.DISPLAY_ON_MAIN_PAGE,
2664 ff.ENABLED,
2665 f.datatype,
2666 f.system_flag
2667 from pon_form_section_fields ff,
2668 pon_fields f
2669 where form_id = p_form_id
2670 and f.field_code(+) = ff.field_code
2671 order by sequence_number;
2672
2673
2674 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_FORM';
2675
2676 begin
2677
2678 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
2679
2680 g_date_sequence_number := 0;
2681
2682 g_number_sequence_number := 0;
2683
2684 g_text_sequence_number := 0;
2685
2686 g_internal_sequence_number := 10;
2687
2688 Delete pon_form_section_compiled
2689 where form_id = p_form_id;
2690 -- Inser a row for the form
2691 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => null,
2692 p_form_id => p_form_id,
2693 p_type => 'FORM',
2694 p_field_code => null,
2695 p_INTERNAL_SEQUENCE_NUMBER =>0,
2696 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2697 p_REQUIRED => null,
2698 p_LEVEL1_SECTION_ID => null,
2699 p_LEVEL2_SECTION_ID => null,
2700 p_REPEATING_SECTION_ID => null,
2701 p_DISPLAY_ON_MAIN_PAGE =>null,
2702 p_ENABLED =>'Y');
2703
2704 for form_section_compiled in c1_form_section_fields LOOP
2705
2706 if form_section_compiled.TYPE = 'FIELD' then
2707 x_mapping_column := null;
2708 if form_section_compiled.system_flag ='N' then
2709 GetMappingColumn(p_datatype => form_section_compiled.datatype,
2710 p_mapping_column => x_mapping_column);
2711 end if;
2712
2713 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2714 p_form_id => form_section_compiled.FORM_ID,
2715 p_type => 'FORM_FIELD',
2716 p_field_code => form_section_compiled.FIELD_CODE,
2717 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2718 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
2719 p_REQUIRED => form_section_compiled.REQUIRED,
2720 p_LEVEL1_SECTION_ID => null,
2721 p_LEVEL2_SECTION_ID => null,
2722 p_REPEATING_SECTION_ID => null,
2723 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2724 p_ENABLED =>form_section_compiled.ENABLED);
2725
2726 g_internal_sequence_number := g_internal_sequence_number + 10;
2727
2728 elsif form_section_compiled.TYPE = 'SECTION' then
2729
2730 x_section_id := form_section_compiled.section_id;
2731
2732 select
2733 nvl(is_repeating_section_flag,'N')
2734 into
2735 x_is_repeating_section_flag
2736 from
2737 pon_forms_sections
2738 where
2739 FORM_ID = x_section_id;
2740
2741 if x_is_repeating_section_flag = 'Y' then
2742
2743 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2744 p_form_id => form_section_compiled.FORM_ID,
2745 p_type => 'REPEAT_SECTION',
2746 p_field_code => null,
2747 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2748 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2749 p_REQUIRED => null,
2750 p_LEVEL1_SECTION_ID => null,
2751 p_LEVEL2_SECTION_ID => null,
2752 p_REPEATING_SECTION_ID => form_section_compiled.SECTION_ID,
2753 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2754 p_ENABLED =>form_section_compiled.ENABLED);
2755
2756 g_internal_sequence_number := g_internal_sequence_number + 10;
2757 else
2758 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2759 p_form_id => form_section_compiled.FORM_ID,
2760 p_type => 'NORMAL_SECTION',
2761 p_field_code => null,
2762 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2763 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2764 p_REQUIRED => null,
2765 p_LEVEL1_SECTION_ID => form_section_compiled.SECTION_ID,
2766 p_LEVEL2_SECTION_ID => null,
2767 p_REPEATING_SECTION_ID => null,
2768 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2769 p_ENABLED =>form_section_compiled.ENABLED);
2770
2771 g_internal_sequence_number := g_internal_sequence_number + 10;
2772
2773 INSERT_LEVEL1_SECTION_IN_FORM(p_form_id,form_section_compiled.SECTION_ID);
2774
2775 if form_section_compiled.ENABLED = 'N' then
2776
2777 update pon_form_section_compiled
2778 set enabled = 'N'
2779 where form_id = p_form_id
2780 and level1_section_id = form_section_compiled.SECTION_ID;
2781
2782 end if;
2783
2784 end if;
2785
2786 end if;
2787
2788 END LOOP;
2789
2790 print_debug_log(l_api_name, 'END '||l_api_name);
2791
2792 END COMPILE_FORM;
2793
2794
2795
2796 /*======================================================================
2797 PROCEDURE: INSERT_LEVEL1_SECTION_IN_FORM PRIVATE
2798 PARAMETERS:
2799 COMMENT :
2800 ======================================================================*/
2801 PROCEDURE INSERT_LEVEL1_SECTION_IN_FORM(p_form_id IN NUMBER,
2802 p_level1_section_id IN NUMBER) IS
2803
2804 x_section_id NUMBER;
2805
2806 x_is_repeating_section_flag VARCHAR2(1);
2807
2808 x_form_section_field_id NUMBER;
2809
2810
2811 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
2812
2813 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LEVEL1_SECTION_IN_FORM';
2814
2815 CURSOR LEVEL1_SECTION_fields IS
2816 select
2817 ff.FORM_SECTION_FIELD_ID,
2818 ff.FORM_ID,
2819 ff.TYPE,
2820 ff.FIELD_CODE,
2821 ff.SEQUENCE_NUMBER,
2822 ff.REQUIRED,
2823 ff.SECTION_ID,
2824 ff.DISPLAY_ON_MAIN_PAGE,
2825 ff.ENABLED,
2826 f.datatype,
2827 f.system_flag
2828 from pon_form_section_fields ff,
2829 pon_fields f
2830 where form_id = p_level1_section_id
2831 and f.field_code(+) = ff.field_code
2832 order by sequence_number;
2833
2834 BEGIN
2835
2836 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
2837 p_level1_section_id = '||p_level1_section_id);
2838
2839 for LEVEL1_SECTION_fields_record in LEVEL1_SECTION_fields LOOP
2840
2841 if LEVEL1_SECTION_fields_record.TYPE = 'FIELD' then
2842 x_mapping_column := null;
2843 if LEVEL1_SECTION_fields_record.system_flag ='N' then
2844
2845 GetMappingColumn(p_datatype => LEVEL1_SECTION_fields_record.datatype,
2846 p_mapping_column => x_mapping_column);
2847 end if;
2848
2849 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2850 p_form_id => p_form_id,
2851 p_type => 'SECTION_FIELD',
2852 p_field_code => LEVEL1_SECTION_fields_record.FIELD_CODE,
2853 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2854 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
2855 p_REQUIRED => LEVEL1_SECTION_fields_record.REQUIRED,
2856 p_LEVEL1_SECTION_ID => p_level1_section_id,
2857 p_LEVEL2_SECTION_ID => null,
2858 p_REPEATING_SECTION_ID => null,
2859 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2860 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED);
2861
2862 g_internal_sequence_number := g_internal_sequence_number + 10;
2863
2864 elsif LEVEL1_SECTION_fields_record.TYPE = 'SECTION' then
2865
2866 x_section_id := LEVEL1_SECTION_fields_record.section_id;
2867
2868 select
2869 nvl(is_repeating_section_flag,'N')
2870 into
2871 x_is_repeating_section_flag
2872 from
2873 pon_forms_sections
2874 where
2875 FORM_ID = x_section_id;
2876
2877 if x_is_repeating_section_flag = 'Y' then
2878
2879 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2880 p_form_id => p_form_id,
2881 p_type => 'INNER_REPEAT_SECTION',
2882 p_field_code => null,
2883 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2884 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2885 p_REQUIRED => null,
2886 p_LEVEL1_SECTION_ID => p_level1_section_id,
2887 p_LEVEL2_SECTION_ID => null,
2888 p_REPEATING_SECTION_ID => LEVEL1_SECTION_fields_record.SECTION_ID,
2889 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2890 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED);
2891
2892 g_internal_sequence_number := g_internal_sequence_number + 10;
2893 else
2894
2895 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2896 p_form_id => p_form_id,
2897 p_type => 'INNER_NORMAL_SECTION',
2898 p_field_code => null,
2899 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2900 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2901 p_REQUIRED => null,
2902 p_LEVEL1_SECTION_ID => p_level1_section_id,
2903 p_LEVEL2_SECTION_ID => LEVEL1_SECTION_fields_record.SECTION_ID,
2904 p_REPEATING_SECTION_ID => null,
2905 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2906 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED);
2907
2908 g_internal_sequence_number := g_internal_sequence_number + 10;
2909
2910 INSERT_LEVEL2_SECTION_IN_FORM(p_form_id,
2911 p_level1_section_id,
2912 LEVEL1_SECTION_fields_record.SECTION_ID);
2913
2914 end if;
2915
2916 end if;
2917
2918 end loop;
2919
2920 print_debug_log(l_api_name, 'END '||l_api_name);
2921
2922 END INSERT_LEVEL1_SECTION_IN_FORM;
2923
2924
2925
2926 /*======================================================================
2927 PROCEDURE: INSERT_LEVEL2_SECTION_IN_FORM PRIVATE
2928 PARAMETERS:
2929 COMMENT :
2930 ======================================================================*/
2931 PROCEDURE INSERT_LEVEL2_SECTION_IN_FORM(p_form_id IN NUMBER,
2932 p_level1_section_id IN NUMBER,
2933 p_level2_section_id IN NUMBER) IS
2934
2935 x_section_id NUMBER;
2936
2937 x_is_repeating_section_flag VARCHAR2(1);
2938
2939 x_form_section_field_id NUMBER;
2940
2941
2942 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
2943
2944 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LEVEL2_SECTION_IN_FORM';
2945
2946 CURSOR inner_section_fields IS
2947 select
2948 ff.FORM_SECTION_FIELD_ID,
2949 ff.FORM_ID,
2950 ff.TYPE,
2951 ff.FIELD_CODE,
2952 ff.SEQUENCE_NUMBER,
2953 ff.REQUIRED,
2954 ff.SECTION_ID,
2955 ff.DISPLAY_ON_MAIN_PAGE,
2956 ff.ENABLED,
2957 f.datatype,
2958 f.system_flag
2959 from pon_form_section_fields ff,
2960 pon_fields f
2961 where form_id = p_level2_section_id
2962 and f.field_code(+) = ff.field_code
2963 order by sequence_number;
2964
2965 BEGIN
2966
2967 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
2968 p_level1_section_id = '||p_level1_section_id||'
2969 p_level2_section_id = '||p_level2_section_id);
2970
2971 for inner_section_fields_record in inner_section_fields LOOP
2972
2973 if inner_section_fields_record.TYPE = 'FIELD' then
2974
2975 x_mapping_column := null;
2976 if inner_section_fields_record.system_flag ='N' then
2977
2978 GetMappingColumn(p_datatype => inner_section_fields_record.datatype,
2979 p_mapping_column => x_mapping_column);
2980 end if;
2981
2982 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => inner_section_fields_record.FORM_SECTION_FIELD_ID,
2983 p_form_id => p_form_id,
2984 p_type => 'INNER_SECTION_FIELD',
2985 p_field_code => inner_section_fields_record.FIELD_CODE,
2986 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2987 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
2988 p_REQUIRED => inner_section_fields_record.REQUIRED,
2989 p_LEVEL1_SECTION_ID => p_level1_section_id,
2990 p_LEVEL2_SECTION_ID => p_level2_section_id,
2991 p_REPEATING_SECTION_ID => null,
2992 p_DISPLAY_ON_MAIN_PAGE =>inner_section_fields_record.DISPLAY_ON_MAIN_PAGE,
2993 p_ENABLED =>inner_section_fields_record.ENABLED);
2994
2995 g_internal_sequence_number := g_internal_sequence_number + 10;
2996
2997 else
2998 -- inner section can have a field or repeatable sections only
2999
3000 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => inner_section_fields_record.FORM_SECTION_FIELD_ID,
3001 p_form_id => p_form_id,
3002 p_type => 'INNER_SECTION_REPEAT_SECTION',
3003 p_field_code => null,
3004 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
3005 p_MAPPING_FIELD_VALUE_COLUMN =>null,
3006 p_REQUIRED => null,
3007 p_LEVEL1_SECTION_ID => p_level1_section_id,
3008 p_LEVEL2_SECTION_ID => p_level2_section_id,
3009 p_REPEATING_SECTION_ID => inner_section_fields_record.SECTION_ID,
3010 p_DISPLAY_ON_MAIN_PAGE =>inner_section_fields_record.DISPLAY_ON_MAIN_PAGE,
3011 p_ENABLED =>inner_section_fields_record.ENABLED);
3012
3013 g_internal_sequence_number := g_internal_sequence_number + 10;
3014
3015 end if;
3016
3017 end loop;
3018
3019 print_debug_log(l_api_name, 'END '||l_api_name);
3020
3021 END INSERT_LEVEL2_SECTION_IN_FORM;
3022
3023 /*======================================================================
3024 PROCEDURE : GENERATE_REPEATING_SECTIONS
3025 PARAMETERS: p_form_id: The id of the form.
3026 COMMENT : This procedure will call the COMPILE_FORM and
3027 PON_FORMS_JRAD_PVT.CREATE_JRAD procedures for all the
3028 repeating sections included in this form.
3029 ======================================================================*/
3030 PROCEDURE GENERATE_REPEATING_SECTIONS (p_form_id IN NUMBER,
3031 p_generate_mode IN VARCHAR2, -- ALL, JRAD, XSD
3032 p_error IN OUT NOCOPY VARCHAR2,
3033 p_result IN OUT NOCOPY NUMBER) IS
3034
3035 l_err_num NUMBER;
3036 l_err_msg VARCHAR2(200);
3037 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_REPEATING_SECTIONS';
3038 CURSOR v_repeating_section_id_cursor is
3039 SELECT
3040 DISTINCT FS.FORM_ID SECTION_ID
3041 FROM
3042 PON_FORM_SECTION_FIELDS FSF,
3043 PON_FORMS_SECTIONS FS
3044 WHERE
3045 FSF.SECTION_ID = FS.FORM_ID
3046 AND
3047 FS.IS_REPEATING_SECTION_FLAG = 'Y'
3048 AND
3049 (FS.JRAD_XML_REGION_NAME IS NULL
3050 OR
3051 FS.JRAD_XML_REGION_NAME_DISP IS NULL)
3052 START WITH
3053 FSF.FORM_ID = p_form_id
3054 CONNECT
3055 BY PRIOR FSF.SECTION_ID = FSF.FORM_ID;
3056
3057 v_repeating_section_id v_repeating_section_id_cursor%ROWTYPE;
3058 v_section_id PON_FORMS_SECTIONS.FORM_ID%TYPE;
3059 x_error_message VARCHAR2(100);
3060 x_error_code VARCHAR2(100);
3061 x_result VARCHAR2(100);
3062 v_read_only_region_name VARCHAR2(100);
3063 v_edit_region_name VARCHAR2(100);
3064
3065 BEGIN
3066
3067 p_result := 0;
3068
3069 print_debug_log (l_api_name, 'BEGIN- p_form_id = ' || p_form_id);
3070
3071 FOR v_repeating_section_id IN v_repeating_section_id_cursor LOOP
3072
3073 v_section_id := v_repeating_section_id.section_id;
3074
3075 print_debug_log (l_api_name, 'Calling COMPILE_FORM for section id = ' || v_section_id);
3076 COMPILE_FORM (v_section_id);
3077
3078 -- if we need to generate everything or only the JRAD region
3079 IF (p_generate_mode IN ('ALL', 'JRAD')) THEN
3080 print_debug_log (l_api_name, 'Calling CREATE_JRAD for section id = ' || v_section_id);
3081 PON_FORMS_JRAD_PVT.CREATE_JRAD (v_section_id, x_result, x_error_code, x_error_message);
3082
3083 print_debug_log (l_api_name, 'Return values from create_jrad: x_result = ' ||
3084 x_result || ', x_error_message = ' ||
3085 x_error_message || ', x_err_code = ' ||
3086 x_error_code);
3087
3088 if(x_result = fnd_api.g_ret_sts_success) then
3089 p_result := 0;
3090 else
3091 p_result := 1;
3092 p_error := x_error_message;
3093 return;
3094 end if;
3095
3096 -- update the JRAD region references
3097 -- if the form/abstract/section is active
3098 v_read_only_region_name := getReadOnlyRegionName (v_section_id);
3099 v_edit_region_name := getDataEntryRegionName (v_section_id);
3100
3101 UPDATE PON_FORMS_SECTIONS
3102 SET
3103 JRAD_XML_REGION_NAME_DISP = v_read_only_region_name,
3104 JRAD_XML_REGION_NAME = v_edit_region_name
3105 WHERE
3106 FORM_ID = v_section_id
3107 AND STATUS = 'ACTIVE';
3108 END IF;
3109
3110 END LOOP;
3111
3112 print_debug_log (l_api_name, 'END ' || l_api_name);
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 l_err_num := SQLCODE;
3117 l_err_msg := SUBSTR(SQLERRM, 1, 200);
3118 print_error_log(l_api_name, 'EXCEPTION While Creating Jrad l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
3119 p_result := 1;
3120 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_UNABLE_TO_CREATE_JRAD');
3121
3122 END GENERATE_REPEATING_SECTIONS;
3123
3124
3125 /*======================================================================
3126 PROCEDURE: GENERATE_FORM_DETAILS PUBLIC
3127 PARAMETERS:
3128 COMMENT :
3129 ======================================================================*/
3130 PROCEDURE GENERATE_FORM_DETAILS(p_form_id IN NUMBER,
3131 p_generate_mode IN VARCHAR2, -- ALL, XSD, JRAD
3132 p_schema OUT NOCOPY CLOB,
3133 p_error IN OUT NOCOPY VARCHAR2,
3134 p_result IN OUT NOCOPY NUMBER -- 0: success, 1 - failure
3135 ) IS
3136
3137 l_err_num NUMBER;
3138 l_err_msg VARCHAR2(200);
3139 x_form_code VARCHAR2(20);
3140 x_form_version NUMBER;
3141 x_type VARCHAR2(30);
3142 x_data_entry_region_name VARCHAR2(100);
3143 x_read_only_region_name_disp VARCHAR2(100);
3144 x_query_stmt PON_FORMS_SECTIONS.XML_QUERY%TYPE;
3145 x_error_message VARCHAR2(100);
3146 x_error_code VARCHAR2(100);
3147 x_result VARCHAR2(100);
3148 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_FORM_DETAILS';
3149 l_version VARCHAR2(20);
3150 l_compatibility VARCHAR2(20);
3151 l_majorVersion NUMBER;
3152
3153 BEGIN
3154
3155 p_result := 0;
3156
3157 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
3158
3159 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
3160 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
3161
3162 print_debug_log (l_api_name, 'l_version = ' || l_version || ', l_majorVersion = ' || l_majorVersion );
3163
3164 select
3165 type,
3166 jrad_xml_region_name,
3167 jrad_xml_region_name_disp
3168 into
3169 x_type,
3170 x_data_entry_region_name,
3171 x_read_only_region_name_disp
3172 from
3173 pon_forms_sections
3174 where
3175 FORM_ID = p_form_id;
3176
3177 -- generate inner repeating sections
3178 -- for forms and abstract, as well as sections
3179 GENERATE_REPEATING_SECTIONS (p_form_id, p_generate_mode, p_error, p_result);
3180 print_debug_log(l_api_name, 'GENERATE_REPEATING_SECTIONS p_result = '||p_result);
3181
3182 if (p_result = 1) then
3183 return;
3184 end if;
3185
3186 IF (x_data_entry_region_name IS NULL OR x_read_only_region_name_disp IS NULL OR x_type = 'ABSTRACT') THEN
3187 COMPILE_FORM (p_form_id);
3188 print_debug_log(l_api_name, 'COMPILE_FORM p_form_id = '|| p_form_id);
3189 END IF;
3190
3191 -- if we need to generate either just the XSD or everything
3192 -- then need to generate xml query and schema
3193 IF x_type = 'FORM' AND p_generate_mode IN ('ALL', 'XSD') THEN
3194
3195 print_debug_log(l_api_name, 'inside generate xml quer if x_type = ' || x_type || ', p_generate_mode = ' || p_generate_mode);
3196 if (l_majorVersion >= 9) then
3197 print_debug_log(l_api_name, 'major version is greater than 9');
3198
3199 GENERATE_XMLSCHEMA (p_form_id => p_form_id,
3200 p_schema => p_schema,
3201 p_error => p_error,
3202 p_result => p_result,
3203 x_xml_query => x_query_stmt);
3204
3205 if (p_result = 1) then
3206 return;
3207 end if;
3208
3209 update
3210 pon_forms_sections
3211 set
3212 xml_query = x_query_stmt
3213 where
3214 form_id = p_form_id;
3215
3216 print_debug_log(l_api_name, 'GENERATE_XMLSCHEMA p_result = '||p_result);
3217
3218 end if; --if major version >=9
3219
3220 end if;
3221
3222 -- if we need to generate either just the JRAD or everything
3223 IF (p_generate_mode IN ('ALL', 'JRAD')) THEN
3224
3225 PON_FORMS_JRAD_PVT.CREATE_JRAD(p_form_id ,
3226 x_result,
3227 x_error_code,
3228 x_error_message
3229 );
3230
3231 if(x_result = fnd_api.g_ret_sts_success) then
3232 p_result := 0;
3233 else
3234 p_result := 1;
3235 p_error := x_error_message;
3236 return;
3237 end if;
3238
3239 -- update the JRAD region references
3240 -- if the form/abstract/section is active
3241 x_data_entry_region_name := PON_FORMS_UTIL_PVT.getDataEntryRegionName(p_form_id);
3242 x_read_only_region_name_disp := PON_FORMS_UTIL_PVT.getReadOnlyRegionName(p_form_id);
3243
3244 UPDATE pon_forms_sections
3245 SET
3246 jrad_xml_region_name = x_data_entry_region_name,
3247 jrad_xml_region_name_disp = x_read_only_region_name_disp
3248 WHERE form_id = p_form_id
3249 AND status = 'ACTIVE';
3250
3251 print_debug_log(l_api_name, 'PON_FORMS_JRAD_PVT.CREATE_JRAD p_result = '||p_result);
3252
3253 print_debug_log(l_api_name, 'END '||l_api_name);
3254
3255 END IF;
3256
3257 EXCEPTION
3258 WHEN OTHERS THEN
3259 p_result := 1;
3260 l_err_num := SQLCODE;
3261 l_err_msg := SUBSTR(SQLERRM, 1, 200);
3262 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
3263
3264 END GENERATE_FORM_DETAILS;
3265
3266
3267
3268 /*======================================================================
3269 PROCEDURE: publishAbstract PUBLIC
3270 PARAMETERS:
3271 COMMENT : This procedure is used to update the abstract status
3272 when it is published, i.e. this procedure should be
3273 invoked when the user presses either publish or un-publish
3274 abstract button. We also invoke this procedure when the
3275 apply button is pressed on the enter form-data page as
3276 we need to save the 'include pdf' checkbox value.
3277 ======================================================================*/
3278
3279 procedure publishAbstract(p_auction_header_id IN NUMBER,
3280 p_include_pdf_flag IN VARCHAR2,
3281 p_publish_action IN VARCHAR2,
3282 x_result OUT NOCOPY VARCHAR2,
3283 x_error_code OUT NOCOPY VARCHAR2,
3284 x_error_message OUT NOCOPY VARCHAR2) IS
3285
3286 l_api_name CONSTANT VARCHAR2(30) := 'PUBLISHABSTRACT';
3287 l_form_id NUMBER;
3288
3289 BEGIN
3290
3291 print_debug_log(l_api_name, 'BEGIN- p_auction_header_id = '|| p_auction_header_id||'
3292 p_include_pdf_flag = '||p_include_pdf_flag ||'
3293 p_publish_action = '||p_publish_action);
3294
3295 update pon_auction_headers_all
3296 set include_pdf_in_external_page = p_include_pdf_flag,
3297 last_update_date = sysdate
3298 where auction_header_id = p_auction_header_id;
3299
3300 -- if(nvl(p_publish_action, 'A') <> 'A') then
3301
3302 if((nvl(p_publish_action, '@') = 'Y' ) OR (nvl(p_publish_action, '@') = 'N') ) then
3303
3304 -- if the user hasn't pressed the publish abstract button
3305 -- we dont need to update the status in either
3306 -- pon_auction_headers_all.abstract_status OR
3307 -- pon_forms_instances.status
3308
3309 -- as we do invoke this method when the user presses the apply button as well
3310
3311 update pon_auction_headers_all
3312 set abstract_status = decode(nvl(p_publish_action, 'X'), 'Y', 'PUBLISHED', 'NOT_PUBLISHED'),
3313 last_update_date = sysdate
3314 where auction_header_id = p_auction_header_id;
3315
3316 select form_id
3317 into l_form_id
3318 from pon_forms_sections
3319 where form_code = 'ABSTRACT'
3320 and type = 'ABSTRACT';
3321
3322 update pon_forms_instances
3323 set status = decode(nvl(p_publish_action, 'X'), 'Y', 'PUBLISHED', 'NOT_PUBLISHED'),
3324 last_update_date = sysdate
3325 where entity_code = 'PON_AUCTION_HEADERS_ALL'
3326 and entity_pk1 = to_char(p_auction_header_id)
3327 and form_id = l_form_id;
3328
3329 end if;
3330
3331
3332
3333 x_result := fnd_api.g_ret_sts_success;
3334 print_debug_log(l_api_name, 'END');
3335
3336 EXCEPTION
3337 WHEN OTHERS THEN
3338 x_error_code := SQLCODE;
3339 x_error_message := SUBSTR(SQLERRM, 1, 100);
3340 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3341 x_result := fnd_api.g_ret_sts_error;
3342 END publishAbstract;
3343
3344 /*======================================================================
3345 PROCEDURE: performPostSaveChanges PUBLIC
3346 PARAMETERS:
3347 COMMENT : This procedure is used to update the form instance status
3348 for a form attached to an entity. This procedure will be
3349 invoked from the beforeCommit method of the
3350 FormFieldValuesEO entity
3351 ======================================================================*/
3352
3353 PROCEDURE performPostSaveChanges(p_form_id IN NUMBER,
3354 p_entity_pk1 IN VARCHAR2,
3355 p_entity_code IN VARCHAR2,
3356 p_include_pdf IN VARCHAR2,
3357 x_result OUT NOCOPY VARCHAR2,
3358 x_error_code OUT NOCOPY VARCHAR2,
3359 x_error_message OUT NOCOPY VARCHAR2) IS
3360
3361 l_api_name CONSTANT VARCHAR2(30) := 'PERFORMPOSTSAVECHANGES';
3362 l_form_type PON_FORMS_SECTIONS.FORM_CODE%TYPE;
3363 l_old_status PON_FORMS_INSTANCES.STATUS%TYPE;
3364
3365 BEGIN
3366
3367 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3368 p_entity_pk1 = '||p_entity_pk1||'
3369 p_entity_code = '||p_entity_code);
3370
3371 select type
3372 into l_form_type
3373 from pon_forms_sections
3374 where form_id = p_form_id;
3375
3376 if(l_form_type = 'FORM') then
3377
3378 update pon_forms_instances
3379 set status = 'DATA_ENTERED',
3380 last_update_date = sysdate
3381 where entity_code = p_entity_code
3382 and entity_pk1 = p_entity_pk1
3383 and form_id = p_form_id;
3384
3385 elsif(l_form_type = 'ABSTRACT') then
3386
3387 select status
3388 into l_old_status
3389 from pon_forms_instances
3390 where entity_code = p_entity_code
3391 and entity_pk1 = p_entity_pk1
3392 and form_id = p_form_id;
3393
3394 -- if the apply button has been pressed while entering
3395 -- data for a form, we need to update the status if the
3396 -- status hasnt been set
3397
3398 if(NVL(l_old_status , 'x@Y#z') = 'x@Y#z') then
3399
3400 update pon_forms_instances
3401 set status = 'NOT_PUBLISHED',
3402 last_update_date = sysdate
3403 where entity_code = p_entity_code
3404 and entity_pk1 = p_entity_pk1
3405 and form_id = p_form_id;
3406
3407 end if;
3408
3409 end if;
3410
3411
3412 if(NVL(p_include_pdf, 'x@Y#z') <> 'x@Y#z') then
3413
3414 begin
3415 update pon_auction_headers_all
3416 set include_pdf_in_external_page = p_include_pdf,
3417 last_update_date = sysdate
3418 where auction_header_id = to_number(p_entity_pk1);
3419
3420 exception
3421 when others then
3422 null;
3423 end;
3424 end if;
3425
3426 x_result := fnd_api.g_ret_sts_success;
3427
3428 print_debug_log(l_api_name, 'END');
3429
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 x_result := fnd_api.g_ret_sts_error;
3433 x_error_code := SQLCODE;
3434 x_error_message := SUBSTR(SQLERRM, 1, 100);
3435 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3436
3437 END performPostSaveChanges;
3438
3439 /*======================================================================
3440 PROCEDURE: deleteFormFieldValues PUBLIC
3441 PARAMETERS:
3442 COMMENT : This procedure is used to remove all the child rows from
3443 pon_form_field_values table for a given parent row.
3444 This procedure will be invoked from the remove method of the
3445 FormFieldValuesEO entity
3446 ======================================================================*/
3447
3448 PROCEDURE deleteFormFieldValues(p_form_id IN NUMBER,
3449 p_entity_pk1 IN VARCHAR2,
3450 p_entity_code IN VARCHAR2,
3451 p_section_id IN NUMBER,
3452 p_parent_fk IN NUMBER,
3453 x_result OUT NOCOPY VARCHAR2,
3454 x_error_code OUT NOCOPY VARCHAR2,
3455 x_error_message OUT NOCOPY VARCHAR2) IS
3456
3457 l_api_name CONSTANT VARCHAR2(30) := 'DELETEFORMFIELDVALUES';
3458
3459 BEGIN
3460 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3461 p_entity_pk1 = '||p_entity_pk1||'
3462 p_entity_code = '||p_entity_code||'
3463 p_section_id = '||p_section_id||'
3464 p_parent_fk = '||p_parent_fk);
3465
3466 delete from pon_form_field_values
3467 where form_id = p_form_id
3468 and entity_pk1 = p_entity_pk1
3469 and owning_entity_code = p_entity_code
3470 and parent_field_values_fk = p_parent_fk
3471 and nvl(section_id, -1) <> -1;
3472
3473
3474 x_result := fnd_api.g_ret_sts_success;
3475
3476 print_debug_log(l_api_name, 'END');
3477
3478 EXCEPTION
3479 WHEN OTHERS THEN
3480 x_error_code := SQLCODE;
3481 x_error_message := SUBSTR(SQLERRM, 1, 100);
3482 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3483 x_result := fnd_api.g_ret_sts_error;
3484
3485 END deleteFormFieldValues;
3486
3487 /*======================================================================
3488 PROCEDURE : GET_EXTERNAL_REGISTER_URL PUBLIC
3489 PARAMETERS:
3490 COMMENT : This procedure will return the url to be used for
3491 supplier registration.
3492 ======================================================================*/
3493
3494 FUNCTION GET_EXTERNAL_REGISTER_URL (p_org_id IN NUMBER) RETURN VARCHAR2 IS
3495
3496 l_api_name CONSTANT VARCHAR2(30) := 'GET_EXTERNAL_REGISTER_URL';
3497 l_external_register_url VARCHAR2(600);
3498 l_org_hash_key VARCHAR2(80);
3499
3500 BEGIN
3501
3502 print_debug_log(l_api_name, 'BEGIN- p_org_id = '||p_org_id);
3503
3504 print_debug_log(l_api_name, 'Calling POS_URL_PKG.get_External_url');
3505
3506 l_external_register_url := POS_URL_PKG.get_external_url();
3507
3508 print_debug_log(l_api_name, 'Return value from POS_URL_PKG.get_External_url:external url = ' || l_external_register_url);
3509
3510 SELECT HASHKEY
3511 INTO l_org_hash_key
3512 FROM POS_ORG_HASH
3513 WHERE ORG_ID=p_org_id;
3514
3515 l_external_register_url := l_external_register_url || 'OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?ouid=' || l_org_hash_key;
3516
3517 print_debug_log(l_api_name, 'END- external url = ' || l_external_register_url);
3518
3519 return l_external_register_url;
3520
3521 END;
3522
3523 /*======================================================================
3524 PROCEDURE: deleteValues PUBLIC
3525 PARAMETERS:
3526 COMMENT : This procedure is used to remove all the rows from
3527 pon_form_field_values table for a given form.
3528 This procedure should be invoked from the remove method
3529 FormInstancesEO entity
3530 ======================================================================*/
3531
3532 PROCEDURE deleteValues( p_form_id IN NUMBER,
3533 p_entity_pk1 IN VARCHAR2,
3534 p_entity_code IN VARCHAR2,
3535 x_result OUT NOCOPY VARCHAR2,
3536 x_error_code OUT NOCOPY VARCHAR2,
3537 x_error_message OUT NOCOPY VARCHAR2) IS
3538
3539 l_api_name CONSTANT VARCHAR2(30) := 'DELETEVALUES';
3540
3541
3542 BEGIN
3543
3544 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3545 p_entity_pk1 = '||p_entity_pk1||'
3546 p_entity_code = '||p_entity_code);
3547
3548 x_result := fnd_api.g_ret_sts_error;
3549
3550 delete from pon_form_field_values
3551 where form_id = p_form_id
3552 and entity_pk1 = p_entity_pk1
3553 and owning_entity_code = p_entity_code;
3554
3555 x_result := fnd_api.g_ret_sts_success;
3556
3557 print_debug_log(l_api_name, 'END');
3558
3559 EXCEPTION
3560 WHEN OTHERS THEN
3561 x_error_code := SQLCODE;
3562 x_error_message := SUBSTR(SQLERRM, 1, 100);
3563 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3564 x_result := fnd_api.g_ret_sts_error;
3565 END deleteValues;
3566
3567
3568
3569 /*======================================================================
3570 PROCEDURE: PRINT_DEBUG_LOG PRIVATE
3571 PARAMETERS:
3572 COMMENT : This procedure is used to print debug messages into
3573 FND logs
3574 ======================================================================*/
3575 PROCEDURE print_debug_log(p_module IN VARCHAR2,
3576 p_message IN VARCHAR2)
3577
3578 IS
3579
3580 l_message_trun varchar2(800);
3581
3582 BEGIN
3583 IF (g_fnd_debug = 'Y') THEN
3584 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3585 FND_LOG.string(log_level => FND_LOG.level_statement,
3586 module => g_module_prefix || p_module,
3587 message => p_message);
3588 END IF;
3589 END IF;
3590 END;
3591
3592 /*======================================================================
3593 PROCEDURE: PRINT_ERROR_LOG PRIVATE
3594 PARAMETERS:
3595 COMMENT : This procedure is used to print unexpected exceptions or
3596 error messages into FND logs
3597 ======================================================================*/
3598
3599 PROCEDURE print_error_log(p_module IN VARCHAR2,
3600 p_message IN VARCHAR2)
3601 IS
3602 BEGIN
3603 IF (g_fnd_debug = 'Y') THEN
3604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
3605 FND_LOG.string(log_level => FND_LOG.level_procedure,
3606 module => g_module_prefix || p_module,
3607 message => p_message);
3608 END IF;
3609 END IF;
3610 END;
3611
3612
3613 END PON_FORMS_UTIL_PVT;