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

Source


1 Package Body BSC_DBGEN_UTILS AS
2 /* $Header: BSCDBUTB.pls 120.10 2007/04/25 12:52:55 ashankar ship $ */
3 
4 
5 FUNCTION Is_Simulation_Report
6 (
7    p_short_name   IN    BSC_KPIS_VL.short_name%TYPE
8 ) RETURN VARCHAR2 IS
9 
10  l_return   VARCHAR2(10);
11 
12 
13  CURSOR c_sim IS
14  SELECT config_type
15  FROM   bsc_kpis_b
16  WHERE  short_name =p_short_name;
17 BEGIN
18   l_return := FND_API.G_FALSE;
19 
20   FOR cd IN c_sim LOOP
21    IF(cd.config_type =7) THEN
22     l_return :=FND_API.G_TRUE;
23    END IF;
24   END LOOP;
25 
26   RETURN l_return;
27 
28 EXCEPTION
29  WHEN OTHERS THEN
30   l_return := FND_API.G_FALSE;
31 
32 END Is_Simulation_Report;
33 
34 
35 
36 FUNCTION get_bsc_schema return varchar2 is
37 dummy1      VARCHAR2(32)  := null;
38 dummy2      VARCHAR2(32)  := null;
39 l_bsc_schema  VARCHAR2(32)  := null;
40 begin
41   IF (g_bsc_schema IS NOT NULL) THEN
42     return g_bsc_schema;
43   END IF;
44   IF (FND_INSTALLATION.GET_APP_INFO('BSC', dummy1, dummy2, l_bsc_schema)) THEN
45     NULL;
46   END IF;
47   g_bsc_schema := l_bsc_schema;
48   return l_bsc_schema;
49   EXCEPTION when others then
50   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_bsc_schema:'||sqlerrm);
51   raise;
52 end;
53 
54 /*---------------------------------------------------------------------
55  Get the actual schema name for the 'APPS' schema as it could be different
56  in different implementations.
57 
58 ---------------------------------------------------------------------*/
59 Function get_apps_schema  RETURN VARCHAR2 IS
60   l_schema varchar2(100);
61   CURSOR cApps IS
62     SELECT ORACLE_USERNAME
63     FROM  fnd_oracle_userid
64     WHERE oracle_id=900;
65 BEGIN
66   OPEN cApps;
67   FETCH cApps INTO l_schema;
68   CLOSE cApps;
69   return l_schema;
70   EXCEPTION when others then
71   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_apps_schema:'||sqlerrm);
72   raise;
73 END;
74 
75 PROCEDURE init IS
76 BEGIN
77   IF (g_initialized ) THEN
78     return;
79   END IF;
80   bsc_apps.init_bsc_apps;
81   g_bsc_schema := get_bsc_schema;
82   g_apps_schema := get_apps_schema;
83   g_initialized := true;
84   EXCEPTION when others then
85   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.init:'||sqlerrm);
86   raise;
87 
88 END;
89 
90 FUNCTION get_datatype(p_table_name in varchar2, p_column_name in varchar2) return VARCHAR2 IS
91   CURSOR cType IS
92   SELECT data_type, data_length
93   FROM all_tab_columns
94   where
95   (owner = g_bsc_schema or owner = g_apps_schema) and
96   table_name=p_table_name and
97   column_name=p_column_name;
98   l_type varchar2(100);
99   l_length number;
100 BEGIN
101   IF (g_initialized=false) THEN
102     init;
103   END IF;
104   OPEN cType;
105   FETCH cType INTO l_type, l_length;
106   CLOSE cType;
107   IF (l_length IS NOT NULL) THEN
108     l_type := l_type ||'('||l_length||')';
109   END IF;
110   return l_type;
111   EXCEPTION when others then
112   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_datatype:'||sqlerrm);
113   raise;
114 
115 END;
116 
117 --===========================================================================+
118 --
119 --  Name:      Get_New_Big_In_Cond_Number
120 --   Description:   Clean values for the given variable_id and return a 'IN'
121 --            condition string.
122 --   Parameters:  x_variable_id  variable id.
123 --            x_column_name  column name (left part of the condition)
124 --============================================================================
125 
126 Function Get_New_Big_In_Cond_Number( x_variable_id IN NUMBER, x_column_name IN VARCHAR2)
127 return VARCHAR2 IS
128   l_cond varchar2(1000);
129 BEGIN
130   IF (g_initialized=false) THEN
131     init;
132   END IF;
133   DELETE FROM BSC_TMP_BIG_IN_COND WHERE SESSION_ID = USERENV('SESSIONID') AND VARIABLE_ID = x_variable_id;
134   l_cond := x_column_name || ' IN (' ||
135       ' SELECT VALUE_N FROM BSC_TMP_BIG_IN_COND WHERE SESSION_ID = USERENV(''SESSIONID'')'||
136       ' AND VARIABLE_ID = ' || x_variable_id || ')';
137   return l_cond;
138 EXCEPTION when others then
139   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Number:'||sqlerrm);
140   raise;
141 End;
142 
143 --===========================================================================+
144 --   Name:      Add_Value_Big_In_Cond_Number
145 --   Description:   Insert the given value into the temporary table of big
146 --            'in' conditions for the given variable_id.
147 --   Parameters:  x_variable_id  variable id.
148 --            x_value      value
149 --============================================================================
150 PROCEDURE Add_Value_Big_In_Cond_Number(x_variable_id IN NUMBER, x_value IN NUMBER) IS
151 BEGIN
152   IF (g_initialized=false) THEN
153     init;
154   END IF;
155   bsc_apps.Add_Value_Big_In_Cond(x_variable_id , x_value);
156 EXCEPTION when others then
157   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number:'||sqlerrm);
158   raise;
159 End;
160 
161 --===========================================================================+
162 --   Name:      Get_New_Big_In_Cond_Varchar2
163 --   Description:   Clean values for the given variable_id and return a 'IN'
164 --            condition string.
165 --   Parameters:  x_variable_id  variable id.
166 --            x_column_name  column name (left part of the condition)
167 --============================================================================
168 Function Get_New_Big_In_Cond_Varchar2( x_variable_id in number, x_column_name in varchar2)
169 return VARCHAR2 IS
170   cond   varchar2(1000);
171 BEGIN
172   IF (g_initialized=false) THEN
173     init;
174   END IF;
175   DELETE FROM BSC_TMP_BIG_IN_COND WHERE SESSION_ID = USERENV('SESSIONID') AND VARIABLE_ID = x_variable_id;
176   cond := 'UPPER('||  x_column_name  || ') IN ('||
177       ' SELECT UPPER(VALUE_V) FROM BSC_TMP_BIG_IN_COND WHERE SESSION_ID = USERENV(''SESSIONID'') AND VARIABLE_ID = '||x_variable_id||')';
178   return cond;
179 EXCEPTION when others then
180   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Varchar2:'||sqlerrm);
181   raise;
182 End;
183 --===========================================================================+
184 --   Name:      Add_Value_Big_In_Cond_Varchar2
185 --   Description:   Insert the given value into the temporary table of big
186 --            --in' conditions for the given variable_id.
187 --   Parameters:  x_variable_id  variable id.
188 --            x_value      value
189 --============================================================================*/
190 PROCEDURE Add_Value_Big_In_Cond_Varchar2(x_variable_id IN NUMBER, x_value IN VARCHAR2) IS
191 BEGIN
192   IF (g_initialized=false) THEN
193     init;
194   END IF;
195   bsc_apps.Add_Value_Big_In_Cond(x_variable_id , x_value);
196 
197 EXCEPTION when others then
198   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Varchar2:'||sqlerrm);
199   raise;
200 End;
201 
202 FUNCTION get_dbgen_fact_id(p_fact_name IN VARCHAR2, p_application_short_name IN VARCHAR2) RETURN NUMBER IS
203   /*CURSOR cFactID IS
204   SELECT indicator FROM BSC_DBGEN_FACTS
205   WHERE source = p_application_source
206   AND fact_name = p_fact_name;*/
207 
208   l_fact_id NUMBER;
209 BEGIN
210   /*OPEN cFactID;
211   FETCH cFactID INTO l_fact_id;
212   CLOSE cFactID;
213   return l_fact_id;*/
214   return to_number(p_fact_name);
215   EXCEPTION when others then
216   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_dbgen_fact_id:'||sqlerrm);
217   raise;
218 END;
219 
220 --****************************************************************************
221 --  GetCamposExpresion
222 --
223 --   DESCRIPTION:
224 --     Get in an array the list of fields in the given expression.
225 --     Return the number of fields.
226 --     Example. Expresion = 'IIF(Not IsNull(SUM(A)), C, B)'
227 --     CamposExpresion() = |A|C|B|, GetCamposExpresion = 3
228 --  PARAMETERS:
229 --     CamposExpresion(): array to be populated
230 --     Expresion: expression
231 --  AUTHOR/DATE  -  MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
232 --****************************************************************************
233 FUNCTION get_measure_list(p_expression IN VARCHAR2) RETURN dbms_sql.varchar2_table IS
234   i NUMBER;
235   l_measure_list_tmp dbms_sql.varchar2_table;
236   l_expression VARCHAR2(1000);
237   l_measure_list dbms_sql.varchar2_table ;
238 BEGIN
239   IF (g_initialized=false) THEN
240     init;
241   END IF;
242   l_expression := p_expression;
243   BSC_METADATA_OPTIMIZER_PKG.InitReservedFunctions;
244   --Replace the operators by ' '
245   i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.first;
246 
247   LOOP
248     l_expression := Replace(l_expression, BSC_METADATA_OPTIMIZER_PKG.gReservedOperators(i), ' ');
249     EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.last;
250     i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.next(i);
251   END LOOP;
252   --Break down the expression which is separated by ' '
253   i := BSC_MO_HELPER_PKG.DecomposeString(l_expression, ' ', l_measure_list_tmp );
254   i:= l_measure_list_tmp .first;
255   LOOP
256     EXIT WHEN l_measure_list_tmp .count = 0;
257     If l_measure_list_tmp (i) IS NOT NULL Then
258       If BSC_MO_HELPER_PKG.FindIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gReservedFunctions, l_measure_list_tmp (i)) = -1 Then
259         --The word campos(i) is not a reserved function
260         If UPPER(l_measure_list_tmp (i)) <> 'NULL' Then
261           --the word is not 'NULL'
262           If Not  BSC_MO_HELPER_PKG.IsNumber(l_measure_list_tmp (i)) Then
263             --the word is not a constant
264             l_measure_list(l_measure_list.count+1) := l_measure_list_tmp (i);
265           END IF;
266         END IF;
267       END IF;
268     END IF;
269     EXIT WHEN i = l_measure_list_tmp.last;
270     i := l_measure_list_tmp.next(i);
271   END LOOP;
272   return l_measure_list;
273   EXCEPTION when others then
274   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_measure_list:'||sqlerrm||', expression='||p_expression);
275   raise;
276 End;
277 
278 --***************************************************************************
279 --  getKPIPropertyValue : LeerMatrixINfo
280 --  DESCRIPTION:
281 --     Return the value oif the given variable of the given indicator from
282 --     table BSC_KPI_PROPERTIES.
283 --
284 --  PARAMETERS:
285 --     Indic: indicator code
286 --     Variable: variable name
287 --     Default: default value
288 --     db_obj: database
289 --
290 --  AUTHOR/DATE  -  MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
291 --***************************************************************************
292 Function get_kpi_property_value(
293     p_kpi IN NUMBER,
294   p_property IN VARCHAR2,
295   p_default IN NUMBER)
296   return NUMBER IS
297 l_value number := null;
298 CURSOR cProperty IS
299   SELECT PROPERTY_VALUE
300   FROM BSC_KPI_PROPERTIES
301   WHERE INDICATOR = p_kpi
302   AND PROPERTY_CODE = p_property;
303 
304 BEGIN
305   IF (g_initialized=false) THEN
306     init;
307   END IF;
308   OPEN cProperty;
309   FETCH cProperty INTO l_value;
310   CLOSE cProperty;
311   IF l_value is not null THEN
312      return l_value;
313   END IF;
314   return p_default;
315   EXCEPTION when others then
316   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_kpi_property_value:'||sqlerrm||', p_kpi='||p_kpi||', p_property='||p_property||', p_default='||p_default);
317   raise;
318 End;
319 
320 PROCEDURE add_property(
321 p_properties in out nocopy BSC_DBGEN_STD_METADATA.tab_ClsProperties,
322 p_name in varchar2,
323 p_value in number)
324 IS
325 BEGIN
326   add_property(p_properties, p_name, to_char(p_value));
327 END;
328 PROCEDURE add_property(
329 p_properties in out nocopy BSC_DBGEN_STD_METADATA.tab_ClsProperties,
330 p_name in varchar2,
331 p_value in varchar2)
332 IS
333 l_property BSC_DBGEN_STD_METADATA.ClsProperties;
334 BEGIN
335   IF (g_initialized=false) THEN
336     init;
337   END IF;
338   IF (p_properties.count=0) THEN
339     p_properties(1).name := p_name;
340     p_properties(1).value := p_value;
341     return;
342   END IF;
343 
344   FOR i IN p_properties.first..p_properties.last LOOP
345     IF (p_properties(i).name = p_name) THEN
346       p_properties(i).value := p_value;
347       return;
348     END IF;
349   END LOOP;
350 
351   l_property.name := p_name;
352   l_property.value := p_value;
353   p_properties(p_properties.last+1) := l_property;
354   EXCEPTION when others then
355   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.add_property:'||sqlerrm||', p_name='||p_name||', p_Value='||p_value);
356   raise;
357 END;
358 
359 FUNCTION get_property_value(p_properties IN BSC_DBGEN_STD_METADATA.tab_ClsProperties, p_name in varchar2) return VARCHAR2
360 IS
361 BEGIN
362   IF (g_initialized=false) THEN
363     init;
364   END IF;
365   IF (p_properties.count=0) THEN
366     return BSC_DBGEN_STD_METADATA.BSC_PROPERTY_NOT_FOUND ;
367   END IF;
368   FOR i IN p_properties.first..p_properties.last LOOP
369     IF (p_properties(i).name = p_name) THEN
370       return p_properties(i).value;
371     END IF;
372   END LOOP;
373   return BSC_DBGEN_STD_METADATA.BSC_PROPERTY_NOT_FOUND ;
374 
375   EXCEPTION when others then
376   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_property_value:'||sqlerrm||', p_name='||p_name);
377   raise;
378 END;
379 
380 FUNCTION get_source_table_names(p_table_name IN VARCHAR2) RETURN DBMS_SQL.VARCHAR2_TABLE IS
381 CURSOR cList IS
382 select source_table_name from bsc_db_tables_rels
383 connect by table_name = prior source_table_name
384 start with table_name = p_table_name;
385 l_list DBMS_SQL.VARCHAR2_TABLE;
386 BEGIN
387   IF (g_initialized=false) THEN
388     init;
389   END IF;
390   FOR i IN cList LOOP
391     l_list(l_list.count) := i.source_table_name;
392   END LOOP;
393   return l_list;
394   EXCEPTION when others then
395   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_source_table_names:'||sqlerrm||', p_table_name='||p_table_name);
396   raise;
397 END;
398 
399 FUNCTION get_table_type(p_table_name IN VARCHAR2) RETURN VARCHAR2
400 IS
401 l_table_type VARCHAR2(10);
402 CURSOR cTableType IS
403 select count(1)
404 from bsc_db_tables tbl,
405 bsc_db_tables_rels rels
406 where
407 rels.table_name = p_table_name
408 and rels.source_table_name = tbl.table_name
409 and tbl.table_type = 0;
410 
411 BEGIN
412   IF (g_initialized=false) THEN
413     init;
414   END IF;
415   BEGIN
416   SELECT table_type INTO l_table_type FROM BSC_DB_TABLES where table_name = p_table_name;
417     EXCEPTION WHEN OTHERS THEN
418       SELECT count(1) INTO l_table_type FROM BSC_SYS_DIM_LEVELS_B where level_table_name = p_table_name;
419       IF l_table_type <>0  THEN
420         return 'D';
421       ELSE
422         return null;
423       END IF;
424   END;
425   --B = 1, S = 1, T = 1, I = 0, DI = 2
426   IF l_table_type = 0 THEN
427     return 'I';
428   ELSIF l_table_type = 2 THEN
429     return 'DI';
430   ELSIF l_table_type <> 1 THEN
431     return null;
432   END IF;
433 
434   -- Table type is 1 : Can be B, S or T table
435   l_table_type := 0;
436   OPEN cTableType;
437   FETCH cTableType INTO l_table_type ;
438   CLOSE cTableType;
439   IF (l_table_type<>0) THEN
440     return 'B';
441   ELSIF p_table_name like 'BSC_T%' THEN
442     return 'T';
443   ELSIF p_table_name like 'BSC_S%' THEN
444     return 'S';
445   END IF;
446   return null;
447 EXCEPTION when others then
448   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_table_type:'||sqlerrm||', p_table_name='||p_table_name);
449   raise;
450 END;
451 
452 FUNCTION get_mvlog_for_table(p_table_name IN VARCHAR2) RETURN VARCHAR2
453 IS
454 CURSOR cMVLog(p_owner VARCHAR2) IS
455 select log_table from all_snapshot_logs where log_owner=p_owner and master = p_table_name;
456 l_mvlog_name VARCHAR2(100);
457 BEGIN
458   IF (g_initialized=false) THEN
459     init;
460   END IF;
461   l_mvlog_name := null;
462   OPEN cMVLog(g_bsc_schema);
463   FETCH cMVLog INTO l_mvlog_name;
464   CLOSE cMVLog;
465   RETURN l_mvlog_name;
466   EXCEPTION when others then
467   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_mvlog_for_table:'||sqlerrm||', p_table_name='||p_table_name);
468   raise;
469 END;
470 
471 
472 -- given the objective short_name, tells us if the
473 -- the Objective is a BSC Objective or a BSC Sourced Report or a non-BSC Sourced report
474 FUNCTION get_Objective_Type (
475     p_Short_Name IN VARCHAR2
476 ) RETURN VARCHAR2
477 IS
478     l_Count  NUMBER;
479 BEGIN
480   IF (g_initialized=false) THEN
481     init;
482   END IF;
483   IF (p_short_name is NULL) THEN
484     return 'OBJECTIVE';
485   END IF;
486 
487   IF BSC_BIS_CUSTOM_KPI_UTIL_PUB.IS_OBJECTIVE_REPORT_TYPE(p_SHORT_NAME) = FND_API.G_TRUE THEN
488   -- Removed call to API BSC_BIS_CUSTOM_KPI_UTIL_PUB.Is_Objective_AutoGen_Type to fix Bug#4602405
489       RETURN 'BSCREPORT';
490   ELSIF(Is_Simulation_Report(p_SHORT_NAME)= FND_API.G_TRUE)THEN
491       RETURN 'SIMULATION';
492   ELSE
493       RETURN 'OBJECTIVE';
494   END IF;
495 EXCEPTION
496     WHEN OTHERS THEN
497         RETURN 'OBJECTIVE';
498 END get_Objective_Type;
499 
500 -- Given a varchar2 string, chop it into chunks and return
501 FUNCTION get_char_chunks(p_msg IN VARCHAR2, p_chunk_size IN NUMBER default 256) return DBMS_SQL.VARCHAR2_TABLE IS
502 l_varchar2_Table DBMS_SQL.VARCHAR2_TABLE;
503 l_chunk VARCHAR2(2000);
504 l_msg VARCHAR2(32767);
505 l_chunk_size NUMBER;
506 BEGIN
507  IF (p_msg IS NULL or p_chunk_size <= 0) THEN
508    return l_varchar2_table;
509  END IF;
510  l_msg := p_msg;
511  l_chunk_size := p_chunk_size;
512  IF (l_chunk_size > 2000) THEN
513    l_chunk_size := 2000;
514  END IF;
515  LOOP
516    l_chunk := substr(l_msg, 1, l_chunk_size);
517    l_varchar2_table(l_varchar2_table.count) := l_chunk;
518    EXIT WHEN length(l_msg) <= l_chunk_size;
519    l_msg := substr(l_msg, l_chunk_size+1, length(l_msg));
520  END LOOP;
521  return l_varchar2_table;
522 EXCEPTION when others then
523   fnd_file.put_line(FND_FILE.LOG, 'Exception in BSC_DBGEN_UTILS.get_char_chunks:'||sqlerrm||', p_msg='||p_msg||', p_chunk_size='||p_chunk_size);
524   raise;
525 END;
526 
527 /****************************************************************************
528   get_objective_type_for_base_table
529 
530   DESCRIPTION:
531      Returns the type of objective using this B table: AW or MV
532   PARAMETERS:
533      p_b_table_Name: Base table name
534   AUTHOR/DATE  -  MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
535     Arun.Santhanam
536 ****************************************************************************/
537 Function get_objective_type_for_b_table(
538     p_b_table_name IN VARCHAR2
539 ) return  VARCHAR2 IS
540     l_indicator NUMBER;
541     CURSOR cTables IS
542     select distinct indicator from bsc_kpi_data_tables where
543 table_name in (
544 select distinct rels.table_name
545 from bsc_db_Tables_rels rels
546 connect by prior table_name=source_table_name
547 start with rels.source_table_name = p_b_table_name);
548 
549 BEGIN
550     open cTables;
551     FETCH cTables into l_indicator;
552     IF (cTables%NOTFOUND) THEN
553       CLOSE cTables;
554       return null;
555     ELSE
556       CLOSE cTables;
557       IF get_kpi_property_value(l_indicator, 'IMPLEMENTATION_TYPE', 1)=2 THEN
558         return 'AW';
559       ELSE
560       return 'MV';
561     END IF;
562     END IF;
563 End;
564 
565 
566 FUNCTION table_exists(p_table_name IN VARCHAR2) return BOOLEAN IS
567    l_count NUMBER;
568 
569     CURSOR cTables(pTableName IN VARCHAR2, pOwner IN VARCHAR2) IS
570     SELECT 1 FROM ALL_TABLES
571     WHERE TABLE_NAME = pTableName
572     AND OWNER = pOwner;
573 BEGIN
574     IF (g_initialized=false) THEN
575        init;
576     END IF;
577     l_count := 0;
578     open cTables(p_table_name, g_bsc_schema);
579     FETCH cTables into l_count;
580     IF (cTables%NOTFOUND) THEN
581         CLOSE cTables;
582         return false;
583     ELSE
584         CLOSE cTables;
585         return true;
586     END IF;
587 End;
588 
589 /****************************************************************************
590   IS_TMP_TABLE_EXISTS
591 
592   DESCRIPTION:
593      Returns TRUE if the given tmp table exists in the database.
594   PARAMETERS:
595      Table_Name: tmp table name
596   AUTHOR/DATE  -  MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
597     CALAW
598 ****************************************************************************/
599 Function IS_TMP_TABLE_EXISTED(
600     Table_Name IN VARCHAR2
601 ) return Boolean IS
602 
603     l_count NUMBER;
604 
605     CURSOR cTables(pTableName IN VARCHAR2, pOwner IN VARCHAR2) IS
606     SELECT 1 FROM ALL_TABLES
607     WHERE TABLE_NAME = pTableName
608     AND OWNER = pOwner
609     AND TEMPORARY = 'Y';
610 
611 BEGIN
612     IF (g_initialized=false) THEN
613        init;
614     END IF;
615     l_count := 0;
616 
617     open cTables(table_name, g_bsc_schema);
618     FETCH cTables into l_count;
619     IF (cTables%NOTFOUND) THEN
620         CLOSE cTables;
621         return false;
622     ELSE
623         CLOSE cTables;
624         return true;
625     END IF;
626 End;
627 
628 
629 FUNCTION parse_value(p_string IN VARCHAR2, p_property_name IN VARCHAR2, p_assignment_operator IN VARCHAR2, p_pre_separator IN VARCHAR2, p_post_separator IN VARCHAR2) return varchar2 IS
630 l_pos number;
631 l_part_string varchar2(32000);
632 l_pattern VARCHAR2(1000);
633 l_value VARCHAR2(1000);
634 BEGIN
635 
636   if (p_pre_separator is null and p_post_separator is null) then -- single value, just return RHS of assignment operator
637     l_pos := instr(p_string, p_assignment_operator);
638     return substr(p_string, l_pos+1);
639   end if;
640 
641 
642   l_pattern :=  p_pre_separator||p_property_name||p_assignment_operator;
643   l_pos := instr(p_string, l_pattern);
644   if l_pos = 0 then -- check if its the first entry
645     l_pos := instr(p_string, p_property_name||p_assignment_operator);
646     if (l_pos=0) then
647       return null;
648     end if;
649     if (l_pos<>1) then
650       return null;
651     end if;
652     -- this is the first entry and is without pre separator though specified
653     l_part_string := substr(p_string, length(p_property_name||p_assignment_operator)+1);
654   else
655     l_part_string := substr(p_string, l_pos+length(l_pattern));
656   end if;
657 
658 
659   -- so we've reached the point in the string where the value starts
660   if (p_post_separator is not null) then -- post separator specified
661     l_pos := instr(l_part_string, p_post_separator);
662 
663     if l_pos =0 then -- possibly last value without post separator though its specified
664       if (instr(l_part_string, p_assignment_operator)=0) then -- really last value
665         l_pos := length(l_part_string)+1;
666       end if;
667     end if;
668   else
669     l_pos := instr(l_part_string, p_pre_separator);
670     if l_pos=0 then -- end of string
671       return l_part_string;
672     end if;
673   end if;
674 
675   l_value := substr(l_part_string, 1, l_pos-1);
676   return l_value;
677 END;
678 
679 -- assumes object is valid and exists
680 -- returns apps if this does not exist
681 FUNCTION get_table_owner(p_table_name VARCHAR2) RETURN VARCHAR2 IS
682 cursor cOwner is
683 select table_owner from user_synonyms
684 where synonym_name=p_table_name;
685 l_owner varchar2(100);
686 BEGIN
687   IF NOT g_initialized THEN
688     init;
689   END IF;
690   open cOwner;
691   fetch cOwner into l_owner;
692   close cOwner;
693   IF l_owner is NOT NULL THEN
694     return l_owner;
695   ELSE
696     return g_apps_schema;
697   END IF;
698 
699 END;
700 
701 
702 PROCEDURE drop_table(p_table_name IN VARCHAR2) IS
703 BEGIN
704   bsc_apps.init_bsc_apps;
705   bsc_apps.do_ddl(x_statement=>'drop table '||p_table_name,
706                   x_statement_type=>ad_ddl.drop_table,
707                   x_object_name=>p_table_name);
708 END;
709 
710 
711 --New API for Bug 4902308
712 PROCEDURE add_string(p_varchar2_table IN OUT NOCOPY DBMS_SQL.VARCHAR2A, p_string IN VARCHAR2) IS
713 l_index number;
714 l_current_length number;
715 l_val varchar2(32767);
716 BEGIN
717   if p_varchar2_table.count=0 then
718     p_varchar2_table(1) := p_string;
719     return;
720   end if;
721   l_index := p_varchar2_table.last;
722   if length(p_varchar2_table(l_index)) + length(p_string) > 32767 then --
723     l_index := l_index +1;
724     p_varchar2_table(l_index) := p_string;
725     return;
726   end if;
727   p_varchar2_table(l_index) := p_varchar2_table(l_index)||p_string;
728   exception when others then
729     fnd_file.put_line(FND_FILE.log, 'Error in add_string:string='||p_string||', error='||sqlerrm);
730     raise;
731 END;
732 
733 
734 PROCEDURE execute_immediate(p_varchar2_table IN DBMS_SQL.VARCHAR2A) IS
735 l_dummy dbms_sql.varchar2_table;
736 BEGIN
737   execute_immediate(p_varchar2_table, l_dummy, 0);
738 END;
739 
740 ---------------------------------------------------------------
741 --One way to make this proc. generic is to ensure loader populates
742 --the bind variables sequentially
743 --ie assume is that all parameters are bound as :1, :2, :3 etc
744 --currently this is not the case. this requires change in bsc_update_base_v2
745 ---------------------------------------------------------------
746 PROCEDURE execute_immediate(p_varchar2_table IN DBMS_SQL.VARCHAR2A,
747                             p_bind_vars_values dbms_sql.varchar2_table,
748                             p_num_bind_vars number) IS
749 e_max_bind_vars_exceeded exception;
750 
751 l_sql dbms_sql.varchar2a;
752 
753  l_cur         number;
754  dummy         NUMBER;
755 BEGIN
756   fnd_file.put_line(FND_FILE.LOG, 'Chk 0, bind#='||p_num_bind_vars);
757   for i in 1..p_varchar2_table.count loop
758     l_sql(i) := p_varchar2_table(i);
759   end loop;
760   fnd_file.put_line(FND_FILE.LOG, 'Chk 1');
761 
762   for i in p_varchar2_table.count+1..50 loop
763     l_sql(i) := null;
764   end loop;
765   if nvl(p_num_bind_vars,0) = 0 then
766     l_cur := dbms_sql.open_cursor;
767     dbms_sql.parse(
768                 c             =>  l_cur,
769                 statement     => l_sql,
770                 lb            => l_sql.first,
771                 ub            => l_sql.last,
772                 lfflg         => TRUE,
773                 language_flag => dbms_sql.native );
774 
775     dummy := dbms_sql.execute(l_cur);
776     dbms_sql.close_cursor(l_cur);
777     fnd_file.put_line(FND_FILE.LOG, 'Chk 4');
778   elsif p_num_bind_vars = 1 then
779       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
780                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
781                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
782                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
783                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
784       using p_bind_vars_values(1);
785   elsif p_num_bind_vars = 2 then
786       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
787                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
788                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
789                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
790                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
791       using p_bind_vars_values(1), p_bind_vars_values(2);
792   elsif p_num_bind_vars = 3 then
793       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
794                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
795                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
796                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
797                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
798       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3);
799   elsif p_num_bind_vars = 4 then
800       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
801                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
802                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
803                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
804                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
805       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4);
806   elsif p_num_bind_vars = 5 then
807       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
808                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
809                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
810                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
811                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
812       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5);
813   elsif p_num_bind_vars = 6 then
814       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
815                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
816                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
817                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
818                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
819       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
820             p_bind_vars_values(6);
821   elsif p_num_bind_vars = 7 then
822       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
823                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
824                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
825                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
826                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
827       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
828             p_bind_vars_values(6), p_bind_vars_values(7);
829   elsif p_num_bind_vars = 8 then
830       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
831                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
832                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
833                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
834                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
835       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
836             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8);
837   elsif p_num_bind_vars = 9 then
838       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
839                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
840                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
841                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
842                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
843       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
844             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9);
845   elsif p_num_bind_vars = 10 then
846       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
847                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
848                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
849                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
850                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
851       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
852             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10);
853   elsif p_num_bind_vars = 11 then
854       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
855                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
856                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
857                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
858                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
859       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
860             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
861             p_bind_vars_values(11);
862   elsif p_num_bind_vars = 12 then
863       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
864                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
865                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
866                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
867                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
868       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
869             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
870             p_bind_vars_values(11), p_bind_vars_values(12);
871   elsif p_num_bind_vars = 13 then
872       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
873                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
874                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
875                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
876                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
877       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
878             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
879             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13);
880   elsif p_num_bind_vars = 14 then
881       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
882                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
883                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
884                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
885                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
886       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
887             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
888             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14);
889   elsif p_num_bind_vars = 15 then
890       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
891                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
892                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
893                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
894                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
895       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
896             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
897             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15);
898   elsif p_num_bind_vars = 16 then
899       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
900                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
901                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
902                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
903                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
904       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
905             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
906             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15),
907             p_bind_vars_values(16);
908   elsif p_num_bind_vars = 17 then
909       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
910                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
911                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
912                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
913                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
914       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
915             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
916             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15),
917             p_bind_vars_values(16), p_bind_vars_values(17);
918   elsif p_num_bind_vars = 18 then
919       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
920                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
921                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
922                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
923                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
924       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
925             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
926             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15),
927             p_bind_vars_values(16), p_bind_vars_values(17), p_bind_vars_values(18);
928   elsif p_num_bind_vars = 19 then
929       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
930                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
931                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
932                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
933                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
934       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
935             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
936             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15),
937             p_bind_vars_values(16), p_bind_vars_values(17), p_bind_vars_values(18), p_bind_vars_values(19);
938   elsif p_num_bind_vars = 20 then
939       execute immediate l_sql(1) ||l_sql(2) ||l_sql(3) ||l_sql(4) ||l_sql(5) ||l_sql(6) ||l_sql(7) ||l_sql(8) ||l_sql(9) ||l_sql(10)||
940                         l_sql(11)||l_sql(12)||l_sql(13)||l_sql(14)||l_sql(15)||l_sql(16)||l_sql(17)||l_sql(18)||l_sql(19)||l_sql(20)||
941                         l_sql(21)||l_sql(22)||l_sql(23)||l_sql(24)||l_sql(25)||l_sql(26)||l_sql(27)||l_sql(28)||l_sql(29)||l_sql(30)||
942                         l_sql(31)||l_sql(32)||l_sql(33)||l_sql(34)||l_sql(35)||l_sql(36)||l_sql(37)||l_sql(38)||l_sql(39)||l_sql(40)||
943                         l_sql(41)||l_sql(42)||l_sql(43)||l_sql(44)||l_sql(45)||l_sql(46)||l_sql(47)||l_sql(48)||l_sql(49)||l_sql(50)
944       using p_bind_vars_values(1), p_bind_vars_values(2), p_bind_vars_values(3), p_bind_vars_values(4), p_bind_vars_values(5),
945             p_bind_vars_values(6), p_bind_vars_values(7), p_bind_vars_values(8), p_bind_vars_values(9), p_bind_vars_values(10),
946             p_bind_vars_values(11), p_bind_vars_values(12), p_bind_vars_values(13), p_bind_vars_values(14), p_bind_vars_values(15),
947             p_bind_vars_values(16), p_bind_vars_values(17), p_bind_vars_values(18), p_bind_vars_values(20);
948   else
949       raise e_max_bind_vars_exceeded;
950   end if;
951   exception
952     when e_max_bind_vars_exceeded then
953       fnd_file.put_line(FND_FILE.LOG,'Maximun bind variables supported by this api was exceeded');
954       raise;
955     when others then
956       fnd_file.put_line(FND_FILE.LOG,'Error in execute_immediate: error='||sqlerrm||', count = '||p_varchar2_table.count||', bind#='||p_num_bind_vars);
957       for i in 1..p_varchar2_table.count loop
958         for j in 1..128 loop
959           fnd_file.put_line(FND_FILE.LOG, substr(p_varchar2_table(i), (j-1)*256+1, 256));
960         end loop;
961       end loop;
962       raise;
963 END;
964 
965 
966 
967 
968 END BSC_DBGEN_UTILS;