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.GCS_DATA_TEMPLATE_UTIL_PKG

Source


1 PACKAGE BODY GCS_DATA_TEMPLATE_UTIL_PKG  AS
2 /* $Header: gcsdtmanipb.pls 120.17 2007/02/27 14:46:06 sangarg ship $ */
3 
4 g_api varchar2(50) := 'gcs.plsql.GCS_DATA_TEMPLATE_UTIL_PKG';
5 
6 -- PRIVATE PROCEDURE
7 -- Used by value set map template manipulation
8 PROCEDURE replace_clob ( p_src_clob        IN OUT NOCOPY CLOB,
9                          p_replace_with    IN VARCHAR2,
10                          p_first_Offset    IN INTEGER,
11                          p_second_variable IN VARCHAR2
12                        )
13 IS
14 
15  l_buffer     INTEGER := 30000;
16  l_vs_map_clob      CLOB := EMPTY_CLOB;
17  l_varchar    VARCHAR2(32767);
18  l_start      NUMBER;
19  l_read_length NUMBER;
20 
21 BEGIN
22 
23    IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
24        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', '<<begin>>');
25    END IF;
26 
27    DBMS_LOB.createtemporary(l_vs_map_clob,TRUE);
28    l_start:=1;
29    l_read_length := LENGTH( DBMS_LOB.SUBSTR(p_src_clob, l_buffer, l_start));
30    -- WRITE THE FIRST PART OF THE CLOB
31    FOR i IN 1..CEIL(p_first_Offset/l_read_length) LOOP
32 
33       IF(l_start+l_read_length > p_first_Offset) THEN
34          l_buffer := p_first_Offset-l_start;
35       END IF;
36 
37       l_varchar := DBMS_LOB.SUBSTR(p_src_clob, l_buffer, l_start);
38       DBMS_LOB.WRITEAPPEND(l_vs_map_clob, LENGTH(l_varchar), l_varchar);
39       l_start := l_start + LENGTH(l_varchar);
40 
41       IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
42           FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', 'l_start : '||l_start);
43           FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', 'l_read_length : '||l_read_length);
44           FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', 'p_first_Offset : '||p_first_Offset);
45           FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', 'l_buffer : '||l_buffer);
46           FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', 'l_varchar : '||l_varchar);
47       END IF;
48 
49    END LOOP;
50 
51    -- WRITE THE REPLACEMENT
52    DBMS_LOB.writeappend(l_vs_map_clob, length(p_replace_with), p_replace_with);
53    DBMS_LOB.writeappend(l_vs_map_clob, LENGTH(p_second_variable), p_second_variable);
54    p_src_clob:=l_vs_map_clob;
55    DBMS_LOB.freetemporary(l_vs_map_clob);
56 
57    IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
58        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'replace_clob', '<<end>>');
59    END IF;
60 
61    EXCEPTION
62         WHEN OTHERS THEN
63         BEGIN
64            IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_ERROR) THEN
65                   FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_api || '.' || 'replace_clob', substr(SQLERRM,1,255));
66            END IF;
67         END;
68 END replace_clob;
69 
70 --
71 -- Procedure
72 --   GCS_REPLACE_DT_PROC
73 -- Purpose
74 --   Data Template Replacement Procedure.
75 -- Arguments
76 --   * None *
77 -- Example
78 --   GCS_DATA_TEMPLATE_UTIL_PKG.GCS_REPLACE_DT_PROC;
79 -- Notes
80 --
81 PROCEDURE gcs_replace_dt_proc( x_errbuf  OUT NOCOPY VARCHAR2,
82                                x_retcode OUT NOCOPY VARCHAR2
83                               )
84 IS
85 
86   TYPE r_gcs_datatemplates IS RECORD
87                                 (
88                                   template_code          VARCHAR2(150),
89                                   template_data          BLOB
90                                  );
91 
92   TYPE t_gcs_datatemplates	IS TABLE OF r_gcs_datatemplates;
93 
94   l_gcs_base_datatemplates t_gcs_datatemplates;
95   l_gcs_datatemplates t_gcs_datatemplates;
96 
97   l_select_list_gcs     VARCHAR2(10000);
98   l_select_list_dstb    VARCHAR2(10000);
99   l_select_list_dsload  VARCHAR2(10000);
100   l_gl_posted_select_list VARCHAR2(10000);
101 
102   l_table_list_gcs      VARCHAR2(10000);
103   l_table_list_dstb     VARCHAR2(10000);
104   --fix 5351083
105   --l_table_list_dsload   VARCHAR2(10000);
106 
107   l_where_list_gcs      VARCHAR2(10000);
108   --fix 5351083
109   --l_where_list_dsload   VARCHAR2(10000);
110   l_where_list_dstb     VARCHAR2(10000);
111   --fix 5351083
112   --l_group_by_list       VARCHAR2(10000);
113   l_dstb_group_by_list  VARCHAR2(10000);
114   l_gl_posted_group_list VARCHAR2(10000);
115 
116   l_element_list_gcs    VARCHAR2(10000);
117   l_element_list_dsload VARCHAR2(10000);
118   --fix 5351083
119   --l_element_list_dstb   VARCHAR2(10000);
120 
121   l_orderby_list_gcs    VARCHAR2(10000);
122   l_orderby_list_dsload VARCHAR2(10000);
123   l_orderby_list_dstb   VARCHAR2(10000);
124 
125   l_vs_group_list       VARCHAR2(32767);
126   l_remaining_str       VARCHAR2(100);
127 
128   l_base_templatecode   VARCHAR2(50);
129 
130   l_varchar             VARCHAR2(32767);
131   l_start	              INTEGER := 1;
132   l_buffer              INTEGER :=32767;
133   l_off_write           INTEGER := 1;
134   l_amt_write           INTEGER := 32767;
135 
136   l_start_tag           NUMBER;
137   l_end_tag             NUMBER;
138   l_sub_string          VARCHAR2(32767);
139   l_start_tag_length    NUMBER;
140 
141   l_blob_in             BLOB;
142   l_blob_out            BLOB;
143   l_clob                CLOB;
144 
145   CURSOR c_base_data_templates
146   IS
147           SELECT lob_code,
148                  file_data
149             FROM xdo_lobs
150            WHERE application_short_name = 'GCS'
151              AND lob_type = 'DATA_TEMPLATE'
152              AND lob_code IN ('GCS_DS_LOAD_SOURCE_BASE',
153                               'GCS_DS_TB_SOURCE_BASE',
154                               'GCS_AD_TB_SOURCE_BASE',
155                               'GCS_ENTRY_SOURCE_BASE',
156                               'GCS_VS_MAP_SOURCE_BASE',
157                               'GCS_INTER_COMP_SOURCE_BASE',
158                               'GCS_DS_IMPACTED_BAL_SOURCE_BASE',
159                               --Bugfix: 5861665
160                               'GCS_CONS_INTER_COMP_SOURCE_BASE')
161              AND EXISTS (SELECT 1 FROM gcs_system_options);
162 
163 BEGIN
164 
165    IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
166        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', '<<begin>>');
167    END IF;
168 
169    -- JOB 1 : CONSTRUCT THE GCS/FEM ACTIVE DIMS SELECT/TABLE/WHERE/ORDER/GROUP LIST FOR MANIPULATION USE
170    -- select lists
171    l_select_list_gcs :=  gcs_xml_utility_pkg.g_gcs_dims_select_list;
172    l_select_list_dstb :=  gcs_xml_utility_pkg.g_fem_dims_select_list_dstb;
173    l_select_list_dsload :=  gcs_xml_utility_pkg.g_fem_dims_select_list_dsload;
174    l_gl_posted_select_list := gcs_xml_utility_pkg.g_fem_nonposted_select_stmnt;
175 
176    -- table lists
177    l_table_list_gcs  :=  gcs_xml_utility_pkg.g_gcs_dims_table_list;
178    l_table_list_dstb  :=  gcs_xml_utility_pkg.g_fem_dims_table_list_dstb;
179    --fix 5351083
180    --l_table_list_dsload  :=  gcs_xml_utility_pkg.g_fem_dims_table_list_dsload;
181    -- where lists
182    l_where_list_gcs  :=  gcs_xml_utility_pkg.g_gcs_dims_where_clause;
183    --fix 5351083
184    --l_where_list_dsload := gcs_xml_utility_pkg.g_fem_dims_dsload_where_clause;
185    l_where_list_dstb :=gcs_xml_utility_pkg.g_fem_dims_dstb_where_clause;
186    -- group by lists
187    --fix 5351083
188    --l_group_by_list := gcs_xml_utility_pkg.g_group_by_stmnt;
189    l_dstb_group_by_list := gcs_xml_utility_pkg.g_group_by_stmnt;
190    l_gl_posted_group_list := gcs_xml_utility_pkg.g_fem_nonposted_group_stmnt;
191    -- element list
192    l_element_list_gcs := gcs_xml_utility_pkg.g_gcs_dims_xml_elem;
193    l_element_list_dsload := gcs_xml_utility_pkg.g_fem_dims_xml_elem;
194    --fix 5351083
195    --l_element_list_dstb := gcs_xml_utility_pkg.p_element_list_dstb;
196    -- order by list
197    l_orderby_list_gcs := gcs_xml_utility_pkg.g_gcs_dims_select_list;
198    --Santosh -- bug 5234796
199    l_orderby_list_dsload := gcs_xml_utility_pkg.g_fem_dims_dsload_order_clause;
200    l_orderby_list_dstb := gcs_xml_utility_pkg.g_fem_dims_select_list_dstb;
201    -- special list for vs map
202    l_vs_group_list := gcs_xml_utility_pkg.g_gcs_vsmp_xml_elem;
203 
204    IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
205        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_select_list_gcs : '||l_select_list_gcs);
206        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_select_list_dstb : '||l_select_list_dstb);
207        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_select_list_dsload : '||l_select_list_dsload);
208        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_gl_posted_select_list : '||l_gl_posted_select_list);
209        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_table_list_gcs : '||l_table_list_gcs);
210        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_table_list_dstb : '||l_table_list_dstb);
211        --FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_table_list_dsload : '||l_table_list_dsload);
212        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_where_list_gcs : '||l_where_list_gcs);
213        --FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_where_list_dsload : '||l_where_list_dsload);
214        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_where_list_dstb : '||l_where_list_dstb);
215        --FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_group_by_list : '||l_group_by_list);
216        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_dstb_group_by_list : '||l_dstb_group_by_list);
217        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_gl_posted_group_list : '||l_gl_posted_group_list);
218        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_element_list_gcs : '||l_element_list_gcs);
219        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_element_list_dsload : '||l_element_list_dsload);
220        --FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_element_list_dstb : '||l_element_list_dstb);
221        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_orderby_list_gcs : '||l_orderby_list_gcs);
222        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_orderby_list_dsload : '||l_orderby_list_dsload);
223        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_orderby_list_dstb : '||l_orderby_list_dstb);
224        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_vs_group_list : '||l_vs_group_list);
225    END IF;
226 
227    -- JOB 2 : OPEN CURSORS AND LOOP THROUGH EACH OF THE TEMPLATES
228    -- AND CARRY OUT REPORT-SPECIFIC MANIPULATIONS TO THEM
229    OPEN c_base_data_templates;
230    FETCH c_base_data_templates BULK COLLECT INTO l_gcs_base_datatemplates;
231 
232    -- Check if there's atleast one record to process
233    IF (l_gcs_base_datatemplates.FIRST IS NOT NULL AND l_gcs_base_datatemplates.LAST IS NOT NULL) THEN
234 
235      -- Loop through each dt one by one and manipulate it as required
236      FOR l_index IN l_gcs_base_datatemplates.FIRST .. l_gcs_base_datatemplates.LAST LOOP
237 
238        l_base_templatecode := l_gcs_base_datatemplates(l_index).template_code ;
239        l_blob_in := l_gcs_base_datatemplates(l_index).template_data ;
240 
241        IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
242            FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'DT manipulation loop for : ');
243            FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_base_templatecode : '||l_base_templatecode);
244        END IF;
245 
246            -- Reset all the offsets
247            l_start := 1;
248            l_buffer := 32767;
249            l_off_write := 1;
250            l_amt_write := 32767;
251            IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
252                FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'Convert base DT blob to clob.');
253            END IF;
254            -- Create a temporary clob to hold manipulated contents
255            DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);
256            FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(l_blob_in) / l_buffer) LOOP
257              l_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(l_blob_in, l_buffer, l_start));
258              DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_varchar), l_varchar);
259              l_start := l_start + l_buffer;
260              IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
261                  FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_varchar : '||l_varchar);
262              END IF;
263            END LOOP;
264 
265            IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
266                FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'Converted base DT blob to clob and now pointing to target DT blob for manipulation.');
267            END IF;
268 
269            -- Got the clob out of the base dt blob
270            -- manipulate this clob as needed
271            -- Bugfix: 5861665
272            IF(l_base_templatecode = 'GCS_ENTRY_SOURCE_BASE'
273               OR l_base_templatecode = 'GCS_AD_TB_SOURCE_BASE'
274               OR l_base_templatecode = 'GCS_INTER_COMP_SOURCE_BASE'
275               OR l_base_templatecode = 'GCS_CONS_INTER_COMP_SOURCE_BASE') THEN
276 
277                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
278                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'DT manipulation: '||l_base_templatecode);
279                END IF;
280 
281                l_start_tag := DBMS_LOB.instr(l_clob, '/*startSelectList*/', 1, 1);
282                l_end_tag := DBMS_LOB.instr(l_clob, '/*endSelectList*/', 1, 1);
283                l_start_tag_length := length('/*startSelectList*/');
287                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'SELECT literal replacement.');
284                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
285                l_clob := replace(l_clob,l_sub_string, l_select_list_gcs);
286                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
288                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
289                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
290                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
291                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
292                END IF;
293 
294                l_start_tag := DBMS_LOB.instr(l_clob, '/*startFromList*/', 1, 1);
295                l_end_tag := DBMS_LOB.instr(l_clob, '/*endFromList*/', 1, 1);
296                l_start_tag_length := length('/*startFromList*/');
297                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
298                l_clob := replace(l_clob,l_sub_string, l_table_list_gcs);
299                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
300                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'FROM literal replacement.');
301                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
302                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
303                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
304                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
305                END IF;
306 
307                l_start_tag := DBMS_LOB.instr(l_clob, '/*startWhereClause*/', 1, 1);
308                l_end_tag := DBMS_LOB.instr(l_clob, '/*endWhereClause*/', 1, 1);
309                l_start_tag_length := length('/*startWhereClause*/');
310                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
311                l_clob := replace(l_clob,l_sub_string, l_where_list_gcs);
312                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
313                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'WHERE literal replacement.');
314                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
315                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
316                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
317                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
318                END IF;
319 
320                l_start_tag := DBMS_LOB.instr(l_clob, '/*startOrderClause*/', 1, 1);
321                l_end_tag := DBMS_LOB.instr(l_clob, '/*endOrderClause*/', 1, 1);
322                l_start_tag_length := length('/*startOrderClause*/');
323                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
324                l_clob :=replace(l_clob,l_sub_string, l_orderby_list_gcs);
325                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
326                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'ORDER literal replacement.');
327                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
328                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
329                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
330                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
331                END IF;
332 
333                l_start_tag := DBMS_LOB.instr(l_clob, '<!--startElements-->', 1, 1);
334                l_end_tag := DBMS_LOB.instr(l_clob, '<!--endElements-->', 1, 1);
335                l_start_tag_length := length('<!--startElements-->');
336                l_sub_string :=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
337                l_clob := replace(l_clob,l_sub_string, l_element_list_gcs);
338                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
339                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'XML SCHEMA ELEMENT literal replacement.');
340                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
341                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
342                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
343                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
344                END IF;
345 
346            ELSIF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE' OR l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE'
350                END IF;
347                  OR l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
348                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
349                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'DT manipulation: '||l_base_templatecode);
351 
352                l_start_tag := DBMS_LOB.instr(l_clob, '/*startSelectList*/', 1, 1);
353                l_end_tag := DBMS_LOB.instr(l_clob, '/*endSelectList*/', 1, 1);
354                l_start_tag_length := length('/*startSelectList*/');
355                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
356                IF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE') THEN
357                   l_clob := replace(l_clob,l_sub_string, l_select_list_dsload);
358                ELSIF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
359                   l_clob := replace(l_clob,l_sub_string, l_select_list_dstb);
360                ELSIF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
361                   l_clob := replace(l_clob,l_sub_string, l_select_list_dstb);
362                END IF;
363                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
364                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'SELECT (DIMENSION NAME COLUMNS) literal replacement.');
365                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
366                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
367                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
368                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
369                END IF;
370 
371                l_start_tag := DBMS_LOB.instr(l_clob, '/*startSelectIdList*/', 1, 1);
372                l_end_tag := DBMS_LOB.instr(l_clob, '/*endSelectIdList*/', 1, 1);
373                l_start_tag_length := length('/*startSelectIdList*/');
374                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
375 
376                IF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
377                   l_clob := replace(l_clob,l_sub_string, l_gl_posted_select_list);
378                --fix 5351083
379                --ELSE
380                --   l_clob := replace(l_clob,l_sub_string, l_group_by_list);
381                END IF;
382                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
383                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'SELECT (DIMENSION ID COLUMNS) literal replacement.');
384                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
385                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
386                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
387                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
388                END IF;
389 
390                l_start_tag := DBMS_LOB.instr(l_clob, '/*startFromList*/', 1, 1);
391                l_end_tag := DBMS_LOB.instr(l_clob, '/*endFromList*/', 1, 1);
392                l_start_tag_length := length('/*startFromList*/');
393                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
394                --fix 5351083
395                --IF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE') THEN
396                  --l_clob := replace(l_clob,l_sub_string, l_table_list_dsload);
397                --ELSIF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
398                IF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
399                  l_clob := replace(l_clob,l_sub_string, l_table_list_dstb);
400                ELSIF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
401                  l_clob := replace(l_clob,l_sub_string, l_table_list_dstb);
402 		           END IF;
403                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
404                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'FROM literal replacement.');
405                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
406                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
407                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
408                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
409                END IF;
410 
411                l_start_tag := DBMS_LOB.instr(l_clob, '/*startWhereClause*/', 1, 1);
412                l_end_tag := DBMS_LOB.instr(l_clob, '/*endWhereClause*/', 1, 1);
413                l_start_tag_length := length('/*startWhereClause*/');
414                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
415                --fix 5351083
416                --IF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE') THEN
417                --  l_clob := replace(l_clob,l_sub_string, l_where_list_dsload);
418                --ELSIF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
422                  l_clob := replace(l_clob,l_sub_string, l_where_list_dstb);
419                IF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
420                  l_clob := replace(l_clob,l_sub_string, l_where_list_dstb);
421                ELSIF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
423 	             END IF;
424                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
425                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'WHERE literal replacement.');
426                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
427                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
428                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
429                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
430                END IF;
431 
432                l_start_tag := DBMS_LOB.instr(l_clob, '/*startDSTBGroupbyName*/', 1, 1);
433                l_end_tag := DBMS_LOB.instr(l_clob, '/*endDSTBGroupbyName*/', 1, 1);
434                l_start_tag_length := length('/*startDSTBGroupbyName*/');
435                l_sub_string :=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
436                --fix 5351083
437                --IF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE') THEN
438                  -- l_clob := replace(l_clob,l_sub_string, l_select_list_dsload);
439                --ELSIF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
440                  -- l_clob := replace(l_clob,l_sub_string, l_select_list_dstb);
441                --ELSIF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
442                IF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
443                   l_clob := replace(l_clob,l_sub_string, l_gl_posted_group_list);
444 	             END IF;
445                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
446                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'GROUP BY (DIMENSION NAME COLUMNS) literal replacement.');
447                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
448                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
449                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
450                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
451                END IF;
452 
453                l_start_tag := DBMS_LOB.instr(l_clob, '/*startDSTBGroupbyId*/', 1, 1);
454                l_end_tag := DBMS_LOB.instr(l_clob, '/*endDSTBGroupbyId*/', 1, 1);
455                l_start_tag_length := length('/*startDSTBGroupbyId*/');
456                l_sub_string :=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
457                IF(l_base_templatecode = 'GCS_DS_IMPACTED_BAL_SOURCE_BASE') THEN
458                   l_clob := replace(l_clob,l_sub_string, l_dstb_group_by_list);
459                END IF;
460                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
461                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'GROUP BY (DIMENSION ID COLUMNS) literal replacement.');
462                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
463                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
464                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
465                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
466                END IF;
467 
468                l_start_tag := DBMS_LOB.instr(l_clob, '/*startOrderClause*/', 1, 1);
469                l_end_tag := DBMS_LOB.instr(l_clob, '/*endOrderClause*/', 1, 1);
470                l_start_tag_length := length('/*startOrderClause*/');
471                l_sub_string:=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
472                IF(l_base_templatecode = 'GCS_DS_LOAD_SOURCE_BASE') THEN
473                   l_clob :=replace(l_clob,l_sub_string, l_orderby_list_dsload);
474                ELSIF(l_base_templatecode = 'GCS_DS_TB_SOURCE_BASE') THEN
475                l_clob :=replace(l_clob,l_sub_string, l_orderby_list_dstb);
476                END IF;
477                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
478                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'ORDER literal replacement.');
479                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
480                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
481                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
482                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
483                END IF;
484 
485                l_start_tag := DBMS_LOB.instr(l_clob, '<!--startElements-->', 1, 1);
486                l_end_tag := DBMS_LOB.instr(l_clob, '<!--endElements-->', 1, 1);
487                l_start_tag_length := length('<!--startElements-->');
488                l_sub_string :=  DBMS_LOB.substr(l_clob,(l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
489                l_clob := replace(l_clob,l_sub_string, l_element_list_dsload);
490                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
491                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'XML SCHEMA ELEMENT (DIMENSION NAME COLUMNS) literal replacement.');
492                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
493                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
494                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
495                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
496                END IF;
497                --fix 5351083
498                /*
499                l_start_tag := DBMS_LOB.instr(l_clob, '<!--startDSTBIDElements-->', 1, 1);
500                l_end_tag := DBMS_LOB.instr(l_clob, '<!--endDSTBIDElements-->', 1, 1);
501                l_start_tag_length := length('<!--startDSTBIDElements-->');
502                l_sub_string :=  DBMS_LOB.substr(l_clob, (l_end_tag - (l_start_tag+l_start_tag_length) ), l_start_tag+l_start_tag_length);
503                l_clob := replace(l_clob, l_sub_string, l_element_list_dstb);
504                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
505                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'XML SCHEMA ELEMENT (DIMENSION ID COLUMNS) literal replacement.');
506                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
507                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
508                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
509                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
510                END IF;
511                */
512 
513                ELSIF(l_base_templatecode = 'GCS_VS_MAP_SOURCE_BASE') THEN
514 
515                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
516                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'DT manipulation: '||l_base_templatecode);
517                END IF;
518 
519                l_start_tag := DBMS_LOB.instr(l_clob, '<!--startVSElements-->', 1, 1);
520                l_start_tag_length := length('<!--startVSElements-->');
521                l_remaining_str:='<!--endVSElements--></dataStructure></dataTemplate>';
522           	   replace_clob(l_clob, l_vs_group_list, l_start_tag+l_start_tag_length, l_remaining_str);
523                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
524                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'XML SCHEMA ELEMENT literal replacement.');
525                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag : '||l_start_tag);
526                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_end_tag : '||l_end_tag);
527                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start_tag_length : '||l_start_tag_length);
528                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_sub_string : '||l_sub_string);
529                END IF;
530 
531            END IF;
532 
533            -- Finally write back temporary clob to destination blob
534            l_start:=1;
535            l_off_write:=1;
536            DBMS_LOB.createtemporary(l_blob_out,TRUE);
537            IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
538                FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'Write back manipulated clob to target blob.');
539            END IF;
540 
541            FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(l_clob) / l_buffer) LOOP
542 
543                DBMS_LOB.read ( l_clob, l_buffer, l_start, l_varchar );
544                l_amt_write := utl_raw.length (utl_raw.cast_to_raw( l_varchar) );
545                DBMS_LOB.write( l_blob_out, l_amt_write, l_off_write, utl_raw.cast_to_raw( l_varchar ) );
546                l_off_write := l_off_write + l_amt_write;
547                l_start := l_start + l_buffer;
548                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
549                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_start : '||l_start);
550                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_buffer : '||l_buffer);
551                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_amt_write : '||l_amt_write);
552                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_off_write : '||l_off_write);
553                    FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'l_varchar : '||l_varchar);
554                END IF;
555            END LOOP;
556 
557            UPDATE xdo_lobs
558               SET file_data = l_blob_out
559             WHERE application_short_name = 'GCS'
560               AND lob_type = 'DATA_TEMPLATE'
561               AND lob_code = SUBSTR(l_base_templatecode, 1, INSTR(l_base_templatecode,'_BASE')-1);
562 
563            DBMS_LOB.freetemporary(l_blob_out);
564            DBMS_LOB.freetemporary(l_clob);
565            IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
566                FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', 'Manipulated clob written back to target blob.');
567            END IF;
568 
569      END LOOP; -- Loop for template cursor
570 
571    END IF;
572 
573    CLOSE c_base_data_templates;
574    COMMIT;
575    IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_STATEMENT) THEN
576        FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_api || '.' || 'gcs_replace_dt_proc', '<<end>>');
577    END IF;
578 
579    EXCEPTION
580           WHEN OTHERS THEN
581           BEGIN
582                IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL 	<=	FND_LOG.LEVEL_ERROR) THEN
583                    FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_api || '.' || 'gcs_replace_dt_proc', substr(SQLERRM,1,255));
584                END IF;
585           END;
586 
587 END gcs_replace_dt_proc;
588 
589 END GCS_DATA_TEMPLATE_UTIL_PKG;