[Home] [Help]
PACKAGE BODY: APPS.PON_FORMS_UTIL_PVT
Source
1 PACKAGE BODY PON_FORMS_UTIL_PVT as
2 /* $Header: PONFMUTB.pls 120.34 2011/11/16 07:24:05 nrayi ship $ */
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 elsif p_datatype = 'LOV' then
242 g_text_sequence_number := g_text_sequence_number + 1;
243 p_mapping_column := 'Textcol'||g_text_sequence_number;
244
245 else
246
247 g_number_sequence_number := g_number_sequence_number + 1;
248 p_mapping_column := 'Numbercol'||g_number_sequence_number;
249
250 end if;
251
252 print_debug_log(l_api_name, 'END- p_datatype = '||p_datatype||' p_mapping_column = '||p_mapping_column);
253
254 end GetMappingColumn;
255
256
257
258 /*======================================================================
259 FUNCTON: InsertCompiledRow PRIVATE
260 PARAMETERS:
261 COMMENT :
262 ======================================================================*/
263 Procedure InsertCompiledRow( p_FORM_SECTION_FIELD_ID Number,
264 p_form_id Number,
265 p_type Varchar2,
266 p_field_code Varchar2,
267 p_INTERNAL_SEQUENCE_NUMBER Number,
268 p_MAPPING_FIELD_VALUE_COLUMN Varchar2,
269 p_REQUIRED Varchar2,
270 p_LEVEL1_SECTION_ID Number,
271 p_LEVEL2_SECTION_ID Number,
272 p_REPEATING_SECTION_ID Number,
273 p_DISPLAY_ON_MAIN_PAGE Varchar2,
274 p_ENABLED Varchar2,
275 p_EDITABLE Varchar2) is
276
277 l_api_name CONSTANT VARCHAR2(30) := 'InsertCompiledRow';
278
279 begin
280
281 print_debug_log(l_api_name, 'BEGIN- p_FORM_SECTION_FIELD_ID = '||p_FORM_SECTION_FIELD_ID||'
282 p_form_id = '||p_form_id||'
283 p_type = '||p_type||'
284 p_field_code = '||p_field_code||'
285 p_INTERNAL_SEQUENCE_NUMBER = '||p_INTERNAL_SEQUENCE_NUMBER||'
286 p_REQUIRED = '||p_REQUIRED||'
287 p_MAPPING_FIELD_VALUE_COLUMN = '||p_MAPPING_FIELD_VALUE_COLUMN||'
288 p_LEVEL1_SECTION_ID = '||p_LEVEL1_SECTION_ID||'
289 p_LEVEL2_SECTION_ID = '||p_LEVEL2_SECTION_ID||'
290 p_REPEATING_SECTION_ID = '||p_REPEATING_SECTION_ID||'
291 p_DISPLAY_ON_MAIN_PAGE = '||p_DISPLAY_ON_MAIN_PAGE||'
292 p_ENABLED = '||p_ENABLED);
293
294 insert into pon_form_section_compiled
295 (FORM_SECTION_FIELD_ID,
296 FORM_ID,
297 TYPE,
298 FIELD_CODE,
299 INTERNAL_SEQUENCE_NUMBER,
300 MAPPING_FIELD_VALUE_COLUMN,
301 REQUIRED,
302 LEVEL1_SECTION_ID,
303 LEVEL2_SECTION_ID,
304 REPEATING_SECTION_ID,
305 DISPLAY_ON_MAIN_PAGE,
306 ENABLED,
307 CREATION_DATE,
308 CREATED_BY,
309 LAST_UPDATE_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN,
312 EDITABLE)
313 values
314 ( p_FORM_SECTION_FIELD_ID ,
315 p_form_id ,
316 p_type,
317 p_field_code ,
318 p_INTERNAL_SEQUENCE_NUMBER ,
319 p_MAPPING_FIELD_VALUE_COLUMN ,
320 p_REQUIRED ,
321 p_LEVEL1_SECTION_ID ,
322 p_LEVEL2_SECTION_ID ,
323 p_REPEATING_SECTION_ID ,
324 p_DISPLAY_ON_MAIN_PAGE,
325 p_ENABLED,
326 sysdate,
327 fnd_global.user_id,
328 sysdate,
329 fnd_global.user_id,
330 fnd_global.user_id,
331 p_EDITABLE
332 );
333
334 print_debug_log(l_api_name, 'END ');
335
336 end InsertCompiledRow;
337
338 PROCEDURE printClobOut(result IN CLOB) is
339 xmlstr varchar2(32767);
340 line varchar2(2000);
341 l_index Number;
342 begin
343 g_newline := fnd_global.newline();
344 xmlstr := dbms_lob.SUBSTR(result,32767);
345 loop
346 exit when xmlstr is null;
347 l_index := instr(xmlstr,g_newline);
348 line := substr(xmlstr,1,l_index-1);
349 --dbms_output.put_line(line);
350 xmlstr := substr(xmlstr,l_index+1);
351 end loop;
352 End;
353
354 PROCEDURE printLong(result IN Varchar2) is
355 xmlstr Varchar2(31500);
356 line varchar2(2000);
357 l_index Number;
358 begin
359 xmlstr := result;
360 g_newline := fnd_global.newline();
361 loop
362 exit when xmlstr is null;
363 l_index := instr(xmlstr,g_newline);
364 line := substr(xmlstr,1,l_index-1);
365 --dbms_output.put_line(line);
366 xmlstr := substr(xmlstr,l_index+1);
367 end loop;
368 End;
369
370
371 /*======================================================================
372 FUNCTON: Get_Freight PRIVATE
373 PARAMETERS:
374 COMMENT :
375 ======================================================================*/
376 Function Get_Freight(p_carrier_code IN Varchar2,
377 p_inventory_organization_id IN Number)
378 return Varchar2 is
379 rt_value Varchar2(80);
380 l_err_num NUMBER;
381 l_err_msg VARCHAR2(200);
382
383 l_api_name CONSTANT VARCHAR2(30) := 'Get_Freight';
384
385 Begin
386
387 print_debug_log(l_api_name, 'BEGIN- p_carrier_code = '||p_carrier_code||'
388 p_inventory_organization_id = '||p_inventory_organization_id);
389
390 select description
391 into rt_value
392 from org_freight_tl
393 where LANGUAGE = userenv('LANG')
394 and ORGANIZATION_ID = p_inventory_organization_id
395 and FREIGHT_CODE = p_carrier_code;
396
397 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
398
399 return rt_value;
400
401 exception when others then
402 l_err_num := SQLCODE;
403 l_err_msg := SUBSTR(SQLERRM, 1, 200);
404 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);
405 return p_carrier_code;
406 End;
407
408
409
410 /*======================================================================
411 FUNCTON: GetSYSTEMDate PUBLIC
412 PARAMETERS:
413 COMMENT :
414 ======================================================================*/
415 Function GetSYSTEMDate(p_field_code in Varchar2,
416 p_id in Varchar2) return Date is
417 rt_date Date;
418 l_stmt Varchar2(250);
419 l_err_num NUMBER;
420 l_err_msg VARCHAR2(200);
421 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMDate';
422
423 begin
424
425 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
426 p_id = '||p_id);
427
428 if p_id=g_dummy_pk then
429
430 print_debug_log(l_api_name, 'END rt_date (g_dummy_pk)= '||sysdate);
431
432 return(sysdate);
433
434 end if;
435
436 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
437
438 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
439
440
441 if p_field_code = 'AWARD_DATE' then
442
443 select AWARD_BY_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
444
445 elsif p_field_code = 'CLOSE_DATE' then
446
447 select CLOSE_BIDDING_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
448
449 elsif p_field_code = 'OPEN_DATE' then
450
451 select OPEN_BIDDING_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
452
453 elsif p_field_code = 'PREVIEW_DATE' then
454
455 select VIEW_BY_DATE into rt_date from pon_auction_headers_all where auction_header_id = to_number(p_id);
456
457 end if;
458
459 print_debug_log(l_api_name, 'END rt_date = '||rt_date);
460
461 return rt_date;
462 exception when others then
463
464 l_err_num := SQLCODE;
465 l_err_msg := SUBSTR(SQLERRM, 1, 200);
466 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);
467
468 return TO_DATE(NULL) ;
469 end;
470
471
472 /*======================================================================
473 FUNCTON: GetSYSTEMNumber PUBLIC
474 PARAMETERS:
475 COMMENT :
476 ======================================================================*/
477 Function GetSYSTEMNumber(p_field_code in Varchar2,
478 p_id in Varchar2) return Number is
479 rt_value Number;
480 l_stmt Varchar2(250);
481
482 l_err_num NUMBER;
483 l_err_msg VARCHAR2(200);
484 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMNumber';
485
486 begin
487
488 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
489 p_id = '||p_id);
490
491 if p_id=g_dummy_pk then
492
493 print_debug_log(l_api_name, 'END rt_value (g_dummy_num)= '||g_dummy_num);
494 return g_dummy_num;
495
496 end if;
497 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
498
499 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
500
501 if p_field_code = 'PAYMENT_TERMS' then
502
503 select PAYMENT_TERMS_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
504
505 elsif p_field_code = 'ORGANIZATION' then
506
507 select ORG_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
508
509 elsif p_field_code = 'AUCTION_HEADER_ID' then
510
511 select AUCTION_HEADER_ID into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
512
513 end if;
514
515 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
516
517 return rt_value;
518
519 exception when others then
520
521 l_err_num := SQLCODE;
522 l_err_msg := SUBSTR(SQLERRM, 1, 200);
523 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);
524 return TO_NUMBER(NULL) ;
525
526 end;
527
528
529 /*======================================================================
530 FUNCTON: GetSYSTEMChar PUBLIC
531 PARAMETERS:
532 COMMENT :
533 ======================================================================*/
534 Function GetSYSTEMChar(p_field_code in Varchar2,
535 p_id in Varchar2) return Varchar2 is
536 rt_value Varchar2(500);
537 rt_large_value Varchar2(4000);
538 l_stmt Varchar2(250);
539
540 l_api_name CONSTANT VARCHAR2(30) := 'GetSYSTEMChar';
541 l_err_num NUMBER;
542 l_err_msg VARCHAR2(200);
543 begin
544
545 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
546 p_id = '||p_id);
547
548 if p_id=g_dummy_pk then
549
550 print_debug_log(l_api_name, 'END rt_value (g_dummy_pk)= Dummy data');
551
552 return('Dummy data');
553
554 end if;
555
556 l_stmt := 'Selecting ' || p_field_code || ' with auction_header_id = to_number('||p_id||')';
557
558 print_debug_log(l_api_name, 'l_stmt = '||l_stmt);
559
560 if p_field_code = 'ABSTRACT_STATUS' then
561
562 select ABSTRACT_STATUS into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
563
564 elsif p_field_code = 'AMENDMENT_DESCRIPTION' then
565
566 select AMENDMENT_DESCRIPTION into rt_large_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
567
568 elsif p_field_code = 'TITLE' then
569
570 select AUCTION_TITLE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
571
572 elsif p_field_code = 'STYLE' then
573
574 select BID_VISIBILITY_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
575
576 elsif p_field_code = 'CARRIER' then
577
578 select CARRIER_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
579
580 elsif p_field_code = 'NEGOTIATION_CURR' then
581
582 select CURRENCY_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
583
584 elsif p_field_code = 'NEGOTIATION_NUM' then
585
586 select DOCUMENT_NUMBER into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
587
588 elsif p_field_code = 'EVENT' then
589
590 select EVENT_TITLE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
591
592 elsif p_field_code = 'FOB' then
593
594 select FOB_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
595
596 elsif p_field_code = 'FREIGHT_TERMS' then
597
598 select FREIGHT_TERMS_CODE into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
599
600 elsif p_field_code = 'BUYER' then
601
602 select DOCUMENT_NUMBER into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
603
604 elsif p_field_code = 'PAYMENT_TERMS_NAME' then
605
606 select atvl_1.NAME
607 into rt_value
608 from
609 PON_AUCTION_HEADERS_ALL ah,
610 AP_TERMS_VL atvl_1
611 where
612 ah.auction_header_id = to_number(p_id)
613 and atvl_1.TERM_ID(+) = ah.PAYMENT_TERMS_ID;
614
615 elsif p_field_code = 'CARRIER_NAME' then
616
617 select
618 PON_FORMS_UTIL_PVT.GET_FREIGHT(ah.carrier_code, fsp.inventory_organization_id)
619 into rt_large_value
620 from pon_auction_headers_all ah,
621 financials_system_params_all fsp
622 where ah.auction_header_id = to_number(p_id)
623 and fsp.org_id(+) = ah.org_id;
624
625 elsif p_field_code = 'DISPLAY_PDF_FLAG' then
626
627 select
628 decode( nvl(ah.INCLUDE_PDF_IN_EXTERNAL_PAGE, 'N'), 'Y', 'SHOW_PDF', 'HIDE_PDF') DISPLAY_PDF_FLAG
629 into rt_value
630 from pon_auction_headers_all ah
631 where ah.auction_header_id = to_number(p_id);
632
633 elsif p_field_code = 'NEGOTIATION_TYPE' then
634
635 select doc.internal_name
636 into rt_value
637 from
638 pon_auction_headers_all ah,
639 PON_AUC_DOCTYPES doc
640 where
641 ah.auction_header_id = to_number(p_id)
642 and ah.DOCTYPE_ID = doc.DOCTYPE_ID;
643
644 elsif p_field_code = 'NEGOTIATION_TYPE_NAME' then
645
646 select doctl.NAME
647 into rt_value
648 from pon_auction_headers_all ah,
649 PON_AUC_DOCTYPES_TL doctl
650 where
651 ah.auction_header_id = to_number(p_id)
652 and ah.DOCTYPE_ID = doctl.DOCTYPE_ID
653 and doctl.LANGUAGE = userenv('LANG');
654
655 elsif p_field_code = 'STYLE_NAME' then
656
657 select
658 lookup_1.MEANING
659 into
660 rt_value
661 from pon_auction_headers_all ah,
662 FND_LOOKUP_VALUES lookup_1
663 where
664 ah.auction_header_id = to_number(p_id)
665 and lookup_1.LOOKUP_CODE(+) = ah.BID_VISIBILITY_CODE
666 and lookup_1.VIEW_APPLICATION_ID (+) = 0
667 and lookup_1.SECURITY_GROUP_ID (+) = 0
668 and lookup_1.LOOKUP_TYPE(+) = 'PON_BID_VISIBILITY_CODE'
669 and lookup_1.LANGUAGE(+) = userenv('LANG');
670
671 elsif p_field_code = 'FOB_NAME' then
672
673 select
674 lookup_2.MEANING
675 into rt_value
676 from pon_auction_headers_all ah,
677 FND_LOOKUP_VALUES lookup_2
678 where ah.auction_header_id = to_number(p_id)
679 and lookup_2.lookup_code(+) = ah.fob_code
680 and lookup_2.LOOKUP_TYPE(+) = 'FOB'
681 and lookup_2.LANGUAGE(+) = userenv('LANG')
682 and lookup_2.SECURITY_GROUP_ID (+) = 0
683 and lookup_2.VIEW_APPLICATION_ID(+) = 201;
684
685 elsif p_field_code = 'FREIGHT_TERMS_NAME' then
686
687 select
688 lookup_3.MEANING
689 into
690 rt_value
691 from pon_auction_headers_all ah,
692 FND_LOOKUP_VALUES lookup_3
693 where ah.auction_header_id = to_number(p_id)
694 and lookup_3.LOOKUP_CODE(+) = ah.FREIGHT_TERMS_CODE
695 and lookup_3.lookup_type ='FREIGHT TERMS'
696 and lookup_3.LANGUAGE(+) = userenv('LANG')
697 and lookup_3.SECURITY_GROUP_ID (+) = 0
698 and lookup_3.VIEW_APPLICATION_ID(+) = 201;
699
700 elsif p_field_code = 'ORGANIZATION_NAME' then
701
702 select
703 org.NAME
704 into
705 rt_value
706 from pon_auction_headers_all ah,
707 HR_ALL_ORGANIZATION_UNITS_TL org
708 where ah.auction_header_id = to_number(p_id)
709 and org.ORGANIZATION_ID = ah.ORG_ID
710 and org.LANGUAGE = userenv('LANG');
711
712 /*Add for FBO project*/
713 elsif p_field_code = 'FED_DATE' then
714 select to_char(sysdate,'MMDD')
715 into
716 rt_value
717 from dual;
718
719 elsif p_field_code = 'FED_YEAR' then
720 select to_char(sysdate,'YY')
721 into
722 rt_value
723 from dual;
724
725 elsif p_field_code = 'FED_POSTING_DATE' then
726 select to_char(sysdate,'YYYYMMDD')
727 into
728 rt_value
729 from dual;
730
731 elsif p_field_code = 'FED_ZIP' then
732 rt_value := get_uda_fed_zip(to_number(p_id));
733
734 elsif p_field_code = 'FED_OFFADD' then
735 rt_value := get_uda_fed_offadd(to_number(p_id));
736
737 elsif p_field_code = 'FED_SOLNBR' then
738 select DOCUMENT_NUMBER into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
739
740 elsif p_field_code = 'FED_CLOSINGDATE' then
741
742 select TO_CHAR(CLOSE_BIDDING_DATE, 'YYYYMMDD') into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
743
744 elsif p_field_code = 'FED_ARCHDATE' then
745 select TO_CHAR(CLOSE_BIDDING_DATE + 15, 'YYYYMMDD') into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
746
747 elsif p_field_code = 'FED_UPLOADTYPE' then
748 select DECODE(amendment_flag,'Y','A','S') into rt_value from pon_auction_headers_all where auction_header_id = to_number(p_id);
749
750 elsif p_field_code = 'FED_FILE_LINK' then
751 rt_value := get_fed_file_link(to_number(p_id));
752
753 elsif p_field_code = 'FED_CBAC' then
754 select preference_value into rt_value from PON_PARTY_PREFERENCES where app_short_name = 'PON' and preference_name = 'FBO_USER_ID';
755
756 --Temporately use 'FedBizOpps' as key to match with encryption in NegotiationConfCO.java
757 elsif p_field_code = 'FED_PASSWORD' then
758 SELECT PON_FORMS_UTIL_PVT.decrypt('FedBizOpps', (select preference_value from PON_PARTY_PREFERENCES where app_short_name = 'PON' and preference_name = 'FBO_PASSWORD')) into rt_value
759 FROM DUAL;
760
761 end if;
762
763 if (p_field_code = 'CARRIER_NAME' or p_field_code = 'AMENDMENT_DESCRIPTION') then
764 print_debug_log(l_api_name, 'END rt_large_value = '||rt_large_value);
765 return rt_large_value;
766 else
767 print_debug_log(l_api_name, 'END rt_value = '||rt_value);
768 return rt_value;
769 end if;
770
771 exception when others then
772 l_err_num := SQLCODE;
773 l_err_msg := SUBSTR(SQLERRM, 1, 200);
774 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);
775 return NULL ;
776 end;
777
778
779 /*======================================================================
780 FUNCTON: GetValSetQueryIdOrder PUBLIC
781 PARAMETERS:
782 COMMENT :
783 ======================================================================*/
784 Procedure GetValSetQueryIdOrder (
785 p_value_set_name IN VARCHAR2,
786 p_query_stmt OUT NOCOPY Varchar2,
787 p_orderby OUT NOCOPY Varchar2,
788 p_id_column_exists OUT NOCOPY Varchar2,
789 p_is_table_based OUT NOCOPY VARCHAR2,
790 p_error OUT NOCOPY Varchar2,
791 p_result OUT NOCOPY number
792 ) IS
793 l_value_set_type Varchar2(10);
794 l_err_num NUMBER;
795 l_err_msg VARCHAR2(200);
796 l_api_name CONSTANT VARCHAR2(30) := 'GetValSetQueryIdOrder';
797
798 Begin
799
800 p_result := 0;
801
802 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name);
803
804 select validation_type
805 into l_value_set_type
806 from fnd_flex_value_sets
807 where FLEX_VALUE_SET_NAME = p_value_set_name;
808
809 if (l_value_set_type = 'F') then
810 GetValSetTBLQuery (p_value_set_name =>p_value_set_name,
811 p_query_stmt => p_query_stmt,
812 p_orderby => p_orderby,
813 p_id_column_exists=> p_id_column_exists,
814 p_error => p_error,
815 p_result => p_result);
816 p_is_table_based := 'Y';
817 else
818 p_is_table_based := 'N';
819 end if;
820
821 print_debug_log(l_api_name, 'END p_query_stmt = '||p_query_stmt ||'
822 p_orderby = '|| p_orderby ||'
823 p_id_column_exists = '|| p_id_column_exists ||'
824 p_is_table_based = '|| p_is_table_based ||'
825 p_error = '|| p_error ||'
826 p_result = '|| p_result);
827
828 EXCEPTION when others then
829 p_result := 1;
830 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
831 l_err_num := SQLCODE;
832 l_err_msg := SUBSTR(SQLERRM, 1, 200);
833 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);
834 End GetValSetQueryIdOrder;
835
836
837 /*======================================================================
838 FUNCTON: GetValSetTBLQuery PRIVATE
839 PARAMETERS:
840 COMMENT :
841 ======================================================================*/
842 Procedure GetValSetTBLQuery(
843 p_value_set_name in Varchar2,
844 p_query_stmt in out NOCOPY Varchar2,
845 p_orderby in out NOCOPY Varchar2,
846 p_id_column_exists OUT NOCOPY Varchar2,
847 p_error OUT NOCOPY VARCHAR2,
848 p_result OUT NOCOPY number -- 0: Success, 1: failure
849 ) IS
850 l_query_stmt Varchar2(4000);
851 l_success Number;
852 l_id_column_name Varchar2(240) := NULL;
853 l_value_column_name Varchar2(240) := NULL;
854 l_mapping_code Varchar2(80);
855 l_from_index Number;
856 l_orderby_index Number;
857 l_where_index NUMBER;
858 l_where_clause VARCHAR2(4000);
859
860 l_api_name CONSTANT VARCHAR2(30) := 'GetValSetTBLQuery';
861 PON_INVALID_VALSET_EXCEPTION EXCEPTION;
862
863 Begin
864
865 p_result := 0;
866
867 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name||'
868 p_query_stmt = '||p_query_stmt||'
869 p_orderby = '||p_orderby);
870
871 select VALUE_COLUMN_NAME, ID_COLUMN_NAME
872 into l_value_column_name,l_id_column_name
873 from fnd_flex_validation_tables tbl, fnd_flex_value_sets val
874 where tbl.flex_value_set_id =val.flex_value_set_id
875 and val.flex_value_set_name = p_value_set_name;
876
877 print_debug_log(l_api_name, ' l_value_column_name = '||l_value_column_name||'
878 l_id_column_name = '||l_id_column_name);
879
880 fnd_flex_val_api.get_table_vset_select( p_value_set_name =>p_value_set_name,
881 x_select => l_query_stmt,
882 x_mapping_code => l_mapping_code,
883 x_success => l_success);
884
885 print_debug_log(l_api_name, ' l_query_stmt = '||l_query_stmt);
886
887 l_query_stmt := upper(l_query_stmt);
888 l_from_index := instr(l_query_stmt,'FROM');
889 l_where_index := instr (l_query_stmt, 'WHERE');
890 l_orderby_index := instr(l_query_stmt,'ORDER BY') ;
891
892 print_debug_log(l_api_name, ' l_query_stmt = '||l_query_stmt||' l_from_index = '||l_from_index||' l_orderby_index = '||l_orderby_index);
893
894 if l_where_index > l_from_index then
895 if (l_orderby_index > l_where_index) then
896 l_where_clause := substr (l_query_stmt, l_where_index, l_orderby_index - l_where_index -1);
897 else
898 l_where_clause := substr (l_query_stmt, l_where_index);
899 end if;
900
901 print_debug_log(l_api_name, 'index of $ = ' || instr (l_where_clause, '$'));
902
903 if (instr (l_where_clause, '$') <> 0 OR instr (l_where_index, ':') <> 0) then
904 RAISE PON_INVALID_VALSET_EXCEPTION;
905 end if;
906 end if;
907
908 if L_ID_COLUMN_NAME is not null then
909 p_id_column_exists := 'Y';
910 p_query_stmt := 'Select ' || L_ID_COLUMN_NAME || ' AS ID_COLUMN ,'
911 || g_newline || substr(l_value_column_name,1,500) || ' AS VALUE_COLUMN ';
912 else
913 p_id_column_exists := 'N';
914 -- if the ID column does not exist, just return the same column twice
915 p_query_stmt := 'Select ' || l_value_column_name || ' AS ID_COLUMN ,'
916 || g_newline || l_value_column_name || ' AS VALUE_COLUMN ';
917 end if;
918
919 print_debug_log(l_api_name, 'p_id_column_exists = '||p_id_column_exists ||' p_query_stmt = '||p_query_stmt);
920
921 if l_orderby_index >l_from_index then
922 p_query_stmt := p_query_stmt || g_newline ||
923 substr(l_query_stmt,l_from_index,l_orderby_index-l_from_index - 1);
924 p_orderby := substr(l_query_stmt,l_orderby_index);
925 else
926 p_query_stmt := p_query_stmt || g_newline ||
927 substr(l_query_stmt,l_from_index);
928 p_orderby := NULL;
929 end if;
930
931 print_debug_log(l_api_name, ' END =
932 p_query_stmt = '|| p_query_stmt ||'
933 p_orderby = '|| p_orderby ||'
934 p_id_column_exists = '|| p_id_column_exists ||'
935 p_error = '|| p_error ||'
936 p_result = '|| p_result);
937
938 EXCEPTION when PON_INVALID_VALSET_EXCEPTION then
939 p_result := 1;
940 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
941 End GetValSetTBLQuery;
942
943
944
945 /*======================================================================
946 FUNCTON: GetFLEXINDENDENTVALUE PUBLIC
947 PARAMETERS:
948 COMMENT :
949 ======================================================================*/
950 Function GetFLEXINDENDENTVALUE(p_value_set_name in varchar2,
951 p_id_value in Varchar2) Return Varchar2 IS
952 l_err_num NUMBER;
953 l_err_msg VARCHAR2(200);
954 rt_value Varchar2(150);
955 cursor get_value is
956 select tl.FLEX_VALUE_MEANING
957 from fnd_flex_values_tl tl,
958 fnd_flex_values val,
959 fnd_flex_value_sets valset
960 where tl.flex_value_id = val.flex_value_id
961 and tl.language = USERENV('LANG')
962 and val.flex_value_set_id = valset.flex_value_set_id
963 and valset.flex_value_set_name = p_value_set_name
964 and val.FLEX_VALUE = p_id_value;
965
966 l_api_name CONSTANT VARCHAR2(30) := 'GetFLEXINDENDENTVALUE';
967
968 Begin
969
970 print_debug_log(l_api_name, 'BEGIN- p_value_set_name = '||p_value_set_name||'
971 p_id_value = '||p_id_value);
972
973 if p_id_value = g_dummy_char then
974 rt_value := p_id_value;
975 else
976 open get_value;
977 fetch get_value into rt_value;
978 close get_value;
979 end if;
980
981 print_debug_log(l_api_name, ' END rt_value = '|| rt_value );
982
983 return rt_value;
984
985 exception when others then
986 l_err_num := SQLCODE;
987 l_err_msg := SUBSTR(SQLERRM, 1, 200);
988 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);
989
990 return p_id_value;
991
992 End GetFLEXINDENDENTVALUE;
993
994
995
996 /*======================================================================
997 FUNCTON: GetFLEXTBLVALUE PUBLIC
998 PARAMETERS:
999 COMMENT :
1000 ======================================================================*/
1001 Function GetFLEXTBLVALUE(p_field_code in varchar2,
1002 p_id_value in Varchar2) Return Varchar2 IS
1003
1004 l_err_num NUMBER;
1005 l_err_msg VARCHAR2(200);
1006 rt_value Varchar2(500);
1007 l_query_stmt Varchar2(4000);
1008 l_success Number;
1009 l_value_column_name VARCHAR2(240) := NULL;
1010 l_id_column_name Varchar2(240) := NULL;
1011 l_id_column_exists Varchar2(10) := NULL;
1012 l_mapping_code Varchar2(80);
1013 l_id_value Varchar2(500);
1014
1015 l_api_name CONSTANT VARCHAR2(30) := 'GetFLEXTBLVALUE';
1016
1017 Begin
1018
1019 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
1020 p_id_value = '||p_id_value);
1021
1022 if p_id_value = g_dummy_char then
1023 rt_value := p_id_value;
1024 else
1025 select tbl.ID_COLUMN_NAME, tbl.VALUE_COLUMN_NAME, fld.VALUE_SET_QUERY, fld.VALUE_SET_ID_EXISTS
1026 into l_id_column_name, l_value_column_name, l_query_stmt, l_id_column_exists
1027 from fnd_flex_validation_tables tbl,
1028 fnd_flex_value_sets val,
1029 pon_fields fld
1030 where tbl.flex_value_set_id = val.flex_value_set_id
1031 and val.flex_value_set_name = fld.value_set_name
1032 and fld.field_code = p_field_code;
1033
1034 print_debug_log(l_api_name, 'l_id_column_name = '|| l_id_column_name ||'
1035 l_query_stmt = '||l_query_stmt ||'
1036 l_id_column_exists = '||l_id_column_exists);
1037
1038 if L_ID_COLUMN_NAME is null then
1039 rt_value := p_id_value;
1040 elsif L_ID_COLUMN_NAME is not null and l_id_column_exists ='N' then
1041 rt_value := p_id_value;
1042 else
1043 if instr(l_query_stmt,'WHERE') >0 then
1044 l_query_stmt := l_query_stmt || g_newline || ' AND ' || l_value_column_name || ' = :1';
1045 else
1046 l_query_stmt := l_query_stmt || g_newline || ' WHERE ' || l_value_column_name || ' = :1';
1047 end if;
1048
1049 l_query_stmt := l_query_stmt || ' AND ROWNUM = 1';
1050
1051 print_debug_log(l_api_name, 'l_query_stmt = '|| l_query_stmt );
1052
1053 EXECUTE IMMEDIATE l_query_stmt INTO rt_value, l_id_value USING p_id_value;
1054 end if;
1055 end if;
1056
1057 print_debug_log(l_api_name, ' END rt_value = '|| rt_value );
1058
1059 return rt_value;
1060
1061 exception when others then
1062
1063 l_err_num := SQLCODE;
1064 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1065 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);
1066
1067 return p_id_value;
1068
1069 End GetFLEXTBLVALUE;
1070
1071
1072 /*======================================================================
1073 PROCEDURE: ADDSTMTVALUESET PUBLIC
1074 PARAMETERS:
1075 COMMENT :
1076 ======================================================================*/
1077 Procedure ADDSTMTVALUESET(p_field_code in Varchar2,
1078 p_value_alias in Varchar2,
1079 p_value_column in Varchar2,
1080 p_value_set_name in Varchar2,
1081 p_query_stmt in out nocopy Varchar2,
1082 p_error IN OUT NOCOPY VARCHAR2,
1083 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1084 ) IS
1085 l_err_num NUMBER;
1086 l_err_msg VARCHAR2(200);
1087 l_value_set_type Varchar2(10);
1088 l_id_column_name Varchar2(240);
1089 l_value_column_name Varchar2(240);
1090
1091 l_api_name CONSTANT VARCHAR2(30) := 'ADDSTMTVALUESET';
1092
1093 Begin
1094
1095 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
1096 p_value_alias = '||p_value_alias||'
1097 p_value_column = '||p_value_column||'
1098 p_value_set_name = '||p_value_set_name||'
1099 p_query_stmt = '||p_query_stmt);
1100
1101 select validation_type
1102 into l_value_set_type
1103 from fnd_flex_value_sets
1104 where FLEX_VALUE_SET_NAME = p_value_set_name;
1105
1106 print_debug_log(l_api_name, 'l_value_set_type = '||l_value_set_type);
1107
1108 if l_value_set_type in ('I','X') then
1109 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias ||'.' ||p_value_column || ' AS ' || p_field_code
1110 || g_newline || ',PON_FORMS_UTIL_PVT.GetFLEXINDENDENTVALUE('''|| p_value_set_name || ''','
1111 || g_newline || ' ' || p_value_alias ||'.'|| p_value_column || ') AS ' || p_field_code || '_NM';
1112 /* ==============================================================================
1113 p_query_stmt := p_query_stmt || g_newline || ',xmlelement("FIELDVALUE"'
1114 || ' ,xmlattributes(' || p_value_alias ||'.' ||p_value_column || ' AS "CODE"'
1115 || g_newline || ' ,PON_FORMS_UTIL_PVT.GetFLEXINDENDENTVALUE('''
1116 || p_value_set_name || ''',' || p_value_alias ||'.'
1117 || p_value_column || ') AS "DESCRIPTION"'
1118 || ')) AS ' || p_field_code;
1119 ============================================================================== */
1120 elsif l_value_set_type = 'F' then
1121
1122 select VALUE_COLUMN_NAME, ID_COLUMN_NAME
1123 into l_value_column_name,l_id_column_name
1124 from fnd_flex_validation_tables tbl, fnd_flex_value_sets val
1125 where tbl.flex_value_set_id =val.flex_value_set_id
1126 and val.flex_value_set_name = p_value_set_name;
1127
1128 print_debug_log(l_api_name, 'l_value_column_name = '||l_value_column_name ||' l_id_column_name = '||l_id_column_name);
1129
1130 if l_id_column_name is null then -- this is a single column value set
1131
1132 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.'
1133 || p_value_column || ' AS ' || p_field_code;
1134
1135 else
1136
1137 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias ||'.' ||p_value_column || ' AS ' || p_field_code
1138 || g_newline || ',PON_FORMS_UTIL_PVT.GetFLEXTBLVALUE('''|| p_field_code || ''','
1139 || g_newline || ' ' || p_value_alias ||'.'|| p_value_column || ') AS ' || p_field_code || '_NM';
1140 /* ==============================================================================
1141 p_query_stmt := p_query_stmt || g_newline || ',xmlelement("FIELDVALUE"'
1142 || ' ,xmlattributes(' || p_value_alias ||'.' ||p_value_column || ' AS "CODE"'
1143 || g_newline || ' ,PON_FORMS_UTIL_PVT.GetFLEXTBLVALUE('''
1144 || p_field_code || ''',' || p_value_alias ||'.' ||p_value_column
1145 || ') AS "DESCRIPTION"'
1146 || ')) AS ' || p_field_code;
1147 ============================================================================== */
1148
1149 end if;
1150
1151 else
1152
1153 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1154 p_value_column || ' AS ' || p_field_code;
1155
1156 end if;
1157
1158 print_debug_log(l_api_name, 'END- p_query_stmt = '||p_query_stmt);
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 p_result := 1;
1163 l_err_num := SQLCODE;
1164 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1165 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1166 p_error := PON_AUCTION_PKG.getMessage ('PON_INVALID_VALSET_DEF');
1167
1168 End ADDSTMTVALUESET;
1169
1170
1171
1172 /*======================================================================
1173 PROCEDURE: ADDSTMTFIELD PUBLIC
1174 PARAMETERS:
1175 COMMENT :
1176 ======================================================================*/
1177 Procedure ADDSTMTFIELD(p_field_code in Varchar2,
1178 p_datatype in Varchar2,
1179 p_value_alias in Varchar2,
1180 p_value_column in Varchar2,
1181 p_value_set_name in Varchar2,
1182 p_query_stmt in out nocopy Varchar2,
1183 p_error IN OUT NOCOPY VARCHAR2,
1184 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1185 ) IS
1186
1187 l_err_num NUMBER;
1188 l_err_msg VARCHAR2(200);
1189 l_date_value_column Varchar2(60);
1190
1191 l_api_name CONSTANT VARCHAR2(30) := 'ADDSTMTFIELD';
1192
1193 Begin
1194
1195 print_debug_log(l_api_name, 'BEGIN- p_field_code = '||p_field_code||'
1196 p_datatype = '||p_datatype||'
1197 p_value_alias = '||p_value_alias||'
1198 p_value_column = '||p_value_column||'
1199 p_value_set_name = '||p_value_set_name||'
1200 p_query_stmt = '||p_query_stmt);
1201
1202 if p_datatype = 'NUMBER' then
1203
1204 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1205 p_value_column || ' AS ' || p_field_code;
1206 elsif p_datatype = 'AMOUNT' then
1207
1208 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1209 p_value_column || ' AS ' || p_field_code;
1210
1211 elsif p_datatype = 'DATE' then
1212
1213 l_date_value_column := p_value_alias || '.' ||p_value_column;
1214
1215 p_query_stmt := p_query_stmt || g_newline || ',' || l_date_value_column || ' AS ' || p_field_code;
1216
1217 p_query_stmt := p_query_stmt || g_newline || ',cursor (select to_char(' || l_date_value_column || ',''YYYY'') AS "YYYY"'
1218 || g_newline || ' ,to_char(' || l_date_value_column || ',''MM'') AS "MM"'
1219 || g_newline || ' ,to_char(' || l_date_value_column || ',''DD'') AS "DD"'
1220 || g_newline || ' from dual) AS ' || p_field_code ||'_NM';
1221
1222
1223 elsif p_datatype = 'DATETIME' then
1224 l_date_value_column := p_value_alias || '.' ||p_value_column;
1225
1226 p_query_stmt := p_query_stmt || g_newline || ',' || l_date_value_column || ' AS ' || p_field_code;
1227
1228 p_query_stmt := p_query_stmt || g_newline || ',cursor (select to_char(' || l_date_value_column || ',''YYYY'') AS "YYYY"'
1229 || g_newline || ' ,to_char(' || l_date_value_column || ',''MM'') AS "MM"'
1230 || g_newline || ' ,to_char(' || l_date_value_column || ',''DD'') AS "DD"'
1231 || g_newline || ' ,to_char(' || l_date_value_column || ',''HH'') AS "HH"'
1232 || g_newline || ' ,to_char(' || l_date_value_column || ',''MI'') AS "MI"'
1233 || g_newline || ' ,to_char(' || l_date_value_column || ',''SS'') AS "SS"'
1234 || g_newline || ' from dual) AS ' || p_field_code ||'_NM';
1235 elsif p_datatype = 'TEXT' then
1236
1237 if p_value_set_name is null then
1238
1239 print_debug_log(l_api_name, ' p_field_code = '||p_field_code||' value set name is null');
1240
1241 p_query_stmt := p_query_stmt || g_newline || ',' || p_value_alias || '.' ||
1242 p_value_column || ' AS ' || p_field_code;
1243
1244 else
1245
1246 print_debug_log(l_api_name, ' p_field_code = '||p_field_code||' calling ADDSTMTVALUESET');
1247
1248 ADDSTMTVALUESET(p_field_code => p_field_code,
1249 p_value_alias =>p_value_alias,
1250 p_value_column => p_value_column,
1251 p_value_set_name =>p_value_set_name,
1252 p_query_stmt => p_query_stmt,
1253 p_error => p_error,
1254 p_result => p_result);
1255
1256 if p_result = 1 then
1257 return;
1258 end if;
1259
1260 end if;
1261
1262 end if;
1263
1264 print_debug_log(l_api_name, 'END p_query_stmt = '||p_query_stmt);
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 p_result := 1;
1269 l_err_num := SQLCODE;
1270 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1271 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
1272 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_UNABLE_TO_ADD_FIELD');
1273
1274 end ADDSTMTFIELD;
1275
1276
1277 /*======================================================================
1278 PROCEDURE: ADDVIEWFORSECTION PUBLIC
1279 PARAMETERS:
1280 COMMENT :
1281 ======================================================================*/
1282 Procedure ADDVIEWFORSECTION(
1283 p_form_id in Number,
1284 p_section_id in Number,
1285 p_LEVEL1_SECTION_ID in Number,
1286 p_LEVEL2_SECTION_ID in Number,
1287 p_section_code in Varchar2,
1288 p_parent_alias in Varchar2,
1289 p_parent_fk_id in Number,
1290 p_query_stmt in out nocopy Varchar2,
1291 p_error IN OUT NOCOPY VARCHAR2,
1292 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1293 ) is
1294
1295 l_value_alias Varchar2(30);
1296
1297 Cursor Section_field is
1298 select rs.form_code repeating_section_code,
1299 ff.TYPE,
1300 ff.FIELD_CODE,
1301 ff.LEVEL1_SECTION_ID,
1302 ff.LEVEL2_SECTION_ID,
1303 ff.repeating_section_id repeating_section_id,
1304 ff.MAPPING_FIELD_VALUE_COLUMN,
1305 f.datatype,
1306 f.value_set_name,
1307 valset.flex_value_set_id value_set_id,
1308 valset.VALIDATION_TYPE
1309 from pon_form_section_compiled ff,
1310 pon_forms_sections rs,
1311 pon_fields f,
1312 fnd_flex_value_sets valset
1313 where ff.form_id = p_section_id
1314 and rs.form_id(+) = ff.repeating_section_id
1315 and ff.enabled ='Y'
1316 and f.field_code(+) = ff.field_code
1317 and f.value_set_name = valset.flex_value_set_name(+)
1318 order by INTERNAL_SEQUENCE_NUMBER;
1319 l_schema_pk_id Number;
1320
1321 l_api_name CONSTANT VARCHAR2(30) := 'ADDVIEWFORSECTION';
1322
1323 Begin
1324
1325 p_result := 0;
1326
1327 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1328 p_section_id = '||p_section_id||'
1329 p_LEVEL1_SECTION_ID = '||p_LEVEL1_SECTION_ID||'
1330 p_LEVEL2_SECTION_ID = '||p_LEVEL2_SECTION_ID||'
1331 p_section_code = '||p_section_code||'
1332 p_parent_alias = '||p_parent_alias||'
1333 p_parent_fk_id = '||p_parent_fk_id||'
1334 g_mode = '||g_mode);
1335
1336 l_value_alias := 'V_'|| to_char(g_alias_counter);
1337 if g_mode = 'SCHEMA' then
1338 l_schema_pk_id := g_alias_counter * -1;
1339 CreateDummyRowForXML(
1340 p_value_pk_id => l_schema_pk_id,
1341 p_form_id => p_form_id,
1342 p_section_id => p_section_id,
1343 p_parent_fk_id => p_parent_fk_id,
1344 p_level1_section_id => p_level1_section_id,
1345 p_level2_section_id => p_level2_section_id,
1346 p_error => p_error,
1347 p_result => p_result);
1348
1349 if p_result = 1 then
1350 return;
1351 end if;
1352 end if;
1353
1354 g_alias_counter := g_alias_counter + 1;
1355
1356 p_query_stmt := p_query_stmt || g_newline || ',Cursor (SELECT ' || l_value_alias || '.' || 'FORM_FIELD_VALUE_ID AS SECTION_PK_ID,'
1357 || g_newline || l_value_alias || '.' || 'PARENT_FIELD_VALUES_FK AS PARENT_FK_ID' ;
1358
1359 print_debug_log(l_api_name, ' p_query_stmt = '||p_query_stmt);
1360
1361 for r1 in Section_field loop
1362
1363 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||'
1364 r1.field_code = '||r1.field_code||'
1365 r1.repeating_section_id = '||r1.repeating_section_id);
1366
1367 if r1.field_code is not null then
1368
1369 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||'
1370 ADDSTMTFIELD : r1.field_code = '||r1.field_code);
1371
1372 ADDSTMTFIELD(p_field_code => r1.field_code,
1373 p_datatype => r1.datatype,
1374 p_value_alias => l_value_alias,
1375 p_value_column => r1.MAPPING_FIELD_VALUE_COLUMN,
1376 p_value_set_name => r1.value_set_name,
1377 p_query_stmt=> p_query_stmt,
1378 p_error => p_error,
1379 p_result => p_result);
1380
1381 if p_result = 1 then
1382 return;
1383 end if;
1384
1385 elsif r1.repeating_section_id is not null then
1386 -- recursively call the ADDVIEWFORSECTION API
1387 ADDVIEWFORSECTION( p_form_id => p_form_id,
1388 p_section_id => r1.repeating_section_id,
1389 p_LEVEL1_SECTION_ID => r1.LEVEL1_SECTION_ID,
1390 p_LEVEL2_SECTION_ID => r1.LEVEL2_SECTION_ID,
1391 p_section_code => r1.repeating_section_code,
1392 p_parent_alias => l_value_alias,
1393 p_parent_fk_id => l_schema_pk_id,
1394 p_query_stmt=> p_query_stmt,
1395 p_error => p_error,
1396 p_result => p_result);
1397
1398 if p_result = 1 then
1399 return;
1400 end if;
1401 end if;
1402 end loop;
1403
1404 p_query_stmt := p_query_stmt || ' from pon_form_field_values ' || l_value_alias
1405 || g_newline || ' where ' || l_value_alias || '.PARENT_FIELD_VALUES_FK=' ||p_parent_alias||'.' ||'FORM_FIELD_VALUE_ID'
1406 || g_newline || ' and ' || l_value_alias || '.section_id=' || to_char(p_section_id)
1407 || g_newline || ' and nvl(' || l_value_alias || '.LEVEL1_SECTION_ID,-1)= ' || to_char(nvl(p_LEVEL1_SECTION_ID,-1))
1408 || g_newline || ' and nvl(' || l_value_alias || '.LEVEL2_SECTION_ID,-1)= ' || to_char(nvl(p_LEVEL2_SECTION_ID,-1))
1409 || g_newline || ' order by ' || l_value_alias || '.FORM_FIELD_VALUE_ID'
1410 || g_newline || ' ) AS ' || p_Section_code;
1411
1412 print_debug_log(l_api_name, ' END p_query_stmt = '||p_query_stmt);
1413
1414 END ADDVIEWFORSECTION;
1415
1416
1417 /*======================================================================
1418 PROCEDURE: GENERATE_XMLQUERY PUBLIC
1419 PARAMETERS:
1420 COMMENT :
1421 ======================================================================*/
1422 Procedure GENERATE_XMLQUERY (p_form_id in Number, -- top form
1423 p_query_stmt in out NOCOPY Varchar2,
1424 p_error IN OUT NOCOPY VARCHAR2,
1425 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1426 ) IS
1427
1428 l_flex_val_alias Varchar2(30);
1429 l_flex_val_tl_alias Varchar2(30);
1430 l_value_column_size number;
1431 l_id_column_size number;
1432 l_tablebasedvalset_query Varchar2(2000);
1433 l_success NUMBER;
1434 L_ID_COLUMN_exists varchar2(10);
1435 l_value_alias Varchar2(30);
1436 l_prev_level1section_code Varchar2(30) := NULL;
1437 l_prev_section_code Varchar2(30) := NULL;
1438 l_form_code Varchar2(30);
1439 l_schema_pk_id Number;
1440
1441 cursor l_form_entry_cursor is
1442 select ff.TYPE,
1443 ts.form_code LEVEL1_SECTION_CODE,
1444 isec.form_code LEVEL2_SECTION_code,
1445 rs.form_code repeating_section_code,
1446 ff.LEVEL1_SECTION_ID LEVEL1_SECTION_ID,
1447 ff.LEVEL2_SECTION_ID LEVEL2_SECTION_ID,
1448 ff.repeating_section_id repeating_section_id,
1449 ff.FIELD_CODE,
1450 ff.MAPPING_FIELD_VALUE_COLUMN,
1451 f.datatype,
1452 f.value_set_name,
1453 f.system_flag,
1454 f.SYSTEM_FIELD_LOV_FLAG,
1455 valset.flex_value_set_id value_set_id,
1456 valset.VALIDATION_TYPE,
1457 ff.EDITABLE --Add for FOB project
1458 from pon_form_section_compiled ff,
1459 PON_FORMS_SECTIONS ts,
1460 PON_FORMS_SECTIONS isec,
1461 PON_FORMS_SECTIONS rs,
1462 pon_fields f,
1463 fnd_flex_value_sets valset
1464 where ts.form_id(+) = ff.LEVEL1_SECTION_ID
1465 and isec.form_id(+) = ff.LEVEL2_SECTION_ID
1466 and rs.form_id(+) = ff.repeating_section_id
1467 and ff.form_id = p_form_id
1468 and ff.enabled ='Y'
1469 and ff.field_code =f.field_code(+)
1470 and f.value_set_name = valset.flex_value_set_name(+)
1471 order by INTERNAL_SEQUENCE_NUMBER;
1472
1473 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XMLQUERY';
1474
1475 begin
1476
1477 p_result := 0;
1478
1479 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1480 p_query_stmt = '||p_query_stmt||'
1481 g_mode = '||g_mode);
1482
1483 g_newline := fnd_global.newline();
1484 g_alias_counter :=1;
1485 l_value_alias := 'V_'|| to_char(g_alias_counter);
1486
1487 print_debug_log(l_api_name, 'l_value_alias = '||l_value_alias||' g_mode = '||g_mode);
1488
1489 if g_mode = 'SCHEMA' then
1490
1491 l_schema_pk_id := g_alias_counter * -1;
1492
1493 print_debug_log(l_api_name, 'l_schema_pk_id = '||l_schema_pk_id);
1494
1495 CreateDummyRowForXML(
1496 p_value_pk_id => l_schema_pk_id,
1497 p_form_id => p_form_id,
1498 p_section_id =>-1,
1499 p_parent_fk_id => NULL,
1500 p_level1_section_id => NULL,
1501 p_level2_section_id => NULL,
1502 p_error => p_error,
1503 p_result => p_result);
1504
1505 if p_result = 1 then
1506 return;
1507 end if;
1508
1509 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');
1510
1511 end if;
1512
1513 g_alias_counter := g_alias_counter + 1;
1514
1515 select form_code
1516 into l_form_code
1517 from PON_FORMS_SECTIONS
1518 where form_id = p_form_id;
1519
1520 print_debug_log(l_api_name, 'l_form_code = '||l_form_code||' g_alias_counter = '||g_alias_counter);
1521
1522 --
1523 p_query_stmt := 'SELECT ' || l_value_alias || '.' || 'FORM_FIELD_VALUE_ID AS FORM_PK_VALUE,'
1524 || g_newline || l_value_alias || '.' || 'OWNING_ENTITY_CODE,'
1525 || g_newline || l_value_alias || '.' || 'ENTITY_PK1';
1526
1527 print_debug_log(l_api_name, 'p_query_stmt = '|| p_query_stmt);
1528 /*Add for FBO project, hard code the form code here for now because user name pass word is not field of the form, but need to contain these data in XML output*/
1529 if l_form_code in ('FED_PRESOL', 'FED_AMD_COMB_SOL', 'FED_COMB_SOL', 'FED_MOD_PRESOL', 'FED_DOC_UPLOAD') then
1530 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || 'FED_CBAC' || ''',v_1.entity_pk1)'
1531 || ' AS ' || 'FED_CBAC' ;
1532 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || 'FED_PASSWORD' || ''',v_1.entity_pk1)'
1533 || ' AS ' || 'FED_PASSWORD' ;
1534 end if;
1535
1536 for r1 in l_form_entry_cursor loop
1537
1538 print_debug_log(l_api_name, 'r1.TYPE = '||r1.TYPE ||'
1539 r1.LEVEL1_SECTION_CODE = '||r1.LEVEL1_SECTION_CODE ||'
1540 r1.LEVEL2_SECTION_code = '||r1.LEVEL2_SECTION_code ||'
1541 r1.repeating_section_code = '||r1.repeating_section_code ||'
1542 r1.LEVEL1_SECTION_ID = '||r1.LEVEL1_SECTION_ID ||'
1543 r1.LEVEL2_SECTION_ID = '||r1.LEVEL2_SECTION_ID ||'
1544 r1.repeating_section_id = '||r1.repeating_section_id ||'
1545 r1.FIELD_CODE = '||r1.FIELD_CODE ||'
1546 r1.MAPPING_FIELD_VALUE_COLUMN = '||r1.MAPPING_FIELD_VALUE_COLUMN ||'
1547 r1.datatype = '||r1.datatype ||'
1548 r1.value_set_name = '||r1.value_set_name ||'
1549 r1.system_flag = '||r1.system_flag ||'
1550 r1.SYSTEM_FIELD_LOV_FLAG = '||r1.SYSTEM_FIELD_LOV_FLAG ||'
1551 r1.value_set_id = '||r1.value_set_id ||'
1552 r1.VALIDATION_TYPE = '||r1.VALIDATION_TYPE||'
1553 l_prev_level1section_code = '||l_prev_level1section_code ||'
1554 l_prev_section_code = '||l_prev_section_code);
1555
1556
1557 if nvl(r1.LEVEL1_SECTION_CODE,'#x#') <> nvl(l_prev_level1section_code,'#x#') then
1558
1559 if nvl(r1.LEVEL2_SECTION_code,'#x#') <> nvl(l_prev_section_code,'#x#') then
1560
1561 if l_prev_section_code is not null then
1562
1563 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1564
1565 end if;
1566
1567 l_prev_section_code := r1.LEVEL2_SECTION_code;
1568
1569 end if;
1570
1571 if l_prev_level1section_code is not null then
1572 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_level1section_code || ' ';
1573 end if;
1574
1575 if r1.LEVEL1_SECTION_CODE is not null then
1576 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1577 end if;
1578
1579 if r1.LEVEL2_SECTION_code is not null then
1580 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1581 end if;
1582
1583 l_prev_level1section_code := r1.LEVEL1_SECTION_CODE;
1584
1585 print_debug_log(l_api_name, 'First if : p_query_stmt = '||p_query_stmt);
1586
1587 end if;
1588
1589 if nvl(r1.LEVEL2_SECTION_code,'#x#') <> nvl(l_prev_section_code,'#x#') then
1590
1591 print_debug_log(l_api_name, 'r1.LEVEL2_SECTION_code = '||r1.LEVEL2_SECTION_code||'
1592 l_prev_section_code = '|| l_prev_section_code);
1593
1594 if l_prev_section_code is not null then
1595
1596 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1597
1598 end if;
1599
1600 l_prev_section_code := r1.LEVEL2_SECTION_code;
1601
1602 if r1.LEVEL2_SECTION_code is not null then
1603
1604 p_query_stmt := p_query_stmt || g_newline || ' ,cursor (select 1 AS row_num';
1605
1606 end if;
1607
1608 print_debug_log(l_api_name, 'Second if : p_query_stmt = '||p_query_stmt);
1609 end if;
1610
1611
1612
1613 if r1.repeating_section_code is not null then
1614
1615 print_debug_log(l_api_name, ' Calling ADDVIEWFORSECTION for p_form_id = '|| p_form_id ||'
1616 r1.repeating_section_id = '|| r1.repeating_section_id ||'
1617 r1.LEVEL1_SECTION_ID = '||r1.LEVEL1_SECTION_ID ||'
1618 r1.LEVEL2_SECTION_ID = '|| r1.LEVEL2_SECTION_ID ||'
1619 r1.repeating_section_code = '||r1.repeating_section_code );
1620
1621 ADDVIEWFORSECTION(p_form_id => p_form_id,
1622 p_section_id => r1.repeating_section_id,
1623 p_LEVEL1_SECTION_ID => r1.LEVEL1_SECTION_ID,
1624 p_LEVEL2_SECTION_ID => r1.LEVEL2_SECTION_ID,
1625 p_section_code => r1.repeating_section_code,
1626 p_parent_alias => l_value_alias,
1627 p_parent_fk_id => l_schema_pk_id,
1628 p_query_stmt=> p_query_stmt,
1629 p_error => p_error,
1630 p_result => p_result);
1631
1632 if p_result = 1 then
1633 return;
1634 end if;
1635
1636 print_debug_log(l_api_name, 'Third if : p_query_stmt = '||p_query_stmt);
1637
1638 end if;
1639
1640 if r1.field_code is not null then
1641 /*Modified for FBO project*/
1642 if r1.system_flag = 'Y' and NVL(r1.EDITABLE, 'N') = 'N' then
1643
1644 if r1.SYSTEM_FIELD_LOV_FLAG ='Y' then
1645
1646 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || r1.field_code || '_NAME'',v_1.entity_pk1)'
1647 || ' AS ' || r1.field_code || '_NAME';
1648
1649 end if;
1650
1651 if r1.datatype in ('DATE','DATETIME') then
1652
1653 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMDate(''' || r1.field_code || ''',v_1.entity_pk1)'
1654 || ' AS ' || r1.field_code ;
1655
1656 elsif r1.datatype in ( 'NUMBER','AMOUNT') then
1657
1658 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMNumber(''' || r1.field_code || ''',v_1.entity_pk1)'
1659 || ' AS ' || r1.field_code ;
1660
1661 else
1662 p_query_stmt := p_query_stmt || g_newline || ',PON_FORMS_UTIL_PVT.GetSYSTEMChar(''' || r1.field_code || ''',v_1.entity_pk1)'
1663 || ' AS ' || r1.field_code ;
1664
1665 end if;
1666
1667 print_debug_log(l_api_name, 'Fourth if : p_query_stmt = '||p_query_stmt);
1668
1669 else
1670
1671 print_debug_log(l_api_name, ' Calling ADDSTMTFIELD for r1.field_code = '|| r1.field_code);
1672
1673 ADDSTMTFIELD(p_field_code => r1.field_code,
1674 p_datatype => r1.datatype,
1675 p_value_alias => l_value_alias,
1676 p_value_column => r1.MAPPING_FIELD_VALUE_COLUMN,
1677 p_value_set_name => r1.value_set_name,
1678 p_query_stmt=> p_query_stmt,
1679 p_error => p_error,
1680 p_result => p_result);
1681
1682 if p_result = 1 then
1683 return;
1684 end if;
1685
1686 print_debug_log(l_api_name, 'Fifth if : p_query_stmt = '||p_query_stmt);
1687 end if;
1688
1689 end if; -- Field Code Not null
1690
1691 end loop;
1692
1693 print_debug_log(l_api_name, 'l_prev_section_code = '||l_prev_section_code||'
1694 l_prev_level1section_code = '||l_prev_level1section_code);
1695
1696 if l_prev_section_code is not null then
1697
1698 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_section_code || ' ';
1699
1700 end if;
1701
1702 if l_prev_level1section_code is not null then
1703
1704 p_query_stmt := p_query_stmt || g_newline || ' from dual ) as ' || l_prev_level1section_code || ' ';
1705
1706 end if;
1707
1708 print_debug_log(l_api_name, 'Last if : p_query_stmt = '||p_query_stmt);
1709
1710 -- from and where clause for the form
1711 p_query_stmt := p_query_stmt || g_newline || ' from pon_form_field_values ' || l_value_alias;
1712
1713 p_query_stmt := p_query_stmt || g_newline || ' where ' || l_value_alias ||'.OWNING_ENTITY_CODE =:ENTITY_CODE'
1714 || g_newline || ' and ' || l_value_alias ||'.ENTITY_PK1 =:ENTITY_PK1'
1715 || g_newline || ' and ' || l_value_alias ||'.form_id =' || to_char(p_form_id)
1716 || g_newline || ' and ' || l_value_alias ||'.section_id =-1' || g_newline;
1717
1718 print_debug_log(l_api_name, 'END = '||p_query_stmt);
1719
1720
1721 End GENERATE_XMLQUERY;
1722
1723
1724 /*======================================================================
1725 PROCEDURE: GENERATE_XMLSCHEMA PUBLIC
1726 PARAMETERS:
1727 COMMENT :
1728 ======================================================================*/
1729 Procedure GENERATE_XMLSCHEMA (p_form_id in Number, -- top form
1730 p_schema OUT NOCOPY CLOB,
1731 p_error IN OUT NOCOPY VARCHAR2,
1732 p_result IN OUT NOCOPY number, -- 0: Success, 1: failure
1733 x_xml_query OUT NOCOPY VARCHAR2 -- The xml query
1734 ) IS
1735
1736 l_err_num NUMBER;
1737 l_err_msg VARCHAR2(200);
1738 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">
1739 <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
1740 <xsl:template match="/">
1741 <xsl:apply-templates select="DOCUMENT/xsd:schema"/>
1742 </xsl:template>
1743 <xsl:template match="xsd:schema">
1744 <xsl:copy-of select="."/>
1745 </xsl:template>
1746 </xsl:stylesheet>';
1747
1748 l_xml_query Varchar2(31500);
1749 l_form_code Varchar2(30);
1750 l_queryCtx DBMS_XMLquery.ctxType;
1751
1752 l_schemOffset Number;
1753 l_xmlTagOffset INTEGER;
1754 l_documentOffset BINARY_INTEGER;
1755 l_documentTagLength BINARY_INTEGER;
1756 l_blankTextForDocumentTag VARCHAR2(100);
1757 l_count NUMBER;
1758
1759 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XMLSCHEMA';
1760
1761 Begin
1762
1763 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
1764
1765 p_result := 0;
1766
1767 SAVEPOINT PON_XMLSCHEMA;
1768 g_mode := 'SCHEMA';
1769 select form_code
1770 into l_form_code
1771 from pon_forms_sections
1772 where form_id = p_form_id;
1773
1774 print_debug_log(l_api_name, 'g_mode = '||g_mode||' l_form_code = '||l_form_code);
1775
1776 GENERATE_XMLQUERY (p_form_id,
1777 l_xml_query,
1778 p_error,
1779 p_result);
1780
1781 if p_result = 1 then
1782 return;
1783 end if;
1784
1785 x_xml_query := l_xml_query;
1786
1787 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' GOT QUERY FORM SCHEMA ');
1788
1789 l_queryCtx := DBMS_XMLquery.newContext(l_xml_query);
1790 DBMS_XMLQuery.setDateFormat(l_queryCtx,'dd/mm/yyyy HH:mm:ss'); -- sets the row tag name
1791 DBMS_XMLQuery.setRowTag(l_queryCtx,l_form_code || '_ROW'); -- sets the row tag name
1792 DBMS_XMLQuery.setRowSetTag(l_queryCtx,l_form_code);
1793 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_CODE','XML_SCHEMA_GENERATION');
1794 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_PK1',g_dummy_pk);
1795 -- DBMS_XMLquery.setXSLT(l_queryCtx,ldoc );
1796
1797 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' STARTING SCHEMA GENERATION');
1798
1799 p_schema:=DBMS_XMLquery.GETXML(l_queryCtx,2);
1800
1801 print_debug_log(l_api_name, 'p_form_id = '||p_form_id||' GOT SCHEMA ');
1802
1803 g_mode := 'XML';
1804
1805 rollback to SAVEPOINT PON_XMLSCHEMA;
1806
1807 DBMS_XMLQUERY.closecontext(l_queryCtx);
1808
1809 l_schemOffset := DBMS_LOB.INSTR(p_schema,'</xsd:schema>')+12;
1810 DBMS_LOB.TRIM(p_schema,l_schemOffset);
1811
1812 --The code below removed the <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1813 -- from the xsd
1814
1815 -- Get the length of tag for <?xml version = '1.0'?>
1816 l_xmlTagOffset := DBMS_LOB.INSTR(p_schema,'>');
1817
1818 -- Get the position where <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1819 -- ends
1820 l_documentOffset := DBMS_LOB.INSTR(p_schema,'>',1,2);
1821
1822 -- length of <DOCUMENT xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
1823 l_documentTagLength := l_documentOffset-l_xmlTagOffset;
1824 l_blankTextForDocumentTag := '';
1825 l_count := 0;
1826
1827 loop
1828 exit when l_count > l_documentTagLength;
1829 l_blankTextForDocumentTag := l_blankTextForDocumentTag ||' ';
1830 l_count := l_count+1;
1831 end loop;
1832
1833 dbms_lob.write(p_schema,l_documentTagLength,l_xmlTagOffset+1,l_blankTextForDocumentTag);
1834
1835 print_debug_log(l_api_name, 'END ');
1836
1837 exception
1838 when others then
1839 l_err_num := SQLCODE;
1840 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1841 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);
1842 p_result := 1;
1843 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_SCHEMA_GENERATION_FAIL');
1844 DBMS_XMLquery.closecontext (l_queryCtx);
1845
1846 End GENERATE_XMLSCHEMA;
1847
1848
1849
1850 /*======================================================================
1851 PROCEDURE: GENERATE_XML PUBLIC
1852 PARAMETERS:
1853 COMMENT :
1854 ======================================================================*/
1855 Procedure GENERATE_XML(p_form_id in Number, -- top form
1856 p_entity_code Varchar2,
1857 p_entity_pk1 Varchar2,
1858 p_xml OUT NOCOPY CLOB,
1859 p_xdo_stylesheet_code OUT NOCOPY VARCHAR2,
1860 p_error OUT NOCOPY VARCHAR2,
1861 p_result OUT NOCOPY number -- 0: Success, 1: failure
1862 ) IS
1863 l_err_num NUMBER;
1864 l_err_msg VARCHAR2(200);
1865 l_xml_query Varchar2(31500);
1866 l_form_code Varchar2(30);
1867 l_queryCtx DBMS_XMLquery.ctxType;
1868
1869 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XML';
1870
1871 Begin
1872
1873 p_result := 0;
1874
1875 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
1876 p_entity_code = '||p_entity_code||'
1877 p_entity_pk1 = '||p_entity_pk1);
1878
1879 g_mode := 'XML';
1880
1881 select
1882 xdo_stylesheet_code,
1883 form_code
1884 into
1885 p_xdo_stylesheet_code,
1886 l_form_code
1887 from pon_forms_sections
1888 where form_id = p_form_id;
1889
1890
1891 GENERATE_XMLQUERY(p_form_id,
1892 l_xml_query,
1893 p_error,
1894 p_result);
1895
1896 if p_result = 1 then
1897 return;
1898 end if;
1899
1900 print_debug_log(l_api_name, 'Got Xml Query for GENERATE_XML p_form_id = '||p_form_id);
1901
1902 l_queryCtx := DBMS_XMLquery.newContext(l_xml_query);
1903 DBMS_XMLQuery.setDateFormat(l_queryCtx,'dd/mm/yyyy HH:mm:ss'); -- sets the row tag name
1904 DBMS_XMLQuery.setRowTag(l_queryCtx,l_form_code || '_ROW'); -- sets the row tag name
1905 DBMS_XMLQuery.setRowSetTag(l_queryCtx,l_form_code);
1906 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_CODE',p_entity_code);
1907 DBMS_XMLQuery.setBindValue(l_queryCtx,'ENTITY_PK1',p_entity_pk1);
1908
1909 print_debug_log(l_api_name, 'All values bound for GENERATE_XML p_form_id = '||p_form_id);
1910
1911 p_xml:=DBMS_XMLquery.GETXML(l_queryCtx, 0);
1912 DBMS_XMLQUERY.closecontext(l_queryCtx);
1913
1914 exception
1915 when others then
1916 l_err_num := SQLCODE;
1917 l_err_msg := SUBSTR(SQLERRM, 1, 200);
1918 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);
1919 p_result := 1;
1920 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_XML_GENERATION_FAIL');
1921 DBMS_XMLquery.closecontext (l_queryCtx);
1922
1923 End GENERATE_XML;
1924
1925
1926
1927 /*======================================================================
1928 PROCEDURE: CreateDummyRowForXML PRIVATE
1929 PARAMETERS:
1930 COMMENT :
1931 ======================================================================*/
1932 Procedure CreateDummyRowForXML(
1933 p_value_pk_id Number,
1934 p_form_id number,
1935 p_section_id Number,
1936 p_parent_fk_id Number,
1937 p_level1_section_id Number,
1938 p_level2_section_id Number,
1939 p_error IN OUT NOCOPY VARCHAR2,
1940 p_result IN OUT NOCOPY number -- 0: Success, 1: failure
1941 ) is
1942 l_err_num NUMBER;
1943 l_err_msg VARCHAR2(200);
1944 l_api_name CONSTANT VARCHAR2(30) := 'CreateDummyRowForXML';
1945
1946 Begin
1947
1948 p_result := 0;
1949
1950 print_debug_log(l_api_name, 'BEGIN- p_value_pk_id = '||p_value_pk_id||'
1951 p_form_id = '||p_form_id||'
1952 p_section_id = '||p_section_id||'
1953 p_parent_fk_id = '||p_parent_fk_id||'
1954 p_level1_section_id = '||p_level1_section_id||'
1955 p_level2_section_id = '||p_level2_section_id||'
1956 g_dummy_pk = '||g_dummy_pk||'
1957 g_dummy_char = '||g_dummy_char||'
1958 g_dummy_num = '||g_dummy_num);
1959
1960 insert into pon_form_field_values
1961 (FORM_FIELD_VALUE_ID,
1962 FORM_ID,
1963 OWNING_ENTITY_CODE,
1964 ENTITY_PK1,
1965 SECTION_ID,
1966 PARENT_FIELD_VALUES_FK,
1967 TEXTCOL1,
1968 TEXTCOL2,
1969 TEXTCOL3,
1970 TEXTCOL4,
1971 TEXTCOL5,
1972 TEXTCOL6,
1973 TEXTCOL7,
1974 TEXTCOL8,
1975 TEXTCOL9,
1976 TEXTCOL10,
1977 TEXTCOL11,
1978 TEXTCOL12,
1979 TEXTCOL13,
1980 TEXTCOL14,
1981 TEXTCOL15,
1982 TEXTCOL16,
1983 TEXTCOL17,
1984 TEXTCOL18,
1985 TEXTCOL19,
1986 TEXTCOL20,
1987 TEXTCOL21,
1988 TEXTCOL22,
1989 TEXTCOL23,
1990 TEXTCOL24,
1991 TEXTCOL25,
1992 TEXTCOL26,
1993 TEXTCOL27,
1994 TEXTCOL28,
1995 TEXTCOL29,
1996 TEXTCOL30,
1997 TEXTCOL31,
1998 TEXTCOL32,
1999 TEXTCOL33,
2000 TEXTCOL34,
2001 TEXTCOL35,
2002 TEXTCOL36,
2003 TEXTCOL37,
2004 TEXTCOL38,
2005 TEXTCOL39,
2006 TEXTCOL40,
2007 TEXTCOL41,
2008 TEXTCOL42,
2009 TEXTCOL43,
2010 TEXTCOL44,
2011 TEXTCOL45,
2012 TEXTCOL46,
2013 TEXTCOL47,
2014 TEXTCOL48,
2015 TEXTCOL49,
2016 TEXTCOL50,
2017 TEXTCOL51,
2018 TEXTCOL52,
2019 TEXTCOL53,
2020 TEXTCOL54,
2021 TEXTCOL55,
2022 TEXTCOL56,
2023 TEXTCOL57,
2024 TEXTCOL58,
2025 TEXTCOL59,
2026 TEXTCOL60,
2027 TEXTCOL61,
2028 TEXTCOL62,
2029 TEXTCOL63,
2030 TEXTCOL64,
2031 TEXTCOL65,
2032 TEXTCOL66,
2033 TEXTCOL67,
2034 TEXTCOL68,
2035 TEXTCOL69,
2036 TEXTCOL70,
2037 TEXTCOL71,
2038 TEXTCOL72,
2039 TEXTCOL73,
2040 TEXTCOL74,
2041 TEXTCOL75,
2042 TEXTCOL76,
2043 TEXTCOL77,
2044 TEXTCOL78,
2045 TEXTCOL79,
2046 TEXTCOL80,
2047 TEXTCOL81,
2048 TEXTCOL82,
2049 TEXTCOL83,
2050 TEXTCOL84,
2051 TEXTCOL85,
2052 TEXTCOL86,
2053 TEXTCOL87,
2054 TEXTCOL88,
2055 TEXTCOL89,
2056 TEXTCOL90,
2057 TEXTCOL91,
2058 TEXTCOL92,
2059 TEXTCOL93,
2060 TEXTCOL94,
2061 TEXTCOL95,
2062 TEXTCOL96,
2063 TEXTCOL97,
2064 TEXTCOL98,
2065 TEXTCOL99,
2066 TEXTCOL100,
2067 TEXTCOL101,
2068 TEXTCOL102,
2069 TEXTCOL103,
2070 TEXTCOL104,
2071 TEXTCOL105,
2072 TEXTCOL106,
2073 TEXTCOL107,
2074 TEXTCOL108,
2075 TEXTCOL109,
2076 TEXTCOL110,
2077 TEXTCOL111,
2078 TEXTCOL112,
2079 TEXTCOL113,
2080 TEXTCOL114,
2081 TEXTCOL115,
2082 TEXTCOL116,
2083 TEXTCOL117,
2084 TEXTCOL118,
2085 TEXTCOL119,
2086 TEXTCOL120,
2087 TEXTCOL121,
2088 TEXTCOL122,
2089 TEXTCOL123,
2090 TEXTCOL124,
2091 TEXTCOL125,
2092 TEXTCOL126,
2093 TEXTCOL127,
2094 TEXTCOL128,
2095 TEXTCOL129,
2096 TEXTCOL130,
2097 TEXTCOL131,
2098 TEXTCOL132,
2099 TEXTCOL133,
2100 TEXTCOL134,
2101 TEXTCOL135,
2102 TEXTCOL136,
2103 TEXTCOL137,
2104 TEXTCOL138,
2105 TEXTCOL139,
2106 TEXTCOL140,
2107 TEXTCOL141,
2108 TEXTCOL142,
2109 TEXTCOL143,
2110 TEXTCOL144,
2111 TEXTCOL145,
2112 TEXTCOL146,
2113 TEXTCOL147,
2114 TEXTCOL148,
2115 TEXTCOL149,
2116 TEXTCOL150,
2117 TEXTCOL151,
2118 TEXTCOL152,
2119 TEXTCOL153,
2120 TEXTCOL154,
2121 TEXTCOL155,
2122 TEXTCOL156,
2123 TEXTCOL157,
2124 TEXTCOL158,
2125 TEXTCOL159,
2126 TEXTCOL160,
2127 TEXTCOL161,
2128 TEXTCOL162,
2129 TEXTCOL163,
2130 TEXTCOL164,
2131 TEXTCOL165,
2132 TEXTCOL166,
2133 TEXTCOL167,
2134 TEXTCOL168,
2135 TEXTCOL169,
2136 TEXTCOL170,
2137 TEXTCOL171,
2138 TEXTCOL172,
2139 TEXTCOL173,
2140 TEXTCOL174,
2141 TEXTCOL175,
2142 TEXTCOL176,
2143 TEXTCOL177,
2144 TEXTCOL178,
2145 TEXTCOL179,
2146 TEXTCOL180,
2147 TEXTCOL181,
2148 TEXTCOL182,
2149 TEXTCOL183,
2150 TEXTCOL184,
2151 TEXTCOL185,
2152 TEXTCOL186,
2153 TEXTCOL187,
2154 TEXTCOL188,
2155 TEXTCOL189,
2156 TEXTCOL190,
2157 TEXTCOL191,
2158 TEXTCOL192,
2159 TEXTCOL193,
2160 TEXTCOL194,
2161 TEXTCOL195,
2162 TEXTCOL196,
2163 TEXTCOL197,
2164 TEXTCOL198,
2165 TEXTCOL199,
2166 TEXTCOL200,
2167 TEXTCOL201,
2168 TEXTCOL202,
2169 TEXTCOL203,
2170 TEXTCOL204,
2171 TEXTCOL205,
2172 TEXTCOL206,
2173 TEXTCOL207,
2174 TEXTCOL208,
2175 TEXTCOL209,
2176 TEXTCOL210,
2177 TEXTCOL211,
2178 TEXTCOL212,
2179 TEXTCOL213,
2180 TEXTCOL214,
2181 TEXTCOL215,
2182 TEXTCOL216,
2183 TEXTCOL217,
2184 TEXTCOL218,
2185 TEXTCOL219,
2186 TEXTCOL220,
2187 TEXTCOL221,
2188 TEXTCOL222,
2189 TEXTCOL223,
2190 TEXTCOL224,
2191 TEXTCOL225,
2192 TEXTCOL226,
2193 TEXTCOL227,
2194 TEXTCOL228,
2195 TEXTCOL229,
2196 TEXTCOL230,
2197 TEXTCOL231,
2198 TEXTCOL232,
2199 TEXTCOL233,
2200 TEXTCOL234,
2201 TEXTCOL235,
2202 TEXTCOL236,
2203 TEXTCOL237,
2204 TEXTCOL238,
2205 TEXTCOL239,
2206 TEXTCOL240,
2207 TEXTCOL241,
2208 TEXTCOL242,
2209 TEXTCOL243,
2210 TEXTCOL244,
2211 TEXTCOL245,
2212 TEXTCOL246,
2213 TEXTCOL247,
2214 TEXTCOL248,
2215 TEXTCOL249,
2216 TEXTCOL250,
2217 DATECOL1,
2218 DATECOL2,
2219 DATECOL3,
2220 DATECOL4,
2221 DATECOL5,
2222 DATECOL6,
2223 DATECOL7,
2224 DATECOL8,
2225 DATECOL9,
2226 DATECOL10,
2227 DATECOL11,
2228 DATECOL12,
2229 DATECOL13,
2230 DATECOL14,
2231 DATECOL15,
2232 DATECOL16,
2233 DATECOL17,
2234 DATECOL18,
2235 DATECOL19,
2236 DATECOL20,
2237 DATECOL21,
2238 DATECOL22,
2239 DATECOL23,
2240 DATECOL24,
2241 DATECOL25,
2242 DATECOL26,
2243 DATECOL27,
2244 DATECOL28,
2245 DATECOL29,
2246 DATECOL30,
2247 DATECOL31,
2248 DATECOL32,
2249 DATECOL33,
2250 DATECOL34,
2251 DATECOL35,
2252 DATECOL36,
2253 DATECOL37,
2254 DATECOL38,
2255 DATECOL39,
2256 DATECOL40,
2257 DATECOL41,
2258 DATECOL42,
2259 DATECOL43,
2260 DATECOL44,
2261 DATECOL45,
2262 DATECOL46,
2263 DATECOL47,
2264 DATECOL48,
2265 DATECOL49,
2266 DATECOL50,
2267 NUMBERCOL1,
2268 NUMBERCOL2,
2269 NUMBERCOL3,
2270 NUMBERCOL4,
2271 NUMBERCOL5,
2272 NUMBERCOL6,
2273 NUMBERCOL7,
2274 NUMBERCOL8,
2275 NUMBERCOL9,
2276 NUMBERCOL10,
2277 NUMBERCOL11,
2278 NUMBERCOL12,
2279 NUMBERCOL13,
2280 NUMBERCOL14,
2281 NUMBERCOL15,
2282 NUMBERCOL16,
2283 NUMBERCOL17,
2284 NUMBERCOL18,
2285 NUMBERCOL19,
2286 NUMBERCOL20,
2287 NUMBERCOL21,
2288 NUMBERCOL22,
2289 NUMBERCOL23,
2290 NUMBERCOL24,
2291 NUMBERCOL25,
2292 NUMBERCOL26,
2293 NUMBERCOL27,
2294 NUMBERCOL28,
2295 NUMBERCOL29,
2296 NUMBERCOL30,
2297 NUMBERCOL31,
2298 NUMBERCOL32,
2299 NUMBERCOL33,
2300 NUMBERCOL34,
2301 NUMBERCOL35,
2302 NUMBERCOL36,
2303 NUMBERCOL37,
2304 NUMBERCOL38,
2305 NUMBERCOL39,
2306 NUMBERCOL40,
2307 NUMBERCOL41,
2308 NUMBERCOL42,
2309 NUMBERCOL43,
2310 NUMBERCOL44,
2311 NUMBERCOL45,
2312 NUMBERCOL46,
2313 NUMBERCOL47,
2314 NUMBERCOL48,
2315 NUMBERCOL49,
2316 NUMBERCOL50,
2317 CREATION_DATE,
2318 CREATED_BY,
2319 LAST_UPDATE_DATE,
2320 LAST_UPDATED_BY,
2321 LAST_UPDATE_LOGIN,
2322 LEVEL1_SECTION_ID,
2323 LEVEL2_SECTION_ID)
2324 values(
2325 p_value_pk_id
2326 ,p_FORM_ID
2327 ,'XML_SCHEMA_GENERATION'
2328 , g_dummy_pk
2329 ,p_SECTION_ID
2330 ,p_parent_fk_id
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 ,g_dummy_char
2518 ,g_dummy_char
2519 ,g_dummy_char
2520 ,g_dummy_char
2521 ,g_dummy_char
2522 ,g_dummy_char
2523 ,g_dummy_char
2524 ,g_dummy_char
2525 ,g_dummy_char
2526 ,g_dummy_char
2527 ,g_dummy_char
2528 ,g_dummy_char
2529 ,g_dummy_char
2530 ,g_dummy_char
2531 ,g_dummy_char
2532 ,g_dummy_char
2533 ,g_dummy_char
2534 ,g_dummy_char
2535 ,g_dummy_char
2536 ,g_dummy_char
2537 ,g_dummy_char
2538 ,g_dummy_char
2539 ,g_dummy_char
2540 ,g_dummy_char
2541 ,g_dummy_char
2542 ,g_dummy_char
2543 ,g_dummy_char
2544 ,g_dummy_char
2545 ,g_dummy_char
2546 ,g_dummy_char
2547 ,g_dummy_char
2548 ,g_dummy_char
2549 ,g_dummy_char
2550 ,g_dummy_char
2551 ,g_dummy_char
2552 ,g_dummy_char
2553 ,g_dummy_char
2554 ,g_dummy_char
2555 ,g_dummy_char
2556 ,g_dummy_char
2557 ,g_dummy_char
2558 ,g_dummy_char
2559 ,g_dummy_char
2560 ,g_dummy_char
2561 ,g_dummy_char
2562 ,g_dummy_char
2563 ,g_dummy_char
2564 ,g_dummy_char
2565 ,g_dummy_char
2566 ,g_dummy_char
2567 ,g_dummy_char
2568 ,g_dummy_char
2569 ,g_dummy_char
2570 ,g_dummy_char
2571 ,g_dummy_char
2572 ,g_dummy_char
2573 ,g_dummy_char
2574 ,g_dummy_char
2575 ,g_dummy_char
2576 ,g_dummy_char
2577 ,g_dummy_char
2578 ,g_dummy_char
2579 ,g_dummy_char
2580 ,g_dummy_char
2581 ,sysdate -200
2582 ,sysdate -200
2583 ,sysdate -200
2584 ,sysdate -200
2585 ,sysdate -200
2586 ,sysdate -200
2587 ,sysdate -200
2588 ,sysdate -200
2589 ,sysdate -200
2590 ,sysdate -200
2591 ,sysdate -200
2592 ,sysdate -200
2593 ,sysdate -200
2594 ,sysdate -200
2595 ,sysdate -200
2596 ,sysdate -200
2597 ,sysdate -200
2598 ,sysdate -200
2599 ,sysdate -200
2600 ,sysdate -200
2601 ,sysdate -200
2602 ,sysdate -200
2603 ,sysdate -200
2604 ,sysdate -200
2605 ,sysdate -200
2606 ,sysdate -200
2607 ,sysdate -200
2608 ,sysdate -200
2609 ,sysdate -200
2610 ,sysdate -200
2611 ,sysdate -200
2612 ,sysdate -200
2613 ,sysdate -200
2614 ,sysdate -200
2615 ,sysdate -200
2616 ,sysdate -200
2617 ,sysdate -200
2618 ,sysdate -200
2619 ,sysdate -200
2620 ,sysdate -200
2621 ,sysdate -200
2622 ,sysdate -200
2623 ,sysdate -200
2624 ,sysdate -200
2625 ,sysdate -200
2626 ,sysdate -200
2627 ,sysdate -200
2628 ,sysdate -200
2629 ,sysdate -200
2630 ,sysdate -200
2631 ,g_dummy_num
2632 ,g_dummy_num
2633 ,g_dummy_num
2634 ,g_dummy_num
2635 ,g_dummy_num
2636 ,g_dummy_num
2637 ,g_dummy_num
2638 ,g_dummy_num
2639 ,g_dummy_num
2640 ,g_dummy_num
2641 ,g_dummy_num
2642 ,g_dummy_num
2643 ,g_dummy_num
2644 ,g_dummy_num
2645 ,g_dummy_num
2646 ,g_dummy_num
2647 ,g_dummy_num
2648 ,g_dummy_num
2649 ,g_dummy_num
2650 ,g_dummy_num
2651 ,g_dummy_num
2652 ,g_dummy_num
2653 ,g_dummy_num
2654 ,g_dummy_num
2655 ,g_dummy_num
2656 ,g_dummy_num
2657 ,g_dummy_num
2658 ,g_dummy_num
2659 ,g_dummy_num
2660 ,g_dummy_num
2661 ,g_dummy_num
2662 ,g_dummy_num
2663 ,g_dummy_num
2664 ,g_dummy_num
2665 ,g_dummy_num
2666 ,g_dummy_num
2667 ,g_dummy_num
2668 ,g_dummy_num
2669 ,g_dummy_num
2670 ,g_dummy_num
2671 ,g_dummy_num
2672 ,g_dummy_num
2673 ,g_dummy_num
2674 ,g_dummy_num
2675 ,g_dummy_num
2676 ,g_dummy_num
2677 ,g_dummy_num
2678 ,g_dummy_num
2679 ,g_dummy_num
2680 ,g_dummy_num
2681 ,sysdate
2682 ,0
2683 ,sysdate
2684 ,0
2685 ,0
2686 ,p_level1_section_id
2687 ,p_level2_section_id);
2688
2689 print_debug_log(l_api_name, 'END '||l_api_name);
2690
2691 exception
2692 when others then
2693 l_err_num := SQLCODE;
2694 l_err_msg := SUBSTR(SQLERRM, 1, 200);
2695 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);
2696 p_result := 1;
2697 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_CREATE_DUMMY_ROW_ERROR');
2698
2699 End CreateDummyRowForXML;
2700
2701
2702
2703 /*======================================================================
2704 PROCEDURE: COMPILE_FORM PUBLIC
2705 PARAMETERS:
2706 COMMENT :
2707 ======================================================================*/
2708 PROCEDURE COMPILE_FORM(p_form_id IN NUMBER) IS
2709
2710 x_section_id NUMBER;
2711
2712 x_is_repeating_section_flag VARCHAR2(1);
2713
2714 x_form_section_field_id NUMBER;
2715
2716 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
2717
2718 CURSOR c1_form_section_fields IS
2719 select
2720 ff.FORM_SECTION_FIELD_ID,
2721 ff.FORM_ID,
2722 ff.TYPE,
2723 ff.FIELD_CODE,
2724 ff.SEQUENCE_NUMBER,
2725 ff.REQUIRED,
2726 ff.SECTION_ID,
2727 ff.DISPLAY_ON_MAIN_PAGE,
2728 ff.ENABLED,
2729 f.datatype,
2730 f.system_flag,
2731 ff.EDITABLE
2732 from pon_form_section_fields ff,
2733 pon_fields f
2734 where form_id = p_form_id
2735 and f.field_code(+) = ff.field_code
2736 order by sequence_number;
2737
2738
2739 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_FORM';
2740
2741 begin
2742
2743 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
2744
2745 g_date_sequence_number := 0;
2746
2747 g_number_sequence_number := 0;
2748
2749 g_text_sequence_number := 0;
2750
2751 g_internal_sequence_number := 10;
2752
2753 Delete pon_form_section_compiled
2754 where form_id = p_form_id;
2755 -- Inser a row for the form
2756 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => null,
2757 p_form_id => p_form_id,
2758 p_type => 'FORM',
2759 p_field_code => null,
2760 p_INTERNAL_SEQUENCE_NUMBER =>0,
2761 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2762 p_REQUIRED => null,
2763 p_LEVEL1_SECTION_ID => null,
2764 p_LEVEL2_SECTION_ID => null,
2765 p_REPEATING_SECTION_ID => null,
2766 p_DISPLAY_ON_MAIN_PAGE =>null,
2767 p_ENABLED =>'Y',
2768 p_EDITABLE => null);
2769
2770 for form_section_compiled in c1_form_section_fields LOOP
2771
2772 if form_section_compiled.TYPE = 'FIELD' then
2773 x_mapping_column := null;
2774 if form_section_compiled.system_flag ='N' or NVL(form_section_compiled.EDITABLE,'N') ='Y' then
2775 GetMappingColumn(p_datatype => form_section_compiled.datatype,
2776 p_mapping_column => x_mapping_column);
2777 end if;
2778
2779 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2780 p_form_id => form_section_compiled.FORM_ID,
2781 p_type => 'FORM_FIELD',
2782 p_field_code => form_section_compiled.FIELD_CODE,
2783 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2784 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
2785 p_REQUIRED => form_section_compiled.REQUIRED,
2786 p_LEVEL1_SECTION_ID => null,
2787 p_LEVEL2_SECTION_ID => null,
2788 p_REPEATING_SECTION_ID => null,
2789 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2790 p_ENABLED =>form_section_compiled.ENABLED,
2791 p_EDITABLE =>form_section_compiled.EDITABLE);
2792
2793 g_internal_sequence_number := g_internal_sequence_number + 10;
2794
2795 elsif form_section_compiled.TYPE = 'SECTION' then
2796
2797 x_section_id := form_section_compiled.section_id;
2798
2799 select
2800 nvl(is_repeating_section_flag,'N')
2801 into
2802 x_is_repeating_section_flag
2803 from
2804 pon_forms_sections
2805 where
2806 FORM_ID = x_section_id;
2807
2808 if x_is_repeating_section_flag = 'Y' then
2809
2810 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2811 p_form_id => form_section_compiled.FORM_ID,
2812 p_type => 'REPEAT_SECTION',
2813 p_field_code => null,
2814 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2815 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2816 p_REQUIRED => null,
2817 p_LEVEL1_SECTION_ID => null,
2818 p_LEVEL2_SECTION_ID => null,
2819 p_REPEATING_SECTION_ID => form_section_compiled.SECTION_ID,
2820 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2821 p_ENABLED =>form_section_compiled.ENABLED,
2822 p_EDITABLE =>null);
2823
2824 g_internal_sequence_number := g_internal_sequence_number + 10;
2825 else
2826 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => form_section_compiled.FORM_SECTION_FIELD_ID,
2827 p_form_id => form_section_compiled.FORM_ID,
2828 p_type => 'NORMAL_SECTION',
2829 p_field_code => null,
2830 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2831 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2832 p_REQUIRED => null,
2833 p_LEVEL1_SECTION_ID => form_section_compiled.SECTION_ID,
2834 p_LEVEL2_SECTION_ID => null,
2835 p_REPEATING_SECTION_ID => null,
2836 p_DISPLAY_ON_MAIN_PAGE =>form_section_compiled.DISPLAY_ON_MAIN_PAGE,
2837 p_ENABLED =>form_section_compiled.ENABLED,
2838 p_EDITABLE =>null);
2839
2840 g_internal_sequence_number := g_internal_sequence_number + 10;
2841
2842 INSERT_LEVEL1_SECTION_IN_FORM(p_form_id,form_section_compiled.SECTION_ID);
2843
2844 if form_section_compiled.ENABLED = 'N' then
2845
2846 update pon_form_section_compiled
2847 set enabled = 'N'
2848 where form_id = p_form_id
2849 and level1_section_id = form_section_compiled.SECTION_ID;
2850
2851 end if;
2852
2853 end if;
2854
2855 end if;
2856
2857 END LOOP;
2858
2859 print_debug_log(l_api_name, 'END '||l_api_name);
2860
2861 END COMPILE_FORM;
2862
2863
2864
2865 /*======================================================================
2866 PROCEDURE: INSERT_LEVEL1_SECTION_IN_FORM PRIVATE
2867 PARAMETERS:
2868 COMMENT :
2869 ======================================================================*/
2870 PROCEDURE INSERT_LEVEL1_SECTION_IN_FORM(p_form_id IN NUMBER,
2871 p_level1_section_id IN NUMBER) IS
2872
2873 x_section_id NUMBER;
2874
2875 x_is_repeating_section_flag VARCHAR2(1);
2876
2877 x_form_section_field_id NUMBER;
2878
2879
2880 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
2881
2882 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LEVEL1_SECTION_IN_FORM';
2883
2884 CURSOR LEVEL1_SECTION_fields IS
2885 select
2886 ff.FORM_SECTION_FIELD_ID,
2887 ff.FORM_ID,
2888 ff.TYPE,
2889 ff.FIELD_CODE,
2890 ff.SEQUENCE_NUMBER,
2891 ff.REQUIRED,
2892 ff.SECTION_ID,
2893 ff.DISPLAY_ON_MAIN_PAGE,
2894 ff.ENABLED,
2895 f.datatype,
2896 f.system_flag,
2897 ff.EDITABLE
2898 from pon_form_section_fields ff,
2899 pon_fields f
2900 where form_id = p_level1_section_id
2901 and f.field_code(+) = ff.field_code
2902 order by sequence_number;
2903
2904 BEGIN
2905
2906 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
2907 p_level1_section_id = '||p_level1_section_id);
2908
2909 for LEVEL1_SECTION_fields_record in LEVEL1_SECTION_fields LOOP
2910
2911 if LEVEL1_SECTION_fields_record.TYPE = 'FIELD' then
2912 x_mapping_column := null;
2913 if LEVEL1_SECTION_fields_record.system_flag ='N' or NVL(LEVEL1_SECTION_fields_record.EDITABLE,'N') ='Y' then
2914
2915 GetMappingColumn(p_datatype => LEVEL1_SECTION_fields_record.datatype,
2916 p_mapping_column => x_mapping_column);
2917 end if;
2918
2919 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2920 p_form_id => p_form_id,
2921 p_type => 'SECTION_FIELD',
2922 p_field_code => LEVEL1_SECTION_fields_record.FIELD_CODE,
2923 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2924 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
2925 p_REQUIRED => LEVEL1_SECTION_fields_record.REQUIRED,
2926 p_LEVEL1_SECTION_ID => p_level1_section_id,
2927 p_LEVEL2_SECTION_ID => null,
2928 p_REPEATING_SECTION_ID => null,
2929 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2930 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED,
2931 p_EDITABLE =>LEVEL1_SECTION_fields_record.EDITABLE);
2932
2933 g_internal_sequence_number := g_internal_sequence_number + 10;
2934
2935 elsif LEVEL1_SECTION_fields_record.TYPE = 'SECTION' then
2936
2937 x_section_id := LEVEL1_SECTION_fields_record.section_id;
2938
2939 select
2940 nvl(is_repeating_section_flag,'N')
2941 into
2942 x_is_repeating_section_flag
2943 from
2944 pon_forms_sections
2945 where
2946 FORM_ID = x_section_id;
2947
2948 if x_is_repeating_section_flag = 'Y' then
2949
2950 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2951 p_form_id => p_form_id,
2952 p_type => 'INNER_REPEAT_SECTION',
2953 p_field_code => null,
2954 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2955 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2956 p_REQUIRED => null,
2957 p_LEVEL1_SECTION_ID => p_level1_section_id,
2958 p_LEVEL2_SECTION_ID => null,
2959 p_REPEATING_SECTION_ID => LEVEL1_SECTION_fields_record.SECTION_ID,
2960 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2961 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED,
2962 p_EDITABLE =>null);
2963
2964 g_internal_sequence_number := g_internal_sequence_number + 10;
2965 else
2966
2967 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => LEVEL1_SECTION_fields_record.FORM_SECTION_FIELD_ID,
2968 p_form_id => p_form_id,
2969 p_type => 'INNER_NORMAL_SECTION',
2970 p_field_code => null,
2971 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
2972 p_MAPPING_FIELD_VALUE_COLUMN =>null,
2973 p_REQUIRED => null,
2974 p_LEVEL1_SECTION_ID => p_level1_section_id,
2975 p_LEVEL2_SECTION_ID => LEVEL1_SECTION_fields_record.SECTION_ID,
2976 p_REPEATING_SECTION_ID => null,
2977 p_DISPLAY_ON_MAIN_PAGE =>LEVEL1_SECTION_fields_record.DISPLAY_ON_MAIN_PAGE,
2978 p_ENABLED =>LEVEL1_SECTION_fields_record.ENABLED,
2979 p_EDITABLE =>null);
2980
2981 g_internal_sequence_number := g_internal_sequence_number + 10;
2982
2983 INSERT_LEVEL2_SECTION_IN_FORM(p_form_id,
2984 p_level1_section_id,
2985 LEVEL1_SECTION_fields_record.SECTION_ID);
2986
2987 end if;
2988
2989 end if;
2990
2991 end loop;
2992
2993 print_debug_log(l_api_name, 'END '||l_api_name);
2994
2995 END INSERT_LEVEL1_SECTION_IN_FORM;
2996
2997
2998
2999 /*======================================================================
3000 PROCEDURE: INSERT_LEVEL2_SECTION_IN_FORM PRIVATE
3001 PARAMETERS:
3002 COMMENT :
3003 ======================================================================*/
3004 PROCEDURE INSERT_LEVEL2_SECTION_IN_FORM(p_form_id IN NUMBER,
3005 p_level1_section_id IN NUMBER,
3006 p_level2_section_id IN NUMBER) IS
3007
3008 x_section_id NUMBER;
3009
3010 x_is_repeating_section_flag VARCHAR2(1);
3011
3012 x_form_section_field_id NUMBER;
3013
3014
3015 x_mapping_column PON_FORM_SECTION_COMPILED.MAPPING_FIELD_VALUE_COLUMN%TYPE;
3016
3017 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LEVEL2_SECTION_IN_FORM';
3018
3019 CURSOR inner_section_fields IS
3020 select
3021 ff.FORM_SECTION_FIELD_ID,
3022 ff.FORM_ID,
3023 ff.TYPE,
3024 ff.FIELD_CODE,
3025 ff.SEQUENCE_NUMBER,
3026 ff.REQUIRED,
3027 ff.SECTION_ID,
3028 ff.DISPLAY_ON_MAIN_PAGE,
3029 ff.ENABLED,
3030 f.datatype,
3031 f.system_flag,
3032 ff.EDITABLE
3033 from pon_form_section_fields ff,
3034 pon_fields f
3035 where form_id = p_level2_section_id
3036 and f.field_code(+) = ff.field_code
3037 order by sequence_number;
3038
3039 BEGIN
3040
3041 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3042 p_level1_section_id = '||p_level1_section_id||'
3043 p_level2_section_id = '||p_level2_section_id);
3044
3045 for inner_section_fields_record in inner_section_fields LOOP
3046
3047 if inner_section_fields_record.TYPE = 'FIELD' then
3048
3049 x_mapping_column := null;
3050 if inner_section_fields_record.system_flag ='N' then
3051
3052 GetMappingColumn(p_datatype => inner_section_fields_record.datatype,
3053 p_mapping_column => x_mapping_column);
3054 end if;
3055
3056 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => inner_section_fields_record.FORM_SECTION_FIELD_ID,
3057 p_form_id => p_form_id,
3058 p_type => 'INNER_SECTION_FIELD',
3059 p_field_code => inner_section_fields_record.FIELD_CODE,
3060 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
3061 p_MAPPING_FIELD_VALUE_COLUMN =>x_mapping_column,
3062 p_REQUIRED => inner_section_fields_record.REQUIRED,
3063 p_LEVEL1_SECTION_ID => p_level1_section_id,
3064 p_LEVEL2_SECTION_ID => p_level2_section_id,
3065 p_REPEATING_SECTION_ID => null,
3066 p_DISPLAY_ON_MAIN_PAGE =>inner_section_fields_record.DISPLAY_ON_MAIN_PAGE,
3067 p_ENABLED =>inner_section_fields_record.ENABLED,
3068 p_EDITABLE =>null);
3069
3070 g_internal_sequence_number := g_internal_sequence_number + 10;
3071
3072 else
3073 -- inner section can have a field or repeatable sections only
3074
3075 InsertCompiledRow( p_FORM_SECTION_FIELD_ID => inner_section_fields_record.FORM_SECTION_FIELD_ID,
3076 p_form_id => p_form_id,
3077 p_type => 'INNER_SECTION_REPEAT_SECTION',
3078 p_field_code => null,
3079 p_INTERNAL_SEQUENCE_NUMBER =>g_internal_sequence_number,
3080 p_MAPPING_FIELD_VALUE_COLUMN =>null,
3081 p_REQUIRED => null,
3082 p_LEVEL1_SECTION_ID => p_level1_section_id,
3083 p_LEVEL2_SECTION_ID => p_level2_section_id,
3084 p_REPEATING_SECTION_ID => inner_section_fields_record.SECTION_ID,
3085 p_DISPLAY_ON_MAIN_PAGE =>inner_section_fields_record.DISPLAY_ON_MAIN_PAGE,
3086 p_ENABLED =>inner_section_fields_record.ENABLED,
3087 p_EDITABLE =>null);
3088
3089 g_internal_sequence_number := g_internal_sequence_number + 10;
3090
3091 end if;
3092
3093 end loop;
3094
3095 print_debug_log(l_api_name, 'END '||l_api_name);
3096
3097 END INSERT_LEVEL2_SECTION_IN_FORM;
3098
3099 /*======================================================================
3100 PROCEDURE : GENERATE_REPEATING_SECTIONS
3101 PARAMETERS: p_form_id: The id of the form.
3102 COMMENT : This procedure will call the COMPILE_FORM and
3103 PON_FORMS_JRAD_PVT.CREATE_JRAD procedures for all the
3104 repeating sections included in this form.
3105 ======================================================================*/
3106 PROCEDURE GENERATE_REPEATING_SECTIONS (p_form_id IN NUMBER,
3107 p_generate_mode IN VARCHAR2, -- ALL, JRAD, XSD
3108 p_error IN OUT NOCOPY VARCHAR2,
3109 p_result IN OUT NOCOPY NUMBER) IS
3110
3111 l_err_num NUMBER;
3112 l_err_msg VARCHAR2(200);
3113 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_REPEATING_SECTIONS';
3114 CURSOR v_repeating_section_id_cursor is
3115 SELECT
3116 DISTINCT FS.FORM_ID SECTION_ID
3117 FROM
3118 PON_FORM_SECTION_FIELDS FSF,
3119 PON_FORMS_SECTIONS FS
3120 WHERE
3121 FSF.SECTION_ID = FS.FORM_ID
3122 AND
3123 FS.IS_REPEATING_SECTION_FLAG = 'Y'
3124 AND
3125 (FS.JRAD_XML_REGION_NAME IS NULL
3126 OR
3127 FS.JRAD_XML_REGION_NAME_DISP IS NULL)
3128 START WITH
3129 FSF.FORM_ID = p_form_id
3130 CONNECT
3131 BY PRIOR FSF.SECTION_ID = FSF.FORM_ID;
3132
3133 v_repeating_section_id v_repeating_section_id_cursor%ROWTYPE;
3134 v_section_id PON_FORMS_SECTIONS.FORM_ID%TYPE;
3135 x_error_message VARCHAR2(100);
3136 x_error_code VARCHAR2(100);
3137 x_result VARCHAR2(100);
3138 v_read_only_region_name VARCHAR2(100);
3139 v_edit_region_name VARCHAR2(100);
3140
3141 BEGIN
3142
3143 p_result := 0;
3144
3145 print_debug_log (l_api_name, 'BEGIN- p_form_id = ' || p_form_id);
3146
3147 FOR v_repeating_section_id IN v_repeating_section_id_cursor LOOP
3148
3149 v_section_id := v_repeating_section_id.section_id;
3150
3151 print_debug_log (l_api_name, 'Calling COMPILE_FORM for section id = ' || v_section_id);
3152 COMPILE_FORM (v_section_id);
3153
3154 -- if we need to generate everything or only the JRAD region
3155 IF (p_generate_mode IN ('ALL', 'JRAD')) THEN
3156 print_debug_log (l_api_name, 'Calling CREATE_JRAD for section id = ' || v_section_id);
3157 PON_FORMS_JRAD_PVT.CREATE_JRAD (v_section_id, x_result, x_error_code, x_error_message);
3158
3159 print_debug_log (l_api_name, 'Return values from create_jrad: x_result = ' ||
3160 x_result || ', x_error_message = ' ||
3161 x_error_message || ', x_err_code = ' ||
3162 x_error_code);
3163
3164 if(x_result = fnd_api.g_ret_sts_success) then
3165 p_result := 0;
3166 else
3167 p_result := 1;
3168 p_error := x_error_message;
3169 return;
3170 end if;
3171
3172 -- update the JRAD region references
3173 -- if the form/abstract/section is active
3174 v_read_only_region_name := getReadOnlyRegionName (v_section_id);
3175 v_edit_region_name := getDataEntryRegionName (v_section_id);
3176
3177 UPDATE PON_FORMS_SECTIONS
3178 SET
3179 JRAD_XML_REGION_NAME_DISP = v_read_only_region_name,
3180 JRAD_XML_REGION_NAME = v_edit_region_name
3181 WHERE
3182 FORM_ID = v_section_id
3183 AND STATUS = 'ACTIVE';
3184 END IF;
3185
3186 END LOOP;
3187
3188 print_debug_log (l_api_name, 'END ' || l_api_name);
3189
3190 EXCEPTION
3191 WHEN OTHERS THEN
3192 l_err_num := SQLCODE;
3193 l_err_msg := SUBSTR(SQLERRM, 1, 200);
3194 print_error_log(l_api_name, 'EXCEPTION While Creating Jrad l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
3195 p_result := 1;
3196 p_error := PON_AUCTION_PKG.getMessage ('PON_FM_UNABLE_TO_CREATE_JRAD');
3197
3198 END GENERATE_REPEATING_SECTIONS;
3199
3200
3201 /*======================================================================
3202 PROCEDURE: GENERATE_FORM_DETAILS PUBLIC
3203 PARAMETERS:
3204 COMMENT :
3205 ======================================================================*/
3206 PROCEDURE GENERATE_FORM_DETAILS(p_form_id IN NUMBER,
3207 p_generate_mode IN VARCHAR2, -- ALL, XSD, JRAD
3208 p_schema OUT NOCOPY CLOB,
3209 p_error IN OUT NOCOPY VARCHAR2,
3210 p_result IN OUT NOCOPY NUMBER -- 0: success, 1 - failure
3211 ) IS
3212
3213 l_err_num NUMBER;
3214 l_err_msg VARCHAR2(200);
3215 x_form_code VARCHAR2(20);
3216 x_form_version NUMBER;
3217 x_type VARCHAR2(30);
3218 x_data_entry_region_name VARCHAR2(100);
3219 x_read_only_region_name_disp VARCHAR2(100);
3220 x_query_stmt PON_FORMS_SECTIONS.XML_QUERY%TYPE;
3221 x_error_message VARCHAR2(100);
3222 x_error_code VARCHAR2(100);
3223 x_result VARCHAR2(100);
3224 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_FORM_DETAILS';
3225 l_version VARCHAR2(20);
3226 l_compatibility VARCHAR2(20);
3227 l_majorVersion NUMBER;
3228
3229 BEGIN
3230
3231 p_result := 0;
3232
3233 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id);
3234
3235 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
3236 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
3237
3238 print_debug_log (l_api_name, 'l_version = ' || l_version || ', l_majorVersion = ' || l_majorVersion );
3239
3240 select
3241 type,
3242 jrad_xml_region_name,
3243 jrad_xml_region_name_disp
3244 into
3245 x_type,
3246 x_data_entry_region_name,
3247 x_read_only_region_name_disp
3248 from
3249 pon_forms_sections
3250 where
3251 FORM_ID = p_form_id;
3252
3253 -- generate inner repeating sections
3254 -- for forms and abstract, as well as sections
3255 GENERATE_REPEATING_SECTIONS (p_form_id, p_generate_mode, p_error, p_result);
3256 print_debug_log(l_api_name, 'GENERATE_REPEATING_SECTIONS p_result = '||p_result);
3257
3258 if (p_result = 1) then
3259 return;
3260 end if;
3261
3262 IF (x_data_entry_region_name IS NULL OR x_read_only_region_name_disp IS NULL OR x_type = 'ABSTRACT') THEN
3263 COMPILE_FORM (p_form_id);
3264 print_debug_log(l_api_name, 'COMPILE_FORM p_form_id = '|| p_form_id);
3265 END IF;
3266
3267 -- if we need to generate either just the XSD or everything
3268 -- then need to generate xml query and schema
3269 IF x_type = 'FORM' AND p_generate_mode IN ('ALL', 'XSD') THEN
3270
3271 print_debug_log(l_api_name, 'inside generate xml quer if x_type = ' || x_type || ', p_generate_mode = ' || p_generate_mode);
3272 if (l_majorVersion >= 9) then
3273 print_debug_log(l_api_name, 'major version is greater than 9');
3274
3275 GENERATE_XMLSCHEMA (p_form_id => p_form_id,
3276 p_schema => p_schema,
3277 p_error => p_error,
3278 p_result => p_result,
3279 x_xml_query => x_query_stmt);
3280
3281 if (p_result = 1) then
3282 return;
3283 end if;
3284
3285 update
3286 pon_forms_sections
3287 set
3288 xml_query = x_query_stmt
3289 where
3290 form_id = p_form_id;
3291
3292 print_debug_log(l_api_name, 'GENERATE_XMLSCHEMA p_result = '||p_result);
3293
3294 end if; --if major version >=9
3295
3296 end if;
3297
3298 -- if we need to generate either just the JRAD or everything
3299 IF (p_generate_mode IN ('ALL', 'JRAD')) THEN
3300
3301 PON_FORMS_JRAD_PVT.CREATE_JRAD(p_form_id ,
3302 x_result,
3303 x_error_code,
3304 x_error_message
3305 );
3306
3307 if(x_result = fnd_api.g_ret_sts_success) then
3308 p_result := 0;
3309 else
3310 p_result := 1;
3311 p_error := x_error_message;
3312 return;
3313 end if;
3314
3315 -- update the JRAD region references
3316 -- if the form/abstract/section is active
3317 x_data_entry_region_name := PON_FORMS_UTIL_PVT.getDataEntryRegionName(p_form_id);
3318 x_read_only_region_name_disp := PON_FORMS_UTIL_PVT.getReadOnlyRegionName(p_form_id);
3319
3320 UPDATE pon_forms_sections
3321 SET
3322 jrad_xml_region_name = x_data_entry_region_name,
3323 jrad_xml_region_name_disp = x_read_only_region_name_disp
3324 WHERE form_id = p_form_id
3325 AND status = 'ACTIVE';
3326
3327 print_debug_log(l_api_name, 'PON_FORMS_JRAD_PVT.CREATE_JRAD p_result = '||p_result);
3328
3329 print_debug_log(l_api_name, 'END '||l_api_name);
3330
3331 END IF;
3332
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335 p_result := 1;
3336 l_err_num := SQLCODE;
3337 l_err_msg := SUBSTR(SQLERRM, 1, 200);
3338 print_error_log(l_api_name, 'EXCEPTION l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
3339
3340 END GENERATE_FORM_DETAILS;
3341
3342
3343
3344 /*======================================================================
3345 PROCEDURE: publishAbstract PUBLIC
3346 PARAMETERS:
3347 COMMENT : This procedure is used to update the abstract status
3348 when it is published, i.e. this procedure should be
3349 invoked when the user presses either publish or un-publish
3350 abstract button. We also invoke this procedure when the
3351 apply button is pressed on the enter form-data page as
3352 we need to save the 'include pdf' checkbox value.
3353 ======================================================================*/
3354
3355 procedure publishAbstract(p_auction_header_id IN NUMBER,
3356 p_include_pdf_flag IN VARCHAR2,
3357 p_publish_action IN VARCHAR2,
3358 x_result OUT NOCOPY VARCHAR2,
3359 x_error_code OUT NOCOPY VARCHAR2,
3360 x_error_message OUT NOCOPY VARCHAR2) IS
3361
3362 l_api_name CONSTANT VARCHAR2(30) := 'PUBLISHABSTRACT';
3363 l_form_id NUMBER;
3364
3365 BEGIN
3366
3367 print_debug_log(l_api_name, 'BEGIN- p_auction_header_id = '|| p_auction_header_id||'
3368 p_include_pdf_flag = '||p_include_pdf_flag ||'
3369 p_publish_action = '||p_publish_action);
3370
3371 update pon_auction_headers_all
3372 set include_pdf_in_external_page = p_include_pdf_flag,
3373 last_update_date = sysdate
3374 where auction_header_id = p_auction_header_id;
3375
3376 -- if(nvl(p_publish_action, 'A') <> 'A') then
3377
3378 if((nvl(p_publish_action, '@') = 'Y' ) OR (nvl(p_publish_action, '@') = 'N') ) then
3379
3380 -- if the user hasn't pressed the publish abstract button
3381 -- we dont need to update the status in either
3382 -- pon_auction_headers_all.abstract_status OR
3383 -- pon_forms_instances.status
3384
3385 -- as we do invoke this method when the user presses the apply button as well
3386
3387 update pon_auction_headers_all
3388 set abstract_status = decode(nvl(p_publish_action, 'X'), 'Y', 'PUBLISHED', 'NOT_PUBLISHED'),
3389 last_update_date = sysdate
3390 where auction_header_id = p_auction_header_id;
3391
3392 select form_id
3393 into l_form_id
3394 from pon_forms_sections
3395 where form_code = 'ABSTRACT'
3396 and type = 'ABSTRACT';
3397
3398 update pon_forms_instances
3399 set status = decode(nvl(p_publish_action, 'X'), 'Y', 'PUBLISHED', 'NOT_PUBLISHED'),
3400 last_update_date = sysdate
3401 where entity_code = 'PON_AUCTION_HEADERS_ALL'
3402 and entity_pk1 = to_char(p_auction_header_id)
3403 and form_id = l_form_id;
3404
3405 end if;
3406
3407
3408
3409 x_result := fnd_api.g_ret_sts_success;
3410 print_debug_log(l_api_name, 'END');
3411
3412 EXCEPTION
3413 WHEN OTHERS THEN
3414 x_error_code := SQLCODE;
3415 x_error_message := SUBSTR(SQLERRM, 1, 100);
3416 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3417 x_result := fnd_api.g_ret_sts_error;
3418 END publishAbstract;
3419
3420 /*======================================================================
3421 PROCEDURE: performPostSaveChanges PUBLIC
3422 PARAMETERS:
3423 COMMENT : This procedure is used to update the form instance status
3424 for a form attached to an entity. This procedure will be
3425 invoked from the beforeCommit method of the
3426 FormFieldValuesEO entity
3427 ======================================================================*/
3428
3429 PROCEDURE performPostSaveChanges(p_form_id IN NUMBER,
3430 p_entity_pk1 IN VARCHAR2,
3431 p_entity_code IN VARCHAR2,
3432 p_include_pdf IN VARCHAR2,
3433 x_result OUT NOCOPY VARCHAR2,
3434 x_error_code OUT NOCOPY VARCHAR2,
3435 x_error_message OUT NOCOPY VARCHAR2) IS
3436
3437 l_api_name CONSTANT VARCHAR2(30) := 'PERFORMPOSTSAVECHANGES';
3438 l_form_type PON_FORMS_SECTIONS.FORM_CODE%TYPE;
3439 l_old_status PON_FORMS_INSTANCES.STATUS%TYPE;
3440
3441 BEGIN
3442
3443 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3444 p_entity_pk1 = '||p_entity_pk1||'
3445 p_entity_code = '||p_entity_code);
3446
3447 select type
3448 into l_form_type
3449 from pon_forms_sections
3450 where form_id = p_form_id;
3451
3452 if(l_form_type = 'FORM') then
3453 /*Add for FBO project.
3454 If old status is "POSTED", when user enter data again
3455 and press apply button, status should remain "POSTED"
3456 */
3457 select status
3458 into l_old_status
3459 from pon_forms_instances
3460 where entity_code = p_entity_code
3461 and entity_pk1 = p_entity_pk1
3462 and form_id = p_form_id;
3463
3464 if(NVL(l_old_status , 'x@Y#z') <> 'POSTED') then
3465 update pon_forms_instances
3466 set status = 'DATA_ENTERED',
3467 last_update_date = sysdate
3468 where entity_code = p_entity_code
3469 and entity_pk1 = p_entity_pk1
3470 and form_id = p_form_id;
3471 end if;
3472
3473 elsif(l_form_type = 'ABSTRACT') then
3474
3475 select status
3476 into l_old_status
3477 from pon_forms_instances
3478 where entity_code = p_entity_code
3479 and entity_pk1 = p_entity_pk1
3480 and form_id = p_form_id;
3481
3482 -- if the apply button has been pressed while entering
3483 -- data for a form, we need to update the status if the
3484 -- status hasnt been set
3485
3486 if(NVL(l_old_status , 'x@Y#z') = 'x@Y#z') then
3487
3488 update pon_forms_instances
3489 set status = 'NOT_PUBLISHED',
3490 last_update_date = sysdate
3491 where entity_code = p_entity_code
3492 and entity_pk1 = p_entity_pk1
3493 and form_id = p_form_id;
3494
3495 end if;
3496
3497 end if;
3498
3499
3500 if(NVL(p_include_pdf, 'x@Y#z') <> 'x@Y#z') then
3501
3502 begin
3503 update pon_auction_headers_all
3504 set include_pdf_in_external_page = p_include_pdf,
3505 last_update_date = sysdate
3506 where auction_header_id = to_number(p_entity_pk1);
3507
3508 exception
3509 when others then
3510 null;
3511 end;
3512 end if;
3513
3514 x_result := fnd_api.g_ret_sts_success;
3515
3516 print_debug_log(l_api_name, 'END');
3517
3518 EXCEPTION
3519 WHEN OTHERS THEN
3520 x_result := fnd_api.g_ret_sts_error;
3521 x_error_code := SQLCODE;
3522 x_error_message := SUBSTR(SQLERRM, 1, 100);
3523 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3524
3525 END performPostSaveChanges;
3526
3527 /*======================================================================
3528 PROCEDURE: deleteFormFieldValues PUBLIC
3529 PARAMETERS:
3530 COMMENT : This procedure is used to remove all the child rows from
3531 pon_form_field_values table for a given parent row.
3532 This procedure will be invoked from the remove method of the
3533 FormFieldValuesEO entity
3534 ======================================================================*/
3535
3536 PROCEDURE deleteFormFieldValues(p_form_id IN NUMBER,
3537 p_entity_pk1 IN VARCHAR2,
3538 p_entity_code IN VARCHAR2,
3539 p_section_id IN NUMBER,
3540 p_parent_fk IN NUMBER,
3541 x_result OUT NOCOPY VARCHAR2,
3542 x_error_code OUT NOCOPY VARCHAR2,
3543 x_error_message OUT NOCOPY VARCHAR2) IS
3544
3545 l_api_name CONSTANT VARCHAR2(30) := 'DELETEFORMFIELDVALUES';
3546
3547 BEGIN
3548 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3549 p_entity_pk1 = '||p_entity_pk1||'
3550 p_entity_code = '||p_entity_code||'
3551 p_section_id = '||p_section_id||'
3552 p_parent_fk = '||p_parent_fk);
3553
3554 delete from pon_form_field_values
3555 where form_id = p_form_id
3556 and entity_pk1 = p_entity_pk1
3557 and owning_entity_code = p_entity_code
3558 and parent_field_values_fk = p_parent_fk
3559 and nvl(section_id, -1) <> -1;
3560
3561
3562 x_result := fnd_api.g_ret_sts_success;
3563
3564 print_debug_log(l_api_name, 'END');
3565
3566 EXCEPTION
3567 WHEN OTHERS THEN
3568 x_error_code := SQLCODE;
3569 x_error_message := SUBSTR(SQLERRM, 1, 100);
3570 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3571 x_result := fnd_api.g_ret_sts_error;
3572
3573 END deleteFormFieldValues;
3574
3575 /*======================================================================
3576 PROCEDURE : GET_EXTERNAL_REGISTER_URL PUBLIC
3577 PARAMETERS:
3578 COMMENT : This procedure will return the url to be used for
3579 supplier registration.
3580 ======================================================================*/
3581
3582 FUNCTION GET_EXTERNAL_REGISTER_URL (p_org_id IN NUMBER) RETURN VARCHAR2 IS
3583
3584 l_api_name CONSTANT VARCHAR2(30) := 'GET_EXTERNAL_REGISTER_URL';
3585 l_external_register_url VARCHAR2(600);
3586 l_org_hash_key VARCHAR2(80);
3587
3588 BEGIN
3589
3590 print_debug_log(l_api_name, 'BEGIN- p_org_id = '||p_org_id);
3591
3592 print_debug_log(l_api_name, 'Calling POS_URL_PKG.get_External_url');
3593
3594 l_external_register_url := POS_URL_PKG.get_external_url();
3595
3596 print_debug_log(l_api_name, 'Return value from POS_URL_PKG.get_External_url:external url = ' || l_external_register_url);
3597
3598 SELECT HASHKEY
3599 INTO l_org_hash_key
3600 FROM POS_ORG_HASH
3601 WHERE ORG_ID=p_org_id;
3602
3603 l_external_register_url := l_external_register_url || 'OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?ouid=' || l_org_hash_key;
3604
3605 print_debug_log(l_api_name, 'END- external url = ' || l_external_register_url);
3606
3607 return l_external_register_url;
3608
3609 END;
3610
3611 /*======================================================================
3612 PROCEDURE: deleteValues PUBLIC
3613 PARAMETERS:
3614 COMMENT : This procedure is used to remove all the rows from
3615 pon_form_field_values table for a given form.
3616 This procedure should be invoked from the remove method
3617 FormInstancesEO entity
3618 ======================================================================*/
3619
3620 PROCEDURE deleteValues( p_form_id IN NUMBER,
3621 p_entity_pk1 IN VARCHAR2,
3622 p_entity_code IN VARCHAR2,
3623 x_result OUT NOCOPY VARCHAR2,
3624 x_error_code OUT NOCOPY VARCHAR2,
3625 x_error_message OUT NOCOPY VARCHAR2) IS
3626
3627 l_api_name CONSTANT VARCHAR2(30) := 'DELETEVALUES';
3628
3629
3630 BEGIN
3631
3632 print_debug_log(l_api_name, 'BEGIN- p_form_id = '||p_form_id||'
3633 p_entity_pk1 = '||p_entity_pk1||'
3634 p_entity_code = '||p_entity_code);
3635
3636 x_result := fnd_api.g_ret_sts_error;
3637
3638 delete from pon_form_field_values
3639 where form_id = p_form_id
3640 and entity_pk1 = p_entity_pk1
3641 and owning_entity_code = p_entity_code;
3642
3643 x_result := fnd_api.g_ret_sts_success;
3644
3645 print_debug_log(l_api_name, 'END');
3646
3647 EXCEPTION
3648 WHEN OTHERS THEN
3649 x_error_code := SQLCODE;
3650 x_error_message := SUBSTR(SQLERRM, 1, 100);
3651 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
3652 x_result := fnd_api.g_ret_sts_error;
3653 END deleteValues;
3654
3655
3656
3657 /*======================================================================
3658 PROCEDURE: PRINT_DEBUG_LOG PRIVATE
3659 PARAMETERS:
3660 COMMENT : This procedure is used to print debug messages into
3661 FND logs
3662 ======================================================================*/
3663 PROCEDURE print_debug_log(p_module IN VARCHAR2,
3664 p_message IN VARCHAR2)
3665
3666 IS
3667
3668 l_message_trun varchar2(800);
3669
3670 BEGIN
3671 IF (g_fnd_debug = 'Y') THEN
3672 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3673 FND_LOG.string(log_level => FND_LOG.level_statement,
3674 module => g_module_prefix || p_module,
3675 message => p_message);
3676 END IF;
3677 END IF;
3678 END;
3679
3680 /*======================================================================
3681 PROCEDURE: PRINT_ERROR_LOG PRIVATE
3682 PARAMETERS:
3683 COMMENT : This procedure is used to print unexpected exceptions or
3684 error messages into FND logs
3685 ======================================================================*/
3686
3687 PROCEDURE print_error_log(p_module IN VARCHAR2,
3688 p_message IN VARCHAR2)
3689 IS
3690 BEGIN
3691 IF (g_fnd_debug = 'Y') THEN
3692 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
3693 FND_LOG.string(log_level => FND_LOG.level_procedure,
3694 module => g_module_prefix || p_module,
3695 message => p_message);
3696 END IF;
3697 END IF;
3698 END;
3699
3700
3701 /*======================================================================
3702 FUNCTION: default_system_field_url PUBLIC
3703 PARAMETERS: p_auction_header_id - negotiation header id
3704 p_attr_name - name of the attribute
3705 COMMENT : This function is use to get the default value of the system field
3706 from the UDA of the line item. If the document has multiple line items with
3707 different attribute value, the function will group the line items by the attr
3708 and return the attr associated with the highest dollar value. (clm_amount)
3709 ======================================================================*/
3710 FUNCTION default_system_field_url RETURN VARCHAR2 IS
3711 l_result VARCHAR(240);
3712 BEGIN
3713 /*select preference_value
3714 into l_result
3715 from PON_PARTY_PREFERENCES
3716 where app_short_name = 'PON'
3717 and preference_name = 'FBO_URL';*/
3718
3719 -- CLM Phase2: we will take the url value from PON_FBO_URL profile.
3720 FND_PROFILE.get('PON_FBO_URL',l_result);
3721
3722 return l_result;
3723 EXCEPTION
3724 WHEN NO_DATA_FOUND THEN
3725 return null;
3726 END;
3727
3728 /*======================================================================
3729 FUNCTION: default_system_field_line_item PUBLIC
3730 PARAMETERS: p_auction_header_id - negotiation header id
3731 p_attr_name - name of the attribute
3732 COMMENT : This function is use to get the default value of the system field
3733 from the UDA of the line item. If the document has multiple line items with
3734 different attribute value, the function will group the line items by the attr
3735 and return the attr associated with the highest dollar value. (clm_amount)
3736 ======================================================================*/
3737 FUNCTION default_system_field_line_item(p_auction_header_id IN NUMBER,
3738 p_attr_name IN VARCHAR2)
3739 RETURN VARCHAR2 IS
3740 l_column_name VARCHAR2(50);
3741 l_sql VARCHAR2(1000);
3742 l_result VARCHAR(4000);
3743 l_line_number PO_TBL_NUMBER;
3744 l_attr_grp_id NUMBER;
3745 BEGIN
3746
3747
3748 select ag.database_column
3749 into l_column_name
3750 from EGO_ATTRS_V ag, ego_fnd_dsc_flx_ctx_ext egoag
3751 where ag.attr_group_type = egoag.descriptive_flexfield_name
3752 and ag.attr_group_name = egoag.descriptive_flex_context_code
3753 AND ag.attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS'
3754 AND ag.ATTR_GROUP_NAME = 'FED_CUST_DESG'
3755 AND ag.ATTR_NAME = p_attr_name;
3756
3757 select attr_group_id into l_attr_grp_id from ego_attr_groups_v where attr_group_name = 'FED_CUST_DESG' and attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS';
3758
3759 select line_number BULK COLLECT INTO l_line_number from (select line_number, clm_amount line_amount from pon_auction_item_prices_all
3760 where order_type_lookup_code <> 'QUANTITY'
3761 and auction_header_id = p_auction_header_id
3762 and clm_amount is not null
3763 union
3764 select line_number, current_price * quantity line_amount from pon_auction_item_prices_all
3765 where order_type_lookup_code = 'QUANTITY'
3766 and auction_header_id = p_auction_header_id
3767 and current_price is not null)
3768 order by line_amount desc;
3769
3770 if ( l_line_number.count > 0 ) then
3771 l_sql := 'select paip_ext.' || l_column_name ||
3772 ' from pon_auction_item_prices_ext_b paip_ext' ||
3773 ' where paip_ext.auction_header_id = ' || p_auction_header_id ||
3774 ' and paip_ext.line_number = '|| l_line_number(1) ||
3775 ' and paip_ext.attr_group_id = '||l_attr_grp_id;
3776
3777 EXECUTE IMMEDIATE l_sql INTO l_result;
3778
3779 end if;
3780
3781 return l_result;
3782 END default_system_field_line_item;
3783
3784 /*======================================================================
3785 FUNCTION: default_sys_field_max_amount PUBLIC
3786 PARAMETERS: p_auction_header_id - negotiation header id
3787 p_attr_name - name of the attribute
3788 COMMENT : This function is use to get the default value of the system field
3789 from the UDA of the line item. If the document has multiple line items with
3790 different attribute value, the function will group the line items by the attr
3791 and return the attr associated with the highest dollar value. (clm_amount)
3792 This function will be replacing above function
3793 ======================================================================*/
3794 FUNCTION default_sys_field_max_amount(p_auction_header_id IN NUMBER,
3795 p_attr_name IN VARCHAR2)
3796 RETURN VARCHAR2 IS
3797 l_column_name VARCHAR2(50);
3798 l_sql VARCHAR2(1000);
3799 l_result VARCHAR2(4000);
3800 l_ret_sys_field PO_TBL_VARCHAR4000;
3801 l_attr_grp_id NUMBER;
3802 l_amount PO_TBL_NUMBER;
3803 BEGIN
3804
3805 select ag.database_column
3806 into l_column_name
3807 from EGO_ATTRS_V ag, ego_fnd_dsc_flx_ctx_ext egoag
3808 where ag.attr_group_type = egoag.descriptive_flexfield_name
3809 and ag.attr_group_name = egoag.descriptive_flex_context_code
3810 AND ag.attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS'
3811 AND ag.ATTR_GROUP_NAME = 'FED_CUST_DESG'
3812 AND ag.ATTR_NAME = p_attr_name;
3813
3814
3815 select attr_group_id into l_attr_grp_id from ego_attr_groups_v where attr_group_name = 'FED_CUST_DESG' and attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS';
3816 print_error_log('default_sys_field_max_amount ','l_attr_grp_id ' || l_attr_grp_id);
3817
3818 l_sql := 'select paip_ext.' || l_column_name ||', Sum(paip.clm_amount) amount ' ||
3819 'from pon_auction_item_prices_ext_b paip_ext, pon_auction_Item_prices_all paip '||
3820 'where paip_ext.auction_header_id = ' || p_auction_header_id ||
3821 'and paip_ext.attr_group_id = ' || l_attr_grp_id ||
3822 'AND paip.auction_header_id = paip_ext.auction_header_id ' ||
3823 'AND paip_ext.line_number = paip.line_number ' ||
3824 ' AND paip_ext.'|| l_column_name || ' IS NOT NULL ' ||
3825 ' GROUP BY paip_ext.' || l_column_name ||
3826 ' ORDER BY amount DESC';
3827
3828
3829
3830 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_ret_sys_field,l_amount;
3831
3832 if ( l_ret_sys_field.count > 0 ) THEN
3833 l_result:= l_ret_sys_field(1);
3834 print_error_log('default_sys_field_max_amount returns ',l_result);
3835 END IF;
3836
3837 RETURN l_result;
3838
3839 EXCEPTION
3840 WHEN OTHERS THEN
3841 print_error_log('default_sys_field_max_amount ','l_err_num = '||SQLERRM ||' l_err_msg = '||SQLCODE );
3842 RETURN l_result;
3843
3844 END default_sys_field_max_amount;
3845
3846
3847 /*
3848 * bug 9861282
3849 * This method returns the value for fed_desc_name based on whether the
3850 * current document is amendment or not
3851 */
3852 FUNCTION get_fed_desc(p_auction_header_id IN NUMBER)
3853 RETURN VARCHAR2 IS
3854 l_fed_desc pon_auction_headers_all.amendment_description%TYPE;
3855
3856 BEGIN
3857
3858 SELECT Decode(Nvl(amendment_flag,'N'),'Y',amendment_description,abstract_details)
3859 INTO l_fed_desc
3860 FROM pon_auction_headers_all
3861 WHERE auction_header_id = p_auction_header_id;
3862
3863 RETURN l_fed_desc;
3864
3865 END;
3866
3867 /*
3868 * bug 9861282
3869 * This method returns the value for fed_uploadtype based on whether the
3870 * current document is amendment or not
3871 */
3872 FUNCTION get_fed_uploadtype(p_auction_header_id IN NUMBER)
3873 RETURN VARCHAR2 IS
3874 l_uploadtype VARCHAR2(1);
3875
3876 BEGIN
3877
3878 SELECT Decode(Nvl(amendment_number,0),0,'S','A')
3879 INTO l_uploadtype
3880 FROM pon_auction_headers_all
3881 WHERE auction_header_id = p_auction_header_id;
3882
3883 RETURN l_uploadtype;
3884
3885 END;
3886
3887
3888
3889 /*======================================================================
3890 FUNCTION: get_uda_value PUBLIC
3891 PARAMETERS: p_attr_group_type - attribute group type
3892 p_attr_group_name - attribute group name
3893 p_attr_name - attribute name
3894 p_from - table name
3895 p_where - additional where clause
3896 p_auction_header_id - solicitation header id
3897 COMMENT : This function return the value of UDA field on the solicitation
3898 document.
3899 ======================================================================*/
3900 FUNCTION get_uda_value(p_attr_group_type IN VARCHAR2 DEFAULT 'PON_AUC_HDRS_EXT_ATTRS',
3901 p_attr_group_name IN VARCHAR2,
3902 p_attr_name IN VARCHAR2,
3903 p_from IN VARCHAR2 DEFAULT 'pon_auction_headers_ext_b',
3904 p_where IN VARCHAR2 DEFAULT NULL,
3905 p_auction_header_id IN NUMBER)
3906 RETURN VARCHAR2 IS
3907 l_column_name VARCHAR2(50);
3908 l_attr_group_id NUMBER;
3909 l_sql VARCHAR2(1000);
3910 l_result VARCHAR(4000);
3911 BEGIN
3912 select ag.database_column, egoag.attr_group_id
3913 into l_column_name, l_attr_group_id
3914 from EGO_ATTRS_V ag, ego_fnd_dsc_flx_ctx_ext egoag
3915 where ag.attr_group_type = egoag.descriptive_flexfield_name
3916 and ag.attr_group_name = egoag.descriptive_flex_context_code
3917 AND ag.attr_group_type = p_attr_group_type
3918 AND ag.ATTR_GROUP_NAME = p_attr_group_name
3919 AND ag.ATTR_NAME = p_attr_name;
3920
3921 l_sql := 'select ' || l_column_name || ' from ' || p_from ||
3922 ' where auction_header_id = :1 and attr_group_id = :2';
3923
3924 if p_where is not null then
3925 l_sql := l_sql || ' and ' || p_where;
3926 end if;
3927
3928 EXECUTE IMMEDIATE l_sql
3929 INTO l_result
3930 USING p_auction_header_id, l_attr_group_id;
3931
3932 return l_result;
3933
3934 EXCEPTION
3935 WHEN OTHERS THEN
3936 RETURN NULL; -- bug 9953058
3937
3938 END;
3939
3940 /*======================================================================
3941 FUNCTION: get_uda_fed_offadd PUBLIC
3942 PARAMETERS: p_auction_header_id - solicitation header id
3943 COMMENT : This function return the issuing office address from the
3944 solicitation document specified by p_auction_header_id
3945 ======================================================================*/
3946 FUNCTION get_uda_fed_offadd(p_auction_header_id IN NUMBER)
3947 RETURN VARCHAR2 IS
3948 BEGIN
3949 return get_uda_value(p_attr_group_name => 'addresses',
3950 p_attr_name => 'addressdetails',
3951 p_where => 'c_ext_attr39=''ISSUING_OFFICE''',
3952 p_auction_header_id => p_auction_header_id);
3953 END;
3954
3955 /*======================================================================
3956 FUNCTION: get_uda_fed_zip PUBLIC
3957 PARAMETERS: p_auction_header_id - solicitation header id
3958 COMMENT : This function return the zip code of the issuing office address
3959 from the solicitation document specified by p_auction_header_id
3960 ======================================================================*/
3961 FUNCTION get_uda_fed_zip(p_auction_header_id IN NUMBER)
3962 RETURN VARCHAR2 IS
3963 BEGIN
3964 return get_uda_value(p_attr_group_name => 'addresses',
3965 p_attr_name => 'hiddenZipCode',
3966 p_where => 'c_ext_attr39=''ISSUING_OFFICE''',
3967 p_auction_header_id => p_auction_header_id);
3968 END;
3969
3970 /*======================================================================
3971 FUNCTION: get_uda_fed_contact PUBLIC
3972 PARAMETERS: p_auction_header_id - solicitation header id
3973 COMMENT : This function return the contacting officer's name on the
3974 solicitation document specified by p_auction_header_id
3975 ======================================================================*/
3976 FUNCTION get_uda_fed_contact(p_auction_header_id IN NUMBER)
3977 RETURN VARCHAR2 IS
3978 l_attr_internal_num_value number;
3979 l_err_num NUMBER;
3980 l_err_msg VARCHAR2(200);
3981 BEGIN
3982 l_attr_internal_num_value := to_number(get_uda_value(p_attr_group_name => 'addresses',
3983 p_attr_name => 'contact',
3984 p_where => 'c_ext_attr39=''ISSUING_OFFICE''',
3985 p_auction_header_id => p_auction_header_id));
3986
3987 return get_UDA_display_value(p_auction_header_id => p_auction_header_id,
3988 p_attr_internal_num_value => l_attr_internal_num_value,
3989 p_attr_internal_name => 'contact',
3990 p_attr_group_int_name => 'addresses'
3991 );
3992 EXCEPTION
3993 WHEN OTHERS THEN
3994 l_err_num := SQLCODE;
3995 l_err_msg := SUBSTR(SQLERRM, 1, 200);
3996 print_error_log('get_uda_fed_contact ',' l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
3997 return l_attr_internal_num_value;
3998
3999 END;
4000
4001 /*======================================================================
4002 FUNCTION: get_uda_fed_setaside PUBLIC
4003 PARAMETERS: p_auction_header_id - solicitation header id
4004 COMMENT : This function return the Set Aside Type on the
4005 solicitation document specified by p_auction_header_id
4006 ======================================================================*/
4007 FUNCTION get_uda_fed_setaside(p_auction_header_id IN NUMBER)
4008 RETURN VARCHAR2 IS
4009 BEGIN
4010 return get_uda_value(p_attr_group_name => 'SET_ASIDE_INFO',
4011 p_attr_name => 'SET_ASIDE_TYPE', -- bug 9924551
4012 p_auction_header_id => p_auction_header_id);
4013 END;
4014
4015 /*======================================================================
4016 FUNCTION: get_uda_fed_popaddress PUBLIC
4017 PARAMETERS: p_auction_header_id - solicitation header id
4018 COMMENT : This function return the delivery to address from the first line
4019 of the solicitation document specified by p_auction_header_id
4020 ======================================================================*/
4021 FUNCTION get_uda_fed_popaddress(p_auction_header_id IN NUMBER)
4022 RETURN VARCHAR2 IS
4023 l_line_number NUMBER;
4024 l_ship_addr_xml varchar2(4000);
4025 parser xmlparser.parser;
4026 xmlDoc xmldom.DOMDocument;
4027
4028 nodeList xmldom.DOMNodeList;
4029 len number;
4030 node xmldom.DOMNode;
4031 childNode xmldom.DOMNode;
4032 l_pop_address varchar2(4000);
4033 BEGIN
4034 select line_number into l_line_number from pon_auction_item_prices_all
4035 where auction_header_id = p_auction_header_id AND Nvl(clm_info_flag,'N') = 'N' AND rownum < 2;
4036
4037 l_ship_addr_xml := get_uda_value(p_attr_group_name => 'SHIP_INFO',
4038 p_attr_name => 'hiddShipAddXml', -- bug 9856935
4039 p_attr_group_type => 'PON_AUC_PRICES_EXT_ATTRS',
4040 p_from => 'pon_auction_item_prices_ext_b',
4041 p_where => 'line_number = '||l_line_number,
4042 p_auction_header_id => p_auction_header_id);
4043 l_ship_addr_xml := '<SHIP_INFO>'||l_ship_addr_xml||'</SHIP_INFO>';
4044
4045 --parse the xml
4046 parser := xmlparser.newParser;
4047 xmlparser.setValidationMode(parser, FALSE);
4048 xmlparser.parseBuffer(parser,l_ship_addr_xml);
4049
4050 xmlDoc := xmlparser.getDocument(parser);
4051
4052 nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4053 len := xmldom.getLength(nodeList);
4054 l_pop_address := NULL;
4055
4056 for i in 0..len-1
4057 loop
4058 node := xmldom.item(nodeList,i);
4059 childNode := xmldom.getFirstChild(node);
4060
4061 if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062 or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063 or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064 or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065 or xmldom.getNodeName(node) = 'REGION1_DESC'
4066 or xmldom.getNodeName(node) = 'REGION2_DESC'
4067 ) THEN
4068 if ( l_pop_address is null )
4069 then
4070 l_pop_address := '';
4071 else
4072 l_pop_address := l_pop_address || ', ';
4073 end if;
4074 l_pop_address := l_pop_address || xmldom.getNodeValue(childNode);
4075 end if;
4076 end loop;
4077 return l_pop_address;
4078 exception
4079 when others then
4080 return null;
4081 END;
4082
4083
4084 /*======================================================================
4085 FUNCTION: get_uda_fed_popzip PUBLIC
4086 PARAMETERS: p_auction_header_id - solicitation header id
4087 COMMENT : This function return the zip code from delivery to address from
4088 the first line of the solicitation document specified by p_auction_header_id
4089 ======================================================================*/
4090 FUNCTION get_uda_fed_popzip(p_auction_header_id IN NUMBER)
4091 RETURN VARCHAR2 IS
4092 l_line_number NUMBER;
4093 BEGIN
4094
4095 select line_number into l_line_number from pon_auction_item_prices_all
4096 where auction_header_id = p_auction_header_id AND Nvl(clm_info_flag,'N') = 'N' AND rownum < 2;
4097 return get_uda_value(p_attr_group_name => 'SHIP_INFO',
4098 p_attr_name => 'hiddenZipCode',
4099 p_attr_group_type => 'PON_AUC_PRICES_EXT_ATTRS',
4100 p_from => 'pon_auction_item_prices_ext_b',
4101 p_where => 'line_number = '||l_line_number,
4102 p_auction_header_id => p_auction_header_id);
4103 exception
4104 when others then
4105 return null;
4106 END;
4107
4108 /*======================================================================
4109 FUNCTION: get_uda_fed_popcountry PUBLIC
4110 PARAMETERS: p_auction_header_id - solicitation header id
4111 COMMENT : This function return the country from delivery to address from
4112 the first line of the solicitation document specified by p_auction_header_id
4113 ======================================================================*/
4114 FUNCTION get_uda_fed_popcountry(p_auction_header_id IN NUMBER)
4115 RETURN VARCHAR2 IS
4116 l_line_number NUMBER;
4117 BEGIN
4118 select line_number into l_line_number from pon_auction_item_prices_all
4119 where auction_header_id = p_auction_header_id AND Nvl(clm_info_flag,'N') = 'N' AND rownum < 2;
4120 return get_uda_value(p_attr_group_name => 'SHIP_INFO',
4121 p_attr_name => 'hiddenCountry',
4122 p_attr_group_type => 'PON_AUC_PRICES_EXT_ATTRS',
4123 p_from => 'pon_auction_item_prices_ext_b',
4124 p_where => 'line_number = '||l_line_number,
4125 p_auction_header_id => p_auction_header_id);
4126 exception
4127 when others then
4128 return null;
4129 END;
4130
4131 /*======================================================================
4132 FUNCTION: get_uda_fed_email PUBLIC
4133 PARAMETERS: p_auction_header_id - solicitation header id
4134 COMMENT : This function return the contacting officer's email on the
4135 solicitation document specified by p_auction_header_id
4136 ======================================================================*/
4137 FUNCTION get_uda_fed_email(p_auction_header_id IN NUMBER)
4138 RETURN VARCHAR2 IS
4139 l_contact_xml varchar2(4000);
4140 parser xmlparser.parser;
4141 xmlDoc xmldom.DOMDocument;
4142
4143 nodeList xmldom.DOMNodeList;
4144 len number;
4145 node xmldom.DOMNode;
4146 childNode xmldom.DOMNode;
4147 l_email_address varchar2(4000);
4148
4149 BEGIN
4150
4151 l_contact_xml := pon_forms_util_Pvt.get_uda_value(p_attr_group_name => 'addresses',
4152 p_attr_name => 'contactdtlsxml',
4153 p_where => 'c_ext_attr39=''ISSUING_OFFICE''',
4154 p_auction_header_id => p_auction_header_id);
4155
4156 --parse the xml
4157 parser := xmlparser.newParser;
4158 xmlparser.setValidationMode(parser, FALSE);
4159 xmlparser.parseBuffer(parser,l_contact_xml);
4160
4161 xmlDoc := xmlparser.getDocument(parser);
4162
4163 nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4164 len := xmldom.getLength(nodeList);
4165 l_email_address := NULL;
4166
4167 for i in 0..len-1
4168 loop
4169 node := xmldom.item(nodeList,i);
4170 childNode := xmldom.getFirstChild(node);
4171
4172 IF(xmldom.getNodeName(node) = 'ISSUING_OFFICE_CTEMAIL') THEN
4173 l_email_address := xmldom.getNodeValue(childNode);
4174 END IF;
4175
4176
4177 end loop;
4178
4179 --l_single_attr_value will be XML. Need to write XML parser to retrieve data inside <EMAIL> tag
4180 return l_email_address;
4181
4182 EXCEPTION
4183 WHEN OTHERS THEN
4184 RETURN NULL; -- bug 9953058
4185
4186 END;
4187
4188
4189 /*======================================================================
4190 FUNCTION: get_fed_file_link PUBLIC
4191 PARAMETERS: p_auction_header_id - solicitation header id
4192 COMMENT : This function return the link to active
4193 solicitation document specified by p_auction_header_id
4194 ======================================================================*/
4195 FUNCTION get_fed_file_link(p_auction_header_id IN NUMBER)
4196 RETURN VARCHAR2 IS
4197 return_url VARCHAR2(4000);
4198 BEGIN
4199
4200 return_url := 'URL';
4201 return return_url;
4202
4203 END;
4204
4205 /*======================================================================
4206 FUNCTION: get_UDA_display_value PUBLIC
4207 PARAMETERS: p_auction_header_id - solicitation header id
4208 p_attr_internal_num_value - UDA inertal number value
4209 p_attr_internal_num_value - UDA inertal string value
4210 p_attr_internal_name - attribute internal name
4211 p_attr_group_int_name - attribute group name
4212 p_attr_group_type - attribute group type
4213 p_attr_name - attribute name
4214 COMMENT : This function return the link to active
4215 solicitation document specified by p_auction_header_id
4216 ======================================================================*/
4217 FUNCTION get_UDA_display_value(p_auction_header_id IN NUMBER,
4218 p_attr_internal_num_value IN NUMBER DEFAULT null,
4219 p_attr_internal_str_value IN VARCHAR2 DEFAULT null,
4220 p_attr_internal_name IN VARCHAR2,
4221 p_attr_group_int_name IN VARCHAR2,
4222 p_attr_group_type IN VARCHAR2 DEFAULT 'PON_AUC_HDRS_EXT_ATTRS'
4223 )
4224 RETURN VARCHAR2 IS
4225 l_application_id number;
4226 l_attr_id number;
4227 l_err_num NUMBER;
4228 l_err_msg VARCHAR2(200);
4229 BEGIN
4230
4231 select application_id, attr_id
4232 into l_application_id, l_attr_id
4233 from ego_attrs_v
4234 where ATTR_NAME = p_attr_internal_name
4235 and ATTR_GROUP_NAME = p_attr_group_int_name
4236 and ATTR_GROUP_TYPE = p_attr_group_type;
4237
4238 return EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
4239 p_application_id => l_application_id
4240 ,p_attr_internal_num_value => p_attr_internal_num_value
4241 ,p_attr_internal_str_value => p_attr_internal_str_value
4242 ,p_attr_internal_name => p_attr_internal_name
4243 ,p_attr_group_int_name => p_attr_group_int_name
4244 ,p_attr_id => l_attr_id
4245 ,p_attr_group_type => p_attr_group_type
4246 ,p_object_name => 'PON_AUCTION_HEADERS_ALL'
4247 ,p_pk1_column_name => 'AUCTION_HEADER_ID'
4248 ,p_pk1_value => p_auction_header_id
4249 );
4250
4251 EXCEPTION
4252 WHEN OTHERS THEN
4253 l_err_num := SQLCODE;
4254 l_err_msg := SUBSTR(SQLERRM, 1, 200);
4255 print_error_log('get_UDA_display_value ','l_err_num = '||l_err_num||' l_err_msg = '||l_err_msg);
4256 return p_attr_internal_num_value;
4257
4258
4259 END;
4260
4261
4262 FUNCTION decrypt (KEY IN VARCHAR2,
4263 VALUE IN VARCHAR2
4264 )
4265 RETURN VARCHAR2
4266 AS
4267 LANGUAGE JAVA
4268 NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
4269
4270 /*======================================================================
4271 PROCEDURE: post_wf PUBLIC
4272 PARAMETERS: p_itemtype - workflow internal name
4273 p_from_role_name -role name of sender (Buyer)
4274 p_xml - message body that need to send to FBO, in the format required by FOB
4275 COMMENT : This procedure create the FBO adhocrole on the fly if needed, then start the workflow
4276 to send email to FBO in the correct format.
4277 ======================================================================*/
4278 PROCEDURE post_wf(p_itemtype IN VARCHAR2,
4279 p_form_id IN Number, -- top form
4280 p_entity_pk1 IN Varchar2,
4281 p_from_role_name IN Varchar2,
4282 p_xml IN VARCHAR2,
4283 x_return_status OUT NOCOPY VARCHAR2,
4284 x_reutrn_error OUT NOCOPY VARCHAR2
4285 ) IS
4286 l_item_key VARCHAR2(100);
4287 l_count_role INTEGER := 0;
4288 l_buyer_email VARCHAR2(320);
4289 l_to_role_name VARCHAR2(100) := 'FEDBIZOPPS';
4290 l_sequence NUMBER;
4291 l_solnbr VARCHAR2(240);
4292 l_form_name VARCHAR2(80);
4293 l_fed_email VARCHAR2(320);
4294 l_notification_preference VARCHAR2(80);
4295 l_error_code VARCHAR2(100);
4296 l_email_to VARCHAR2 (500);
4297 l_email_cc VARCHAR2 (500);
4298 l_email_enabled VARCHAR2(1);
4299 l_email_start_date date;
4300 l_email_end_date date;
4301 PON_INVALID_EMAIL_TO_EXCEPTION EXCEPTION;
4302 l_curr_lang FND_LANGUAGES.language_code%TYPE;
4303
4304 BEGIN
4305 x_return_status := FND_API.G_RET_STS_SUCCESS;
4306
4307 SAVEPOINT post_wf;
4308
4309 select pon_post_wf_s.nextval
4310 into l_sequence
4311 from dual;
4312
4313 --get solicitation number
4314 select DOCUMENT_NUMBER into l_solnbr from pon_auction_headers_all where auction_header_id = p_entity_pk1;
4315
4316 l_curr_lang := fnd_global.current_language;
4317
4318 --get form name
4319 select FORM_NAME into l_form_name from PON_FORMS_SECTIONS_TL where form_id = p_form_id
4320 AND LANGUAGE = l_curr_lang; -- bug 9855606
4321
4322 --get item key
4323 select to_char(l_form_name) || '-' ||to_char(l_solnbr) ||'-' ||to_char(l_sequence)
4324 into l_item_key from dual;
4325
4326 --get sender email
4327 select email_address into l_buyer_email
4328 from WF_LOCAL_ROLES
4329 where NAME = p_from_role_name;
4330
4331 -- get send to email from fnd_lookups
4332 select meaning, enabled_flag, start_date_active, end_date_active
4333 into l_email_to,l_email_enabled,l_email_start_date,l_email_end_date
4334 from fnd_lookups
4335 where lookup_type ='PON_FBO_EMAIL'
4336 and lookup_code = 'EMAIL_TO';
4337
4338 --Email to does not setup properly in lookup, raise error.
4339 if (l_email_to is null or
4340 l_email_enabled = 'N' or
4341 sysdate <l_email_start_date or
4342 sysdate >l_email_end_date)then
4343 raise PON_INVALID_EMAIL_TO_EXCEPTION;
4344 end if;
4345
4346 -- get cc email from fnd_lookups
4347 select meaning
4348 into l_email_cc
4349 from fnd_lookups
4350 where lookup_type = 'PON_FBO_EMAIL'
4351 and lookup_code = 'EMAIL_CC';
4352
4353
4354 SELECT COUNT(*)
4355 INTO l_count_role
4356 FROM wf_local_roles
4357 WHERE NAME = l_to_role_name;
4358
4359 IF l_count_role = 0
4360 /*Role is not exists, create a new role*/
4361 THEN
4362 --If the sender does not exist in WF Local Roles, then create one on the fly
4363 wf_directory.createadhocrole(role_name => l_to_role_name
4364 ,role_display_name => l_to_role_name
4365 ,role_description => l_to_role_name
4366 ,notification_preference => 'MAILTEXT'
4367 ,email_address => l_email_to
4368 ,status => 'ACTIVE'
4369 ,expiration_date => null);
4370
4371
4372 ELSE --role already exists, check whether email has been changed
4373 SELECT EMAIL_ADDRESS, notification_preference
4374 INTO l_fed_email, l_notification_preference
4375 FROM wf_local_roles
4376 WHERE NAME = l_to_role_name;
4377
4378 IF l_fed_email <> l_email_to or l_notification_preference <> 'MAILTEXT' THEN
4379 /*Role exists, but email address is not up to date*/
4380
4381 wf_directory.SetAdHocRoleAttr(role_name => l_to_role_name
4382 ,display_name => l_to_role_name
4383 ,notification_preference => 'MAILTEXT'
4384 ,email_address => l_email_to);
4385 END IF;
4386 END IF;
4387 wf_engine.createProcess(itemtype => p_itemtype,
4388 itemkey => l_item_key,
4389 process => 'PON_FED_POST');
4390
4391 --set attribute for sender
4392 wf_engine.SetItemAttrText(itemtype => p_itemtype
4393 ,itemkey => l_item_key
4394 ,aname => '#FROM_ROLE'
4395 ,avalue => p_from_role_name);
4396
4397 wf_engine.SetItemAttrText(itemtype => p_itemtype
4398 ,itemkey => l_item_key
4399 ,aname => '#WFM_FROM'
4400 ,avalue => p_from_role_name);
4401
4402 wf_engine.SetItemAttrText(itemtype => p_itemtype
4403 ,itemkey => l_item_key
4404 ,aname => '#WFM_REPLYTO'
4405 ,avalue => l_buyer_email);
4406
4407 -- set attribute for receiver
4408 wf_engine.SetItemAttrText (itemtype => p_itemtype,
4409 itemkey => l_item_key,
4410 aname => 'TO_ROLE',
4411 avalue => l_to_role_name);
4412 -- set attribute for Email CC
4413 wf_engine.SetItemAttrText(itemtype => p_itemtype
4414 ,itemkey => l_item_key
4415 ,aname => '#WFM_CC'
4416 ,avalue => l_email_cc);
4417
4418 -- set email subject information
4419 wf_engine.SetItemAttrText (itemtype => p_itemtype,
4420 itemkey => l_item_key,
4421 aname => 'FORM_NAME',
4422 avalue => l_form_name);
4423
4424 wf_engine.SetItemAttrText (itemtype => p_itemtype,
4425 itemkey => l_item_key,
4426 aname => 'SOLNBR',
4427 avalue => l_solnbr);
4428
4429 -- Set email body information
4430 wf_engine.SetItemAttrText (itemtype => p_itemtype,
4431 itemkey => l_item_key,
4432 aname => 'XML',
4433 avalue => p_xml);
4434
4435 wf_engine.StartProcess (itemType => p_itemtype,
4436 itemKey => l_item_key);
4437
4438 EXCEPTION
4439 when PON_INVALID_EMAIL_TO_EXCEPTION then
4440 rollback to SAVEPOINT post_wf;
4441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4442 x_reutrn_error :='Email of FedBizOppsis is not setup properly.';
4443 WHEN OTHERS THEN
4444 rollback to SAVEPOINT post_wf;
4445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4446 l_error_code := SQLCODE;
4447 x_reutrn_error := 'Exception occured in post_wf'|| ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 500) ;
4448
4449 END;
4450
4451 procedure validate_fed_fields_length(p_field_code IN VARCHAR2,
4452 p_field_length IN NUMBER,
4453 x_result OUT NOCOPY VARCHAR2,
4454 x_error_code OUT NOCOPY VARCHAR2,
4455 x_error_message OUT NOCOPY VARCHAR2)
4456
4457 AS
4458 l_max_field_length NUMBER;
4459
4460 BEGIN
4461 SELECT field_length INTO l_max_field_length FROM pon_fields WHERE field_code = p_field_code;
4462
4463 IF( l_max_field_length IS NULL OR p_field_length <= l_max_field_length) THEN
4464 x_result:='S';
4465 ELSE
4466 x_result:='E';
4467 END IF;
4468
4469 EXCEPTION
4470 WHEN OTHERS THEN
4471 x_error_code := SQLCODE;
4472 x_error_message := SUBSTR(SQLERRM, 1, 100);
4473
4474 END validate_fed_fields_length;
4475
4476 FUNCTION get_fed_onfbo(p_auction_header_id IN NUMBER)
4477 RETURN VARCHAR2 IS
4478 l_status VARCHAR2(10);
4479 l_result VARCHAR2(10);
4480 BEGIN
4481
4482 pon_fbo_pkg.is_presol_published(p_auction_header_id,l_status);
4483 IF(l_status='S') THEN
4484 l_result:= 'True';
4485 ELSE
4486 l_result:= 'False';
4487 END IF;
4488
4489 RETURN l_result;
4490 END get_fed_onfbo;
4491
4492 procedure get_posting_date(p_auction_header_id IN NUMBER,
4493 p_form_id IN NUMBER,
4494 x_posting_date OUT NOCOPY VARCHAR2,
4495 x_result OUT NOCOPY VARCHAR2,
4496 x_error_code OUT NOCOPY VARCHAR2,
4497 x_error_message OUT NOCOPY VARCHAR2)
4498 AS
4499 l_api_name CONSTANT VARCHAR2(30) := 'GET_POSTING_DATE';
4500 BEGIN
4501
4502 SELECT To_Char(Nvl(Max(FBO_POST_DATE),SYSDATE),'YYYYMMDD') INTO x_posting_date FROM pon_fbo_posts
4503 WHERE DOCUMENT_ID = p_auction_header_id
4504 AND FBO_POST_STATUS = 'SUCCESS'
4505 AND form_id=p_form_id;
4506
4507 x_result:='S';
4508
4509 EXCEPTION
4510 WHEN OTHERS THEN
4511 x_error_code := SQLCODE;
4512 x_error_message := SUBSTR(SQLERRM, 1, 100);
4513 print_error_log(l_api_name, 'EXCEPTION x_error_code = '||x_error_code||' x_error_message = '||x_error_message);
4514 x_result := fnd_api.g_ret_sts_error;
4515 END get_posting_date;
4516
4517
4518 END PON_FORMS_UTIL_PVT;