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

Source


1 PACKAGE BODY modify_abm_sequences AS
2 /*$Header: abmseqb.pls 115.5 2002/03/06 18:35:50 pkm ship    $*/
3 
4      xSqlCode VARCHAR2(1000);
5      xSqlErr VARCHAR2(1000);
6      xUserException EXCEPTION;
7 
8     /* The following procedure accepts the sequence name from the user
9        retrieves the corresponding table_column_name and the table name
10        It calls a function to modify the value of table_column_name
11        by 5. And then uses this incremented value of the table column
12        and calls a procedure that recreates the sequence with the starting
13        value as the "incremented table column value"*/
14 
15     PROCEDURE get_tabcol_and_seq(sequence_name VARCHAR2) IS
16          table_column_name VARCHAR2(1000) := NULL;
17          table_name VARCHAR2(1000) := NULL;
18          connect_statement VARCHAR2(1000) := NULL;
19          sequence_start_value NUMBER;
20     BEGIN
21 
22     /* Get the appropriate table_column_name and sequence_name from the
23        given table name */
24 
25          IF sequence_name = 'ABM_BATCH_CALCS_SEQ' THEN
26               table_column_name := 'M84_BATCH_PROG_ID_CTR';
27               table_name := 'ABM_BATCH_CALCS';
28          END IF;
29          IF sequence_name = 'ABM_BOR_HIER_SEQ' THEN
30               table_column_name := 'M33_BOR_LINE_ID_CTR';
31               table_name := 'ABM_BOR_HIER';
32          END IF;
33          IF sequence_name = 'ABM_CALCS_LOG_SEQ' THEN
34               table_column_name := 'M83_STEP_LOG_ID_CTR';
35               table_name := 'ABM_CALCS_LOG';
36          END IF;
37          IF sequence_name = 'ABM_CALCULATIONS_SEQ' THEN
38               table_column_name := 'M03_CALC_PROC_ELEM_STEP_ID_CTR';
39               table_name := 'ABM_CALCULATIONS';
40          END IF;
41          IF sequence_name = 'ABM_CALC_PROCS_SEQ' THEN
42               table_column_name := 'M01_CALC_PROC_ID_CTR';
43               table_name := 'ABM_CALC_PROCS';
44          END IF;
45          IF sequence_name = 'ABM_CALC_PROC_STEPS_SEQ' THEN
46               table_column_name := 'M02_CALC_STEP_ID_CTR';
47               table_name := 'ABM_CALC_PROC_STEPS';
48          END IF;
49          IF sequence_name = 'ABM_CAL_EXT_COSTS_SEQ' THEN
50               table_column_name := 'T05_SEQUENCE_NUMBER';
51               table_name := 'ABM_CAL_EXT_COSTS';
52          END IF;
53          IF sequence_name = 'ABM_COMP_DS_VAL_SEQ' THEN
54               table_column_name := 'T10_SEQUENCE_NUMBER';
55               table_name := 'ABM_COMP_DS_VAL';
56          END IF;
57          IF sequence_name = 'ABM_COMP_RES_VAL_SEQ' THEN
58               table_column_name := 'T09_SEQUENCE_NUMBER';
59               table_name := 'ABM_COMP_RES_VAL';
60          END IF;
61          IF sequence_name = 'ABM_CO_UNIT_COST_DAT_SEQ' THEN
62               table_column_name := 'M47A_SEQ_NBR';
63               table_name := 'ABM_CO_UNIT_COST_DAT';
64          END IF;
65          IF sequence_name = 'ABM_CO_UNIT_COST_EXT_SEQ' THEN
66               table_column_name := 'M47B_BOR_NBR';
67               table_name := 'ABM_CO_UNIT_COST_EXT';
68          END IF;
69          IF sequence_name = 'ABM_DERIVE_ACT_COSTS_SEQ' THEN
70               table_column_name := 'RUN_NUMBER';
71               table_name := 'ABM_DERIVE_ACT_COSTS';
72          END IF;
73          IF sequence_name = 'ABM_DERIVE_DS_VALS_SEQ' THEN
74               table_column_name := 'RUN_NUMBER';
75               table_name := 'ABM_DERIVE_DS_VALS';
76          END IF;
77          IF sequence_name = 'ABM_DERIVE_RES_QTYS_SEQ' THEN
78               table_column_name := 'RUN_NUMBER';
79               table_name := 'ABM_DERIVE_RES_QTYS';
80          END IF;
81          IF sequence_name = 'ABM_IMP_RES_TRANS_SEQ' THEN
82               table_column_name := 'N12_SEQUENCE_NUMBER';
83               table_name := 'ABM_IMP_RES_TRANS';
84          END IF;
85          IF sequence_name = 'ABM_MAP_BASES_SEQ' THEN
86               table_column_name := 'M67_MAPPING_BASIS_ID';
87               table_name := 'ABM_MAP_BASES';
88          END IF;
89          IF sequence_name = 'ABM_NAV_PROCEDURES_SEQ' THEN
90               table_column_name := 'NAV_PROC_ID';
91               table_name := 'ABM_NAV_PROCEDURES';
92          END IF;
93          IF sequence_name = 'ABM_NAV_PROC_STEPS_SEQ1' THEN
94               table_column_name := 'NAV_PROC_STEP_ID';
95               table_name := 'ABM_NAV_PROC_STEPS';
96          END IF;
97          IF sequence_name = 'ABM_NAV_PROC_STEPS_SEQ2' THEN
98               table_column_name := 'NAV_PROC_STEP_SEQ_NUM';
99               table_name := 'ABM_NAV_PROC_STEPS';
100          END IF;
101          IF sequence_name = 'ABM_NAV_STEPS_SEQ' THEN
102               table_column_name := 'STEP_ID';
103               table_name := 'ABM_NAV_STEPS';
104          END IF;
105          IF sequence_name = 'ABM_PDS_ACT_RATE_SEQ' THEN
106               table_column_name := 'T14_T42_ACT_RATE_CTR';
107               table_name := 'ABM_PDS_ACT_RATE';
108          END IF;
109          IF sequence_name = 'ABM_PROCS_SEQ' THEN
110               table_column_name := 'I04_PROC_ID_CTR';
111               table_name := 'ABM_PROCS';
112          END IF;
113          IF sequence_name = 'ABM_PROC_HIER_SEQ' THEN
114               table_column_name := 'T07_SEQUENCE_NUMBER';
115               table_name := 'ABM_PROC_HIER';
116          END IF;
117          IF sequence_name = 'ABM_PROC_STEPS_SEQ' THEN
118               table_column_name := 'I05_STEP_ID_CTR';
119               table_name := 'ABM_PROC_STEPS';
120          END IF;
121          IF sequence_name = 'ABM_RES_RE_ACC_DAT_SEQ' THEN
122               table_column_name := 'M16_SEQUENCE_NUMBER';
123               table_name := 'ABM_RES_RE_ACC_DAT';
124          END IF;
125          IF sequence_name = 'ABM_RES_RE_STA_DAT_SEQ' THEN
126               table_column_name := 'M17_SEQUENCE_NUMBER';
127               table_name := 'ABM_RES_RE_STA_DAT';
128          END IF;
129          IF sequence_name = 'ABM_RE_ACC_DAT_SEQ' THEN
130               table_column_name := 'M60_SEQUENCE_NUMBER';
131               table_name := 'ABM_RE_ACC_DAT';
132          END IF;
133          IF sequence_name = 'ABM_RE_ACC_MAP_FORMS_SEQ' THEN
134               table_column_name := 'M08_MAPPING_FORMULA_ID';
135               table_name := 'ABM_RE_ACC_MAP_FORMS';
136          END IF;
137          IF sequence_name = 'ABM_SEC_OBJS_SEQ' THEN
138               table_column_name := 'I91_OBJ_ID';
139               table_name := 'ABM_SEC_OBJS';
140          END IF;
141          IF sequence_name = 'ABM_ACC_MAP_SUM_REP_SEQ' THEN
142               table_column_name := 'ROW_CTR';
143               table_name := 'ABM_ACC_MAP_SUM_REP';
144          END IF;
145          IF sequence_name = 'ABM_ATT_ID_SEQ' THEN
146               table_column_name := 'ATTRIBUTE_TYPE_ID';
147               table_name := 'ABM_ATTRIBUTE_TYPE';
148          END IF;
149          IF sequence_name = 'ABM_BOR_HIER_REP_SEQ' THEN
150               table_column_name := 'T33_HIER_ID_CTR';
151               table_name := 'ABM_BOR_HIER_REP';
152          END IF;
153          IF sequence_name = 'ABM_BOR_LINE_REP_SEQ' THEN
154               table_column_name := 'T33_BOR_LINE_ID_CTR';
155               table_name := 'ABM_BOR_LINE_REP';
156          END IF;
157          IF sequence_name = 'ABM_CO_MARG_COMP_REP_SEQ' THEN
158               table_column_name := 'RUN_NUMBER';
159               table_name := 'ABM_CO_MARG_COMP_REP';
160          END IF;
161          IF sequence_name = 'ABM_DRV_DS_DTL_REP_SEQ' THEN
162               table_column_name := 'SURRROGATE_ID';
163               table_name := 'ABM_DRV_DS_DTL_REP';
164          END IF;
165          IF sequence_name = 'ABM_FLAT_PROC_HIER_SEQ' THEN
166               table_column_name := 'T06_SEQUENCE_NUMBER';
167               table_name := 'ABM_FLAT_PROC_HIER';
168          END IF;
169          IF sequence_name = 'ABM_PROC_WIN_STEPS_SEQ' THEN
170               table_column_name := 'I08_STEP_ID';
171               table_name := 'ABM_PROC_WIN_STEPS';
172          END IF;
173          IF sequence_name = 'ABM_RES_STA_REP_SEQ' THEN
174               table_column_name := 'RUN_NUMBER';
175               table_name := 'ABM_RES_STA_REP';
176          END IF;
177          IF sequence_name = 'ABM_SYS_ERRORS_LOG_SEQ' THEN
178               table_column_name := 'I21_SYS_ERR_REF_NUM';
179               table_name := 'ABM_SYS_ERRORS_LOG';
180          END IF;
181          IF sequence_name = 'ABM_TEMPLATE_S' THEN
182               table_column_name := 'TEMPLATE_ID';
183               table_name := 'ABM_BUS_VIEW_TEMPLATES';
184          END IF;
185 
186          IF (table_column_name IS NULL) OR (table_name IS NULL) THEN
187               --dbms_output.put_line('An Error has occured');
188               --dbms_output.put_line('Incorrect sequence name ' || sequence_name);
189               RAISE xUserException;
190          ELSE
191              /* Get the starting value for the sequence from this function
192                 modify_table_column_by_value */
193              sequence_start_value := modify_table_column_by_value(table_name, table_column_name,5);
194              IF(xSqlCode <> 0)THEN
195                 RAISE xUserException;
196              END IF;
197 
198              /* Recreate the sequence with the start value as the value
199                 obtained from the previous function*/
200              create_sequence_with_new_value ( sequence_name, sequence_start_value);
201              IF(xSqlCode <> 0)THEN
202                 RAISE xUserException;
203              END IF;
204 
205          END IF;
206 
207          EXCEPTION
208             WHEN xUserException THEN
209                  ROLLBACK;
210                  --dbms_output.put_line('An Error has occured please check the following code........');
211                  --dbms_output.put_line(xSqlCode);
212                  --dbms_output.put_line(xSqlErr);
213                  RAISE;
214 
215             WHEN OTHERS THEN
216                 xSqlCode := SQLCODE;
217                 xSqlErr := SQLERRM(xSqlCode);
218                 ROLLBACK;
219                 --dbms_output.put_line('An Error has occured please check the following code..........');
220                 --dbms_output.put_line(xSqlCode);
221                 --dbms_output.put_line(xSqlErr);
222                 RAISE;
223 
224     END get_tabcol_and_seq;
225 
226 
227     /*--------------------------------------------------------------------*/
228     /* This function accepts the ABM table column name , selects it into a temporary variable and increments the value by 5 */
229 
230     FUNCTION modify_table_column_by_value ( table_name VARCHAR2, table_column_name VARCHAR2,increment_value NUMBER) RETURN NUMBER IS
231     column_holder NUMBER;
232     select_statement VARCHAR2(1000);
233     BEGIN
234 
235         select_statement :=  'SELECT NVL(MAX(' || table_column_name || '),0)'
236                               ||' FROM ABM.'|| table_name;
237 
238 
239  	    EXECUTE IMMEDIATE select_statement INTO column_holder;
240 
241         column_holder := column_holder + increment_value;
242 
243         RETURN column_holder;
244 
245         EXCEPTION
246           WHEN OTHERS THEN
247               xSqlCode := SQLCODE;
248               xSqlErr := SQLERRM(xSqlCode);
249 
250 
251     END modify_table_column_by_value;
252 
253 
254     /*--------------------------------------------------------------------*/
255     /* This procedure drops the selected sequence and recreates it with
256        the input starting value */
257 
258     PROCEDURE create_sequence_with_new_value ( sequence_name VARCHAR2, starting_value NUMBER) IS
259 
260     sequence_holder VARCHAR2(1000);
261     create_statement VARCHAR2(1000);
262     select_statement VARCHAR2(1000);
263     drop_statement VARCHAR2(1000);
264     seq_max_value VARCHAR2(1000) := 2147483647;
265 
266     BEGIN
267 
268         BEGIN
269 
270          	select_statement :=  'SELECT SEQUENCE_NAME '
271               ||' FROM ALL_SEQUENCES'
272               ||' WHERE SEQUENCE_NAME = '
273               ||'''' || sequence_name || ''''
274               ||'AND SEQUENCE_OWNER = ' || '''' || 'ABM' || '''';
275 
276 
277          	EXECUTE IMMEDIATE select_statement INTO  sequence_holder;
278 
279 
280          	EXECUTE IMMEDIATE 'DROP SEQUENCE ABM.' || sequence_name;
281 
282          	EXCEPTION
283                 	WHEN NO_DATA_FOUND THEN
284                   		NULL;
285 
286          END;
287 
288          	create_statement :=  'CREATE SEQUENCE ABM.' || sequence_name
289                   ||' INCREMENT BY 1'
290                   ||' START WITH '|| starting_value
291                   ||' MINVALUE 1 '
292                   ||' MAXVALUE ' || seq_max_value
293                   ||' NOCYCLE NOORDER CACHE 20';
294 
295          	EXECUTE IMMEDIATE create_statement;
296 
297             --dbms_output.put_line(sequence_name || ' From ' || starting_value);
298 
299          EXCEPTION
300             WHEN OTHERS THEN
301                 xSqlCode := SQLCODE;
302                 xSqlErr := SQLERRM(xSqlCode);
303 
304    END create_sequence_with_new_value;
305 
306 
307 END modify_abm_sequences;