1 PACKAGE BODY ibe_jpcv_migration_pvt AS
2 /* $Header: IBEVJMGB.pls 120.1 2005/08/09 22:39:09 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_JPCV_MIGRATION_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVJMGB.pls';
6
7 TYPE GENERIC_CSR IS REF CURSOR;
8
9 PROCEDURE Migrate_Sequence
10 (
11 p_old_seq IN VARCHAR2,
12 p_new_seq IN VARCHAR2
13 )
14 IS
15 cv1 GENERIC_CSR;
16 l_old_num NUMBER;
17 l_new_num NUMBER;
18
19 l_seq_sql1 VARCHAR2(200);
20 l_seq_sql2 VARCHAR2(200);
21 BEGIN
22 IF (p_old_seq = 'jtf_dsp_sections_b_s1')
23 AND (p_new_seq = 'ibe_dsp_sections_b_s1') THEN
24 l_seq_sql1 := 'SELECT jtf_dsp_sections_b_s1.nextval FROM dual';
25 l_seq_sql2 := 'SELECT ibe_dsp_sections_b_s1.nextval FROM dual';
26 ELSIF (p_old_seq = 'jtf_dsp_section_items_s1')
27 AND (p_new_seq = 'ibe_dsp_section_items_s1') THEN
28 l_seq_sql1 := 'SELECT jtf_dsp_section_items_s1.nextval FROM dual';
29 l_seq_sql2 := 'SELECT ibe_dsp_section_items_s1.nextval FROM dual';
30 ELSIF (p_old_seq = 'jtf_dsp_msite_sct_sects_s1')
31 AND (p_new_seq = 'ibe_dsp_msite_sct_sects_s1') THEN
32 l_seq_sql1 := 'SELECT jtf_dsp_msite_sct_sects_s1.nextval FROM dual';
33 l_seq_sql2 := 'SELECT ibe_dsp_msite_sct_sects_s1.nextval FROM dual';
34 ELSIF (p_old_seq = 'jtf_dsp_msite_sct_items_s1')
35 AND (p_new_seq = 'ibe_dsp_msite_sct_items_s1') THEN
36 l_seq_sql1 := 'SELECT jtf_dsp_msite_sct_items_s1.nextval FROM dual';
37 l_seq_sql2 := 'SELECT ibe_dsp_msite_sct_items_s1.nextval FROM dual';
38 ELSIF (p_old_seq = 'jtf_msites_b_s1')
39 AND (p_new_seq = 'ibe_msites_b_s1') THEN
40 l_seq_sql1 := 'SELECT jtf_msites_b_s1.nextval FROM dual';
41 l_seq_sql2 := 'SELECT ibe_msites_b_s1.nextval FROM dual';
42 ELSIF (p_old_seq = 'jtf_msite_resps_b_s1')
43 AND (p_new_seq = 'ibe_msite_resps_b_s1') THEN
44 l_seq_sql1 := 'SELECT jtf_msite_resps_b_s1.nextval FROM dual';
45 l_seq_sql2 := 'SELECT ibe_msite_resps_b_s1.nextval FROM dual';
46 ELSIF (p_old_seq = 'jtf_msite_prty_accss_s1')
47 AND (p_new_seq = 'ibe_msite_prty_accss_s1') THEN
48 l_seq_sql1 := 'SELECT jtf_msite_prty_accss_s1.nextval FROM dual';
49 l_seq_sql2 := 'SELECT ibe_msite_prty_accss_s1.nextval FROM dual';
50 ELSIF (p_old_seq = 'jtf_msite_currencies_s1')
51 AND (p_new_seq = 'ibe_msite_currencies_s1') THEN
52 l_seq_sql1 := 'SELECT jtf_msite_currencies_s1.nextval FROM dual';
53 l_seq_sql2 := 'SELECT ibe_msite_currencies_s1.nextval FROM dual';
54 ELSIF (p_old_seq = 'jtf_msite_languages_s1')
55 AND (p_new_seq = 'ibe_msite_languages_s1') THEN
56 l_seq_sql1 := 'SELECT jtf_msite_languages_s1.nextval FROM dual';
57 l_seq_sql2 := 'SELECT ibe_msite_languages_s1.nextval FROM dual';
58 ELSIF (p_old_seq = 'jtf_msite_orgs_s1')
59 AND (p_new_seq = 'ibe_msite_orgs_s1') THEN
60 l_seq_sql1 := 'SELECT jtf_msite_orgs_s1.nextval FROM dual';
61 l_seq_sql2 := 'SELECT ibe_msite_orgs_s1.nextval FROM dual';
62 ELSIF (p_old_seq = 'jtf_dsp_context_b_s1')
63 AND (p_new_seq = 'ibe_dsp_context_b_s1') THEN
64 l_seq_sql1 := 'SELECT jtf_dsp_context_b_s1.nextval FROM dual';
65 l_seq_sql2 := 'SELECT ibe_dsp_context_b_s1.nextval FROM dual';
66 ELSIF (p_old_seq = 'jtf_dsp_obj_lgl_ctnt_s1')
67 AND (p_new_seq = 'ibe_dsp_obj_lgl_ctnt_s1') THEN
68 l_seq_sql1 := 'SELECT jtf_dsp_obj_lgl_ctnt_s1.nextval FROM dual';
69 l_seq_sql2 := 'SELECT ibe_dsp_obj_lgl_ctnt_s1.nextval FROM dual';
70 ELSIF (p_old_seq = 'jtf_dsp_lgl_phys_map_s1')
71 AND (p_new_seq = 'ibe_dsp_lgl_phys_map_s1') THEN
72 l_seq_sql1 := 'SELECT jtf_dsp_lgl_phys_map_s1.nextval FROM dual';
73 l_seq_sql2 := 'SELECT ibe_dsp_lgl_phys_map_s1.nextval FROM dual';
74 ELSIF (p_old_seq = 'jtf_dsp_tpl_ctg_s1')
75 AND (p_new_seq = 'ibe_dsp_tpl_ctg_s1') THEN
76 l_seq_sql1 := 'SELECT jtf_dsp_tpl_ctg_s1.nextval FROM dual';
77 l_seq_sql2 := 'SELECT ibe_dsp_tpl_ctg_s1.nextval FROM dual';
78 END IF;
79 --
80 -- Do first nextval for the old sequence
81 --
82 OPEN cv1 FOR l_seq_sql1;
83 FETCH cv1 INTO l_old_num;
84 CLOSE cv1;
85
86 --
87 -- Do first nextval for the new sequence
88 --
89 OPEN cv1 FOR l_seq_sql2;
90 FETCH cv1 INTO l_new_num;
91 CLOSE cv1;
92
93 WHILE (l_new_num <= l_old_num) LOOP
94
95 -- increment the value of new sequence number
96 OPEN cv1 FOR l_seq_sql2;
97 FETCH cv1 INTO l_new_num;
98 CLOSE cv1;
99
100 END LOOP;
101
102 END Migrate_Sequence;
103
104 --
105 -- Return 0 if the table specified by p_table_name has no rows in it
106 -- Return 1 if the table specified by p_table_name has at least has 1 row in it
107 --
108 PROCEDURE Does_Row_Exists
109 (
110 p_table_name IN VARCHAR2,
111 p_primary_col_name IN VARCHAR2,
112 x_count OUT NOCOPY NUMBER
113 )
114 IS
115 cv1 GENERIC_CSR;
116 BEGIN
117
118 OPEN cv1 FOR 'SELECT 1 FROM ' || p_table_name ||
119 ' WHERE ' || p_primary_col_name || ' >= 10000 AND rownum = 1';
120 FETCH cv1 INTO x_count;
121 IF (cv1%NOTFOUND) THEN
122 x_count := 0;
123 ELSE
124 x_count := 1;
125 END IF;
126 CLOSE cv1;
127
128 END Does_Row_Exists;
129
130 PROCEDURE Log_Table_Migration_Start
131 (
132 p_module_name IN VARCHAR2,
133 p_src_table_name IN VARCHAR2,
134 p_dst_table_name IN VARCHAR2
135 )
136 IS
137 BEGIN
138 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
139 FND_MESSAGE.Set_Name('IBE', 'IBE_MIG_TABLE_INSERT_SELECT_ST');
140 FND_MESSAGE.Set_Token('DST_TABLE_NAME', p_dst_table_name);
141 FND_MESSAGE.Set_Token('SRC_TABLE_NAME', p_src_table_name);
142 FND_LOG.Message(FND_LOG.LEVEL_EVENT, p_module_name, TRUE);
143 END IF;
144 END Log_Table_Migration_Start;
145
146 PROCEDURE Log_Table_Migration_Finish
147 (
148 p_module_name IN VARCHAR2,
149 p_src_table_name IN VARCHAR2,
150 p_dst_table_name IN VARCHAR2
151 )
152 IS
153 BEGIN
154 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
155 FND_MESSAGE.Set_Name('IBE', 'IBE_MIG_TABLE_INSERT_SELECT_FN');
156 FND_MESSAGE.Set_Token('DST_TABLE_NAME', p_dst_table_name);
157 FND_MESSAGE.Set_Token('SRC_TABLE_NAME', p_src_table_name);
158 FND_LOG.Message(FND_LOG.LEVEL_EVENT, p_module_name, TRUE);
159 END IF;
160 END Log_Table_Migration_Finish;
161
162 END ibe_jpcv_migration_pvt;
[Home] [Help]