1 PACKAGE BODY JTF_TTY_PROD_CAT_MIGRATE_PVT as
2 /* $Header: jtftrmpb.pls 120.3 2006/07/12 17:31:23 mhtran noship $ */
3
4 --*****************************************************************************
5
6 /* This procedure calls other procedure(s) to migrate the interest types, primary
7 interest codes and secondary interest codes for Opportunity Expected Purchase
8 and Lead Expected Purchase in the JTF_TERR_VALUES_ALL table. It also updates
9 the JTF_TERR_QUAL_ALL table with new qual_usg_ids */
10
11 PROCEDURE Migrate_All ( ERRBUF OUT NOCOPY VARCHAR2,
12 RETCODE OUT NOCOPY VARCHAR2,
13 p_Debug_Flag IN VARCHAR2 default 'N') IS
14 BEGIN
15
16 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
17
18 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration started for Opportunity Expected Purchase');
19 --Pass qual_usg_id for Opportunity Expected Purchase
20 Migrate_Product_Cat_Terr(-1023, -1142, p_Debug_Flag);
21
22 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration started for Lead Expected Purchase');
23 --Pass qual_usg_id for Lead Expected Purchase
24 Migrate_Product_Cat_Terr(-1018, -1131, p_Debug_Flag);
25
26 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration started for Interest Types in JTF_TTY_ROLE_PROD_INT');
27 --Updating JTF_TTY_ROLE_PROD_INT table
28 Migrate_Product_Cat_Role(p_Debug_Flag);
29
30 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration completed successfully');
31
32 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 Rollback;
37 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Product Catalog data migration '||SQLERRM);
38 END Migrate_All;
39
40 /* This procedure migrates interest types, primary interest codes and secondary
41 interest codes in the JTF_TERR_VALUES_ALL table */
42 PROCEDURE Migrate_Product_Cat_Terr(p_Qual_Usg_Id IN NUMBER,
43 p_Qual_Usg_Id_New IN NUMBER,
44 p_Debug_Flag IN VARCHAR2 Default 'N') IS
45
46 TYPE NumTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
47 l_terr_val_ids_arr NumTableType;
48
49 -- Cursor for Interest Type Updates
50 CURSOR c_int_type_upd_list(l_qual_usg_id NUMBER, l_qual_usg_id_new NUMBER ) IS
51 SELECT a.terr_value_id
52 FROM jtf_terr_values_all a, jtf_terr_qual_all b
53 WHERE a.terr_qual_id = b.terr_qual_id
54 AND b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
55 AND a.interest_type_id is not null
56 AND a.primary_interest_code_id is null
57 AND a.secondary_interest_code_id is null;
58
59 -- Cursor for Primary Interest Code Updates
60 CURSOR c_pri_int_upd_list(l_qual_usg_id NUMBER, l_qual_usg_id_new NUMBER) IS
61 SELECT a.terr_value_id
62 FROM jtf_terr_values_all a, jtf_terr_qual_all b
63 WHERE a.terr_qual_id = b.terr_qual_id
64 AND b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
65 AND a.primary_interest_code_id is not null
66 AND a.secondary_interest_code_id is null;
67
68 -- Cursor for Secondary Interest Code Updates
69 CURSOR c_sec_int_upd_list(l_qual_usg_id NUMBER, l_qual_usg_id_new NUMBER) IS
70 SELECT a.terr_value_id
71 FROM jtf_terr_values_all a, jtf_terr_qual_all b
72 WHERE a.terr_qual_id = b.terr_qual_id
73 AND b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
74 AND a.secondary_interest_code_id is not null;
75
76 BEGIN
77
78 if (upper(p_Debug_Flag) = 'Y') then
79 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
80 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start: Migrating Interest Types');
81 end if;
82
83 -- Interest Types
84 OPEN c_int_type_upd_list(p_Qual_Usg_Id, p_Qual_Usg_Id_New);
85 FETCH c_int_type_upd_list BULK COLLECT INTO l_terr_val_ids_arr;
86 IF (NVL(l_terr_val_ids_arr.COUNT,0) > 0) THEN
87 if (upper(p_Debug_Flag) = 'Y') then
88 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interest Types: Rows to be migrated: ' || l_terr_val_ids_arr.COUNT);
89 end if;
90 FORALL j IN l_terr_val_ids_arr.FIRST..l_terr_val_ids_arr.LAST
91 UPDATE jtf_terr_values_all jtv
92 SET (value1_id, value2_id) =
93 (SELECT int.product_category_id, int.product_cat_set_id
94 FROM as_interest_types_b int
95 WHERE jtv.interest_type_id = int.interest_type_id)
96 WHERE jtv.terr_value_id = l_terr_val_ids_arr(j);
97 if (upper(p_Debug_Flag) = 'Y') then
98 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interest Types: Rows migrated: ' || SQL%ROWCOUNT);
99 end if;
100 ELSE
101 if (upper(p_Debug_Flag) = 'Y') then
102 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interest Types: Rows to be migrated: 0');
103 end if;
104 END IF;
105 CLOSE c_int_type_upd_list;
106
107 if (upper(p_Debug_Flag) = 'Y') then
108 FND_FILE.PUT_LINE(FND_FILE.LOG,'End: Migrating Interest Types');
109 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
110 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start: Migrating Primary Interest Codes');
111 end if;
112
113 -- Primary Interest Codes
114 OPEN c_pri_int_upd_list(p_Qual_Usg_Id, p_Qual_Usg_Id_New);
115 FETCH c_pri_int_upd_list BULK COLLECT INTO l_terr_val_ids_arr;
116 IF (NVL(l_terr_val_ids_arr.COUNT,0) > 0) THEN
117 if (upper(p_Debug_Flag) = 'Y') then
118 FND_FILE.PUT_LINE(FND_FILE.LOG,'Primary Interest Codes: Rows to be migrated: ' || l_terr_val_ids_arr.COUNT);
119 end if;
120 FORALL j IN l_terr_val_ids_arr.FIRST..l_terr_val_ids_arr.LAST
121 UPDATE jtf_terr_values_all jtv
122 SET (value1_id, value2_id) =
123 (SELECT int.product_category_id, int.product_cat_set_id
124 FROM as_interest_codes_b int
125 WHERE jtv.primary_interest_code_id = int.interest_code_id)
126 WHERE jtv.terr_value_id = l_terr_val_ids_arr(j);
127 if (upper(p_Debug_Flag) = 'Y') then
128 FND_FILE.PUT_LINE(FND_FILE.LOG,'Primary Interest codes: Rows migrated: ' || SQL%ROWCOUNT);
129 end if;
130 ELSE
131 if (upper(p_Debug_Flag) = 'Y') then
132 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interest Types: Rows to be migrated: 0');
133 end if;
134 END IF;
135 CLOSE c_pri_int_upd_list;
136
137 if (upper(p_Debug_Flag) = 'Y') then
138 FND_FILE.PUT_LINE(FND_FILE.LOG,'End: Migrating Primary Interest Codes');
139 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
140 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start: Migrating Secondary Interest Codes');
141 end if;
142
143 -- Secondary Interest Codes
144 OPEN c_sec_int_upd_list(p_Qual_Usg_Id, p_Qual_Usg_Id_New);
145 FETCH c_sec_int_upd_list BULK COLLECT INTO l_terr_val_ids_arr;
146 IF (NVL(l_terr_val_ids_arr.COUNT,0) > 0) THEN
147 if (upper(p_Debug_Flag) = 'Y') then
148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Secondary Interest Codes: Rows to be migrated: ' || l_terr_val_ids_arr.COUNT);
149 end if;
150 FORALL j IN l_terr_val_ids_arr.FIRST..l_terr_val_ids_arr.LAST
151 UPDATE jtf_terr_values_all jtv
152 SET (value1_id, value2_id) =
153 (SELECT int.product_category_id, int.product_cat_set_id
154 FROM as_interest_codes_b int
155 WHERE jtv.secondary_interest_code_id = int.interest_code_id)
156 WHERE jtv.terr_value_id = l_terr_val_ids_arr(j);
157 if (upper(p_Debug_Flag) = 'Y') then
158 FND_FILE.PUT_LINE(FND_FILE.LOG,'Secondary Interest Codes: Rows migrated: ' || SQL%ROWCOUNT);
159 end if;
160 ELSE
161 if (upper(p_Debug_Flag) = 'Y') then
162 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interest Types: Rows to be migrated: 0');
163 end if;
164 END IF;
165 CLOSE c_sec_int_upd_list;
166
167 if (upper(p_Debug_Flag) = 'Y') then
168 FND_FILE.PUT_LINE(FND_FILE.LOG,'End: Migrating Secondary Interest Codes');
169 end if;
170
171 if (p_Qual_Usg_Id = -1023) then
172
173 --Update Qualifiers
174 UPDATE jtf_terr_qual_all qual
175 SET qual_usg_id = -1142
176 WHERE qual_usg_id = -1023;
177 if (upper(p_Debug_Flag) = 'Y') then
178 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated Qualifier Opportunity Expected Purchase to Opportunity Product Category');
179 end if;
180
181 --Disable usage of old qualifier
182 /*
183 commented out 07/10/2006, ref bug 5193133 replaced below
184 UPDATE jtf_qual_usgs_all
185 SET enabled_flag = 'N'
186 WHERE qual_usg_id = -1023;
187 */
188
189 delete from jtf_seeded_qual_all_b
190 where seeded_qual_id = -1024;
191
192 delete from jtf_seeded_qual_all_tl
193 where seeded_qual_id = -1024;
194
195 DELETE FROM jtf_qual_usgs_all
196 WHERE qual_usg_id = -1023;
197
198 if (upper(p_Debug_Flag) = 'Y') then
199 FND_FILE.PUT_LINE(FND_FILE.LOG,'Disabled Qualifier Opportunity Expected Purchase');
200 end if;
201
202 --Enable usage of new qualifier
203 UPDATE jtf_qual_usgs_all
204 SET enabled_flag = 'Y'
205 WHERE qual_usg_id = -1142;
206 if (upper(p_Debug_Flag) = 'Y') then
207 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enabled Qualifier Opportunity Product Category');
208 end if;
209
210 elsif (p_Qual_Usg_Id = -1018) then
211 UPDATE jtf_terr_qual_all qual
212 SET qual_usg_id = -1131
213 WHERE qual_usg_id = -1018;
214 if (upper(p_Debug_Flag) = 'Y') then
215 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated Qualifier Lead Expected Purchase to Lead Product Category');
216 end if;
217
218 --Disable usage of old qualifier, lead expected
219 /*
220 commented out 07/10/2006, ref bug 5193133 replaced below
221 UPDATE jtf_qual_usgs_all
222 SET enabled_flag = 'N'
223 WHERE qual_usg_id = -1018;
224 */
225
226 delete from jtf_seeded_qual_all_b
227 where seeded_qual_id = -1019;
228
229 delete from jtf_seeded_qual_all_tl
230 where seeded_qual_id = -1019;
231
232 DELETE FROM jtf_qual_usgs_all
233 WHERE qual_usg_id = -1018;
234
235 if (upper(p_Debug_Flag) = 'Y') then
236 FND_FILE.PUT_LINE(FND_FILE.LOG,'Disabled Qualifier Lead Expected Purchase');
237 end if;
238
239 UPDATE jtf_qual_usgs_all
240 SET enabled_flag = 'Y'
241 WHERE qual_usg_id = -1131;
242 if (upper(p_Debug_Flag) = 'Y') then
243 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enabled Qualifier Lead Product Category');
244 end if;
245
246 end if;
247 COMMIT;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 Rollback;
252 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Product Catalog data migration: ' || SQLERRM);
253 RAISE;
254 END Migrate_Product_Cat_Terr;
255
256 /* This procedure migrates interest types in the JTF_TTY_ROLE_PROD_INT table */
257 PROCEDURE Migrate_Product_Cat_Role(p_Debug_Flag IN VARCHAR2 Default 'N') IS
258
259 BEGIN
260
261 if (upper(p_Debug_Flag) = 'Y') then
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start: Migrating Interest Types in JTF_TTY_ROLE_PROD_INT table');
263 end if;
264
265 UPDATE jtf_tty_role_prod_int jtr
266 SET (product_category_id, product_category_set_id) =
267 (SELECT int.product_category_id, int.product_cat_set_id
268 FROM as_interest_types_b int
269 WHERE jtr.interest_type_id = int.interest_type_id);
270 COMMIT;
271
272 if (upper(p_Debug_Flag) = 'Y') then
273 FND_FILE.PUT_LINE(FND_FILE.LOG,'End: Migrating Interest Types in JTF_TTY_ROLE_PROD_INT table');
274 end if;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 Rollback;
279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Product Catalog data migration: ' || SQLERRM);
280 RAISE;
281 END Migrate_Product_Cat_Role;
282
283 END JTF_TTY_PROD_CAT_MIGRATE_PVT;
284
[Home] [Help]