[Home] [Help]
PACKAGE BODY: APPS.JTY_COLLECTION_MIGRATION_PKG
Source
1 PACKAGE BODY JTY_COLLECTION_MIGRATION_PKG AS
2 /* $Header: jtfcolmb.pls 120.1 2006/09/11 21:27:45 mhtran noship $ */
3
4 -- Start of Comments
5
6 -- ---------------------------------------------------
7
8 -- PACKAGE NAME: JTY_COLLECTION_MIGRATION_PKG
9
10 -- ---------------------------------------------------
11
12
13
14 -- PURPOSE
15
16 -- to migrate specific hierarchy to collection usage
17
18 --
19
20 --
21
22 -- PROCEDURES:
23
24 -- (see below for specification)
25
26 --
27
28 --
29
30 -- HISTORY
31
32 -- 08/25/2006 MHTRAN Package Body Created
33 --
34
35 -- End of Comments
36
37 --
38
39 TYPE number_tbl_type is table of NUMBER index by PLS_INTEGER;
40 TYPE varchar2_tbl_type is table of VARCHAR2(360) index by PLS_INTEGER;
41 TYPE date_tbl_type is table of DATE index by PLS_INTEGER;
42 TYPE var_1_tbl_type is table of VARCHAR2(1) index by PLS_INTEGER;
43 TYPE var_2000_tbl_type is table of VARCHAR2(2000) index by PLS_INTEGER;
44
45 TYPE Terr_All_Rec_Type IS RECORD
46 (TERR_ID number_tbl_type,
47 LAST_UPDATE_DATE date_tbl_type,
48 LAST_UPDATED_BY number_tbl_type,
49 CREATION_DATE date_tbl_type,
50 CREATED_BY number_tbl_type,
51 LAST_UPDATE_LOGIN number_tbl_type,
52 APPLICATION_SHORT_NAME varchar2_tbl_type,
53 NAME var_2000_tbl_type,
54 RANK number_tbl_type,
55 ENABLED_FLAG var_1_tbl_type,
56 START_DATE_ACTIVE date_tbl_type,
57 END_DATE_ACTIVE date_tbl_type,
58 PARENT_TERRITORY_ID number_tbl_type,
59 TERRITORY_TYPE_ID number_tbl_type,
60 DESCRIPTION varchar2_tbl_type,
61 UPDATE_FLAG var_1_tbl_type,
62 ATTRIBUTE_CATEGORY varchar2_tbl_type,
63 ATTRIBUTE1 varchar2_tbl_type,
64 ATTRIBUTE2 varchar2_tbl_type,
65 ATTRIBUTE3 varchar2_tbl_type,
66 ATTRIBUTE4 varchar2_tbl_type,
67 ATTRIBUTE5 varchar2_tbl_type,
68 ATTRIBUTE6 varchar2_tbl_type,
69 ATTRIBUTE7 varchar2_tbl_type,
70 ATTRIBUTE8 varchar2_tbl_type,
71 ATTRIBUTE9 varchar2_tbl_type,
72 ATTRIBUTE10 varchar2_tbl_type,
73 ATTRIBUTE11 varchar2_tbl_type,
74 ATTRIBUTE12 varchar2_tbl_type,
75 ATTRIBUTE13 varchar2_tbl_type,
76 ATTRIBUTE14 varchar2_tbl_type,
77 ATTRIBUTE15 varchar2_tbl_type,
78 ORG_ID number_tbl_type ,
79 NUM_WINNERS number_tbl_type,
80 NUM_QUAL number_tbl_type,
81 OBJECT_VERSION_NUMBER number_tbl_type,
82 NEW_TERR_ID number_tbl_type
83 );
84
85 TYPE terr_qual_rec_type IS RECORD
86 (TERR_QUAL_ID number_tbl_type,
87 NEW_TERR_QUAL_ID number_tbl_type,
88 TERR_ID number_tbl_type,
89 NEW_TERR_ID number_tbl_type,
90 LAST_UPDATE_DATE date_tbl_type,
91 LAST_UPDATED_BY number_tbl_type,
92 CREATION_DATE date_tbl_type,
93 CREATED_BY number_tbl_type,
94 LAST_UPDATE_LOGIN number_tbl_type,
95 QUAL_USG_ID number_tbl_type,
96 QUALIFIER_MODE varchar2_tbl_type,
97 OVERLAP_ALLOWED_FLAG var_1_tbl_type,
98 USE_TO_NAME_FLAG var_1_tbl_type,
99 GENERATE_FLAG var_1_tbl_type,
100 ORG_ID number_tbl_type,
101 SECURITY_GROUP_ID number_tbl_type,
102 OBJECT_VERSION_NUMBER number_tbl_type
103 );
104
105 TYPE terr_rsc_rec_type IS RECORD
106 (TERR_RSC_ID number_tbl_type,
107 NEW_TERR_ID number_tbl_type,
108 TERR_ID number_tbl_type
109 );
110
111
112 PROCEDURE UPDATE_TERR_RECORD (
113 x_errbuf OUT NOCOPY VARCHAR2,
114 x_retcode OUT NOCOPY VARCHAR2,
115 p_terr_id IN NUMBER
116 ) IS
117
118 CURSOR get_terr_def_csr
119 (v_terr_id number) IS
120 SELECT terr.TERR_ID, terr.LAST_UPDATE_DATE, terr.LAST_UPDATED_BY,
121 terr.CREATION_DATE, terr.CREATED_BY, terr.LAST_UPDATE_LOGIN,
122 terr.APPLICATION_SHORT_NAME, terr.NAME, terr.RANK,
123 terr.ENABLED_FLAG, terr.START_DATE_ACTIVE, terr.END_DATE_ACTIVE,
124 terr.PARENT_TERRITORY_ID, terr.TERRITORY_TYPE_ID,
125 terr.DESCRIPTION, terr.UPDATE_FLAG,
126 terr.ATTRIBUTE_CATEGORY, terr.ATTRIBUTE1, terr.ATTRIBUTE2,
127 terr.ATTRIBUTE3, terr.ATTRIBUTE4, terr.ATTRIBUTE5,
128 terr.ATTRIBUTE6, terr.ATTRIBUTE7, terr.ATTRIBUTE8,
129 terr.ATTRIBUTE9, terr.ATTRIBUTE10, terr.ATTRIBUTE11,
130 terr.ATTRIBUTE12, terr.ATTRIBUTE13, terr.ATTRIBUTE14,
131 terr.ATTRIBUTE15, terr.ORG_ID,
132 terr.NUM_WINNERS, terr.NUM_QUAL, terr.OBJECT_VERSION_NUMBER--,
133 --JTF_TERR_s.nextval NEW_TERR_ID
134 from JTF_TERR_ALL terr
135 where nvl(terr.TERR_GROUP_FLAG,'N') = 'N'
136 and NVL(terr.ENABLE_SELF_SERVICE,'N') = 'N'
137 CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
138 AND terr.TERR_ID <> 1
139 START WITH terr.terr_id = v_terr_id
140 order siblings by terr.terr_id;
141
142 CURSOR get_terr_qual_csr(
143 v_terr_id number,
144 v_new_terr_id number,
145 v_qtype_usg_id number,
146 v_sales_qtype_usg_id number) IS
147 select jtq.TERR_QUAL_ID, JTF_TERR_QUAL_s.nextval NEW_TERR_QUAL_ID,
148 jtq.TERR_ID, v_new_terr_id NEW_TERR_ID,
149 jtq.LAST_UPDATE_DATE, jtq.LAST_UPDATED_BY,
150 jtq.CREATION_DATE, jtq.CREATED_BY, jtq.LAST_UPDATE_LOGIN,
151 col_usg.qual_usg_id QUAL_USG_ID, jtq.QUALIFIER_MODE,
152 jtq.OVERLAP_ALLOWED_FLAG, jtq.USE_TO_NAME_FLAG, jtq.GENERATE_FLAG,
153 jtq.ORG_ID, jtq.SECURITY_GROUP_ID, jtq.OBJECT_VERSION_NUMBER
154 from JTF_TERR_QUAL_ALL jtq,
155 jtf_qual_usgs_all col_usg, jtf_qual_usgs_all sales_usg
156 where jtq.terr_id = v_terr_id
157 and jtq.qual_usg_id = sales_usg.qual_usg_id
158 and jtq.org_id = sales_usg.org_id
159 and col_usg.qual_type_usg_id = v_qtype_usg_id
160 and col_usg.seeded_qual_id = sales_usg.seeded_qual_id
161 and col_usg.org_id = sales_usg.org_id
162 and sales_usg.qual_type_usg_id = v_sales_qtype_usg_id;
163
164 l_terr_def_rec Terr_All_Rec_Type;
165 l_terr_qual_rec terr_qual_rec_type;
166 l_terr_rsc_rec terr_rsc_rec_type;
167
168
169 l_terr_type_id number;
170 l_source_id number;
171 l_qtype_usg_id number;
172 l_qual_type_id number;
173 l_sales_qtype_usg_id number := -1001;
174
175 l_access_type varchar2(32);
176
177 BEGIN
178
179 -- get template id
180 begin
181 select terr_type_id into l_terr_type_id
182 from jtf_terr_types_all
183 where name = 'General Collections'
184 and rownum = 1;
185 --dbms_output.put_line('template_id: '||l_terr_type_id);
186
187 exception
188 when no_data_found then
189 x_retcode := FND_API.G_RET_STS_ERROR;
190 x_errbuf := 'Exception in get template id: ' || sqlcode||': '||SQLERRM;
191 end;
192
193 -- get source_id
194 begin
195 select source_id into l_source_id
196 from jtf_sources_all
197 where description = 'Collections'
198 and rownum = 1;
199 --dbms_output.put_line('source_id: '||l_source_id);
200
201 exception
202 when no_data_found then
203 x_retcode := FND_API.G_RET_STS_ERROR;
204 x_errbuf := 'Exception in get source_id: ' || sqlcode||': '||SQLERRM;
205 end;
206
207 -- get qual_type_usgs_id
208 begin
209
210 select qual_type_usg_id, qual_type_id
211 into l_qtype_usg_id, l_qual_type_id
212 from jtf_qual_type_usgs_all
213 where source_id = l_source_id
214 and rownum = 1;
215 --dbms_output.put_line('qual_type_usgs_id,qual_type_id: '||l_qtype_usg_id ||', '|| l_qual_type_id);
216
217 exception
218 when no_data_found then
219 x_retcode := FND_API.G_RET_STS_ERROR;
220 x_errbuf := 'Exception in get qual_type_usgs_id: ' || sqlcode||': '||SQLERRM;
221 end;
222
223 -- get access_type
224 begin
225
226 select name into l_access_type
227 from JTF_QUAL_TYPES_ALL
228 where qual_type_id = l_qual_type_id
229 and rownum = 1;
230 --dbms_output.put_line('access_type: '|| l_access_type);
231
232 exception
233 when no_data_found then
234 x_retcode := FND_API.G_RET_STS_ERROR;
235 x_errbuf := 'Exception in get access_type: ' || sqlcode||': '||SQLERRM;
236 end;
237
238 open get_terr_def_csr (p_terr_id);
239 fetch get_terr_def_csr bulk collect into
240 l_terr_def_rec.TERR_ID, l_terr_def_rec.LAST_UPDATE_DATE, l_terr_def_rec.LAST_UPDATED_BY,
241 l_terr_def_rec.CREATION_DATE, l_terr_def_rec.CREATED_BY, l_terr_def_rec.LAST_UPDATE_LOGIN,
242 l_terr_def_rec.APPLICATION_SHORT_NAME, l_terr_def_rec.NAME, l_terr_def_rec.RANK,
243 l_terr_def_rec.ENABLED_FLAG, l_terr_def_rec.START_DATE_ACTIVE, l_terr_def_rec.END_DATE_ACTIVE,
244 l_terr_def_rec.PARENT_TERRITORY_ID, l_terr_def_rec.TERRITORY_TYPE_ID,
245 l_terr_def_rec.DESCRIPTION, l_terr_def_rec.UPDATE_FLAG,
246 l_terr_def_rec.ATTRIBUTE_CATEGORY, l_terr_def_rec.ATTRIBUTE1, l_terr_def_rec.ATTRIBUTE2,
247 l_terr_def_rec.ATTRIBUTE3, l_terr_def_rec.ATTRIBUTE4, l_terr_def_rec.ATTRIBUTE5,
248 l_terr_def_rec.ATTRIBUTE6, l_terr_def_rec.ATTRIBUTE7, l_terr_def_rec.ATTRIBUTE8,
249 l_terr_def_rec.ATTRIBUTE9, l_terr_def_rec.ATTRIBUTE10, l_terr_def_rec.ATTRIBUTE11,
250 l_terr_def_rec.ATTRIBUTE12, l_terr_def_rec.ATTRIBUTE13, l_terr_def_rec.ATTRIBUTE14,
251 l_terr_def_rec.ATTRIBUTE15, l_terr_def_rec.ORG_ID,
252 l_terr_def_rec.NUM_WINNERS, l_terr_def_rec.NUM_QUAL, l_terr_def_rec.OBJECT_VERSION_NUMBER;
253 --l_terr_def_rec.NEW_TERR_ID;
254 close get_terr_def_csr;
255
256 -- copy territory definition rows
257 IF (l_terr_def_rec.TERR_ID.count > 0) THEN
258 --dbms_output.put_line('Start copy territory definition');
259
260 for i in l_terr_def_rec.TERR_ID.first..l_terr_def_rec.TERR_ID.last loop
261 select JTF_TERR_s.nextval into l_terr_def_rec.NEW_TERR_ID(i)
262 from dual;
263 --dbms_output.put_line('New terr_id: '|| l_terr_def_rec.NEW_TERR_ID(i));
264 end loop;
265
266 forall i in l_terr_def_rec.TERR_ID.first..l_terr_def_rec.TERR_ID.last
267 INSERT INTO JTF_TERR_ALL (
268 TERR_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
269 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
270 APPLICATION_SHORT_NAME, NAME, RANK,
271 ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE,
272 PARENT_TERRITORY_ID, TERRITORY_TYPE_ID,
273 DESCRIPTION, UPDATE_FLAG,
274 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
275 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
276 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
277 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
278 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
279 ATTRIBUTE15, ORG_ID,
280 NUM_WINNERS, NUM_QUAL, OBJECT_VERSION_NUMBER)
281 VALUES (l_terr_def_rec.NEW_TERR_ID(i), l_terr_def_rec.LAST_UPDATE_DATE(i), l_terr_def_rec.LAST_UPDATED_BY(i),
282 l_terr_def_rec.CREATION_DATE(i), l_terr_def_rec.CREATED_BY(i), l_terr_def_rec.LAST_UPDATE_LOGIN(i),
283 l_terr_def_rec.APPLICATION_SHORT_NAME(i), l_terr_def_rec.NAME(i), l_terr_def_rec.RANK(i),
284 l_terr_def_rec.ENABLED_FLAG(i), l_terr_def_rec.START_DATE_ACTIVE(i), l_terr_def_rec.END_DATE_ACTIVE(i),
285 l_terr_def_rec.PARENT_TERRITORY_ID(i), l_terr_type_id,
286 l_terr_def_rec.DESCRIPTION(i), l_terr_def_rec.UPDATE_FLAG(i),
287 l_terr_def_rec.ATTRIBUTE_CATEGORY(i), l_terr_def_rec.ATTRIBUTE1(i), l_terr_def_rec.ATTRIBUTE2(i),
288 l_terr_def_rec.ATTRIBUTE3(i), l_terr_def_rec.ATTRIBUTE4(i), l_terr_def_rec.ATTRIBUTE5(i),
289 l_terr_def_rec.ATTRIBUTE6(i), l_terr_def_rec.ATTRIBUTE7(i), l_terr_def_rec.ATTRIBUTE8(i),
290 l_terr_def_rec.ATTRIBUTE9(i), l_terr_def_rec.ATTRIBUTE10(i), l_terr_def_rec.ATTRIBUTE11(i),
291 l_terr_def_rec.ATTRIBUTE12(i), l_terr_def_rec.ATTRIBUTE13(i), l_terr_def_rec.ATTRIBUTE14(i),
292 l_terr_def_rec.ATTRIBUTE15(i), l_terr_def_rec.ORG_ID(i),
293 l_terr_def_rec.NUM_WINNERS(i), l_terr_def_rec.NUM_QUAL(i), l_terr_def_rec.OBJECT_VERSION_NUMBER(i));
294
295 --dbms_output.put_line('Terr def insert completed: ' ||SQL%ROWCOUNT);
296
297 forall i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last
298 UPDATE JTF_TERR_ALL
299 set parent_territory_id = l_terr_def_rec.NEW_TERR_ID(i)
300 where parent_territory_id = l_terr_def_rec.TERR_ID(i)
301 and TERRITORY_TYPE_ID = l_terr_type_id;
302
303 forall i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last
304 INSERT INTO JTF_TERR_USGS_ALL(
305 TERR_USG_ID,
306 LAST_UPDATE_DATE,
307 LAST_UPDATED_BY,
308 CREATION_DATE,
309 CREATED_BY,
310 LAST_UPDATE_LOGIN,
311 TERR_ID,
312 SOURCE_ID,
313 ORG_ID
314 )
315 VALUES (
316 JTF_TERR_USGS_s.nextval,
317 l_terr_def_rec.LAST_UPDATE_DATE(i),
318 l_terr_def_rec.LAST_UPDATED_BY(i),
319 l_terr_def_rec.CREATION_DATE(i),
320 l_terr_def_rec.CREATED_BY(i),
321 l_terr_def_rec.LAST_UPDATE_LOGIN(i),
322 l_terr_def_rec.NEW_TERR_ID(i),
323 l_source_id,
324 l_terr_def_rec.ORG_ID(i));
325 --dbms_output.put_line('Terr usage insert completed: ' ||SQL%ROWCOUNT);
326
327 forall i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last
328 INSERT INTO JTF_TERR_QTYPE_USGS_ALL(
329 TERR_QTYPE_USG_ID,
330 LAST_UPDATED_BY,
331 LAST_UPDATE_DATE,
332 CREATED_BY,
333 CREATION_DATE,
334 LAST_UPDATE_LOGIN,
335 TERR_ID,
336 QUAL_TYPE_USG_ID,
337 ORG_ID
338 )
339 VALUES (
340 JTF_TERR_QTYPE_USGS_s.nextval,
341 l_terr_def_rec.LAST_UPDATED_BY(i),
342 l_terr_def_rec.LAST_UPDATE_DATE(i),
343 l_terr_def_rec.CREATED_BY(i),
344 l_terr_def_rec.CREATION_DATE(i),
345 l_terr_def_rec.LAST_UPDATE_LOGIN(i),
346 l_terr_def_rec.NEW_TERR_ID(i),
347 l_qtype_usg_id,
348 l_terr_def_rec.ORG_ID(i));
349 --dbms_output.put_line('Terr qual type insert completed: ' ||SQL%ROWCOUNT);
350
351 -- copy terr qual rows
352 FOR i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last LOOP
353 OPEN get_terr_qual_csr(l_terr_def_rec.TERR_ID(i), l_terr_def_rec.NEW_TERR_ID(i),
354 l_qtype_usg_id, l_sales_qtype_usg_id);
355 FETCH get_terr_qual_csr BULK COLLECT INTO
356 l_terr_qual_rec.TERR_QUAL_ID, l_terr_qual_rec.NEW_TERR_QUAL_ID,
357 l_terr_qual_rec.TERR_ID, l_terr_qual_rec.NEW_TERR_ID,
358 l_terr_qual_rec.LAST_UPDATE_DATE, l_terr_qual_rec.LAST_UPDATED_BY,
359 l_terr_qual_rec.CREATION_DATE, l_terr_qual_rec.CREATED_BY, l_terr_qual_rec.LAST_UPDATE_LOGIN,
360 l_terr_qual_rec.QUAL_USG_ID, l_terr_qual_rec.QUALIFIER_MODE,
361 l_terr_qual_rec.OVERLAP_ALLOWED_FLAG, l_terr_qual_rec.USE_TO_NAME_FLAG, l_terr_qual_rec.GENERATE_FLAG,
362 l_terr_qual_rec.ORG_ID, l_terr_qual_rec.SECURITY_GROUP_ID, l_terr_qual_rec.OBJECT_VERSION_NUMBER;
363 CLOSE get_terr_qual_csr;
364
365 IF (l_terr_qual_rec.NEW_TERR_QUAL_ID.COUNT > 0) THEN
366 forall j in l_terr_qual_rec.NEW_TERR_QUAL_ID.first..l_terr_qual_rec.NEW_TERR_QUAL_ID.last
367 INSERT INTO JTF_TERR_QUAL_ALL (
368 TERR_QUAL_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
369 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
370 TERR_ID,
371 QUAL_USG_ID, QUALIFIER_MODE,
372 OVERLAP_ALLOWED_FLAG, USE_TO_NAME_FLAG, GENERATE_FLAG,
373 ORG_ID, SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER)
374 VALUES (l_terr_qual_rec.NEW_TERR_QUAL_ID(j),
375 l_terr_qual_rec.LAST_UPDATE_DATE(j), l_terr_qual_rec.LAST_UPDATED_BY(j),
376 l_terr_qual_rec.CREATION_DATE(j), l_terr_qual_rec.CREATED_BY(j),
377 l_terr_qual_rec.LAST_UPDATE_LOGIN(j), l_terr_qual_rec.NEW_TERR_ID(j),
378 l_terr_qual_rec.QUAL_USG_ID(j), l_terr_qual_rec.QUALIFIER_MODE(j),
379 l_terr_qual_rec.OVERLAP_ALLOWED_FLAG(j), l_terr_qual_rec.USE_TO_NAME_FLAG(j),
380 l_terr_qual_rec.GENERATE_FLAG(j),
381 l_terr_qual_rec.ORG_ID(j), l_terr_qual_rec.SECURITY_GROUP_ID(j),
382 l_terr_qual_rec.OBJECT_VERSION_NUMBER(j));
383
384 --dbms_output.put_line('Terr qual all insert completed: ' ||SQL%ROWCOUNT);
385
386 forall i in l_terr_qual_rec.TERR_QUAL_ID.first..l_terr_qual_rec.TERR_QUAL_ID.last
387 INSERT INTO JTF_TERR_VALUES_ALL (
388 TERR_VALUE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE,
389 CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
390 TERR_QUAL_ID, INCLUDE_FLAG, COMPARISON_OPERATOR,
391 ID_USED_FLAG, LOW_VALUE_CHAR_ID, LOW_VALUE_CHAR,
395 SECURITY_GROUP_ID, CNR_GROUP_ID, VALUE1_ID,
392 HIGH_VALUE_CHAR, LOW_VALUE_NUMBER, HIGH_VALUE_NUMBER,
393 VALUE_SET, INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID,
394 SECONDARY_INTEREST_CODE_ID, CURRENCY_CODE, ORG_ID,
396 VALUE2_ID, VALUE3_ID, FIRST_CHAR,
397 OBJECT_VERSION_NUMBER, VALUE4_ID, SELF_SERVICE_TERR_VALUE_ID)
398 (SELECT JTF_TERR_VALUES_s.nextval TERR_VALUE_ID, jtv.LAST_UPDATED_BY, jtv.LAST_UPDATE_DATE,
399 jtv.CREATED_BY, jtv.CREATION_DATE, jtv.LAST_UPDATE_LOGIN,
400 l_terr_qual_rec.NEW_TERR_QUAL_ID(i) TERR_QUAL_ID,
401 jtv.INCLUDE_FLAG, jtv.COMPARISON_OPERATOR,
402 jtv.ID_USED_FLAG, jtv.LOW_VALUE_CHAR_ID, jtv.LOW_VALUE_CHAR,
403 jtv.HIGH_VALUE_CHAR, jtv.LOW_VALUE_NUMBER, jtv.HIGH_VALUE_NUMBER,
404 jtv.VALUE_SET, jtv.INTEREST_TYPE_ID, jtv.PRIMARY_INTEREST_CODE_ID,
405 jtv.SECONDARY_INTEREST_CODE_ID, jtv.CURRENCY_CODE, jtv.ORG_ID,
406 jtv.SECURITY_GROUP_ID, jtv.CNR_GROUP_ID, jtv.VALUE1_ID,
407 jtv.VALUE2_ID, jtv.VALUE3_ID, jtv.FIRST_CHAR,
408 jtv.OBJECT_VERSION_NUMBER, jtv.VALUE4_ID, jtv.SELF_SERVICE_TERR_VALUE_ID
409 FROM JTF_TERR_VALUES_ALL jtv, JTF_TERR_QUAL_ALL jtq
410 WHERE jtv.TERR_QUAL_ID = jtq.terr_qual_id
411 and jtv.org_id = jtq.org_id
412 and jtq.terr_id = l_terr_qual_rec.TERR_ID(i)
413 and jtq.terr_qual_id = l_terr_qual_rec.terr_qual_id(i));
414 --dbms_output.put_line('Terr values all insert completed: ' ||SQL%ROWCOUNT);
415
416 END IF; -- l_terr_qual_rec.NEW_TERR_QUAL_ID.COUNT
417 END LOOP; -- copy terr qual rows
418
419 -- copy terr resource rows
420 forall i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last
421 INSERT INTO JTF_TERR_RSC_ALL (
422 TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
423 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
424 TERR_ID, RESOURCE_ID, RESOURCE_TYPE,
425 ROLE, PRIMARY_CONTACT_FLAG, START_DATE_ACTIVE,
426 END_DATE_ACTIVE, ORG_ID, FULL_ACCESS_FLAG,
427 GROUP_ID, SECURITY_GROUP_ID, PERSON_ID,
428 OBJECT_VERSION_NUMBER, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
429 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
430 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
431 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
432 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
433 ATTRIBUTE14, ATTRIBUTE15)
434 (SELECT JTF_TERR_RSC_s.nextval TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
435 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
436 l_terr_def_rec.NEW_TERR_ID(i) TERR_ID, RESOURCE_ID, RESOURCE_TYPE,
437 ROLE, PRIMARY_CONTACT_FLAG, START_DATE_ACTIVE,
438 END_DATE_ACTIVE, ORG_ID, FULL_ACCESS_FLAG,
439 GROUP_ID, SECURITY_GROUP_ID, PERSON_ID,
440 OBJECT_VERSION_NUMBER, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
441 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
442 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
443 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
444 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
445 ATTRIBUTE14, ATTRIBUTE15
446 FROM JTF_TERR_RSC_ALL
447 where terr_id = l_terr_def_rec.TERR_ID(i));
448 --dbms_output.put_line('Terr rsc all insert completed: ' ||SQL%ROWCOUNT);
449
450 END IF;
451
452 IF (l_terr_def_rec.NEW_TERR_ID.count > 0) THEN
453
454 --dbms_output.put_line('Start terr rsc access insert');
455
456 for i in l_terr_def_rec.NEW_TERR_ID.first..l_terr_def_rec.NEW_TERR_ID.last loop
457
458 --dbms_output.put_line('Old terr_id: ' || l_terr_def_rec.TERR_ID(i));
459 --dbms_output.put_line('New terr_id: ' || l_terr_def_rec.NEW_TERR_ID(i));
460
461 SELECT TERR_RSC_ID, TERR_ID, l_terr_def_rec.TERR_ID(i)
462 BULK COLLECT INTO l_terr_rsc_rec.terr_rsc_id,
463 l_terr_rsc_rec.new_terr_id, l_terr_rsc_rec.terr_id
464 FROM JTF_TERR_RSC_ALL
465 WHERE TERR_ID = l_terr_def_rec.NEW_TERR_ID(i);
466
467 --dbms_output.put_line('Terr_rsc_id Row count: ' ||l_terr_rsc_rec.terr_rsc_id.count);
468
469 -- copy resource access rows
470 IF (l_terr_rsc_rec.terr_rsc_id.COUNT > 0) THEN
471 forall i in l_terr_rsc_rec.terr_rsc_id.first..l_terr_rsc_rec.terr_rsc_id.last
472 INSERT INTO JTF_TERR_RSC_ACCESS_ALL
473 (TERR_RSC_ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
474 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
475 TERR_RSC_ID, ACCESS_TYPE, ORG_ID,
476 SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER, TRANS_ACCESS_CODE )
477 SELECT JTF_TERR_RSC_ACCESS_s.nextval TERR_RSC_ACCESS_ID,
478 jtra.LAST_UPDATE_DATE, jtra.LAST_UPDATED_BY,
479 jtra.CREATION_DATE, jtra.CREATED_BY, jtra.LAST_UPDATE_LOGIN,
480 l_terr_rsc_rec.terr_rsc_id(i) TERR_RSC_ID,
481 l_access_type ACCESS_TYPE, jtra.ORG_ID,
482 jtra.SECURITY_GROUP_ID, jtra.OBJECT_VERSION_NUMBER, jtra.TRANS_ACCESS_CODE
483 FROM JTF_TERR_RSC_ALL jtr, JTF_TERR_RSC_ACCESS_ALL jtra
484 where jtr.terr_rsc_id = jtra.terr_rsc_id
485 and jtr.terr_id = l_terr_rsc_rec.terr_id(i)
486 and rownum = 1;
487 --dbms_output.put_line('Terr rsc access all insert completed: ' ||SQL%ROWCOUNT);
488 END IF;
489
490 end loop;
491 END IF; --l_terr_def_rec
492
493 commit;
494 x_retcode := FND_API.G_RET_STS_SUCCESS;
495 x_errbuf := 'UPDATE_TERR_RECORD completed successfully';
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 x_retcode := FND_API.G_RET_STS_ERROR;
500 x_errbuf := 'Exception in UPDATE_TERR_RECORD: ' || sqlcode||': '||SQLERRM;
501
502 END UPDATE_TERR_RECORD;
503
504 END JTY_COLLECTION_MIGRATION_PKG;