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

Source


1 PACKAGE BODY JTY_TRANS_USG_PGM_SQL_PKG as
2 /* $Header: jtftupsb.pls 120.2 2005/11/21 13:34:02 achanda noship $ */
3 --    Start of Comments
4 --    ---------------------------------------------------
5 --    PACKAGE NAME:   JTY_TRANS_USG_PGM_SQL_PKG
6 --    ---------------------------------------------------
7 --    PURPOSE
8 --      This package is used to create the transaction type SQLs
9 --      and the corresponding TRANS tables.
10 --
11 --      Procedures:
12 --         (see below for specification)
13 --
14 --    NOTES
15 --      This package is publicly available for use
16 --
17 --    HISTORY
18 --      09/08/05    ACHANDA         Created
19 --
20 --    End of Comments
21 --
22 
23 PROCEDURE Insert_Row(
24    p_source_id IN NUMBER
25   ,p_trans_type_id IN NUMBER
26   ,p_program_name IN VARCHAR2
27   ,p_version_name IN VARCHAR2
28   ,p_real_time_sql IN VARCHAR2
29   ,p_batch_total_sql IN VARCHAR2
30   ,p_batch_incr_sql IN VARCHAR2
31   ,p_batch_dea_sql IN VARCHAR2
32   ,p_incr_reassign_sql IN VARCHAR2
33   ,p_use_total_for_dea_flag IN VARCHAR2
34   ,p_enabled_flag IN VARCHAR2
35   ,retcode OUT NOCOPY VARCHAR2
36   ,errbuf OUT NOCOPY VARCHAR2) IS
37 
38   l_enabled_flag           VARCHAR2(1);
39   l_use_total_for_dea_flag VARCHAR2(1);
40 
41   l_param_passing_mechanism     VARCHAR2(3);
42   l_real_time_enable_flag       VARCHAR2(1);
43   l_batch_enable_flag           VARCHAR2(1);
44   l_real_time_trans_table_name  VARCHAR2(30);
45   l_batch_trans_table_name      VARCHAR2(30);
46   l_batch_nm_trans_table_name   VARCHAR2(30);
47   l_batch_dea_trans_table_name  VARCHAR2(30);
48 
49   l_user_id              NUMBER;
50   l_login_id             NUMBER;
51   l_sysdate              DATE;
52   first_time             BOOLEAN;
53   l_trans_usg_pgm_sql_id NUMBER;
54   l_new_line             VARCHAR2(02) := fnd_global.local_chr(10);
55   l_indent               VARCHAR2(30);
56 
57   l_real_time_insert VARCHAR2(32767);
58   l_real_time_select VARCHAR2(32000);
59   l_create_gt_stmt   VARCHAR2(32767);
60   l_drop_gt_stmt     VARCHAR2(1000);
61   l_create_tbl_stmt  VARCHAR2(32767);
62   l_drop_tbl_stmt    VARCHAR2(1000);
63   l_alter_tbl_stmt   VARCHAR2(32767);
64 
65   l_real_time_sql_clob	    CLOB;
66   l_real_time_insert_clob	CLOB;
67   l_batch_total_sql_clob	CLOB;
68   l_batch_incr_sql_clob	    CLOB;
69   l_batch_dea_sql_clob	    CLOB;
70   l_incr_reassign_sql_clob	CLOB;
71 
72   l_status         VARCHAR2(30);
73   l_industry       VARCHAR2(30);
74   l_jtf_schema     VARCHAR2(30);
75 
76   CURSOR c_column_names(p_table_name IN VARCHAR2) is
77   SELECT column_name
78   FROM  user_tab_columns
79   WHERE table_name = p_table_name
80   ORDER BY column_id;
81 
82   CURSOR CUR_REAL_TIME(cl_trans_usg_pgm_sql_id IN NUMBER) IS
83   SELECT REAL_TIME_SQL
84   FROM   JTY_TRANS_USG_PGM_SQL
85   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
86   FOR UPDATE OF REAL_TIME_SQL NOWAIT;
87 
88   CURSOR CUR_REAL_TIME_INSERT(cl_trans_usg_pgm_sql_id IN NUMBER) IS
89   SELECT REAL_TIME_INSERT
90   FROM   JTY_TRANS_USG_PGM_SQL
91   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
92   FOR UPDATE OF REAL_TIME_INSERT NOWAIT;
93 
94   CURSOR CUR_BATCH_TOTAL(cl_trans_usg_pgm_sql_id IN NUMBER) IS
95   SELECT BATCH_TOTAL_SQL
96   FROM   JTY_TRANS_USG_PGM_SQL
97   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
98   FOR UPDATE OF BATCH_TOTAL_SQL NOWAIT;
99 
100   CURSOR CUR_BATCH_INCR(cl_trans_usg_pgm_sql_id IN NUMBER) IS
101   SELECT BATCH_INCR_SQL
102   FROM   JTY_TRANS_USG_PGM_SQL
103   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
104   FOR UPDATE OF BATCH_INCR_SQL NOWAIT;
105 
106   CURSOR CUR_BATCH_DEA(cl_trans_usg_pgm_sql_id IN NUMBER) IS
107   SELECT BATCH_DEA_SQL
108   FROM   JTY_TRANS_USG_PGM_SQL
109   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
110   FOR UPDATE OF BATCH_DEA_SQL NOWAIT;
111 
112   CURSOR CUR_INCR_REASSIGN(cl_trans_usg_pgm_sql_id IN NUMBER) IS
113   SELECT INCR_REASSIGN_SQL
114   FROM   JTY_TRANS_USG_PGM_SQL
115   WHERE  TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
116   FOR UPDATE OF INCR_REASSIGN_SQL NOWAIT;
117 
118 BEGIN
119 
120   retcode := 0;
121   errbuf  := null;
122 
123   /* Version cannot be null or ORACLE (reserved for seeded SQLs) */
124   IF ((p_version_name IS NULL) OR (p_version_name = 'ORACLE')) THEN
125     retcode := 2;
126     errbuf  := 'Version Name cannot be null or oracle';
127     RAISE FND_API.G_EXC_ERROR;
128   END IF;
129 
130   l_enabled_flag := NVL(p_enabled_flag, 'Y');
131   l_use_total_for_dea_flag := NVL(p_use_total_for_dea_flag, 'N');
132 
133   l_user_id  := FND_GLOBAL.USER_ID;
134   l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
135   l_sysdate  := sysdate;
136 
137   BEGIN
138     SELECT
139        param_passing_mechanism
140       ,real_time_enable_flag
141       ,batch_enable_flag
142       ,real_time_trans_table_name
143       ,batch_trans_table_name
144       ,batch_nm_trans_table_name
145       ,batch_dea_trans_table_name
146     INTO
147        l_param_passing_mechanism
148       ,l_real_time_enable_flag
149       ,l_batch_enable_flag
150       ,l_real_time_trans_table_name
151       ,l_batch_trans_table_name
152       ,l_batch_nm_trans_table_name
153       ,l_batch_dea_trans_table_name
154     FROM jty_trans_usg_pgm_details
155     WHERE source_id = p_source_id
156     AND   trans_type_id = p_trans_type_id
157     AND   program_name = p_program_name;
158   EXCEPTION
159     WHEN NO_DATA_FOUND THEN
160       retcode := 2;
161       errbuf  := 'No row in the table jty_trans_usg_pgm_details corr to the source, transaction type and program name passed';
162       RAISE FND_API.G_EXC_ERROR;
163     WHEN OTHERS THEN
164       RAISE;
165   END;
166 
167   /* Custom Qualifier supported for pass by reference only */
168   IF (l_param_passing_mechanism = 'PBV') THEN
169     retcode := 2;
170     errbuf  := 'Custom Qualifier is not supported for transactions having pass by value parameter passing mechanism';
171     RAISE FND_API.G_EXC_ERROR;
172   END IF;
173 
174   /* If this version needs to be enabled, then all other versions need to be disabled */
175   IF (l_enabled_flag = 'Y') THEN
176     UPDATE jty_trans_usg_pgm_sql
177     SET    enabled_flag = 'N'
178     WHERE  SOURCE_ID     = p_source_id
179     AND    TRANS_TYPE_ID = p_trans_type_id
180     AND    PROGRAM_NAME  = p_program_name;
181   END IF;
182 
183   /* If present, delete the old entries from jty_trnas_usg_pgm_sql */
184   BEGIN
185     DELETE FROM JTY_TRANS_USG_PGM_SQL
186     WHERE SOURCE_ID     = p_source_id
187     AND   TRANS_TYPE_ID = p_trans_type_id
188     AND   PROGRAM_NAME  = p_program_name
189     AND   VERSION_NAME  = p_version_name;
190   EXCEPTION
191     WHEN OTHERS THEN
192       NULL;
193   END;
194 
195   /* Get the unique id from sequence */
196   SELECT JTY_TRANS_USG_PGM_SQL_S.nextval
197   INTO   l_trans_usg_pgm_sql_id
198   FROM   DUAL;
199 
200   /* Insert a record with all the SQLs as NULL */
201   INSERT INTO JTY_TRANS_USG_PGM_SQL (
202      TRANS_USG_PGM_SQL_ID
203     ,SOURCE_ID
204     ,TRANS_TYPE_ID
205     ,PROGRAM_NAME
206     ,VERSION_NAME
207     ,USE_TOTAL_FOR_DEA_FLAG
208     ,ENABLED_FLAG
209     ,REAL_TIME_SQL
210     ,REAL_TIME_INSERT
211     ,BATCH_TOTAL_SQL
212     ,BATCH_INCR_SQL
213     ,BATCH_DEA_SQL
214     ,INCR_REASSIGN_SQL
215     ,CREATED_BY
216     ,CREATION_DATE
217     ,LAST_UPDATED_BY
218     ,LAST_UPDATE_DATE
219     ,LAST_UPDATE_LOGIN
220     ,OBJECT_VERSION_NUMBER)
221   VALUES(
222      l_trans_usg_pgm_sql_id
223     ,p_source_id
224     ,p_trans_type_id
225     ,p_program_name
226     ,p_version_name
227     ,l_use_total_for_dea_flag
228     ,l_enabled_flag
229     ,EMPTY_CLOB()
230     ,EMPTY_CLOB()
231     ,EMPTY_CLOB()
232     ,EMPTY_CLOB()
233     ,EMPTY_CLOB()
234     ,EMPTY_CLOB()
235     ,l_user_id
236     ,l_sysdate
237     ,l_user_id
238     ,l_sysdate
239     ,l_login_id
240     ,1);
241 
242   /* Create the real time trans table and real time transaction type SQL */
243   IF ((l_real_time_enable_flag = 'Y') AND (p_real_time_sql IS NOT NULL) AND (l_real_time_trans_table_name IS NOT NULL)) THEN
244     l_real_time_select := p_real_time_sql;
245     l_real_time_select := replace(l_real_time_select, 'l_txn_date', 'sysdate');
246     l_real_time_select := replace(l_real_time_select, 'l_trans_object_id1', '-999');
247     l_real_time_select := replace(l_real_time_select, 'l_trans_object_id2', '-999');
248     l_real_time_select := replace(l_real_time_select, 'l_trans_object_id3', '-999');
249     l_real_time_select := replace(l_real_time_select, 'l_trans_object_id4', '-999');
250     l_real_time_select := replace(l_real_time_select, 'l_trans_object_id5', '-999');
251 
252     BEGIN
253       l_drop_gt_stmt := 'DROP TABLE ' || l_real_time_trans_table_name;
254 
255       EXECUTE IMMEDIATE l_drop_gt_stmt;
256     EXCEPTION
257       WHEN OTHERS THEN
258         NULL;
259     END;
260 
261     l_create_gt_stmt :=
262       'CREATE GLOBAL TEMPORARY TABLE ' || l_real_time_trans_table_name || ' ON COMMIT PRESERVE ROWS AS ' ||
263       l_real_time_select;
264 
265     BEGIN
266       EXECUTE IMMEDIATE l_create_gt_stmt;
267     EXCEPTION
268       when others then
269         retcode := 2;
270         errbuf  := 'Error while trying to create ' || l_real_time_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
271                    ' : SQLERRM : ' || SQLERRM;
272         RAISE;
273     END;
274 
275     first_time := TRUE;
276     l_indent   := '  ';
277 
278     l_real_time_insert := 'INSERT INTO ' || l_real_time_trans_table_name || ' ( ';
279 
280     FOR column_names in c_column_names(l_real_time_trans_table_name) LOOP
281       IF (first_time) THEN
282         l_real_time_insert := l_real_time_insert || l_new_line || l_indent || column_names.column_name;
283         first_time := FALSE;
284       ELSE
285         l_real_time_insert := l_real_time_insert || l_new_line || l_indent || ',' || column_names.column_name;
286       END IF;
287     END LOOP;
288     l_real_time_insert := l_real_time_insert || ')' || l_new_line || p_real_time_sql || ';';
289 
290     BEGIN
291       OPEN CUR_REAL_TIME_INSERT(l_trans_usg_pgm_sql_id);
292       LOOP
293         FETCH CUR_REAL_TIME_INSERT INTO l_real_time_insert_clob;
294         EXIT WHEN CUR_REAL_TIME_INSERT%NOTFOUND;
295         DBMS_LOB.OPEN(l_real_time_insert_clob, DBMS_LOB.LOB_READWRITE);
296         DBMS_LOB.WRITEAPPEND(l_real_time_insert_clob,LENGTH(l_real_time_insert),l_real_time_insert);
297         DBMS_LOB.CLOSE(l_real_time_insert_clob);
298       END LOOP;
299       CLOSE CUR_REAL_TIME_INSERT;
300 
301       OPEN CUR_REAL_TIME(l_trans_usg_pgm_sql_id);
302       LOOP
303         FETCH CUR_REAL_TIME INTO l_real_time_sql_clob;
304         EXIT WHEN CUR_REAL_TIME%NOTFOUND;
305         DBMS_LOB.OPEN(l_real_time_sql_clob, DBMS_LOB.LOB_READWRITE);
306         DBMS_LOB.WRITEAPPEND(l_real_time_sql_clob,LENGTH(p_real_time_sql),p_real_time_sql);
307         DBMS_LOB.CLOSE(l_real_time_sql_clob);
308       END LOOP;
309       CLOSE CUR_REAL_TIME;
310     EXCEPTION
311       when others then
312         retcode := 2;
313         errbuf  := 'Error while trying to update real_time_sql or real_time_insert' || ' : SQLCODE : ' || SQLCODE ||
314                    ' : SQLERRM : ' || SQLERRM;
315         RAISE;
316     END;
317   END IF;
318 
319   IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
320     NULL;
321   END IF;
322 
323   /* Create the batch trans table and batch transaction type SQL */
324   IF ((l_batch_enable_flag = 'Y') AND (l_batch_trans_table_name IS NOT NULL) AND (p_batch_total_sql IS NOT NULL)) THEN
325     l_drop_tbl_stmt := 'DROP TABLE ' || l_jtf_schema || '.' || l_batch_trans_table_name;
326     BEGIN
327       execute immediate l_drop_tbl_stmt;
328     EXCEPTION
329       when others then
330         null;
331     END;
332 
333     l_create_tbl_stmt := 'CREATE TABLE ' || l_jtf_schema || '.' || l_batch_trans_table_name ||
334                            ' PARTITION BY RANGE (worker_id) ' ||
335                              '(PARTITION WORKER1 VALUES LESS THAN (1), ' ||
336                               'PARTITION WORKER2 VALUES LESS THAN (2), ' ||
337                               'PARTITION WORKER3 VALUES LESS THAN (3), ' ||
338                               'PARTITION WORKER4 VALUES LESS THAN (4), ' ||
339                               'PARTITION WORKER5 VALUES LESS THAN (5), ' ||
340                               'PARTITION WORKER6 VALUES LESS THAN (6), ' ||
341                               'PARTITION WORKER7 VALUES LESS THAN (7), ' ||
342                               'PARTITION WORKER8 VALUES LESS THAN (8), ' ||
343                               'PARTITION WORKER9 VALUES LESS THAN (9), ' ||
344                               'PARTITION WORKER10 VALUES LESS THAN (10), ' ||
345                               'PARTITION WORKER11 VALUES LESS THAN (11) ' ||
346                              ') ' ||
347                          ' AS (SELECT A.*, 1 WORKER_ID FROM ( ' || p_batch_total_sql || ' ) A WHERE 1 = 2 )';
348 
349     BEGIN
350       EXECUTE IMMEDIATE l_create_tbl_stmt;
351     EXCEPTION
352       when others then
353         retcode := 2;
354         errbuf  := 'Error while trying to create ' || l_batch_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
355                    ' : SQLERRM : ' || SQLERRM;
356         RAISE;
357     END;
358 
359     l_alter_tbl_stmt := 'ALTER TABLE ' || l_jtf_schema || '.' || l_batch_trans_table_name || ' ADD ( ' ||
360                         'LAST_UPDATE_DATE DATE, ' ||
361                         'LAST_UPDATED_BY NUMBER, ' ||
362                         'CREATION_DATE DATE, ' ||
363                         'CREATED_BY NUMBER, ' ||
364                         'LAST_UPDATE_LOGIN NUMBER, ' ||
365                         'REQUEST_ID NUMBER, ' ||
366                         'PROGRAM_APPLICATION_ID NUMBER, ' ||
367                         'PROGRAM_ID NUMBER, ' ||
368                         'PROGRAM_UPDATE_DATE DATE, ' ||
369                         'TXN_DATE DATE, ' ||
370                         'SECURITY_GROUP_ID NUMBER, ' ||
371                         'OBJECT_VERSION_NUMBER NUMBER) ';
372     BEGIN
373       EXECUTE IMMEDIATE l_alter_tbl_stmt;
374     EXCEPTION
375       when others then
376         retcode := 2;
377         errbuf  := 'Error while trying to alter ' || l_batch_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
378                    ' : SQLERRM : ' || SQLERRM;
379       RAISE;
380     END;
381 
382     BEGIN
383       OPEN CUR_BATCH_TOTAL(l_trans_usg_pgm_sql_id);
384       LOOP
385         FETCH CUR_BATCH_TOTAL INTO l_batch_total_sql_clob;
386         EXIT WHEN CUR_BATCH_TOTAL%NOTFOUND;
387         DBMS_LOB.OPEN(l_batch_total_sql_clob, DBMS_LOB.LOB_READWRITE);
388         DBMS_LOB.WRITEAPPEND(l_batch_total_sql_clob,LENGTH(p_batch_total_sql),p_batch_total_sql);
389         DBMS_LOB.CLOSE(l_batch_total_sql_clob);
390       END LOOP;
391       CLOSE CUR_BATCH_TOTAL;
392     EXCEPTION
393       when others then
394         retcode := 2;
395         errbuf  := 'Error while trying to update batch_total_sql' || ' : SQLCODE : ' || SQLCODE ||
396                    ' : SQLERRM : ' || SQLERRM;
397         RAISE;
398     END;
399   END IF; /* end IF ((l_batch_enable_flag = 'Y') AND ... */
400 
401   /* Create the batch new mode trans table and batch new mode transaction type SQL */
402   IF ((l_batch_enable_flag = 'Y') AND (l_batch_nm_trans_table_name IS NOT NULL) AND (p_batch_incr_sql IS NOT NULL)) THEN
403     l_drop_tbl_stmt := 'DROP TABLE ' || l_jtf_schema || '.' || l_batch_nm_trans_table_name;
404     BEGIN
405       execute immediate l_drop_tbl_stmt;
406     EXCEPTION
407       when others then
408         null;
409     END;
410 
411 
412     l_create_tbl_stmt := 'CREATE TABLE ' || l_jtf_schema || '.' || l_batch_nm_trans_table_name || ' AS (SELECT * FROM ( ' ||
413                     p_batch_total_sql || ' ) WHERE 1 = 2 ) ';
414     BEGIN
415       EXECUTE IMMEDIATE l_create_tbl_stmt;
416     EXCEPTION
417       when others then
418         retcode := 2;
419         errbuf  := 'Error while trying to create ' || l_batch_nm_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
420                    ' : SQLERRM : ' || SQLERRM;
421         RAISE;
422     END;
423 
424 
425     l_alter_tbl_stmt := 'ALTER TABLE ' || l_jtf_schema || '.' || l_batch_nm_trans_table_name || ' ADD ( ' ||
429                         'CREATED_BY NUMBER, ' ||
426                         'LAST_UPDATE_DATE DATE, ' ||
427                         'LAST_UPDATED_BY NUMBER, ' ||
428                         'CREATION_DATE DATE, ' ||
430                         'LAST_UPDATE_LOGIN NUMBER, ' ||
431                         'REQUEST_ID NUMBER, ' ||
432                         'PROGRAM_APPLICATION_ID NUMBER, ' ||
433                         'PROGRAM_ID NUMBER, ' ||
434                         'PROGRAM_UPDATE_DATE DATE, ' ||
435                         'TXN_DATE DATE, ' ||
436                         'SECURITY_GROUP_ID NUMBER, ' ||
437                         'OBJECT_VERSION_NUMBER NUMBER, ' ||
438                         'WORKER_ID NUMBER) ';
439     BEGIN
440       EXECUTE IMMEDIATE l_alter_tbl_stmt;
441     EXCEPTION
442       when others then
443         retcode := 2;
444         errbuf  := 'Error while trying to alter ' || l_batch_nm_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
445                    ' : SQLERRM : ' || SQLERRM;
446         RAISE;
447     END;
448 
449     BEGIN
450       OPEN CUR_BATCH_INCR(l_trans_usg_pgm_sql_id);
451       LOOP
452         FETCH CUR_BATCH_INCR INTO l_batch_incr_sql_clob;
453         EXIT WHEN CUR_BATCH_INCR%NOTFOUND;
454         DBMS_LOB.OPEN(l_batch_incr_sql_clob, DBMS_LOB.LOB_READWRITE);
455         DBMS_LOB.WRITEAPPEND(l_batch_incr_sql_clob,LENGTH(p_batch_incr_sql),p_batch_incr_sql);
456         DBMS_LOB.CLOSE(l_batch_incr_sql_clob);
457       END LOOP;
458       CLOSE CUR_BATCH_INCR;
459     EXCEPTION
460       when others then
461         retcode := 2;
462         errbuf  := 'Error while trying to update batch_incr_sql' || ' : SQLCODE : ' || SQLCODE ||
463                    ' : SQLERRM : ' || SQLERRM;
464         RAISE;
465     END;
466   END IF; /* end IF ((l_batch_enable_flag = 'Y') AND ... */
467 
468   /* Create the batch dea trans table and batch dea transaction type SQL */
469   IF ((l_batch_enable_flag = 'Y') AND (l_batch_dea_trans_table_name IS NOT NULL) AND (p_batch_dea_sql IS NOT NULL)) THEN
470     l_drop_tbl_stmt := 'DROP TABLE ' || l_jtf_schema || '.' || l_batch_dea_trans_table_name;
471     BEGIN
472       execute immediate l_drop_tbl_stmt;
473     EXCEPTION
474       when others then
475         null;
476     END;
477 
478 
479     l_create_tbl_stmt := 'CREATE TABLE ' || l_jtf_schema || '.' || l_batch_dea_trans_table_name ||
480                            ' PARTITION BY RANGE (worker_id) ' ||
481                              '(PARTITION WORKER1 VALUES LESS THAN (1), ' ||
482                               'PARTITION WORKER2 VALUES LESS THAN (2), ' ||
483                               'PARTITION WORKER3 VALUES LESS THAN (3), ' ||
484                               'PARTITION WORKER4 VALUES LESS THAN (4), ' ||
485                               'PARTITION WORKER5 VALUES LESS THAN (5), ' ||
486                               'PARTITION WORKER6 VALUES LESS THAN (6), ' ||
487                               'PARTITION WORKER7 VALUES LESS THAN (7), ' ||
488                               'PARTITION WORKER8 VALUES LESS THAN (8), ' ||
489                               'PARTITION WORKER9 VALUES LESS THAN (9), ' ||
490                               'PARTITION WORKER10 VALUES LESS THAN (10), ' ||
491                               'PARTITION WORKER11 VALUES LESS THAN (11) ' ||
492                              ') ' ||
493                          ' AS (SELECT A.*, 1 WORKER_ID FROM ( ' || p_batch_dea_sql || ' ) A WHERE 1 = 2 )';
494     BEGIN
495       EXECUTE IMMEDIATE l_create_tbl_stmt;
496     EXCEPTION
497       when others then
498         retcode := 2;
499         errbuf  := 'Error while trying to create ' || l_batch_dea_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
500                    ' : SQLERRM : ' || SQLERRM;
501         RAISE;
502     END;
503 
504     l_alter_tbl_stmt := 'ALTER TABLE ' || l_jtf_schema || '.' || l_batch_dea_trans_table_name || ' ADD ( ' ||
505                         'LAST_UPDATE_DATE DATE, ' ||
506                         'LAST_UPDATED_BY NUMBER, ' ||
507                         'CREATION_DATE DATE, ' ||
508                         'CREATED_BY NUMBER, ' ||
509                         'LAST_UPDATE_LOGIN NUMBER, ' ||
510                         'REQUEST_ID NUMBER, ' ||
511                         'PROGRAM_APPLICATION_ID NUMBER, ' ||
512                         'PROGRAM_ID NUMBER, ' ||
513                         'PROGRAM_UPDATE_DATE DATE, ' ||
514                         'SECURITY_GROUP_ID NUMBER, ' ||
515                         'OBJECT_VERSION_NUMBER NUMBER) ';
516     BEGIN
517       EXECUTE IMMEDIATE l_alter_tbl_stmt;
518     EXCEPTION
519       when others then
520         retcode := 2;
521         errbuf  := 'Error while trying to alter ' || l_batch_dea_trans_table_name || ' : SQLCODE : ' || SQLCODE ||
522                    ' : SQLERRM : ' || SQLERRM;
523         RAISE;
524     END;
525 
526     BEGIN
527       OPEN CUR_BATCH_DEA(l_trans_usg_pgm_sql_id);
528       LOOP
529         FETCH CUR_BATCH_DEA INTO l_batch_dea_sql_clob;
530         EXIT WHEN CUR_BATCH_DEA%NOTFOUND;
531         DBMS_LOB.OPEN(l_batch_dea_sql_clob, DBMS_LOB.LOB_READWRITE);
532         DBMS_LOB.WRITEAPPEND(l_batch_dea_sql_clob,LENGTH(p_batch_dea_sql),p_batch_dea_sql);
533         DBMS_LOB.CLOSE(l_batch_dea_sql_clob);
534       END LOOP;
535       CLOSE CUR_BATCH_DEA;
536     EXCEPTION
537       when others then
538         retcode := 2;
539         errbuf  := 'Error while trying to update batch_dea_sql' || ' : SQLCODE : ' || SQLCODE ||
540                    ' : SQLERRM : ' || SQLERRM;
541         RAISE;
542     END;
543   END IF; /* end IF ((l_batch_enable_flag = 'Y') AND ... */
544 
545   IF ((l_batch_enable_flag = 'Y') AND (p_incr_reassign_sql IS NOT NULL)) THEN
546     BEGIN
547       OPEN CUR_INCR_REASSIGN(l_trans_usg_pgm_sql_id);
548       LOOP
549         FETCH CUR_INCR_REASSIGN INTO l_incr_reassign_sql_clob;
550         EXIT WHEN CUR_INCR_REASSIGN%NOTFOUND;
551         DBMS_LOB.OPEN(l_incr_reassign_sql_clob, DBMS_LOB.LOB_READWRITE);
552         DBMS_LOB.WRITEAPPEND(l_incr_reassign_sql_clob,LENGTH(p_incr_reassign_sql),p_incr_reassign_sql);
553         DBMS_LOB.CLOSE(l_incr_reassign_sql_clob);
554       END LOOP;
555       CLOSE CUR_INCR_REASSIGN;
556     EXCEPTION
557       when others then
558         retcode := 2;
559         errbuf  := 'Error while trying to update incr_reassign_sql' || ' : SQLCODE : ' || SQLCODE ||
560                    ' : SQLERRM : ' || SQLERRM;
561         RAISE;
562     END;
563   END IF; /* end IF ((l_batch_enable_flag = 'Y') AND ... */
564 
565   retcode := 0;
566   errbuf  := null;
567 
568 EXCEPTION
569   WHEN FND_API.G_EXC_ERROR THEN
570     NULL;
571 
572   WHEN OTHERS THEN
573     if (retcode = 0) then
574       retcode := 2;
575     end if;
579 End Insert_Row;
576     if (errbuf is null) then
577       errbuf := 'SQLCODE : ' || SQLCODE || ' : SQLERRM : ' || SQLERRM;
578     end if;
580 
581 END JTY_TRANS_USG_PGM_SQL_PKG;