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

Source


1 PACKAGE BODY HZ_MIGRATE_MOSR_REFERENCES AS
2 /* $Header: ARHMPINSB.pls 120.4 2006/02/15 06:28:33 vravicha noship $ */
3 
4 PROCEDURE MIGRATE_PARTY_REFERENCES(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, l_batch_size IN varchar2) IS
5 
6 cursor parties is
7 select orig_system_reference, party_id, created_by, nvl(created_by_module,'TCA'),
8  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
9  from hz_parties party where orig_system_reference is not null and status in ('A','I')
10  and not exists(select 'X' from hz_orig_sys_references osr
11                 where party.party_id = osr.owner_table_id
12                 and  osr.owner_table_name = 'HZ_PARTIES'
13                 and osr.orig_system = 'UNKNOWN'
14                 and party.orig_system_reference =
15                     osr.orig_system_reference
16                 and osr.status = 'A')
17  and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_PARTIES' and multiple_flag='N' and orig_system='UNKNOWN');
18 
19 cursor locations is
20 select orig_system_reference, location_id, created_by, nvl(created_by_module,'TCA'),
21  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
22  from hz_locations locations where orig_system_reference is not null
23  and not exists(select 'X' from hz_orig_sys_references osr
24                 where locations.location_id = osr.owner_table_id
25                 and  osr.owner_table_name = 'HZ_LOCATIONS'
26                 and osr.orig_system = 'UNKNOWN'
27                 and locations.orig_system_reference =
28                     osr.orig_system_reference
29                 and osr.status = 'A')
30  and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_LOCATIONS' and multiple_flag='N' and orig_system='UNKNOWN');
31 
32 cursor org_contacts is
33 select orig_system_reference, org_contact_id, created_by, nvl(created_by_module,'TCA'),
34  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
35  from hz_org_contacts org_contacts where orig_system_reference is not null and status in ('A','I')
36  and not exists(select 'X' from hz_orig_sys_references osr
37                 where org_contacts.org_contact_id = osr.owner_table_id
38                 and  osr.owner_table_name = 'HZ_ORG_CONTACTS'
39                 and osr.orig_system = 'UNKNOWN'
40                 and org_contacts.orig_system_reference =
41                     osr.orig_system_reference
42                 and osr.status = 'A')
43 and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_ORG_CONTACTS' and multiple_flag='N' and orig_system='UNKNOWN');
44 
45 -- Bug 4956873
46 cursor contact_points is
47 select /*+ parallel(contact) */ orig_system_reference, contact_point_id, created_by, nvl(created_by_module,'TCA'),
48  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
49  from hz_contact_points contact where orig_system_reference is not null and status in ('A','I')
50  and not exists(select /*+ parallel(osr) */ 'X' from hz_orig_sys_references osr
51                 where contact.contact_point_id = osr.owner_table_id
52                 and  osr.owner_table_name = 'HZ_CONTACT_POINTS'
53                 and osr.orig_system = 'UNKNOWN'
54                 and contact.orig_system_reference =
55                     osr.orig_system_reference
56                 and osr.status = 'A')
57  and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_CONTACT_POINTS' and multiple_flag='N' and orig_system='UNKNOWN');
58 
59 -- Bug 4956873
60 cursor org_contact_roles is
61 select /*+ parallel(contact_roles) */ orig_system_reference, org_contact_role_id, created_by, nvl(created_by_module,'TCA'),
62  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
63  from hz_org_contact_roles contact_roles where orig_system_reference is not null and status in ('A','I')
64  and not exists(select /*+ parallel(osr) */ 'X' from hz_orig_sys_references osr
65                 where contact_roles.org_contact_role_id = osr.owner_table_id
66                 and  osr.owner_table_name = 'HZ_ORG_CONTACT_ROLES'
67                 and osr.orig_system = 'UNKNOWN'
68                 and contact_roles.orig_system_reference =
69                     osr.orig_system_reference
70                 and osr.status = 'A')
71  and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_ORG_CONTACT_ROLES' and multiple_flag='N' and orig_system='UNKNOWN');
72 
73 cursor party_sites is
74 select orig_system_reference, party_site_id, created_by, nvl(created_by_module,'TCA'),
75  nvl(application_id,'222'), creation_date,last_updated_by, last_update_date, nvl(last_update_login,-1)
76  from hz_party_sites party_sites where orig_system_reference is not null and status in ('A','I')
77  and not exists(select 'X' from hz_orig_sys_references osr
78                 where party_sites.party_site_id = osr.owner_table_id
79                 and  osr.owner_table_name = 'HZ_PARTY_SITES'
80                 and osr.orig_system = 'UNKNOWN'
81                 and party_sites.orig_system_reference =
82                     osr.orig_system_reference
83                 and osr.status = 'A')
84  and not exists(select 'N' from hz_orig_sys_mapping where owner_table_name='HZ_PARTY_SITES' and multiple_flag='N' and orig_system='UNKNOWN');
85 
86 TYPE orig_sys_ref_type is TABLE OF HZ_ORIG_SYS_REFERENCES.orig_system_reference%TYPE;
87 Type created_by_type is TABLE of HZ_ORIG_SYS_REFERENCES.created_by%TYPE;
88 Type created_by_module_type is TABLE of HZ_ORIG_SYS_REFERENCES.created_by_module%TYPE;
89 Type appl_id_type is TABLE of HZ_ORIG_SYS_REFERENCES.application_id%TYPE;
90 Type last_updated_by_type is TABLE of HZ_ORIG_SYS_REFERENCES.last_updated_by%TYPE;
91 Type creation_date_type is TABLE of HZ_ORIG_SYS_REFERENCES.creation_date%TYPE;
92 Type last_update_date_type is TABLE of HZ_ORIG_SYS_REFERENCES.last_update_date%TYPE;
93 Type last_update_login_type is TABLE of HZ_ORIG_SYS_REFERENCES.last_update_login%TYPE;
94 
95 TYPE owner_tableid_type IS TABLE OF HZ_ORIG_SYS_REFERENCES.owner_table_id%TYPE;
96 
97 orig_sys_ref orig_sys_ref_type;
98 created_by created_by_type;
99 created_by_module created_by_module_type;
100 appl_id appl_id_type;
101 last_updated_by last_updated_by_type;
102 creation_date creation_date_type;
103 last_update_date last_update_date_type;
104 last_update_login last_update_login_type;
105 
106 party_id owner_tableid_type;
107 location_id owner_tableid_type;
108 org_contact_id owner_tableid_type;
109 contact_point_id owner_tableid_type;
110 org_contact_role_id owner_tableid_type;
111 party_site_id owner_tableid_type;
112 
113 l_limit_rows number := 10000; /* Bug 4026560 */
114 i number;
115 l_last_fetch boolean;
116 
117 BEGIN
118 retcode:=0;
119 
120 IF l_batch_size < 10000 THEN
121      l_limit_rows := l_batch_size ;
122 END IF ;
123 
124 l_last_fetch:=false;
125 open parties;
126 loop
127  fetch parties bulk collect into
128  orig_sys_ref, party_id, created_by, created_by_module,
129  appl_id, creation_date, last_updated_by, last_update_date,
130  last_update_login limit l_limit_rows;
131 
132  if parties%NOTFOUND then
133   l_last_fetch:=true;
134  end if;
135 
136  if party_id.COUNT=0 and l_last_fetch then
137   exit;
138  end if;
139 
140  forall i in party_id.FIRST..party_id.LAST
141 insert into hz_orig_sys_references
142 (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
143  status, start_date_active, end_date_active, reason_code, created_by,
144  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
145  last_update_date,last_update_login, object_version_number)
146  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_PARTIES', party_id(i),
147  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i),
148  creation_date(i), last_updated_by(i), last_update_date(i),
149  last_update_login(i), 1);
150 
151  if l_last_fetch then
152   exit;
153  end if;
154 
155   end loop;
156 close parties;
157 
158 l_last_fetch:=false;
159 open locations;
160 loop
161  fetch locations bulk collect into
162  orig_sys_ref, location_id, created_by, created_by_module,
163  appl_id, creation_date, last_updated_by, last_update_date,
164  last_update_login limit l_limit_rows;
165 
166  if locations%NOTFOUND then
167   l_last_fetch:=true;
168  end if;
169 
170  if location_id.COUNT=0 and l_last_fetch then
171   exit;
172  end if;
173 
174  forall i in location_id.first..location_id.last
175  insert into hz_orig_sys_references
176  (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
177  status, start_date_active, end_date_active, reason_code, created_by,
178  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
179  last_update_date,last_update_login, object_version_number)
180  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_LOCATIONS', location_id(i),
181  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i),
182  creation_date(i), last_updated_by(i), last_update_date(i), last_update_login(i), 1);
183 
184  if l_last_fetch then
185   exit;
186  end if;
187 
188   end loop;
189 close locations;
190 
191 
192 l_last_fetch:=false;
193 open org_contacts;
194 loop
195  fetch org_contacts bulk collect into
196  orig_sys_ref, org_contact_id, created_by, created_by_module,
197  appl_id, creation_date, last_updated_by, last_update_date,
198  last_update_login limit l_limit_rows;
199 
200  if org_contacts%NOTFOUND then
201   l_last_fetch:=true;
202  end if;
203 
204  if org_contact_id.COUNT=0 and l_last_fetch then
205   exit;
206  end if;
207  forall i in org_contact_id.first..org_contact_id.last
208  insert into hz_orig_sys_references
209  (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
210  status, start_date_active, end_date_active, reason_code, created_by,
211  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
212  last_update_date,last_update_login, object_version_number)
213  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_ORG_CONTACTS', org_contact_id(i),
214  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i), creation_date(i),
215  last_updated_by(i), last_update_date(i), last_update_login(i), 1);
216 
217  if l_last_fetch then
218   exit;
219  end if;
220 
221   end loop;
222 close org_contacts;
223 
224 l_last_fetch:=false;
225 open contact_points;
226 loop
227  fetch contact_points bulk collect into
228  orig_sys_ref, contact_point_id, created_by, created_by_module,
229  appl_id, creation_date, last_updated_by, last_update_date,
230  last_update_login limit l_limit_rows;
231 
232  if contact_points%NOTFOUND then
233   l_last_fetch:=true;
234  end if;
235 
236  if contact_point_id.COUNT=0 and l_last_fetch then
237   exit;
238  end if;
239  forall i in contact_point_id.first..contact_point_id.last
240  insert into hz_orig_sys_references
241  (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
242  status, start_date_active, end_date_active, reason_code, created_by,
243  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
244  last_update_date,last_update_login, object_version_number)
245  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_CONTACT_POINTS', contact_point_id(i),
246  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i), creation_date(i), last_updated_by(i),
247  last_update_date(i), last_update_login(i), 1);
248 
249  if l_last_fetch then
250   exit;
251  end if;
252 
253   end loop;
254 close contact_points;
255 
256  l_last_fetch:=false;
257 open org_contact_roles;
258 loop
259  fetch org_contact_roles bulk collect into
260  orig_sys_ref, org_contact_role_id, created_by, created_by_module,
261  appl_id, creation_date, last_updated_by, last_update_date,
262  last_update_login limit l_limit_rows;
263 
264  if org_contact_roles%NOTFOUND then
265   l_last_fetch:=true;
266  end if;
267 
268  if org_contact_role_id.COUNT=0 and l_last_fetch then
269   exit;
270  end if;
271  forall i in org_contact_role_id.first..org_contact_role_id.last
272  insert into hz_orig_sys_references
273  (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
274  status, start_date_active, end_date_active, reason_code, created_by,
275  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
276  last_update_date,last_update_login, object_version_number)
277  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_ORG_CONTACT_ROLES', org_contact_role_id(i),
278  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i), creation_date(i), last_updated_by(i),
279  last_update_date(i), last_update_login(i), 1);
280 
281   if l_last_fetch then
282   exit;
283  end if;
284 
285   end loop;
286 close org_contact_roles;
287 
288 l_last_fetch:=false;
289 open party_sites;
290 loop
291  fetch party_sites bulk collect into
292  orig_sys_ref, party_site_id, created_by, created_by_module,
293  appl_id, creation_date, last_updated_by, last_update_date,
294  last_update_login limit l_limit_rows;
295 
296  if party_sites%NOTFOUND then
297   l_last_fetch:=true;
298  end if;
299 
300  if party_site_id.COUNT=0 and l_last_fetch then
301   exit;
302  end if;
303  forall i in party_site_id.first..party_site_id.last
304  insert into hz_orig_sys_references
305  (orig_system_ref_id, orig_system, orig_system_reference, owner_table_name, owner_table_id,
306  status, start_date_active, end_date_active, reason_code, created_by,
307  old_orig_system_reference, created_by_module, application_id, creation_date, last_updated_by,
308  last_update_date,last_update_login, object_version_number)
309  values(hz_orig_system_ref_s.nextval,'UNKNOWN', orig_sys_ref(i), 'HZ_PARTY_SITES', party_site_id(i),
310  'A', sysdate, null, null, created_by(i), null, created_by_module(i), appl_id(i), creation_date(i),
311  last_updated_by(i), last_update_date(i), last_update_login(i), 1);
312 
313  if l_last_fetch then
314   exit;
315  end if;
316 
317   end loop;
318 close party_sites;
319 
320 EXCEPTION
321   WHEN FND_API.G_EXC_ERROR THEN
322     retcode := 2;
323     errbuf := errbuf || SQLERRM;
324     FND_FILE.close;
325   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
326     retcode := 2;
327     errbuf := errbuf || SQLERRM;
328     FND_FILE.close;
329   WHEN OTHERS THEN
330     retcode := 2;
331     errbuf := errbuf || SQLERRM;
332     FND_FILE.close;
333 
334 END;
335 END HZ_MIGRATE_MOSR_REFERENCES;