[Home] [Help]
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;