[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_SSM_MATCHING_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_SSM_MATCHING_PKG AS
2 /*$Header: ARHLSSMB.pls 120.39 2011/09/22 08:48:23 vsegu ship $*/
3
4
5
6 c_end_date DATE := to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI');
7 l_no_end_date DATE := TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
8
9 PROCEDURE match_parties(
10 P_BATCH_ID IN NUMBER,
11 P_OS IN VARCHAR2,
12 P_FROM_OSR IN VARCHAR2,
13 P_TO_OSR IN VARCHAR2,
14 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
15 P_RERUN IN VARCHAR2,
16 P_BATCH_MODE_FLAG IN VARCHAR2
17 ) IS
18
19 /******
20 Populattion of new_osr_exists_flag for DNB Data
21 - The new_osr_exists_flag is populated for the following three cases
22
23 Interface Table Entry: Party_ID Party_OSR
24 1000 ABC
25
26 SSM Table Entry: Owner_table_id Party_OSR
27 a) OSR Collision 1000 CDE (end-date in import)
28 2000 ABC (end-date in import)
29 ------------------------------------
30 1000 ABC (insert in import)
31 b) First time DNB for existing data
32
33 ------------------------------------
34 1000 ABC (insert in import)
35 c) First time DNB for exisitng data, party already exists for the OSR
36 2000 ABC (end-date in import)
37 ------------------------------------
38 1000 ABC (insert in import)
39
40 The flag is populated for different values for the above cases, null oterwise
41 a) 'Y'
42 b) 'E'
43 c) 'R'
44 ******/
45
46 BEGIN
47 if (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN = 'Y') then
48 -- re-run for third party vender, OSR change possible
49 --dbms_output.put_line('flag 1');
50 insert all
51 when ( 1 = 1 )
52 then
53 into hz_imp_parties_sg
54 (
55 PARTY_ID,
56 PARTY_ORIG_SYSTEM,
57 PARTY_ORIG_SYSTEM_REFERENCE,
58 INT_ROW_ID,
59 ACTION_FLAG,
60 OLD_ORIG_SYSTEM_REFERENCE,
61 new_osr_exists_flag,
62 batch_mode_flag,
63 batch_id
64 ) values
65 (
66 nvl(party_id,HZ_PARTIES_S.NextVal),
67 party_os,
68 party_osr,
69 int_row_id,
70 action_flag,
71 old_osr,
72 new_osr_exists_flag,
73 P_BATCH_MODE_FLAG,
74 P_BATCH_ID
75 )
76 when (
77 old_osr is not null
78 ) then
79 into hz_imp_osr_change (
80 entity_name,
81 new_osr_exists_flag,
82 OLD_ORIG_SYSTEM_REFERENCE,
83 NEW_ORIG_SYSTEM_REFERENCE,
84 ENTITY_ID,
85 BATCH_ID
86 ) values
87 ( 'HZ_PARTIES',
88 new_osr_exists_flag,
89 old_osr,
90 party_osr,
91 party_id,
92 P_BATCH_ID
93 )
94 select /*+ leading(p_int) index_asc(p_int) use_nl(mosr) use_nl(mosr2) */
95 nvl(p_int.party_id, mosr.owner_table_id) party_id,
96 p_int.party_orig_system party_os,
97 p_int.party_orig_system_reference party_osr,
98 p_int.rowid int_row_id,
99 nvl2(nvl(p_int.party_id, mosr.owner_table_id), 'U', 'I') action_flag,
100 -- populate old_osr only if osr change
101 nullif(mosr2.orig_system_reference, p_int.party_orig_system_reference) old_osr,
102 -- if party id in interface,
103 -- populate 'E' if no match for the id at all in mosr table
104 -- populate 'Y' if the new osr is occupied by another party
105 nvl2(p_int.party_id,
106 nvl2(mosr2.orig_system_reference,
107 nvl2(nullif(mosr.owner_table_id, p_int.party_id), 'Y', null),
108 nvl2(mosr.owner_table_id, 'R', 'E')
109 ), null) new_osr_exists_flag
110 from hz_imp_parties_int p_int,
111 hz_orig_sys_references mosr, -- OSR look up
112 hz_orig_sys_references mosr2 -- look up for osr collision
113 where p_int.party_orig_system_reference = mosr.orig_system_reference (+)
114 and p_int.party_orig_system = mosr.orig_system (+)
115 and p_int.party_id = mosr2.owner_table_id (+)
116 and p_int.batch_id = P_BATCH_ID
117 and p_int.party_orig_system = P_OS
118 and p_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
119 and mosr.owner_table_name (+) = 'HZ_PARTIES'
120 and mosr.status (+) = 'A'
121 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
122 and mosr2.orig_system (+) = P_ACTUAL_CONTENT_SRC
123 and mosr2.status (+) = 'A'
124 and p_int.interface_status = 'C';
125 elsif (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN <> 'Y') then
126
127 -- first run for third party vender, OSR change possible
128
129 --dbms_output.put_line('flag 2');
130
131 insert all
132 when ( 1 = 1 )
133 then
134 into hz_imp_parties_sg
135 (
136 PARTY_ID,
137 PARTY_ORIG_SYSTEM,
138 PARTY_ORIG_SYSTEM_REFERENCE,
139 INT_ROW_ID,
140 ACTION_FLAG,
141 OLD_ORIG_SYSTEM_REFERENCE,
142 new_osr_exists_flag,
143 batch_mode_flag,
144 batch_id
145 ) values
146 (
147 nvl(party_id,HZ_PARTIES_S.NextVal),
148 party_os,
149 party_osr,
150 int_row_id,
151 action_flag,
152 old_osr,
153 new_osr_exists_flag,
154 P_BATCH_MODE_FLAG,
155 P_BATCH_ID
156 )
157 when (
158 old_osr is not null
159 ) then
160 into hz_imp_osr_change (
161 entity_name,
162 new_osr_exists_flag,
163 OLD_ORIG_SYSTEM_REFERENCE,
164 NEW_ORIG_SYSTEM_REFERENCE,
165 ENTITY_ID,
166 BATCH_ID
167 ) values
168 ( 'HZ_PARTIES',
169 new_osr_exists_flag,
170 old_osr,
171 party_osr,
172 party_id,
173 P_BATCH_ID
174 )
175 select /*+ leading(p_int) index_asc(p_int) use_nl(mosr) use_nl(mosr2) */
176 nvl(p_int.party_id, mosr.owner_table_id) party_id,
177 p_int.party_orig_system party_os,
178 p_int.party_orig_system_reference party_osr,
179 p_int.rowid int_row_id,
180 nvl2(nvl(p_int.party_id, mosr.owner_table_id), 'U', 'I') action_flag,
181 -- populate old_osr only if osr change
182 nullif(mosr2.orig_system_reference, p_int.party_orig_system_reference) old_osr,
183 -- if party id in interface,
184 -- populate 'E' if no match for the id at all in mosr table
185 -- populate 'Y' if the new osr is occupied by another party
186 nvl2(p_int.party_id,
187 nvl2(mosr2.orig_system_reference,
188 nvl2(nullif(mosr.owner_table_id, p_int.party_id), 'Y', null),
189 nvl2(mosr.owner_table_id, 'R', 'E')
190 ), null) new_osr_exists_flag
191 from hz_imp_parties_int p_int,
192 hz_orig_sys_references mosr, -- OSR look up
193 hz_orig_sys_references mosr2 -- look up for osr collision
194 where p_int.party_orig_system_reference = mosr.orig_system_reference (+)
195 and p_int.party_orig_system = mosr.orig_system (+)
196 and p_int.party_id = mosr2.owner_table_id (+)
197 and p_int.batch_id = P_BATCH_ID
198 and p_int.party_orig_system = P_OS
199 and p_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
200 and mosr.owner_table_name (+) = 'HZ_PARTIES'
201 and mosr.status (+) = 'A'
202 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
203 and mosr2.orig_system (+) = P_ACTUAL_CONTENT_SRC
204 and mosr2.status (+) = 'A'
205 and p_int.interface_status is null;
206 elsif (P_ACTUAL_CONTENT_SRC = 'USER_ENTERED' and P_RERUN = 'Y') then
207 -- re-run for legacy system, OSR change ignored
208
209 --dbms_output.put_line('flag 3');
210
211 insert into hz_imp_parties_sg
212 (
213 PARTY_ID,
214 PARTY_ORIG_SYSTEM,
215 PARTY_ORIG_SYSTEM_REFERENCE,
216 INT_ROW_ID,
217 ACTION_FLAG,
218 batch_mode_flag,
219 batch_id
220 )
221 (
222 select /*+ leading(p_int) index_asc(p_int) use_nl(mosr) */
223 nvl(mosr.owner_table_id, HZ_PARTIES_S.NextVal) party_id,
224 p_int.party_orig_system party_os,
225 p_int.party_orig_system_reference party_osr,
226 p_int.rowid int_row_id,
227 nvl2(mosr.owner_table_id, 'U', 'I') action_flag,
228 P_BATCH_MODE_FLAG, P_BATCH_ID
229 from hz_imp_parties_int p_int,
230 hz_orig_sys_references mosr
231 where p_int.party_orig_system_reference = mosr.orig_system_reference (+)
232 and p_int.party_orig_system = mosr.orig_system (+)
233 and p_int.batch_id = P_BATCH_ID
234 and p_int.party_orig_system = P_OS
235 and p_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
236 and mosr.owner_table_name (+) = 'HZ_PARTIES'
237 and mosr.status (+) = 'A'
238 and p_int.interface_status = 'C'
239 );
240 else
241 -- first run for legacy system, OSR change ignored
242
243 --dbms_output.put_line('flag 4');
244
245 insert into hz_imp_parties_sg
246 (
247 PARTY_ID,
248 PARTY_ORIG_SYSTEM,
249 PARTY_ORIG_SYSTEM_REFERENCE,
250 INT_ROW_ID,
251 ACTION_FLAG,
252 batch_mode_flag,
253 batch_id
254 )
255 (
256 select /*+ leading(p_int) index_asc(p_int) use_nl(mosr) */
257 nvl(mosr.owner_table_id, HZ_PARTIES_S.NextVal) party_id,
258 p_int.party_orig_system party_os,
259 p_int.party_orig_system_reference party_osr,
260 p_int.rowid int_row_id,
261 nvl2(mosr.owner_table_id, 'U', 'I') action_flag,
262 P_BATCH_MODE_FLAG, P_BATCH_ID
263 from hz_imp_parties_int p_int,
264 hz_orig_sys_references mosr
265 where p_int.party_orig_system_reference = mosr.orig_system_reference (+)
266 and p_int.party_orig_system = mosr.orig_system (+)
267 and p_int.batch_id = P_BATCH_ID
268 and p_int.party_orig_system = P_OS
269 and p_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
270 and mosr.owner_table_name (+) = 'HZ_PARTIES'
271 and mosr.status (+) = 'A'
272 and p_int.interface_status is null
273 );
274 end if;
275 commit;
276 end match_parties;
277
278
279 PROCEDURE match_addresses(
280 P_BATCH_ID IN NUMBER,
281 P_OS IN VARCHAR2,
282 P_FROM_OSR IN VARCHAR2,
283 P_TO_OSR IN VARCHAR2,
284 P_RERUN IN VARCHAR2,
285 P_BATCH_MODE_FLAG IN VARCHAR2
286 ) IS
287
288 BEGIN
289 /*
290 Fix bug 4374278: If party_id is passed from address_int table, check if the
291 party_id is valid. If party_id is not passed, retain original
292 testing.
293 */
294 if (P_RERUN = 'Y') then
295 -- re-run
296 insert all
297 when ( 1 = 1 )
298 then
299 into hz_imp_addresses_sg
300 (
301 PARTY_ID,
302 party_orig_system,
303 PARTY_ORIG_SYSTEM_REFERENCE,
304 site_orig_system,
305 SITE_ORIG_SYSTEM_REFERENCE,
306 INT_ROW_ID,
307 ACTION_FLAG,
308 PARTY_SITE_ID,
309 ERROR_FLAG,
310 PARTY_ACTION_FLAG,
311 OLD_SITE_ORIG_SYSTEM_REF,
312 new_osr_exists_flag,
313 BATCH_ID,
314 BATCH_MODE_FLAG,
315 PRIMARY_FLAG
316 ) values
317 (
318 pid, pos, posr,
319 psos, psosr,
320 int_row_id, action_flag,
321 nvl(party_site_id, hz_party_sites_s.nextval),
322 error_flag, party_action_flag,
323 old_psosr, overwrite_osr_flag,
324 P_BATCH_ID, P_BATCH_MODE_FLAG,
325 primary_flag
326 )
327 when (
328 psosr <> old_psosr
329 ) then
330 into hz_imp_osr_change (
331 entity_name,
332 new_osr_exists_flag,
333 OLD_ORIG_SYSTEM_REFERENCE,
334 NEW_ORIG_SYSTEM_REFERENCE,
335 ENTITY_ID,
336 BATCH_ID,
337 PARTY_ID
338 ) values
339 ( 'HZ_PARTY_SITES',
340 overwrite_osr_flag,
341 old_psosr,
342 psosr,
343 nvl(party_site_id, hz_party_sites_s.nextval),
344 P_BATCH_ID, pid
345 )
346 select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
347 ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,
348 ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
349 ps_int_w_psosr.int_row_id,
350 nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,
351 nvl2(ps_int_w_psosr.owning_pty_chg_flag,null,psosr_mosr.owner_table_id) party_site_id,
352 --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,
353 --psosr_mosr.owner_table_id party_site_id, -- Bug6082657
354 DECODE (ps_int_w_psosr.ps_int_pid, NULL, nvl2(nullif(psosr_mosr.party_id, ps_int_w_psosr.pid), 2, null),
355 NVL2(p.party_id, NULL, 3) ) error_flag,
356 ps_int_w_psosr.party_action_flag,
357 ps_int_w_psosr.final_psosr old_psosr,
358 ps_int_w_psosr.psg_new_osr_flag overwrite_osr_flag,
359 -- parttition by id to select one primary per party
360 nvl2(hz_ps.party_site_id, null,
361 decode(row_number() over
362 (partition by ps_int_w_psosr.pid order by
363 ps_int_w_psosr.primary_flag
364 desc nulls last),
365 1, 'Y', null)) primary_flag
366 from hz_orig_sys_references psosr_mosr,
367 hz_party_sites hz_ps,
368 (
369 select /*+ no_merge leading(ps_int) index_asc(ps_int) use_hash(psg) use_nl(mosr) */
370 ps_int.rowid int_row_id,
371 -- note osr_change.entity_id must come before mosr
372 -- in case of osr collision mosr.owner_table_id is not correct
373 nvl(nvl(nvl(ps_int.party_id, psg.party_id), osr_change_tbl.entity_id), mosr.owner_table_id) pid,
374 ps_int.party_id ps_int_pid,
375 mosr.owner_table_id mosr_pid,
376 ps_int.party_orig_system_reference posr,
377 ps_int.party_orig_system pos,
378 ps_int.site_orig_system_reference psosr,
379 ps_int.site_orig_system psos,
380 -- the psosr to match, changed back to old osr for osr change case
381 decode(osr_change_tbl.old_orig_system_reference, null,
382 ps_int.site_orig_system_reference,
383 osr_change_tbl.old_orig_system_reference || substr(
384 ps_int.site_orig_system_reference, instr(
385 ps_int.site_orig_system_reference, '-'))) final_psosr,
386 nvl2(ps_int.party_id,'U',nvl(psg.action_flag, 'U')) party_action_flag,
387 osr_change_tbl.new_osr_exists_flag psg_new_osr_flag,
388 ps_int.primary_flag primary_flag,
392 hz_orig_sys_references mosr,
389 nvl2(nullif(ps_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
390 from hz_imp_addresses_int ps_int,
391 hz_imp_parties_sg psg,
393 hz_imp_osr_change osr_change_tbl
394 where mosr.owner_table_name (+) = 'HZ_PARTIES'
395 and mosr.status (+) = 'A'
396 and ps_int.batch_id = P_BATCH_ID
397 and ps_int.party_orig_system = P_OS
398 and ps_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
399 and ps_int.party_orig_system_reference = psg.party_orig_system_reference (+)
400 and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
401 and psg.party_orig_system (+) = P_OS
402 and psg.batch_id(+) = P_BATCH_ID
403 and ps_int.party_orig_system = psg.party_orig_system (+)
404 and ps_int.batch_id = psg.batch_id(+)
405 and psg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
406 and psg.action_flag(+) = 'I'
407 and ps_int.party_orig_system_reference = mosr.orig_system_reference (+)
408 and ps_int.party_orig_system = mosr.orig_system (+)
409 and ps_int.interface_status = 'C'
410 and ps_int.party_orig_system_reference = osr_change_tbl.new_orig_system_reference (+)
411 and osr_change_tbl.entity_name (+) = 'HZ_PARTIES'
412 and osr_change_tbl.batch_id (+) = P_BATCH_ID
413 and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
414 ) ps_int_w_psosr,
415 hz_parties p
416 where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
417 and ps_int_w_psosr.final_psosr =
418 psosr_mosr.orig_system_reference (+)
419 and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
420 and psosr_mosr.status (+) = 'A'
421 and ps_int_w_psosr.pid = hz_ps.party_id (+)
422 and 'Y' = hz_ps.identifying_address_flag (+)
423 and 'A' = hz_ps.status (+)
424 AND ps_int_w_psosr.ps_int_pid = p.party_id (+);
425 else
426 -- first run
427 insert all
428 when ( 1 = 1 )
429 then
430 into hz_imp_addresses_sg
431 (
432 PARTY_ID,
433 party_orig_system,
434 PARTY_ORIG_SYSTEM_REFERENCE,
435 site_orig_system,
436 SITE_ORIG_SYSTEM_REFERENCE,
437 INT_ROW_ID,
438 ACTION_FLAG,
439 PARTY_SITE_ID,
440 ERROR_FLAG,
441 PARTY_ACTION_FLAG,
442 OLD_SITE_ORIG_SYSTEM_REF,
443 new_osr_exists_flag,
444 BATCH_ID,
445 BATCH_MODE_FLAG,
446 PRIMARY_FLAG
447 ) values
448 (
449 pid, pos, posr,
450 psos, psosr,
451 int_row_id, action_flag,
452 nvl(party_site_id, hz_party_sites_s.nextval),
453 error_flag, party_action_flag,
454 old_psosr, overwrite_osr_flag,
455 P_BATCH_ID, P_BATCH_MODE_FLAG,
456 primary_flag
457 )
458 when (
459 psosr <> old_psosr
460 ) then
461 into hz_imp_osr_change (
462 entity_name,
463 new_osr_exists_flag,
464 OLD_ORIG_SYSTEM_REFERENCE,
465 NEW_ORIG_SYSTEM_REFERENCE,
466 ENTITY_ID,
467 BATCH_ID, PARTY_ID
468 ) values
469 ( 'HZ_PARTY_SITES',
470 overwrite_osr_flag,
471 old_psosr,
472 psosr,
473 nvl(party_site_id, hz_party_sites_s.nextval),
474 P_BATCH_ID, pid
475 )
476 select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
477 ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,
478 ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
479 ps_int_w_psosr.int_row_id,
480 nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,
481 nvl2(ps_int_w_psosr.owning_pty_chg_flag,null,psosr_mosr.owner_table_id) party_site_id,
482 --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,
483 --psosr_mosr.owner_table_id party_site_id, -- Bug6082657
484 DECODE (ps_int_w_psosr.ps_int_pid, NULL, nvl2(nullif(psosr_mosr.party_id, ps_int_w_psosr.pid), 2, null),
485 NVL2(p.party_id, NULL, 3) ) error_flag,
486 ps_int_w_psosr.party_action_flag,
487 ps_int_w_psosr.final_psosr old_psosr,
488 ps_int_w_psosr.psg_new_osr_flag overwrite_osr_flag,
489 -- parttition by id to select one primary per party
490 nvl2(hz_ps.party_site_id, null,
491 decode(row_number() over
492 (partition by ps_int_w_psosr.pid order by
493 ps_int_w_psosr.primary_flag
494 desc nulls last),
495 1, 'Y', null)) primary_flag
496 from hz_orig_sys_references psosr_mosr,
497 hz_party_sites hz_ps,
498 (
499 select /*+ no_merge leading(ps_int) index_asc(ps_int) use_hash(psg) use_nl(mosr) */
500 ps_int.rowid int_row_id,
501 -- note osr_change.entity_id must come before mosr
502 -- in case of osr collision mosr.owner_table_id is not correct
503 nvl(nvl(nvl(ps_int.party_id, psg.party_id), osr_change_tbl.entity_id), mosr.owner_table_id) pid,
504 ps_int.party_id ps_int_pid,
505 mosr.owner_table_id mosr_pid,
506 ps_int.party_orig_system_reference posr,
507 ps_int.party_orig_system pos,
508 ps_int.site_orig_system_reference psosr,
509 ps_int.site_orig_system psos,
513 osr_change_tbl.old_orig_system_reference || substr(
510 -- the psosr to match, changed back to old osr for osr change case
511 decode(osr_change_tbl.old_orig_system_reference, null,
512 ps_int.site_orig_system_reference,
514 ps_int.site_orig_system_reference, instr(
515 ps_int.site_orig_system_reference, '-'))) final_psosr,
516 nvl2(ps_int.party_id,'U',nvl(psg.action_flag, 'U')) party_action_flag,
517 osr_change_tbl.new_osr_exists_flag psg_new_osr_flag,
518 ps_int.primary_flag primary_flag,
519 nvl2(nullif(ps_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
520 from hz_imp_addresses_int ps_int,
521 hz_imp_parties_sg psg,
522 hz_orig_sys_references mosr,
523 hz_imp_osr_change osr_change_tbl
524 where mosr.owner_table_name (+) = 'HZ_PARTIES'
525 and mosr.status (+) = 'A'
526 and ps_int.batch_id = P_BATCH_ID
527 and ps_int.party_orig_system = P_OS
528 and ps_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
529 and ps_int.party_orig_system_reference = psg.party_orig_system_reference (+)
530 and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
531 and psg.party_orig_system (+) = P_OS
532 and psg.batch_id(+) = P_BATCH_ID
533 and ps_int.party_orig_system = psg.party_orig_system (+)
534 and ps_int.batch_id = psg.batch_id(+)
535 and psg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
536 and psg.action_flag(+) = 'I'
537 and ps_int.party_orig_system_reference = mosr.orig_system_reference (+)
538 and ps_int.party_orig_system = mosr.orig_system (+)
539 and ps_int.interface_status is null
540 and ps_int.party_orig_system_reference = osr_change_tbl.new_orig_system_reference (+)
541 and osr_change_tbl.entity_name (+) = 'HZ_PARTIES'
542 and osr_change_tbl.batch_id (+) = P_BATCH_ID
543 and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
544 ) ps_int_w_psosr,
545 hz_parties p
546 where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
547 and ps_int_w_psosr.final_psosr =
548 psosr_mosr.orig_system_reference (+)
549 and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
550 and psosr_mosr.status (+) = 'A'
551 and ps_int_w_psosr.pid = hz_ps.party_id (+)
552 and 'Y' = hz_ps.identifying_address_flag (+)
553 and 'A' = hz_ps.status (+)
554 AND ps_int_w_psosr.ps_int_pid = p.party_id (+)
555 ;
556 end if;
557 commit;
558 end match_addresses;
559
560
561 PROCEDURE match_contact_points(
562 P_BATCH_ID IN NUMBER,
563 P_OS IN VARCHAR2,
564 P_FROM_OSR IN VARCHAR2,
565 P_TO_OSR IN VARCHAR2,
566 P_RERUN IN VARCHAR2,
567 P_BATCH_MODE_FLAG IN VARCHAR2
568 ) IS
569
570 BEGIN
571
572 if (P_RERUN = 'Y') then
573 -- re-run
574 INSERT INTO HZ_IMP_CONTACTPTS_SG
575 (
576 PARTY_ID,
577 PARTY_ORIG_SYSTEM,
578 PARTY_ORIG_SYSTEM_REFERENCE,
579 PARTY_SITE_ID,
580 INT_ROW_ID,
581 ACTION_FLAG,
582 CONTACT_POINT_ID,
583 ERROR_FLAG,
584 PARTY_ACTION_FLAG,
585 OLD_CP_ORIG_SYSTEM_REF,
586 new_osr_exists_flag,
587 BATCH_MODE_FLAG, BATCH_ID,
588 PRIMARY_FLAG,
589 CONTACT_POINT_TYPE
590 )
591 (
592 select /*+ leading(cpi_cosr) use_nl(asg, ps_mosr, cp_mosr, hz_cpt_pri) index(hz_cpt_pri, hz_contact_points_n6) index(asg, hz_imp_addresses_sg_n2) */
593 cpi_cosr.pid party_id, cpi_cosr.pos party_os, cpi_cosr.posr,
594 nvl(nvl(asg.party_site_id, addr_osr_ch_tbl.entity_id), ps_mosr.owner_table_id) site_id,
595 cpi_cosr.int_row_id,
596 nvl2(cp_mosr.owner_table_id, decode(cpi_cosr.owning_pty_chg_flag,'Y','I','U'), 'I') action_flag,
597 nvl(nvl2(cpi_cosr.owning_pty_chg_flag,null,cp_mosr.owner_table_id),hz_contact_points_s.nextval) cp_id,
598 -- nvl(cp_mosr.owner_table_id, hz_contact_points_s.nextval) cp_id, -- Bug6082657
599 nvl2(nullif(ps_mosr.party_id,cpi_cosr.pid), 2, null) error_flag,
600 cpi_cosr.party_action_flag party_action_flag,
601 decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr, cpi_cosr.old_posr ||
602 substr(cpi_cosr.new_cosr, instr(cpi_cosr.new_cosr, '-'))) old_cp_osr,
603 cpi_cosr.new_osr_exists_flag, p_batch_mode_flag, p_batch_id,
604 nvl2(hz_cpt_pri.owner_table_id,null,
605 -- partition by party id and contact point type, select a primary per
606 -- party and per type
607 decode(row_number() over
608 (partition by
609 cpi_cosr.pid, cpi_cosr.contact_point_type
610 order by nvl2(cp_mosr.owner_table_id,
611 null,cpi_cosr.primary_flag
612 ) desc nulls last,
613 cp_mosr.owner_table_id nulls last), 1, 'Y', null) ) primary_flag,
614 cpi_cosr.contact_point_type
615 from hz_imp_addresses_sg asg,
616 hz_imp_osr_change addr_osr_ch_tbl,
617 hz_contact_points hz_cpt_pri,
618 hz_orig_sys_references ps_mosr,
619 hz_orig_sys_references cp_mosr,
620 (
621 select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
622 cp_int.party_orig_system_reference posr,
626 cp_int.cp_orig_system_reference new_cosr,
623 cp_int.rowid int_row_id, cp_int.cp_orig_system cos,
624 cp_int.site_orig_system los, cp_int.party_orig_system pos,
625 party_osr_ch_tbl.old_orig_system_reference old_posr,
627 cp_int.site_orig_system_reference new_losr,
628 nvl(nvl(nvl(cp_int.party_id, psg.party_id), party_osr_ch_tbl.entity_id), mosr.owner_table_id) pid,
629 nvl2(cp_int.party_id,'U',nvl(psg.action_flag, 'U')) party_action_flag,
630 party_osr_ch_tbl.new_osr_exists_flag, cp_int.primary_flag,
631 cp_int.batch_id, cp_int.contact_point_type,
632 nvl2(nullif(cp_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
633 from hz_imp_contactpts_int cp_int,
634 hz_imp_parties_sg psg,
635 hz_orig_sys_references mosr,
636 hz_imp_osr_change party_osr_ch_tbl
637 where cp_int.interface_status = 'C'
638 and cp_int.batch_id = P_BATCH_ID
639 and cp_int.party_orig_system = P_OS
640 and cp_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
641 and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
642 and psg.party_orig_system (+) = P_OS
643 and psg.batch_id(+) = P_BATCH_ID
644 and party_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
645 and party_osr_ch_tbl.batch_id (+) = P_BATCH_ID
646 and party_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cp_int.party_orig_system_reference
647 and party_osr_ch_tbl.entity_id (+) = cp_int.party_id /* Bug 5383200 */
648 and cp_int.party_orig_system_reference = psg.party_orig_system_reference (+)
649 and cp_int.party_orig_system = psg.party_orig_system (+)
650 and cp_int.batch_id = psg.batch_id (+)
651 and psg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
652 and psg.action_flag (+) = 'I'
653 and cp_int.party_orig_system_reference = mosr.orig_system_reference (+)
654 and cp_int.party_orig_system = mosr.orig_system (+)
655 and mosr.owner_table_name (+) = 'HZ_PARTIES'
656 and mosr.status (+) = 'A'
657 ) cpi_cosr
658 where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
659 and cpi_cosr.los = asg.site_orig_system (+)
660 and cpi_cosr.batch_id = asg.batch_id (+)
661 and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'
662 and addr_osr_ch_tbl.batch_id (+) = P_BATCH_ID
663 and addr_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cpi_cosr.new_losr
664 and addr_osr_ch_tbl.party_id (+) = cpi_cosr.pid /* Bug 5383200 */
665 and asg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
666 and asg.action_flag (+) = 'I'
667 and decode(cpi_cosr.old_posr, null, cpi_cosr.new_losr,
668 cpi_cosr.old_posr || substr(cpi_cosr.new_losr, instr(
669 cpi_cosr.new_losr, '-'))) = ps_mosr.orig_system_reference (+)
670 and cpi_cosr.los = ps_mosr.orig_system (+)
671 and decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr,
672 cpi_cosr.old_posr || substr(cpi_cosr.new_cosr, instr(
673 cpi_cosr.new_cosr, '-'))) = cp_mosr.orig_system_reference (+)
674 and cpi_cosr.cos = cp_mosr.orig_system (+)
675 and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
676 and ps_mosr.status (+) = 'A'
677 and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
678 and cp_mosr.status (+) = 'A'
679 and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'
680 and hz_cpt_pri.owner_table_id (+) = cpi_cosr.pid
681 and hz_cpt_pri.contact_point_type (+) = cpi_cosr.contact_point_type
682 and hz_cpt_pri.primary_flag (+) = 'Y'
683 and hz_cpt_pri.status (+) = 'A'
684 );
685 else
686 -- first run
687 INSERT INTO HZ_IMP_CONTACTPTS_SG
688 (
689 PARTY_ID,
690 PARTY_ORIG_SYSTEM,
691 PARTY_ORIG_SYSTEM_REFERENCE,
692 PARTY_SITE_ID,
693 INT_ROW_ID,
694 ACTION_FLAG,
695 CONTACT_POINT_ID,
696 ERROR_FLAG,
697 PARTY_ACTION_FLAG,
698 OLD_CP_ORIG_SYSTEM_REF,
699 new_osr_exists_flag,
700 BATCH_MODE_FLAG, BATCH_ID,
701 PRIMARY_FLAG,
702 CONTACT_POINT_TYPE
703 )
704 (
705 select /*+ leading(cpi_cosr) use_nl(asg, ps_mosr, cp_mosr, hz_cpt_pri) index(hz_cpt_pri, hz_contact_points_n6) index(asg, hz_imp_addresses_sg_n2) */
706 cpi_cosr.pid party_id, cpi_cosr.pos party_os, cpi_cosr.posr,
707 nvl(nvl(asg.party_site_id, addr_osr_ch_tbl.entity_id), ps_mosr.owner_table_id) site_id,
708 cpi_cosr.int_row_id,
709 nvl2(cp_mosr.owner_table_id,decode(cpi_cosr.owning_pty_chg_flag,'Y','I','U'), 'I') action_flag,
710 nvl(nvl2(cpi_cosr.owning_pty_chg_flag,null,cp_mosr.owner_table_id),hz_contact_points_s.nextval) cp_id,
711 -- nvl(cp_mosr.owner_table_id, hz_contact_points_s.nextval) cp_id, --Bug6082657
712 nvl2(nullif(ps_mosr.party_id,cpi_cosr.pid), 2, null) error_flag,
713 cpi_cosr.party_action_flag party_action_flag,
714 decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr, cpi_cosr.old_posr ||
715 substr(cpi_cosr.new_cosr, instr(cpi_cosr.new_cosr, '-'))) old_cp_osr,
716 cpi_cosr.new_osr_exists_flag, p_batch_mode_flag, p_batch_id,
717 nvl2(hz_cpt_pri.owner_table_id,null,
718 -- partition by party id and contact point type, select a primary per
719 -- party and per type
720 decode(row_number() over
721 (partition by
725 ) desc nulls last,
722 cpi_cosr.pid, cpi_cosr.contact_point_type
723 order by nvl2(cp_mosr.owner_table_id,
724 null,cpi_cosr.primary_flag
726 cp_mosr.owner_table_id nulls last), 1, 'Y', null) ) primary_flag,
727 cpi_cosr.contact_point_type
728 from hz_imp_addresses_sg asg,
729 hz_imp_osr_change addr_osr_ch_tbl,
730 hz_contact_points hz_cpt_pri,
731 hz_orig_sys_references ps_mosr,
732 hz_orig_sys_references cp_mosr,
733 (
734 select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
735 cp_int.party_orig_system_reference posr,
736 cp_int.rowid int_row_id, cp_int.cp_orig_system cos,
737 cp_int.site_orig_system los, cp_int.party_orig_system pos,
738 party_osr_ch_tbl.old_orig_system_reference old_posr,
739 cp_int.cp_orig_system_reference new_cosr,
740 cp_int.site_orig_system_reference new_losr,
741 nvl(nvl(nvl(cp_int.party_id, psg.party_id), party_osr_ch_tbl.entity_id), mosr.owner_table_id) pid,
742 nvl2(cp_int.party_id,'U',nvl(psg.action_flag, 'U')) party_action_flag,
743 party_osr_ch_tbl.new_osr_exists_flag, cp_int.primary_flag,
744 cp_int.batch_id, cp_int.contact_point_type,
745 nvl2(nullif(cp_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
746 from hz_imp_contactpts_int cp_int,
747 hz_imp_parties_sg psg,
748 hz_orig_sys_references mosr,
749 hz_imp_osr_change party_osr_ch_tbl
750 where cp_int.interface_status is null
751 and cp_int.batch_id = P_BATCH_ID
752 and cp_int.party_orig_system = P_OS
753 and cp_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
754 and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
755 and psg.party_orig_system (+) = P_OS
756 and psg.batch_id(+) = P_BATCH_ID
757 and party_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
758 and party_osr_ch_tbl.batch_id (+) = P_BATCH_ID
759 and party_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cp_int.party_orig_system_reference
760 and party_osr_ch_tbl.entity_id (+) = cp_int.party_id /* Bug 5383200 */
761 and cp_int.party_orig_system_reference = psg.party_orig_system_reference (+)
762 and cp_int.party_orig_system = psg.party_orig_system (+)
763 and cp_int.batch_id = psg.batch_id (+)
764 and psg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
765 and psg.action_flag (+) = 'I'
766 and cp_int.party_orig_system_reference = mosr.orig_system_reference (+)
767 and cp_int.party_orig_system = mosr.orig_system (+)
768 and mosr.owner_table_name (+) = 'HZ_PARTIES'
769 and mosr.status (+) = 'A'
770 ) cpi_cosr
771 where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
772 and cpi_cosr.los = asg.site_orig_system (+)
773 and cpi_cosr.batch_id = asg.batch_id (+)
774 and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'
775 and addr_osr_ch_tbl.batch_id (+) = P_BATCH_ID
776 and addr_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cpi_cosr.new_losr
777 and addr_osr_ch_tbl.party_id (+) = cpi_cosr.pid /* Bug 5383200 */
778 and asg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
779 and asg.action_flag (+) = 'I'
780 and decode(cpi_cosr.old_posr, null, cpi_cosr.new_losr,
781 cpi_cosr.old_posr || substr(cpi_cosr.new_losr, instr(
782 cpi_cosr.new_losr, '-'))) = ps_mosr.orig_system_reference (+)
783 and cpi_cosr.los = ps_mosr.orig_system (+)
784 and decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr,
785 cpi_cosr.old_posr || substr(cpi_cosr.new_cosr, instr(
786 cpi_cosr.new_cosr, '-'))) = cp_mosr.orig_system_reference (+)
787 and cpi_cosr.cos = cp_mosr.orig_system (+)
788 and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
789 and ps_mosr.status (+) = 'A'
790 and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
791 and cp_mosr.status (+) = 'A'
792 and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'
793 and hz_cpt_pri.owner_table_id (+) = cpi_cosr.pid
794 and hz_cpt_pri.contact_point_type (+) = cpi_cosr.contact_point_type
795 and hz_cpt_pri.primary_flag (+) = 'Y'
796 and hz_cpt_pri.status (+) = 'A'
797 );
798 end if;
799 commit;
800 end match_contact_points;
801
802
803 PROCEDURE match_credit_ratings(
804 P_BATCH_ID IN NUMBER,
805 P_OS IN VARCHAR2,
806 P_FROM_OSR IN VARCHAR2,
807 P_TO_OSR IN VARCHAR2,
808 P_DEF_START_TIME IN DATE,
809 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
810 P_RERUN IN VARCHAR2,
811 P_BATCH_MODE_FLAG IN VARCHAR2
812 ) IS
813
814 BEGIN
815 if(P_RERUN = 'Y') then
816 -- re-run
817 INSERT INTO HZ_IMP_CREDITRTNGS_SG
818 (
819 PARTY_ID,
820 party_orig_system,
821 party_orig_system_reference,
822 INT_ROW_ID,
823 ACTION_FLAG,
824 CREDIT_RATING_ID,
825 BATCH_ID,
826 BATCH_MODE_FLAG
827 )
828 (
829 select /*+ leading(cr_int_w_pid) use_nl(hz_cr) */
830 cr_int_w_pid.pid pid,
834 nvl2(hz_cr.credit_rating_id, 'U', 'I') action_flag,
831 cr_int_w_pid.party_os party_os,
832 cr_int_w_pid.party_osr party_osr,
833 cr_int_w_pid.int_row_id int_row_id,
835 nvl(hz_cr.credit_rating_id, hz_credit_ratings_s.NextVal) cr_id,
836 P_BATCH_ID, P_BATCH_MODE_FLAG
837 from HZ_CREDIT_RATINGS HZ_CR,
838 (select /*+ leading(cr_int) index_asc(cr_int) use_hash(party_sg) use_nl(mosr) */
839 nvl(nvl(nvl(cr_int.party_id, party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
840 cr_int.party_orig_system party_os,
841 cr_int.party_orig_system_reference party_osr,
842 cr_int.rated_as_of_date, cr_int.rating_organization
843 rating_org, cr_int.rowid int_row_id
844 from hz_imp_creditrtngs_int cr_int,
845 hz_imp_parties_sg party_sg,
846 hz_orig_sys_references mosr,
847 hz_imp_osr_change osr_ch_tbl
848 where cr_int.interface_status = 'C'
849 and cr_int.batch_id = P_BATCH_ID
850 and cr_int.party_orig_system = P_OS
851 and cr_int.batch_id = party_sg.batch_id(+)
852 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
853 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
854 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cr_int.party_orig_system_reference
855 and osr_ch_tbl.entity_id (+) = cr_int.party_id /* Bug 5383200 */
856 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
857 and party_sg.action_flag(+)='I'
858 and cr_int.party_orig_system_reference between p_from_osr and p_to_osr
859 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
860 and party_sg.party_orig_system (+) = P_OS
861 and party_sg.batch_id(+) = P_BATCH_ID
862 and cr_int.party_orig_system_reference = mosr.orig_system_reference (+)
863 and cr_int.party_orig_system = mosr.orig_system (+)
864 and mosr.owner_table_name (+) = 'HZ_PARTIES'
865 and mosr.status (+) = 'A'
866 and cr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
867 and cr_int.party_orig_system = party_sg.party_orig_system (+)
868 ) cr_int_w_pid
869 where cr_int_w_pid.pid = hz_cr.party_id (+)
870 and cr_int_w_pid.rating_org = hz_cr.rating_organization (+)
871 and trunc(nvl(cr_int_w_pid.rated_as_of_date, P_DEF_START_TIME)) =
872 trunc(hz_cr.rated_as_of_date (+))
873 and HZ_CR.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC);
874 else
875 -- frist run
876 INSERT INTO HZ_IMP_CREDITRTNGS_SG
877 (
878 PARTY_ID,
879 party_orig_system,
880 party_orig_system_reference,
881 INT_ROW_ID,
882 ACTION_FLAG,
883 CREDIT_RATING_ID,
884 BATCH_ID,
885 BATCH_MODE_FLAG
886 )
887 (
888 select /*+ leading(cr_int_w_pid) use_nl(hz_cr) */
889 cr_int_w_pid.pid pid,
890 cr_int_w_pid.party_os party_os,
891 cr_int_w_pid.party_osr party_osr,
892 cr_int_w_pid.int_row_id int_row_id,
893 nvl2(hz_cr.credit_rating_id, 'U', 'I') action_flag,
894 nvl(hz_cr.credit_rating_id, hz_credit_ratings_s.NextVal) cr_id,
895 P_BATCH_ID, P_BATCH_MODE_FLAG
896 from HZ_CREDIT_RATINGS HZ_CR,
897 (select /*+ leading(cr_int) index_asc(cr_int) use_hash(party_sg) use_nl(mosr) */
898 nvl(nvl(nvl(cr_int.party_id, party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
899 cr_int.party_orig_system party_os,
900 cr_int.party_orig_system_reference party_osr,
901 cr_int.rated_as_of_date, cr_int.rating_organization
902 rating_org, cr_int.rowid int_row_id
903 from hz_imp_creditrtngs_int cr_int,
904 hz_imp_parties_sg party_sg,
905 hz_orig_sys_references mosr,
906 hz_imp_osr_change osr_ch_tbl
907 where cr_int.interface_status is null
908 and cr_int.batch_id = P_BATCH_ID
909 and cr_int.party_orig_system = P_OS
910 and cr_int.batch_id = party_sg.batch_id(+)
911 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
912 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
913 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cr_int.party_orig_system_reference
914 and osr_ch_tbl.entity_id (+) = cr_int.party_id /* Bug 5383200 */
915 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
916 and party_sg.action_flag(+)='I'
917 and cr_int.party_orig_system_reference between p_from_osr and p_to_osr
918 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
919 and party_sg.party_orig_system (+) = P_OS
920 and party_sg.batch_id(+) = P_BATCH_ID
921 and cr_int.party_orig_system_reference = mosr.orig_system_reference (+)
922 and cr_int.party_orig_system = mosr.orig_system (+)
923 and mosr.owner_table_name (+) = 'HZ_PARTIES'
924 and mosr.status (+) = 'A'
925 and cr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
926 and cr_int.party_orig_system = party_sg.party_orig_system (+)
927 ) cr_int_w_pid
928 where cr_int_w_pid.pid = hz_cr.party_id (+)
929 and cr_int_w_pid.rating_org = hz_cr.rating_organization (+)
930 and trunc(nvl(cr_int_w_pid.rated_as_of_date, P_DEF_START_TIME)) =
934 commit;
931 trunc(hz_cr.rated_as_of_date (+))
932 and HZ_CR.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC);
933 end if;
935 end match_credit_ratings;
936
937
938 PROCEDURE match_code_assignments(
939 P_BATCH_ID IN NUMBER,
940 P_OS IN VARCHAR2,
941 P_FROM_OSR IN VARCHAR2,
942 P_TO_OSR IN VARCHAR2,
943 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
944 P_RERUN IN VARCHAR2,
945 P_BATCH_MODE_FLAG IN VARCHAR2
946 ) IS
947
948 BEGIN
949 if (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN = 'Y') then
950 -- re-run/DNB
951 INSERT INTO HZ_IMP_CLASSIFICS_SG
952 ( CLASS_CATEGORY,
953 CLASS_CODE,
954 START_DATE_ACTIVE,
955 END_DATE_ACTIVE,
956 PARTY_ID,
957 party_orig_system,
958 party_orig_system_reference,
959 INT_ROW_ID,
960 ACTION_FLAG,
961 CODE_ASSIGNMENT_ID,
962 BATCH_MODE_FLAG, BATCH_ID,
963 PRIMARY_FLAG
964 )
965 (
966 select /*+ leading(ca_int_w_pid) use_nl(hz_ca) */
967 ca_int_w_pid.class_category, ca_int_w_pid.class_code,
968 ca_int_w_pid.start_date_active, ca_int_w_pid.end_date_active,
969 ca_int_w_pid.pid, ca_int_w_pid.party_os, ca_int_w_pid.party_osr,
970 ca_int_w_pid.int_row_id, nvl2(hz_ca.code_assignment_id, 'U', 'I')
971 action_flag, nvl(hz_ca.code_assignment_id,
972 hz_code_assignments_s.nextval) ca_id, p_batch_mode_flag, p_batch_id,
973 -- set as not primary if already a primary,
974 -- pick on primary per party per class category
975 -- if any of the code assignment set as primary in interface
976 nvl2(hz_ca3.code_assignment_id, null,
977 decode(row_number() over
978 (partition by pid, ca_int_w_pid.class_category
979 order by ca_int_w_pid.primary_flag
980 desc nulls last),
981 1, ca_int_w_pid.primary_flag, null)) primary_flag
982 from hz_code_assignments hz_ca,
983 hz_code_assignments hz_ca3,
984 (
985 select /*+ leading(ca_int) index_asc(ca_int) use_hash(party_sg) use_nl(mosr) */
986 nvl(nvl(nvl(ca_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
987 ca_int.party_orig_system party_os,
988 ca_int.party_orig_system_reference party_osr,
989 ca_int.class_code,
990 ca_int.start_date_active start_date_active,
991 ca_int.end_date_active end_date_active,
992 ca_int.rowid int_row_id,
993 case when ca_int.class_category
994 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
995 then 'SIC' else ca_int.class_category end class_category,
996 ca_int.primary_flag
997 from hz_imp_classifics_int ca_int,
998 hz_imp_parties_sg party_sg,
999 hz_orig_sys_references mosr,
1000 hz_imp_osr_change osr_ch_tbl
1001 where ca_int.interface_status = 'C'
1002 and ca_int.batch_id = p_batch_id
1003 and ca_int.party_orig_system = p_os
1004 and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1005 and ca_int.batch_id = party_sg.batch_id (+)
1006 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1007 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1008 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1009 and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1010 and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1011 and party_sg.action_flag (+) = 'I'
1012 and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1013 and ca_int.party_orig_system = party_sg.party_orig_system (+)
1014 and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1015 and ca_int.party_orig_system = mosr.orig_system (+)
1016 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1017 and mosr.status (+) = 'A'
1018 ) ca_int_w_pid
1019 where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1020 --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1021 and (case when hz_ca.class_category (+)
1022 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1023 then 'SIC' else hz_ca.class_category (+) end ) =
1024 ca_int_w_pid.class_category
1025 and ca_int_w_pid.class_code = hz_ca.class_code (+)
1026
1027 --wawong: ignore start data for DNB data
1028 --and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1029 and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1030
1031 and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1032 /* Bug 4979902 */
1033 --and hz_ca.content_source_type (+) = p_actual_content_src
1034 and hz_ca.actual_content_source (+) = p_actual_content_src
1035 and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1036 and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1037 and nvl(hz_ca3.end_date_active (+),c_end_date ) = c_end_date
1038 and (case when hz_ca3.class_category (+)
1039 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1043 and hz_ca3.status (+) = 'A'
1040 then 'SIC' else hz_ca3.class_category (+) end ) =
1041 ca_int_w_pid.class_category
1042 and hz_ca3.primary_flag (+) = 'Y'
1044 );
1045 elsif (P_ACTUAL_CONTENT_SRC = 'USER_ENTERED' and P_RERUN = 'Y') then
1046 -- re-run/NON-DNB
1047 INSERT INTO HZ_IMP_CLASSIFICS_SG
1048 ( CLASS_CATEGORY,
1049 CLASS_CODE,
1050 START_DATE_ACTIVE,
1051 END_DATE_ACTIVE,
1052 PARTY_ID,
1053 party_orig_system,
1054 party_orig_system_reference,
1055 INT_ROW_ID,
1056 ACTION_FLAG,
1057 CODE_ASSIGNMENT_ID,
1058 BATCH_MODE_FLAG, BATCH_ID,
1059 PRIMARY_FLAG
1060 )
1061 (
1062 select /*+ leading(ca_int_w_pid) use_nl(hz_ca) */
1063 ca_int_w_pid.class_category, ca_int_w_pid.class_code,
1064 ca_int_w_pid.start_date_active, ca_int_w_pid.end_date_active,
1065 ca_int_w_pid.pid, ca_int_w_pid.party_os, ca_int_w_pid.party_osr,
1066 ca_int_w_pid.int_row_id, nvl2(hz_ca.code_assignment_id, 'U', 'I')
1067 action_flag, nvl(hz_ca.code_assignment_id,
1068 hz_code_assignments_s.nextval) ca_id, p_batch_mode_flag, p_batch_id,
1069 -- set as not primary if already a primary,
1070 -- pick on primary per party per class category
1071 -- if any of the code assignment set as primary in interface
1072 nvl2(hz_ca3.code_assignment_id, null,
1073 decode(row_number() over
1074 (partition by pid, ca_int_w_pid.class_category
1075 order by ca_int_w_pid.primary_flag
1076 desc nulls last),
1077 1, ca_int_w_pid.primary_flag, null)) primary_flag
1078 from hz_code_assignments hz_ca,
1079 hz_code_assignments hz_ca3,
1080 (
1081 select /*+ leading(ca_int) index_asc(ca_int) use_hash(party_sg) use_nl(mosr) */
1082 nvl(nvl(nvl(ca_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1083 ca_int.party_orig_system party_os,
1084 ca_int.party_orig_system_reference party_osr,
1085 ca_int.class_code,
1086 ca_int.start_date_active start_date_active,
1087 ca_int.end_date_active end_date_active,
1088 ca_int.rowid int_row_id,
1089 case when ca_int.class_category
1090 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1091 then 'SIC' else ca_int.class_category end class_category,
1092 ca_int.primary_flag
1093 from hz_imp_classifics_int ca_int,
1094 hz_imp_parties_sg party_sg,
1095 hz_orig_sys_references mosr,
1096 hz_imp_osr_change osr_ch_tbl
1097 where ca_int.interface_status = 'C'
1098 and ca_int.batch_id = p_batch_id
1099 and ca_int.party_orig_system = p_os
1100 and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1101 and ca_int.batch_id = party_sg.batch_id (+)
1102 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1103 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1104 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1105 and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1106 and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1107 and party_sg.action_flag (+) = 'I'
1108 and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1109 and ca_int.party_orig_system = party_sg.party_orig_system (+)
1110 and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1111 and ca_int.party_orig_system = mosr.orig_system (+)
1112 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1113 and mosr.status (+) = 'A'
1114 ) ca_int_w_pid
1115 where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1116 --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1117 and (case when hz_ca.class_category (+)
1118 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1119 then 'SIC' else hz_ca.class_category (+) end ) =
1120 ca_int_w_pid.class_category
1121 and ca_int_w_pid.class_code = hz_ca.class_code (+)
1122
1123 --wawong: ignore start data for DNB data
1124 and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1125 --and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1126
1127 and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1128 /* Bug 4979902 */
1129 --and hz_ca.content_source_type (+) = p_actual_content_src
1130 and hz_ca.actual_content_source (+) = p_actual_content_src
1131 and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1132 and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1133 and (case when hz_ca3.class_category (+)
1134 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1135 then 'SIC' else hz_ca3.class_category (+) end ) =
1136 ca_int_w_pid.class_category
1137 and hz_ca3.primary_flag (+) = 'Y'
1138 and hz_ca3.status (+) = 'A'
1139 );
1140 elsif (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN <> 'Y') then
1141 -- first run/DNB
1142 INSERT INTO HZ_IMP_CLASSIFICS_SG
1143 ( CLASS_CATEGORY,
1144 CLASS_CODE,
1145 START_DATE_ACTIVE,
1146 END_DATE_ACTIVE,
1147 PARTY_ID,
1148 party_orig_system,
1152 CODE_ASSIGNMENT_ID,
1149 party_orig_system_reference,
1150 INT_ROW_ID,
1151 ACTION_FLAG,
1153 BATCH_MODE_FLAG, BATCH_ID,
1154 PRIMARY_FLAG
1155 )
1156 (
1157 select /*+ leading(ca_int_w_pid) use_nl(hz_ca) */
1158 ca_int_w_pid.class_category, ca_int_w_pid.class_code,
1159 ca_int_w_pid.start_date_active, ca_int_w_pid.end_date_active,
1160 ca_int_w_pid.pid, ca_int_w_pid.party_os, ca_int_w_pid.party_osr,
1161 ca_int_w_pid.int_row_id, nvl2(hz_ca.code_assignment_id, 'U', 'I')
1162 action_flag, nvl(hz_ca.code_assignment_id,
1163 hz_code_assignments_s.nextval) ca_id, p_batch_mode_flag, p_batch_id,
1164 -- set as not primary if already a primary,
1165 -- pick on primary per party per class category
1166 -- if any of the code assignment set as primary in interface
1167 nvl2(hz_ca3.code_assignment_id, null,
1168 decode(row_number() over
1169 (partition by pid, ca_int_w_pid.class_category
1170 order by ca_int_w_pid.primary_flag
1171 desc nulls last),
1172 1, ca_int_w_pid.primary_flag, null)) primary_flag
1173 from hz_code_assignments hz_ca,
1174 hz_code_assignments hz_ca3,
1175 (
1176 select /*+ leading(ca_int) index_asc(ca_int) use_hash(party_sg) use_nl(mosr) */
1177 nvl(nvl(nvl(ca_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1178 ca_int.party_orig_system party_os,
1179 ca_int.party_orig_system_reference party_osr,
1180 ca_int.class_code,
1181 ca_int.start_date_active start_date_active,
1182 ca_int.end_date_active end_date_active,
1183 ca_int.rowid int_row_id,
1184 case when ca_int.class_category
1185 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1186 then 'SIC' else ca_int.class_category end class_category,
1187 ca_int.primary_flag
1188 from hz_imp_classifics_int ca_int,
1189 hz_imp_parties_sg party_sg,
1190 hz_orig_sys_references mosr,
1191 hz_imp_osr_change osr_ch_tbl
1192 where ca_int.interface_status is null
1193 and ca_int.batch_id = p_batch_id
1194 and ca_int.party_orig_system = p_os
1195 and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1196 and ca_int.batch_id = party_sg.batch_id (+)
1197 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1198 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1199 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1200 and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1201 and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1202 and party_sg.action_flag (+) = 'I'
1203 and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1204 and ca_int.party_orig_system = party_sg.party_orig_system (+)
1205 and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1206 and ca_int.party_orig_system = mosr.orig_system (+)
1207 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1208 and mosr.status (+) = 'A'
1209 ) ca_int_w_pid
1210 where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1211 --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1212 and (case when hz_ca.class_category (+)
1213 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1214 then 'SIC' else hz_ca.class_category (+) end ) =
1215 ca_int_w_pid.class_category
1216 and ca_int_w_pid.class_code = hz_ca.class_code (+)
1217
1218 --wawong: ignore start data for DNB data
1219 --and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1220 and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1221
1222 and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1223 /* bug 4079902 */
1224 --and hz_ca.content_source_type (+) = p_actual_content_src
1225 and hz_ca.actual_content_source (+) = p_actual_content_src
1226 and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1227 and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1228 and nvl(hz_ca3.end_date_active (+),c_end_date ) = c_end_date
1229 and (case when hz_ca3.class_category (+)
1230 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1231 then 'SIC' else hz_ca3.class_category (+) end ) =
1232 ca_int_w_pid.class_category
1233 and hz_ca3.primary_flag (+) = 'Y'
1234 and hz_ca3.status (+) = 'A'
1235 );
1236 else
1237 -- first run/non-DNB
1238 INSERT INTO HZ_IMP_CLASSIFICS_SG
1239 ( CLASS_CATEGORY,
1240 CLASS_CODE,
1241 START_DATE_ACTIVE,
1242 END_DATE_ACTIVE,
1243 PARTY_ID,
1244 party_orig_system,
1245 party_orig_system_reference,
1246 INT_ROW_ID,
1247 ACTION_FLAG,
1248 CODE_ASSIGNMENT_ID,
1249 BATCH_MODE_FLAG, BATCH_ID,
1250 PRIMARY_FLAG
1251 )
1252 (
1253 select /*+ leading(ca_int_w_pid) use_nl(hz_ca) */
1254 ca_int_w_pid.class_category, ca_int_w_pid.class_code,
1255 ca_int_w_pid.start_date_active, ca_int_w_pid.end_date_active,
1256 ca_int_w_pid.pid, ca_int_w_pid.party_os, ca_int_w_pid.party_osr,
1257 ca_int_w_pid.int_row_id, nvl2(hz_ca.code_assignment_id, 'U', 'I')
1261 -- pick on primary per party per class category
1258 action_flag, nvl(hz_ca.code_assignment_id,
1259 hz_code_assignments_s.nextval) ca_id, p_batch_mode_flag, p_batch_id,
1260 -- set as not primary if already a primary,
1262 -- if any of the code assignment set as primary in interface
1263 nvl2(hz_ca3.code_assignment_id, null,
1264 decode(row_number() over
1265 (partition by pid, ca_int_w_pid.class_category
1266 order by ca_int_w_pid.primary_flag
1267 desc nulls last),
1268 1, ca_int_w_pid.primary_flag, null)) primary_flag
1269 from hz_code_assignments hz_ca,
1270 hz_code_assignments hz_ca3,
1271 (
1272 select /*+ leading(ca_int) index_asc(ca_int) use_hash(party_sg) use_nl(mosr) */
1273 nvl(nvl(nvl(ca_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1274 ca_int.party_orig_system party_os,
1275 ca_int.party_orig_system_reference party_osr,
1276 ca_int.class_code,
1277 ca_int.start_date_active start_date_active,
1278 ca_int.end_date_active end_date_active,
1279 ca_int.rowid int_row_id,
1280 case when ca_int.class_category
1281 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1282 then 'SIC' else ca_int.class_category end class_category,
1283 ca_int.primary_flag
1284 from hz_imp_classifics_int ca_int,
1285 hz_imp_parties_sg party_sg,
1286 hz_orig_sys_references mosr,
1287 hz_imp_osr_change osr_ch_tbl
1288 where ca_int.interface_status is null
1289 and ca_int.batch_id = p_batch_id
1290 and ca_int.party_orig_system = p_os
1291 and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1292 and ca_int.batch_id = party_sg.batch_id (+)
1293 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1294 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1295 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1296 and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1297 and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1298 and party_sg.action_flag (+) = 'I'
1299 and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1300 and ca_int.party_orig_system = party_sg.party_orig_system (+)
1301 and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1302 and ca_int.party_orig_system = mosr.orig_system (+)
1303 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1304 and mosr.status (+) = 'A'
1305 ) ca_int_w_pid
1306 where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1307 --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1308 and (case when hz_ca.class_category (+)
1309 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1310 then 'SIC' else hz_ca.class_category (+) end ) =
1311 ca_int_w_pid.class_category
1312 and ca_int_w_pid.class_code = hz_ca.class_code (+)
1313
1314 --wawong: ignore start data for DNB data
1315 and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1316 --and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1317
1318 and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1319 /* Bug 4979902 */
1320 --and hz_ca.content_source_type (+) = p_actual_content_src
1321 and hz_ca.actual_content_source (+) = p_actual_content_src
1322 and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1323 and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1324 and (case when hz_ca3.class_category (+)
1325 in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1326 then 'SIC' else hz_ca3.class_category (+) end ) =
1327 ca_int_w_pid.class_category
1328 and hz_ca3.primary_flag (+) = 'Y'
1329 and hz_ca3.status (+) = 'A'
1330 );
1331 end if;
1332 commit;
1333 end match_code_assignments;
1334
1335
1336 PROCEDURE match_financial_reports(
1337 P_BATCH_ID IN NUMBER,
1338 P_OS IN VARCHAR2,
1339 P_FROM_OSR IN VARCHAR2,
1340 P_TO_OSR IN VARCHAR2,
1341 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
1342 P_RERUN IN VARCHAR2,
1343 P_BATCH_MODE_FLAG IN VARCHAR2
1344 ) IS
1345
1346 BEGIN
1347 if (P_RERUN = 'Y') then
1348 INSERT INTO HZ_IMP_FINREPORTS_SG
1349 (
1350 PARTY_ID,
1351 PARTY_ORIG_SYSTEM,
1352 PARTY_ORIG_SYSTEM_REFERENCE,
1353 INT_ROW_ID,
1354 ACTION_FLAG,
1355 FINANCIAL_REPORT_ID,
1356 DOCUMENT_REFERENCE,
1357 TYPE_OF_FINANCIAL_REPORT,
1358 DATE_REPORT_ISSUED,
1359 REPORT_START_DATE,
1360 REPORT_END_DATE,
1361 ISSUED_PERIOD,
1362 BATCH_ID,
1363 BATCH_MODE_FLAG
1364 )
1365 select pid, party_os, party_osr, int_row_id, nvl2(FINANCIAL_REPORT_ID,
1366 'U', 'I'), nvl(FINANCIAL_REPORT_ID, hz_financial_reports_s.NextVal),
1367 DOCUMENT_REFERENCE, TYPE_OF_FINANCIAL_REPORT, DATE_REPORT_ISSUED,
1368 REPORT_START_DATE, REPORT_END_DATE, ISSUED_PERIOD, P_BATCH_ID,
1372 nvl2(ranking, FINANCIAL_REPORT_ID, null) FINANCIAL_REPORT_ID,
1369 P_BATCH_MODE_FLAG
1370 from (
1371 select pid, party_os, party_osr, int_row_id,
1373 DOCUMENT_REFERENCE, TYPE_OF_FINANCIAL_REPORT, DATE_REPORT_ISSUED,
1374 REPORT_START_DATE, REPORT_END_DATE, ISSUED_PERIOD, rank() over
1375 (partition by int_row_id order by ranking nulls last,
1376 financial_report_id) new_rank
1377 from (
1378 select /*+ leading(fr_int_w_pid) use_nl(hz_fr1) */
1379 fr_int_w_pid.pid,
1380 fr_int_w_pid.party_os,
1381 fr_int_w_pid.party_osr,
1382 fr_int_w_pid.int_row_id,
1383 hz_fr1.FINANCIAL_REPORT_ID,
1384 fr_int_w_pid.DOCUMENT_REFERENCE,
1385 fr_int_w_pid.TYPE_OF_FINANCIAL_REPORT,
1386 fr_int_w_pid.DATE_REPORT_ISSUED,
1387 fr_int_w_pid.REPORT_START_DATE,
1388 fr_int_w_pid.REPORT_END_DATE,
1389 fr_int_w_pid.ISSUED_PERIOD,
1390 case /*when trunc(fr_int_w_pid.DATE_REPORT_ISSUED) =
1391 trunc(hz_fr1.DATE_REPORT_ISSUED) then 1*/
1392 when fr_int_w_pid.ISSUED_PERIOD = hz_fr1.ISSUED_PERIOD then 1
1393 when trunc(fr_int_w_pid.REPORT_START_DATE) =
1394 trunc(hz_fr1.REPORT_START_DATE)
1395 and trunc(fr_int_w_pid.REPORT_END_DATE) =
1396 trunc(hz_fr1.REPORT_END_DATE) then 2 end ranking
1397 from HZ_FINANCIAL_REPORTS hz_fr1,
1398 (select /*+ no_merge leading(fr_int) index_asc(fr_int)
1399 use_hash(party_sg) use_nl(mosr) */
1400 nvl(nvl(nvl(fr_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1401 fr_int.party_orig_system party_os,
1402 fr_int.party_orig_system_reference party_osr,
1403 TYPE_OF_FINANCIAL_REPORT,
1404 DOCUMENT_REFERENCE,
1405 DATE_REPORT_ISSUED,
1406 ISSUED_PERIOD,
1407 REPORT_END_DATE,
1408 REPORT_START_DATE,
1409 fr_int.rowid int_row_id
1410 from hz_imp_finreports_int fr_int,
1411 hz_imp_parties_sg party_sg,
1412 hz_orig_sys_references mosr,
1413 hz_imp_osr_change osr_ch_tbl
1414 where fr_int.interface_status = 'C'
1415 and fr_int.batch_id = P_BATCH_ID
1416 and fr_int.party_orig_system = P_OS
1417 and fr_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1418 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1419 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1420 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fr_int.party_orig_system_reference
1421 and osr_ch_tbl.entity_id (+) = fr_int.party_id /* Bug 5383200 */
1422 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1423 and party_sg.party_orig_system (+) = P_OS
1424 and party_sg.batch_id(+) = P_BATCH_ID
1425 and fr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1426 and fr_int.party_orig_system = party_sg.party_orig_system (+)
1427 and fr_int.batch_id = party_sg.batch_id(+)
1428 and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1429 and party_sg.action_flag(+) = 'I'
1430 and fr_int.party_orig_system_reference = mosr.orig_system_reference (+)
1431 and fr_int.party_orig_system = mosr.orig_system (+)
1432 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1433 and mosr.status (+) = 'A'
1434 ) fr_int_w_pid
1435 where fr_int_w_pid.pid = hz_fr1.PARTY_ID (+)
1436 and nvl(trunc(fr_int_w_pid.DATE_REPORT_ISSUED), c_end_date) =
1437 nvl(trunc(hz_fr1.DATE_REPORT_ISSUED (+) ) , c_end_date)
1438 and fr_int_w_pid.TYPE_OF_FINANCIAL_REPORT = hz_fr1.TYPE_OF_FINANCIAL_REPORT (+)
1439 and fr_int_w_pid.DOCUMENT_REFERENCE = hz_fr1.DOCUMENT_REFERENCE (+)
1440 and hz_fr1.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC))
1441 where new_rank = 1;
1442 else
1443 -- first run
1444 INSERT INTO HZ_IMP_FINREPORTS_SG
1445 (
1446 PARTY_ID,
1447 PARTY_ORIG_SYSTEM,
1448 PARTY_ORIG_SYSTEM_REFERENCE,
1449 INT_ROW_ID,
1450 ACTION_FLAG,
1451 FINANCIAL_REPORT_ID,
1452 DOCUMENT_REFERENCE,
1453 TYPE_OF_FINANCIAL_REPORT,
1454 DATE_REPORT_ISSUED,
1455 REPORT_START_DATE,
1456 REPORT_END_DATE,
1457 ISSUED_PERIOD,
1458 BATCH_ID,
1459 BATCH_MODE_FLAG
1460 )
1461 select pid, party_os, party_osr, int_row_id, nvl2(FINANCIAL_REPORT_ID,
1462 'U', 'I'), nvl(FINANCIAL_REPORT_ID, hz_financial_reports_s.NextVal),
1463 DOCUMENT_REFERENCE, TYPE_OF_FINANCIAL_REPORT, DATE_REPORT_ISSUED,
1464 REPORT_START_DATE, REPORT_END_DATE, ISSUED_PERIOD, P_BATCH_ID,
1465 P_BATCH_MODE_FLAG
1466 from (
1467 select pid, party_os, party_osr, int_row_id,
1468 nvl2(ranking, FINANCIAL_REPORT_ID, null) FINANCIAL_REPORT_ID,
1469 DOCUMENT_REFERENCE, TYPE_OF_FINANCIAL_REPORT, DATE_REPORT_ISSUED,
1470 REPORT_START_DATE, REPORT_END_DATE, ISSUED_PERIOD, rank() over
1471 (partition by int_row_id order by ranking nulls last,
1472 financial_report_id) new_rank
1473 from (
1474 select /*+ leading(fr_int_w_pid) use_nl(hz_fr1) */
1475 fr_int_w_pid.pid,
1476 fr_int_w_pid.party_os,
1477 fr_int_w_pid.party_osr,
1478 fr_int_w_pid.int_row_id,
1479 hz_fr1.FINANCIAL_REPORT_ID,
1480 fr_int_w_pid.DOCUMENT_REFERENCE,
1481 fr_int_w_pid.TYPE_OF_FINANCIAL_REPORT,
1482 fr_int_w_pid.DATE_REPORT_ISSUED,
1483 fr_int_w_pid.REPORT_START_DATE,
1487 trunc(hz_fr1.DATE_REPORT_ISSUED) then 1*/
1484 fr_int_w_pid.REPORT_END_DATE,
1485 fr_int_w_pid.ISSUED_PERIOD,
1486 case /*when trunc(fr_int_w_pid.DATE_REPORT_ISSUED) =
1488 when fr_int_w_pid.ISSUED_PERIOD = hz_fr1.ISSUED_PERIOD then 1
1489 when trunc(fr_int_w_pid.REPORT_START_DATE) =
1490 trunc(hz_fr1.REPORT_START_DATE)
1491 and trunc(fr_int_w_pid.REPORT_END_DATE) =
1492 trunc(hz_fr1.REPORT_END_DATE) then 2 end ranking
1493 from HZ_FINANCIAL_REPORTS hz_fr1,
1494 (select /*+ no_merge leading(fr_int) index_asc(fr_int)
1495 use_hash(party_sg) use_nl(mosr) */
1496 nvl(nvl(nvl(fr_int.party_id,party_sg.party_id),osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1497 fr_int.party_orig_system party_os,
1498 fr_int.party_orig_system_reference party_osr,
1499 TYPE_OF_FINANCIAL_REPORT,
1500 DOCUMENT_REFERENCE,
1501 DATE_REPORT_ISSUED,
1502 ISSUED_PERIOD,
1503 REPORT_END_DATE,
1504 REPORT_START_DATE,
1505 fr_int.rowid int_row_id
1506 from hz_imp_finreports_int fr_int,
1507 hz_imp_parties_sg party_sg,
1508 hz_orig_sys_references mosr,
1509 hz_imp_osr_change osr_ch_tbl
1510 where fr_int.interface_status is null
1511 and fr_int.batch_id = P_BATCH_ID
1512 and fr_int.party_orig_system = P_OS
1513 and fr_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1514 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1515 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1516 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fr_int.party_orig_system_reference
1517 and osr_ch_tbl.entity_id (+) = fr_int.party_id /* Bug 5383200 */
1518 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1519 and party_sg.party_orig_system (+) = P_OS
1520 and party_sg.batch_id(+) = P_BATCH_ID
1521 and fr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1522 and fr_int.party_orig_system = party_sg.party_orig_system (+)
1523 and fr_int.batch_id = party_sg.batch_id(+)
1524 and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1525 and party_sg.action_flag(+) = 'I'
1526 and fr_int.party_orig_system_reference = mosr.orig_system_reference (+)
1527 and fr_int.party_orig_system = mosr.orig_system (+)
1528 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1529 and mosr.status (+) = 'A'
1530 ) fr_int_w_pid
1531 where fr_int_w_pid.pid = hz_fr1.PARTY_ID (+)
1532 and nvl(trunc(fr_int_w_pid.DATE_REPORT_ISSUED), c_end_date) =
1533 nvl(trunc(hz_fr1.DATE_REPORT_ISSUED (+) ) , c_end_date)
1534 and fr_int_w_pid.TYPE_OF_FINANCIAL_REPORT = hz_fr1.TYPE_OF_FINANCIAL_REPORT (+)
1535 and fr_int_w_pid.DOCUMENT_REFERENCE = hz_fr1.DOCUMENT_REFERENCE (+)
1536 and hz_fr1.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC))
1537 where new_rank = 1;
1538 end if;
1539
1540 commit;
1541 end match_financial_reports;
1542
1543
1544 PROCEDURE match_financial_numbers(
1545 P_BATCH_ID IN NUMBER,
1546 P_OS IN VARCHAR2,
1547 P_FROM_OSR IN VARCHAR2,
1548 P_TO_OSR IN VARCHAR2,
1549 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
1550 P_RERUN IN VARCHAR2,
1551 P_BATCH_MODE_FLAG IN VARCHAR2
1552 ) IS
1553
1554 BEGIN
1555 if(P_RERUN = 'Y') then
1556 -- re-run
1557 INSERT INTO HZ_IMP_FINNUMBERS_SG
1558 (
1559 PARTY_ID,
1560 PARTY_ORIG_SYSTEM,
1561 PARTY_ORIG_SYSTEM_REFERENCE,
1562 INT_ROW_ID,
1563 ACTION_FLAG,
1564 FINANCIAL_NUMBER_ID,
1565 FINANCIAL_REPORT_ID,
1566 TYPE_OF_FINANCIAL_REPORT,
1567 DOCUMENT_REFERENCE,
1568 DATE_REPORT_ISSUED,
1569 ISSUED_PERIOD,
1570 REPORT_START_DATE,
1571 REPORT_END_DATE,
1572 BATCH_MODE_FLAG, BATCH_ID
1573 )
1574 (
1575 -- filter out all less ranking
1576 select party_id,
1577 party_os,
1578 party_osr,
1579 int_row_id,
1580 -- if ranking is null, there is no match in FR and FN
1581 nvl2(ranking, action_flag, 'I'),
1582 nvl2(ranking, nvl(hz_fn2.financial_number_id, hz_financial_numbers_s.NextVal), hz_financial_numbers_s.NextVal) fn_id,
1583 nvl2(ranking, fr_id, null),
1584 type_of_financial_report,
1585 document_reference,
1586 date_report_issued,
1587 issued_period,
1588 report_start_date,
1589 report_end_date,
1590 P_BATCH_MODE_FLAG, P_BATCH_ID
1591 from (
1592 -- match all fn ids
1593 select /*+ leading(fi_frid) use_nl(hz_fn) */
1594 pid party_id,
1595 fi_frid.party_os,
1596 fi_frid.party_osr,
1597 fi_frid.int_row_id,
1598 nvl2(hz_fn.financial_number_id, 'U', 'I') action_flag,
1599 hz_fn.financial_number_id,
1600 fr_id,
1601 fi_frid.type_of_financial_report,
1602 fi_frid.document_reference,
1603 fi_frid.date_report_issued,
1604 fi_frid.issued_period,
1605 fi_frid.report_start_date,
1606 fi_frid.report_end_date,
1607 -- select the highest ranking
1611 from hz_financial_numbers hz_fn,
1608 rank() over (partition by fi_frid.int_row_id
1609 order by fi_frid.ranking nulls last, fr_rowid) new_rank,
1610 fi_frid.ranking ranking
1612 (
1613 -- match all fr ids without the date columns
1614 select /*+ no_merge leading(fi_pid) use_nl(frsg, fr) */
1615 fi_pid.pid, fi_pid.party_os, fi_pid.party_osr,
1616 nvl(frsg.financial_report_id, fr.financial_report_id) fr_id,
1617 fi_pid.type_of_financial_report, fi_pid.document_reference,
1618 fi_pid.date_report_issued, fi_pid.issued_period,
1619 fi_pid.report_start_date, fi_pid.report_end_date,
1620 fi_pid.financial_number_name, fi_pid.int_row_id,
1621 -- rank the matched FR which matches other than date cols
1622 case when fi_pid.ISSUED_PERIOD = frsg.ISSUED_PERIOD then 1
1623 when trunc(fi_pid.REPORT_START_DATE) = trunc(frsg.REPORT_START_DATE)
1624 and trunc(fi_pid.REPORT_END_DATE) = trunc(frsg.REPORT_END_DATE) then 2
1625 when fi_pid.ISSUED_PERIOD = fr.ISSUED_PERIOD then 3
1626 when trunc(fi_pid.REPORT_START_DATE) = trunc(fr.REPORT_START_DATE)
1627 and trunc(fi_pid.REPORT_END_DATE) = trunc(fr.REPORT_END_DATE) then 4
1628 end ranking,
1629 fr.rowid fr_rowid
1630 from (
1631 -- match with party id
1632 select /*+ no_merge leading(fn_int) index_asc(fn_int) use_hash(party_sg) use_nl(mosr) */
1633 nvl(nvl(nvl(fn_int.party_id,party_sg.party_id), osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1634 fn_int.party_orig_system party_os,
1635 fn_int.party_orig_system_reference party_osr,
1636 fn_int.type_of_financial_report,
1637 fn_int.document_reference, trunc(
1638 fn_int.date_report_issued) date_report_issued,
1639 fn_int.issued_period, trunc(fn_int.report_end_date)
1640 report_end_date, trunc(fn_int.report_start_date)
1641 report_start_date, fn_int.financial_number_name,
1642 fn_int.rowid int_row_id,
1643 fn_int.batch_id
1644 from hz_imp_finnumbers_int fn_int,
1645 hz_imp_parties_sg party_sg,
1646 hz_orig_sys_references mosr,
1647 hz_imp_osr_change osr_ch_tbl
1648 where fn_int.interface_status = 'C'
1649 and fn_int.batch_id = P_BATCH_ID
1650 and fn_int.party_orig_system = P_OS
1651 and fn_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1652 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1653 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1654 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fn_int.party_orig_system_reference
1655 and osr_ch_tbl.entity_id (+) = fn_int.party_id /* Bug 5383200 */
1656 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1657 and party_sg.party_orig_system (+) = P_OS
1658 and party_sg.batch_id(+) = P_BATCH_ID
1659 and fn_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1660 and fn_int.party_orig_system = party_sg.party_orig_system (+)
1661 and fn_int.batch_id = party_sg.batch_id(+)
1662 and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1663 and party_sg.action_flag(+) = 'I'
1664 and fn_int.party_orig_system_reference = mosr.orig_system_reference (+)
1665 and fn_int.party_orig_system = mosr.orig_system (+)
1666 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1667 and mosr.status (+) = 'A'
1668 ) fi_pid,
1669 hz_imp_finreports_sg frsg,
1670 hz_financial_reports fr
1671 where fi_pid.pid = frsg.party_id (+)
1672 and fi_pid.type_of_financial_report = frsg.type_of_financial_report (+)
1673 and fi_pid.document_reference = frsg.document_reference (+)
1674 and frsg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1675 and frsg.action_flag(+) = 'I'
1676 and fi_pid.batch_id = frsg.batch_id(+)
1677 and fi_pid.pid = fr.party_id (+)
1678 and nvl(trunc(fi_pid.DATE_REPORT_ISSUED), c_end_date) =
1679 nvl(trunc(fr.DATE_REPORT_ISSUED (+) ) , c_end_date)
1680 and fi_pid.type_of_financial_report = fr.type_of_financial_report (+)
1681 and fi_pid.document_reference = fr.document_reference (+)
1682 and fr.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1683 ) fi_frid
1684 where fi_frid.financial_number_name = hz_fn.financial_number_name (+)
1685 and fi_frid.fr_id = hz_fn.financial_report_id (+)
1686 and HZ_FN.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1687 ) hz_fn2
1688 where new_rank = 1
1689 );
1690
1691 else
1692 -- first run
1693 INSERT INTO HZ_IMP_FINNUMBERS_SG
1694 (
1695 PARTY_ID,
1696 PARTY_ORIG_SYSTEM,
1697 PARTY_ORIG_SYSTEM_REFERENCE,
1698 INT_ROW_ID,
1699 ACTION_FLAG,
1700 FINANCIAL_NUMBER_ID,
1701 FINANCIAL_REPORT_ID,
1702 TYPE_OF_FINANCIAL_REPORT,
1703 DOCUMENT_REFERENCE,
1704 DATE_REPORT_ISSUED,
1705 ISSUED_PERIOD,
1706 REPORT_START_DATE,
1707 REPORT_END_DATE,
1708 BATCH_MODE_FLAG, BATCH_ID
1709 )
1713 party_os,
1710 (
1711 -- filter out all less ranking
1712 select party_id,
1714 party_osr,
1715 int_row_id,
1716 -- if ranking is null, there is no match in FR and FN
1717 nvl2(ranking, action_flag, 'I'),
1718 nvl2(ranking, nvl(hz_fn2.financial_number_id, hz_financial_numbers_s.NextVal), hz_financial_numbers_s.NextVal) fn_id,
1719 nvl2(ranking, fr_id, null),
1720 type_of_financial_report,
1721 document_reference,
1722 date_report_issued,
1723 issued_period,
1724 report_start_date,
1725 report_end_date,
1726 P_BATCH_MODE_FLAG, P_BATCH_ID
1727 from (
1728 -- match all fn ids
1729 select /*+ leading(fi_frid) use_nl(hz_fn) */
1730 pid party_id,
1731 fi_frid.party_os,
1732 fi_frid.party_osr,
1733 fi_frid.int_row_id,
1734 nvl2(hz_fn.financial_number_id, 'U', 'I') action_flag,
1735 hz_fn.financial_number_id,
1736 fr_id,
1737 fi_frid.type_of_financial_report,
1738 fi_frid.document_reference,
1739 fi_frid.date_report_issued,
1740 fi_frid.issued_period,
1741 fi_frid.report_start_date,
1742 fi_frid.report_end_date,
1743 -- select the highest ranking
1744 rank() over (partition by fi_frid.int_row_id
1745 order by fi_frid.ranking nulls last, fr_rowid) new_rank,
1746 fi_frid.ranking ranking
1747 from hz_financial_numbers hz_fn,
1748 (
1749 -- match all fr ids without the date columns
1750 select /*+ no_merge leading(fi_pid) use_nl(frsg, fr) */
1751 fi_pid.pid, fi_pid.party_os, fi_pid.party_osr,
1752 nvl(frsg.financial_report_id, fr.financial_report_id) fr_id,
1753 fi_pid.type_of_financial_report, fi_pid.document_reference,
1754 fi_pid.date_report_issued, fi_pid.issued_period,
1755 fi_pid.report_start_date, fi_pid.report_end_date,
1756 fi_pid.financial_number_name, fi_pid.int_row_id,
1757 -- rank the matched FR which matches other than date cols
1758 case when fi_pid.ISSUED_PERIOD = frsg.ISSUED_PERIOD then 1
1759 when trunc(fi_pid.REPORT_START_DATE) = trunc(frsg.REPORT_START_DATE)
1760 and trunc(fi_pid.REPORT_END_DATE) = trunc(frsg.REPORT_END_DATE) then 2
1761 when fi_pid.ISSUED_PERIOD = fr.ISSUED_PERIOD then 3
1762 when trunc(fi_pid.REPORT_START_DATE) = trunc(fr.REPORT_START_DATE)
1763 and trunc(fi_pid.REPORT_END_DATE) = trunc(fr.REPORT_END_DATE) then 4
1764 end ranking,
1765 fr.rowid fr_rowid
1766 from (
1767 -- match with party id
1768 select /*+ no_merge leading(fn_int) index_asc(fn_int) use_hash(party_sg) use_nl(mosr) */
1769 nvl(nvl(nvl(fn_int.party_id,party_sg.party_id), osr_ch_tbl.entity_id),mosr.owner_table_id) pid,
1770 fn_int.party_orig_system party_os,
1771 fn_int.party_orig_system_reference party_osr,
1772 fn_int.type_of_financial_report,
1773 fn_int.document_reference, trunc(
1774 fn_int.date_report_issued) date_report_issued,
1775 fn_int.issued_period, trunc(fn_int.report_end_date)
1776 report_end_date, trunc(fn_int.report_start_date)
1777 report_start_date, fn_int.financial_number_name,
1778 fn_int.rowid int_row_id,
1779 fn_int.batch_id
1780 from hz_imp_finnumbers_int fn_int,
1781 hz_imp_parties_sg party_sg,
1782 hz_orig_sys_references mosr,
1783 hz_imp_osr_change osr_ch_tbl
1784 where fn_int.interface_status is null
1785 and fn_int.batch_id = P_BATCH_ID
1786 and fn_int.party_orig_system = P_OS
1787 and fn_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1788 and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1789 and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1790 and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fn_int.party_orig_system_reference
1791 and osr_ch_tbl.entity_id (+) = fn_int.party_id /* Bug 5383200 */
1792 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1793 and party_sg.party_orig_system (+) = P_OS
1794 and party_sg.batch_id(+) = P_BATCH_ID
1795 and fn_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1796 and fn_int.party_orig_system = party_sg.party_orig_system (+)
1797 and fn_int.batch_id = party_sg.batch_id(+)
1798 and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1799 and party_sg.action_flag(+) = 'I'
1800 and fn_int.party_orig_system_reference = mosr.orig_system_reference (+)
1801 and fn_int.party_orig_system = mosr.orig_system (+)
1802 and mosr.owner_table_name (+) = 'HZ_PARTIES'
1803 and mosr.status (+) = 'A'
1804 ) fi_pid,
1805 hz_imp_finreports_sg frsg,
1806 hz_financial_reports fr
1807 where fi_pid.pid = frsg.party_id (+)
1808 and fi_pid.type_of_financial_report = frsg.type_of_financial_report (+)
1809 and fi_pid.document_reference = frsg.document_reference (+)
1810 and frsg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1811 and frsg.action_flag(+) = 'I'
1812 and fi_pid.batch_id = frsg.batch_id(+)
1816 and fi_pid.type_of_financial_report = fr.type_of_financial_report (+)
1813 and fi_pid.pid = fr.party_id (+)
1814 and nvl(trunc(fi_pid.DATE_REPORT_ISSUED), c_end_date) =
1815 nvl(trunc(fr.DATE_REPORT_ISSUED (+) ) , c_end_date)
1817 and fi_pid.document_reference = fr.document_reference (+)
1818 and fr.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1819 ) fi_frid
1820 where fi_frid.financial_number_name = hz_fn.financial_number_name (+)
1821 and fi_frid.fr_id = hz_fn.financial_report_id (+)
1822 and HZ_FN.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1823 ) hz_fn2
1824 where new_rank = 1
1825 );
1826 end if;
1827 commit;
1828 end match_financial_numbers;
1829
1830
1831 PROCEDURE match_relationships(
1832 P_BATCH_ID IN NUMBER,
1833 P_OS IN VARCHAR2,
1834 P_FROM_OSR IN VARCHAR2,
1835 P_TO_OSR IN VARCHAR2,
1836 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
1837 P_RERUN IN VARCHAR2,
1838 P_BATCH_MODE_FLAG IN VARCHAR2
1839 ) IS
1840
1841 BEGIN
1842 /*
1843 Fix bug 4374278: For DNB, when comparing the end_date cannot use c_end_date.
1844 The c_end_date = to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI'); but when relationship records
1845 are created in Data Load HZ_IMP_LOAD_RELATIONSHIPS_PKG it use
1846 l_no_end_date := TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
1847
1848 Change the code to use l_no_end_date.
1849 */
1850 if (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN = 'Y') then
1851 -- re-run/DNB
1852 INSERT INTO HZ_IMP_RELSHIPS_SG
1853 ( RELATIONSHIP_TYPE,
1854 RELATIONSHIP_CODE,
1855 START_DATE,
1856 END_DATE,
1857 SUB_ORIG_SYSTEM,
1858 SUB_ORIG_SYSTEM_REFERENCE,
1859 SUB_ID,
1860 OBJ_ID,
1861 INT_ROW_ID,
1862 ACTION_FLAG,
1863 RELATIONSHIP_ID,
1864 ERROR_FLAG,
1865 BATCH_MODE_FLAG, BATCH_ID
1866 )
1867 (
1868 select /*+ leading(ri_ids) use_nl(hz_rel) */
1869 ri_ids.rel_type,
1870 ri_ids.rel_code,
1871 ri_ids.start_date,
1872 ri_ids.end_date,
1873 ri_ids.subject_os,
1874 ri_ids.subject_osr,
1875 ri_ids.subject_id subject_id,
1876 ri_ids.object_id object_id,
1877 ri_ids.int_row_id int_row_id,
1878 nvl2(hz_rel.relationship_id, 'U', 'I') action_flag,
1879 nvl(hz_rel.relationship_id, hz_relationships_s.NextVal) relationship_id,
1880 -- populate error flag
1881 -- error 1 - object not found; error 2 - subject not found
1882 nvl2(ri_ids.object_id, nvl2(ri_ids.subject_id, null, 2), 1) error_flag,
1883 P_BATCH_MODE_FLAG, P_BATCH_ID
1884 from hz_relationships hz_rel,
1885 (select /*+ no_merge leading(ri) index_asc(ri)
1886 use_nl(mosr,mosr2,PARTY_SGA) use_hash(PARTY_SGB) */
1887 ri.relationship_type rel_type,
1888 ri.relationship_code rel_code,
1889 ri.sub_orig_system subject_os,
1890 ri.sub_orig_system_reference subject_osr,
1891 nvl(nvl(nvl(ri.obj_id,PARTY_SGA.party_id), obj_osr_ch_tbl.entity_id), mosr.owner_table_id) object_id,
1892 nvl(nvl(PARTY_SGB.party_id, sub_osr_ch_tbl.entity_id), mosr2.owner_table_id) subject_id,
1893 ri.rowid int_row_id, relationship_type, relationship_code,
1894 ri.start_date start_date,
1895 ri.end_date end_date,
1896 NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change, /* Bug 5383200 */
1897 sub_int.party_type sub_type,
1898 obj_int.party_type obj_type
1899 from hz_imp_RELSHIPS_int ri,
1900 hz_orig_sys_references mosr,
1901 hz_orig_sys_references mosr2,
1902 HZ_IMP_PARTIES_SG PARTY_SGA,
1903 HZ_IMP_PARTIES_SG PARTY_SGB,
1904 hz_imp_osr_change sub_osr_ch_tbl,
1905 hz_imp_osr_change obj_osr_ch_tbl,
1906 hz_imp_parties_int sub_int,
1907 hz_imp_parties_int obj_int
1908 where mosr.owner_table_name (+) = 'HZ_PARTIES'
1909 and mosr.status (+) = 'A'
1910 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1911 and mosr2.status (+) = 'A'
1912 and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1913 and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1914 and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1915 and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
1916 and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1917 and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
1918 and obj_osr_ch_tbl.entity_id (+) = ri.obj_id /* Bug 5383200 */
1919 and ri.batch_id = P_BATCH_ID
1920 and ri.sub_orig_system = P_OS
1921 and ri.interface_status = 'C'
1922 and ri.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
1923 and ri.obj_orig_system_reference = mosr.orig_system_reference (+)
1924 and ri.obj_orig_system = mosr.orig_system (+)
1928 and ri.obj_orig_system = PARTY_SGA.party_orig_system (+)
1925 and ri.sub_orig_system_reference = mosr2.orig_system_reference (+)
1926 and ri.sub_orig_system = mosr2.orig_system (+)
1927 and ri.obj_orig_system_reference = PARTY_SGA.party_orig_system_reference (+)
1929 and ri.batch_id = PARTY_SGA.batch_id(+)
1930 and PARTY_SGA.batch_mode_flag(+)=P_BATCH_MODE_FLAG
1931 and PARTY_SGA.ACTION_FLAG(+) = 'I'
1932 and ri.sub_orig_system_reference = PARTY_SGB.party_orig_system_reference (+)
1933 and ri.sub_orig_system = PARTY_SGB.party_orig_system (+)
1934 and ri.batch_id = PARTY_SGB.batch_id(+)
1935 and PARTY_SGB.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1936 and PARTY_SGB.party_orig_system (+) = P_OS
1937 and PARTY_SGB.batch_id(+) = P_BATCH_ID
1938 and PARTY_SGB.batch_mode_flag(+)= P_BATCH_MODE_FLAG
1939 /* and PARTY_SGB.ACTION_FLAG(+) = 'I' */
1940 and ri.batch_id = sub_int.batch_id(+)
1941 and sub_int.interface_status(+) is null
1942 and ri.sub_orig_system_reference = sub_int.party_orig_system_reference (+)
1943 and ri.sub_orig_system = sub_int.party_orig_system (+)
1944 and ri.batch_id = obj_int.batch_id(+)
1945 and obj_int.interface_status(+) is null
1946 and ri.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
1947 and ri.obj_orig_system = obj_int.party_orig_system (+)
1948 ) ri_ids
1949 where ri_ids.object_id = hz_rel.object_id (+)
1950 and ri_ids.subject_id = hz_rel.subject_id (+)
1951 and ri_ids.obj_type = hz_rel.object_type (+)
1952 and ri_ids.sub_type = hz_rel.subject_type (+)
1953 and ri_ids.relationship_type = hz_rel.relationship_type (+)
1954 and ri_ids.relationship_code = hz_rel.relationship_code (+)
1955 -- for DNB, ignore start date, filter out end-dated records
1956 and nvl(hz_rel.END_DATE (+),l_no_end_date ) = l_no_end_date
1957 and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1958 and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
1959 and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
1960
1961 );
1962 elsif (P_ACTUAL_CONTENT_SRC = 'USER_ENTERED' and P_RERUN = 'Y') then
1963 -- re-run/USER_ENTERED
1964 INSERT INTO HZ_IMP_RELSHIPS_SG
1965 ( RELATIONSHIP_TYPE,
1966 RELATIONSHIP_CODE,
1967 START_DATE,
1968 END_DATE,
1969 SUB_ORIG_SYSTEM,
1970 SUB_ORIG_SYSTEM_REFERENCE,
1971 SUB_ID,
1972 OBJ_ID,
1973 INT_ROW_ID,
1974 ACTION_FLAG,
1975 RELATIONSHIP_ID,
1976 ERROR_FLAG,
1977 BATCH_MODE_FLAG, BATCH_ID
1978 )
1979 (
1980 select /*+ leading(ri_ids) use_nl(hz_rel) */
1981 ri_ids.rel_type,
1982 ri_ids.rel_code,
1983 ri_ids.start_date,
1984 ri_ids.end_date,
1985 ri_ids.subject_os,
1986 ri_ids.subject_osr,
1987 ri_ids.subject_id subject_id,
1988 ri_ids.object_id object_id,
1989 ri_ids.int_row_id int_row_id,
1990 nvl2(hz_rel.relationship_id, 'U', 'I') action_flag,
1991 nvl(hz_rel.relationship_id, hz_relationships_s.NextVal) relationship_id,
1992 nvl2(ri_ids.object_id, nvl2(ri_ids.subject_id, null, 2), 1) error_flag,
1993 P_BATCH_MODE_FLAG, P_BATCH_ID
1994 from hz_relationships hz_rel,
1995 (select /*+ no_merge leading(ri) index_asc(ri)
1996 use_nl(mosr,mosr2,PARTY_SGA) use_hash(PARTY_SGB) */
1997 ri.relationship_type rel_type,
1998 ri.relationship_code rel_code,
1999 ri.sub_orig_system subject_os,
2000 ri.sub_orig_system_reference subject_osr,
2001 nvl(nvl(nvl(ri.obj_id,PARTY_SGA.party_id), obj_osr_ch_tbl.entity_id), mosr.owner_table_id) object_id,
2002 nvl(nvl(PARTY_SGB.party_id, sub_osr_ch_tbl.entity_id), mosr2.owner_table_id) subject_id,
2003 ri.rowid int_row_id, relationship_type, relationship_code,
2004 ri.start_date start_date,
2005 ri.end_date end_date,
2006 sub_int.party_type sub_type,
2007 obj_int.party_type obj_type,
2008 NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2009 from hz_imp_RELSHIPS_int ri,
2010 hz_orig_sys_references mosr,
2011 hz_orig_sys_references mosr2,
2012 HZ_IMP_PARTIES_SG PARTY_SGA,
2013 HZ_IMP_PARTIES_SG PARTY_SGB,
2014 hz_imp_osr_change sub_osr_ch_tbl,
2015 hz_imp_osr_change obj_osr_ch_tbl,
2016 hz_imp_parties_int sub_int,
2017 hz_imp_parties_int obj_int
2018 where mosr.owner_table_name (+) = 'HZ_PARTIES'
2019 and mosr.status (+) = 'A'
2020 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2021 and mosr2.status (+) = 'A'
2022 and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2023 and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2024 and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2025 and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2026 and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2027 and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2028 and obj_osr_ch_tbl.entity_id (+) = ri.obj_id /* Bug 5383200 */
2032 and ri.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2029 and ri.batch_id = P_BATCH_ID
2030 and ri.sub_orig_system = P_OS
2031 and ri.interface_status = 'C'
2033 and ri.obj_orig_system_reference = mosr.orig_system_reference (+)
2034 and ri.obj_orig_system = mosr.orig_system (+)
2035 and ri.sub_orig_system_reference = mosr2.orig_system_reference (+)
2036 and ri.sub_orig_system = mosr2.orig_system (+)
2037 and ri.obj_orig_system_reference = PARTY_SGA.party_orig_system_reference (+)
2038 and ri.obj_orig_system = PARTY_SGA.party_orig_system (+)
2039 and ri.batch_id = PARTY_SGA.batch_id(+)
2040 and PARTY_SGA.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2041 and PARTY_SGA.ACTION_FLAG(+) = 'I'
2042 and ri.sub_orig_system_reference = PARTY_SGB.party_orig_system_reference (+)
2043 and ri.sub_orig_system = PARTY_SGB.party_orig_system (+)
2044 and ri.batch_id = PARTY_SGB.batch_id(+)
2045 and PARTY_SGB.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2046 and PARTY_SGB.party_orig_system (+) = P_OS
2047 and PARTY_SGB.batch_id(+) = P_BATCH_ID
2048 and PARTY_SGB.batch_mode_flag(+)= P_BATCH_MODE_FLAG
2049 /* and PARTY_SGB.ACTION_FLAG(+) = 'I' */
2050 and ri.batch_id = sub_int.batch_id(+)
2051 and sub_int.interface_status(+) is null
2052 and ri.sub_orig_system_reference = sub_int.party_orig_system_reference (+)
2053 and ri.sub_orig_system = sub_int.party_orig_system (+)
2054 and ri.batch_id = obj_int.batch_id(+)
2055 and obj_int.interface_status(+) is null
2056 and ri.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
2057 and ri.obj_orig_system = obj_int.party_orig_system (+)
2058 ) ri_ids
2059 where ri_ids.object_id = hz_rel.object_id (+)
2060 and ri_ids.subject_id = hz_rel.subject_id (+)
2061 and ri_ids.obj_type = hz_rel.object_type (+)
2062 and ri_ids.sub_type = hz_rel.subject_type (+)
2063 and ri_ids.relationship_type = hz_rel.relationship_type (+)
2064 and ri_ids.relationship_code = hz_rel.relationship_code (+)
2065 -- use start date as key for non-DNB data
2066 and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2067 and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2068 and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2069 and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2070
2071 );
2072 elsif (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN <> 'Y') then
2073 -- first run/DNB
2074 INSERT INTO HZ_IMP_RELSHIPS_SG
2075 ( RELATIONSHIP_TYPE,
2076 RELATIONSHIP_CODE,
2077 START_DATE,
2078 END_DATE,
2079 SUB_ORIG_SYSTEM,
2080 SUB_ORIG_SYSTEM_REFERENCE,
2081 SUB_ID,
2082 OBJ_ID,
2083 INT_ROW_ID,
2084 ACTION_FLAG,
2085 RELATIONSHIP_ID,
2086 ERROR_FLAG,
2087 BATCH_MODE_FLAG, BATCH_ID
2088 )
2089 (
2090 select /*+ leading(ri_ids) use_nl(hz_rel) */
2091 ri_ids.rel_type,
2092 ri_ids.rel_code,
2093 ri_ids.start_date,
2094 ri_ids.end_date,
2095 ri_ids.subject_os,
2096 ri_ids.subject_osr,
2097 ri_ids.subject_id subject_id,
2098 ri_ids.object_id object_id,
2099 ri_ids.int_row_id int_row_id,
2100 nvl2(hz_rel.relationship_id, 'U', 'I') action_flag,
2101 nvl(hz_rel.relationship_id, hz_relationships_s.NextVal) relationship_id,
2102 nvl2(ri_ids.object_id, nvl2(ri_ids.subject_id, null, 2), 1) error_flag,
2103 P_BATCH_MODE_FLAG, P_BATCH_ID
2104 from hz_relationships hz_rel,
2105 (select /*+ no_merge leading(ri) index_asc(ri)
2106 use_nl(mosr,mosr2,PARTY_SGA) use_hash(PARTY_SGB) */
2107 ri.relationship_type rel_type,
2108 ri.relationship_code rel_code,
2109 ri.sub_orig_system subject_os,
2110 ri.sub_orig_system_reference subject_osr,
2111 nvl(nvl(nvl(ri.obj_id, PARTY_SGA.party_id), obj_osr_ch_tbl.entity_id), mosr.owner_table_id) object_id,
2112 nvl(nvl(PARTY_SGB.party_id, sub_osr_ch_tbl.entity_id), mosr2.owner_table_id) subject_id,
2113 ri.rowid int_row_id, relationship_type, relationship_code,
2114 ri.start_date start_date,
2115 ri.end_date end_date,
2116 sub_int.party_type sub_type,
2117 obj_int.party_type obj_type,
2118 NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2119 from hz_imp_RELSHIPS_int ri,
2120 hz_orig_sys_references mosr,
2121 hz_orig_sys_references mosr2,
2122 HZ_IMP_PARTIES_SG PARTY_SGA,
2123 HZ_IMP_PARTIES_SG PARTY_SGB,
2124 hz_imp_osr_change sub_osr_ch_tbl,
2125 hz_imp_osr_change obj_osr_ch_tbl,
2126 hz_imp_parties_int sub_int,
2127 hz_imp_parties_int obj_int
2128 where mosr.owner_table_name (+) = 'HZ_PARTIES'
2129 and mosr.status (+) = 'A'
2130 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2131 and mosr2.status (+) = 'A'
2132 and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2133 and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2134 and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2138 and obj_osr_ch_tbl.entity_id (+) = ri.obj_id /* Bug 5383200 */
2135 and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2136 and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2137 and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2139 and ri.batch_id = P_BATCH_ID
2140 and ri.sub_orig_system = P_OS
2141 and ri.interface_status is null
2142 and ri.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2143 and ri.obj_orig_system_reference = mosr.orig_system_reference (+)
2144 and ri.obj_orig_system = mosr.orig_system (+)
2145 and ri.sub_orig_system_reference = mosr2.orig_system_reference (+)
2146 and ri.sub_orig_system = mosr2.orig_system (+)
2147 and ri.obj_orig_system_reference = PARTY_SGA.party_orig_system_reference (+)
2148 and ri.obj_orig_system = PARTY_SGA.party_orig_system (+)
2149 and ri.batch_id = PARTY_SGA.batch_id(+)
2150 and PARTY_SGA.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2151 and PARTY_SGA.ACTION_FLAG(+) = 'I'
2152 and ri.sub_orig_system_reference = PARTY_SGB.party_orig_system_reference (+)
2153 and ri.sub_orig_system = PARTY_SGB.party_orig_system (+)
2154 and ri.batch_id = PARTY_SGB.batch_id(+)
2155 and PARTY_SGB.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2156 and PARTY_SGB.party_orig_system (+) = P_OS
2157 and PARTY_SGB.batch_id(+) = P_BATCH_ID
2158 and PARTY_SGB.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2159 /* and PARTY_SGB.ACTION_FLAG(+) = 'I' */
2160 and ri.batch_id = sub_int.batch_id(+)
2161 and sub_int.interface_status(+) is null
2162 and ri.sub_orig_system_reference = sub_int.party_orig_system_reference (+)
2163 and ri.sub_orig_system = sub_int.party_orig_system (+)
2164 and ri.batch_id = obj_int.batch_id(+)
2165 and obj_int.interface_status(+) is null
2166 and ri.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
2167 and ri.obj_orig_system = obj_int.party_orig_system (+)
2168 ) ri_ids
2169 where ri_ids.object_id = hz_rel.object_id (+)
2170 and ri_ids.subject_id = hz_rel.subject_id (+)
2171 and ri_ids.obj_type = hz_rel.object_type (+)
2172 and ri_ids.sub_type = hz_rel.subject_type (+)
2173 and ri_ids.relationship_type = hz_rel.relationship_type (+)
2174 and ri_ids.relationship_code = hz_rel.relationship_code (+)
2175 --and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2176 and nvl(hz_rel.END_DATE (+),l_no_end_date ) = l_no_end_date
2177 and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2178 and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2179 and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2180 );
2181 else
2182 -- first run/non-DNB
2183 INSERT INTO HZ_IMP_RELSHIPS_SG
2184 ( RELATIONSHIP_TYPE,
2185 RELATIONSHIP_CODE,
2186 START_DATE,
2187 END_DATE,
2188 SUB_ORIG_SYSTEM,
2189 SUB_ORIG_SYSTEM_REFERENCE,
2190 SUB_ID,
2191 OBJ_ID,
2192 INT_ROW_ID,
2193 ACTION_FLAG,
2194 RELATIONSHIP_ID,
2195 ERROR_FLAG,
2196 BATCH_MODE_FLAG, BATCH_ID
2197 )
2198 (
2199 select /*+ leading(ri_ids) use_nl(hz_rel) */
2200 ri_ids.rel_type,
2201 ri_ids.rel_code,
2202 ri_ids.start_date,
2203 ri_ids.end_date,
2204 ri_ids.subject_os,
2205 ri_ids.subject_osr,
2206 ri_ids.subject_id subject_id,
2207 ri_ids.object_id object_id,
2208 ri_ids.int_row_id int_row_id,
2209 nvl2(hz_rel.relationship_id, 'U', 'I') action_flag,
2210 nvl(hz_rel.relationship_id, hz_relationships_s.NextVal) relationship_id,
2211 nvl2(ri_ids.object_id, nvl2(ri_ids.subject_id, null, 2), 1) error_flag,
2212 P_BATCH_MODE_FLAG, P_BATCH_ID
2213 from hz_relationships hz_rel,
2214 (select /*+ no_merge leading(ri) index_asc(ri)
2215 use_nl(mosr,mosr2,PARTY_SGA) use_hash(PARTY_SGB) */
2216 ri.relationship_type rel_type,
2217 ri.relationship_code rel_code,
2218 ri.sub_orig_system subject_os,
2219 ri.sub_orig_system_reference subject_osr,
2220 nvl(nvl(nvl(ri.obj_id,PARTY_SGA.party_id), obj_osr_ch_tbl.entity_id), mosr.owner_table_id) object_id,
2221 nvl(nvl(PARTY_SGB.party_id, sub_osr_ch_tbl.entity_id), mosr2.owner_table_id) subject_id,
2222 ri.rowid int_row_id, relationship_type, relationship_code,
2223 ri.start_date start_date,
2224 ri.end_date end_date,
2225 sub_int.party_type sub_type,
2226 obj_int.party_type obj_type,
2227 NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2228 from hz_imp_RELSHIPS_int ri,
2229 hz_orig_sys_references mosr,
2230 hz_orig_sys_references mosr2,
2231 HZ_IMP_PARTIES_SG PARTY_SGA,
2232 HZ_IMP_PARTIES_SG PARTY_SGB,
2233 hz_imp_osr_change sub_osr_ch_tbl,
2234 hz_imp_osr_change obj_osr_ch_tbl,
2235 hz_imp_parties_int sub_int,
2236 hz_imp_parties_int obj_int
2237 where mosr.owner_table_name (+) = 'HZ_PARTIES'
2238 and mosr.status (+) = 'A'
2239 and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2240 and mosr2.status (+) = 'A'
2244 and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2241 and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2242 and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2243 and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2245 and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2246 and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2247 and obj_osr_ch_tbl.entity_id (+) = ri.obj_id /* Bug 5383200 */
2248 and ri.batch_id = P_BATCH_ID
2249 and ri.sub_orig_system = P_OS
2250 and ri.interface_status is null
2251 and ri.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2252 and ri.obj_orig_system_reference = mosr.orig_system_reference (+)
2253 and ri.obj_orig_system = mosr.orig_system (+)
2254 and ri.sub_orig_system_reference = mosr2.orig_system_reference (+)
2255 and ri.sub_orig_system = mosr2.orig_system (+)
2256 and ri.obj_orig_system_reference = PARTY_SGA.party_orig_system_reference (+)
2257 and ri.obj_orig_system = PARTY_SGA.party_orig_system (+)
2258 and ri.batch_id = PARTY_SGA.batch_id(+)
2259 and PARTY_SGA.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2260 and PARTY_SGA.ACTION_FLAG(+) = 'I'
2261 and ri.sub_orig_system_reference = PARTY_SGB.party_orig_system_reference (+)
2262 and ri.sub_orig_system = PARTY_SGB.party_orig_system (+)
2263 and ri.batch_id = PARTY_SGB.batch_id(+)
2264 and PARTY_SGB.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2265 and PARTY_SGB.party_orig_system (+) = P_OS
2266 and PARTY_SGB.batch_id(+) = P_BATCH_ID
2267 and PARTY_SGB.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2268 /* and PARTY_SGB.ACTION_FLAG(+) = 'I' */
2269 and ri.batch_id = sub_int.batch_id(+)
2270 and sub_int.interface_status(+) is null
2271 and ri.sub_orig_system_reference = sub_int.party_orig_system_reference (+)
2272 and ri.sub_orig_system = sub_int.party_orig_system (+)
2273 and ri.batch_id = obj_int.batch_id(+)
2274 and obj_int.interface_status(+) is null
2275 and ri.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
2276 and ri.obj_orig_system = obj_int.party_orig_system (+)
2277 ) ri_ids
2278 where ri_ids.object_id = hz_rel.object_id (+)
2279 and ri_ids.subject_id = hz_rel.subject_id (+)
2280 and ri_ids.obj_type = hz_rel.object_type (+)
2281 and ri_ids.sub_type = hz_rel.subject_type (+)
2282 and ri_ids.relationship_type = hz_rel.relationship_type (+)
2283 and ri_ids.relationship_code = hz_rel.relationship_code (+)
2284 and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2285 and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2286 and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2287 and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2288 );
2289 end if;
2290 commit;
2291 end match_relationships;
2292
2293
2294 PROCEDURE match_contacts(
2295 P_BATCH_ID IN NUMBER,
2296 P_OS IN VARCHAR2,
2297 P_FROM_OSR IN VARCHAR2,
2298 P_TO_OSR IN VARCHAR2,
2299 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
2300 P_RERUN IN VARCHAR2,
2301 P_BATCH_MODE_FLAG IN VARCHAR2
2302 ) IS
2303
2304 begin
2305
2306 if(P_RERUN = 'Y') then
2307 -- re-run
2308 INSERT INTO HZ_IMP_CONTACTS_SG
2309 ( RELATIONSHIP_TYPE,
2310 RELATIONSHIP_CODE,
2311 START_DATE,
2312 END_DATE,
2313 SUB_ORIG_SYSTEM,
2314 SUB_ORIG_SYSTEM_REFERENCE,
2315 CONTACT_ID,
2316 CONTACT_ORIG_SYSTEM,
2317 CONTACT_ORIG_SYSTEM_REFERENCE,
2318 SUB_ID,
2319 OBJ_ID,
2320 INT_ROW_ID,
2321 ACTION_FLAG,
2322 PARTY_ACTION_FLAG,
2323 BATCH_MODE_FLAG, BATCH_ID
2324 )
2325 (
2326 select /*+ leading(cont_int) index_asc(cont_int)
2327 use_nl(cont_mosr,sub_mosr,obj_sg,obj_mosr) use_hash(sub_sg) */
2328 cont_int.relationship_type,
2329 cont_int.relationship_code,
2330 cont_int.start_date,
2331 cont_int.end_date,
2332 cont_int.sub_orig_system sos,
2333 cont_int.sub_orig_system_reference sosr,
2334 nvl(cont_mosr.owner_table_id, hz_org_contacts_s.NextVal) cont_id,
2335 cont_int.contact_orig_system cont_orig_system,
2336 cont_int.contact_orig_system_reference cont_orig_system_reference,
2337 /*6913856 */
2338 coalesce(sub_sg.party_id, sub_int.party_id,sub_mosr.owner_table_id) sub_id,
2339 coalesce(obj_sg.party_id, obj_int.party_id,obj_mosr.owner_table_id) obj_id,
2340 cont_int.rowid int_row_id,
2341 nvl2(cont_mosr.owner_table_id, 'U', 'I') action_flag,
2342 nvl(obj_sg.action_flag,'U') PARTY_ACTION_FLAG,
2343 P_BATCH_MODE_FLAG, P_BATCH_ID
2344 from hz_imp_contacts_int cont_int,
2345 hz_orig_sys_references cont_mosr,
2346 hz_imp_parties_sg sub_sg,
2347 hz_orig_sys_references sub_mosr,
2348 hz_imp_parties_sg obj_sg,
2349 hz_imp_parties_int sub_int,
2350 hz_imp_parties_int obj_int, /*6913856 */
2351 hz_orig_sys_references obj_mosr
2352 where cont_int.batch_id = P_BATCH_ID
2353 and cont_int.sub_orig_system = P_OS
2357 and sub_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2354 and cont_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2355 and cont_int.interface_status = 'C'
2356 and cont_int.batch_id = sub_sg.batch_id(+)
2358 -- and sub_sg.action_flag(+)='I'
2359 and cont_int.batch_id = obj_sg.batch_id(+)
2360 and obj_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2361 -- and obj_sg.action_flag(+)='I'
2362 and cont_mosr.owner_table_name (+) = 'HZ_ORG_CONTACTS'
2363 and cont_mosr.status (+) = 'A'
2364 and cont_int.contact_orig_system_reference = cont_mosr.orig_system_reference (+)
2365 and cont_int.contact_orig_system = cont_mosr.orig_system (+)
2366 and cont_int.sub_orig_system_reference = sub_sg.party_orig_system_reference (+)
2367 and cont_int.sub_orig_system = sub_sg.party_orig_system (+)
2368 and sub_mosr.owner_table_name (+) = 'HZ_PARTIES'
2369 and sub_mosr.status (+) = 'A'
2370 and sub_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2371 and sub_sg.party_orig_system (+) = P_OS
2372 and sub_sg.batch_id(+) = P_BATCH_ID
2373 and cont_int.sub_orig_system_reference = sub_mosr.orig_system_reference (+)
2374 and cont_int.sub_orig_system = sub_mosr.orig_system (+)
2375 and cont_int.obj_orig_system_reference = obj_sg.party_orig_system_reference (+)
2376 and cont_int.obj_orig_system = obj_sg.party_orig_system (+)
2377 and obj_mosr.owner_table_name (+) = 'HZ_PARTIES'
2378 and obj_mosr.status (+) = 'A'
2379 and cont_int.obj_orig_system_reference = obj_mosr.orig_system_reference (+)
2380 and cont_int.obj_orig_system = obj_mosr.orig_system (+)
2381 and cont_int.batch_id = sub_int.batch_id(+)
2382 and sub_int.interface_status(+)= 'C'
2383 and cont_int.sub_orig_system_reference = sub_int.party_orig_system_reference (+)
2384 and cont_int.sub_orig_system = sub_int.party_orig_system (+)
2385 and cont_int.batch_id = obj_int.batch_id(+)
2386 and obj_int.interface_status(+)= 'C'
2387 and cont_int.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
2388 and cont_int.obj_orig_system = obj_int.party_orig_system (+)
2389 );
2390 else
2391 INSERT INTO HZ_IMP_CONTACTS_SG
2392 ( RELATIONSHIP_TYPE,
2393 RELATIONSHIP_CODE,
2394 START_DATE,
2395 END_DATE,
2396 SUB_ORIG_SYSTEM,
2397 SUB_ORIG_SYSTEM_REFERENCE,
2398 CONTACT_ID,
2399 CONTACT_ORIG_SYSTEM,
2400 CONTACT_ORIG_SYSTEM_REFERENCE,
2401 SUB_ID,
2402 OBJ_ID,
2403 INT_ROW_ID,
2404 ACTION_FLAG,
2405 PARTY_ACTION_FLAG,
2406 BATCH_MODE_FLAG, BATCH_ID
2407 )
2408 (
2409 select /*+ leading(cont_int) index_asc(cont_int)
2410 use_nl(cont_mosr,sub_mosr,obj_sg,obj_mosr) use_hash(sub_sg) */
2411 cont_int.relationship_type,
2412 cont_int.relationship_code,
2413 cont_int.start_date,
2414 cont_int.end_date,
2415 cont_int.sub_orig_system sos,
2416 cont_int.sub_orig_system_reference sosr,
2417 nvl(cont_mosr.owner_table_id, hz_org_contacts_s.NextVal) cont_id,
2418 cont_int.contact_orig_system cont_orig_system,
2419 cont_int.contact_orig_system_reference cont_orig_system_reference,
2420 coalesce(sub_sg.party_id, sub_int.party_id,sub_mosr.owner_table_id) sub_id,
2421 coalesce(obj_sg.party_id, obj_int.party_id,obj_mosr.owner_table_id) obj_id,
2422 cont_int.rowid int_row_id,
2423 nvl2(cont_mosr.owner_table_id, 'U', 'I') action_flag,
2424 nvl(obj_sg.action_flag,'U') PARTY_ACTION_FLAG,
2425 P_BATCH_MODE_FLAG, P_BATCH_ID
2426 from hz_imp_contacts_int cont_int,
2427 hz_orig_sys_references cont_mosr,
2428 hz_imp_parties_sg sub_sg,
2429 hz_orig_sys_references sub_mosr,
2430 hz_imp_parties_sg obj_sg,
2431 hz_imp_parties_int sub_int,
2432 hz_imp_parties_int obj_int,
2433 hz_orig_sys_references obj_mosr
2434 where cont_int.batch_id = P_BATCH_ID
2435 and cont_int.sub_orig_system = P_OS
2436 and cont_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2437 and cont_int.interface_status is null
2438 and cont_int.batch_id = sub_sg.batch_id(+)
2439 and sub_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2440 -- and sub_sg.action_flag(+)='I'
2441 and cont_int.batch_id = obj_sg.batch_id(+)
2442 and obj_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2443 -- and obj_sg.action_flag(+)='I'
2444 and cont_mosr.owner_table_name (+) = 'HZ_ORG_CONTACTS'
2445 and cont_mosr.status (+) = 'A'
2446 and cont_int.contact_orig_system_reference = cont_mosr.orig_system_reference (+)
2447 and cont_int.contact_orig_system = cont_mosr.orig_system (+)
2448 and cont_int.sub_orig_system_reference = sub_sg.party_orig_system_reference (+)
2449 and cont_int.sub_orig_system = sub_sg.party_orig_system (+)
2450 and sub_mosr.owner_table_name (+) = 'HZ_PARTIES'
2451 and sub_mosr.status (+) = 'A'
2452 and sub_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2453 and sub_sg.party_orig_system (+) = P_OS
2454 and sub_sg.batch_id(+) = P_BATCH_ID
2455 and cont_int.sub_orig_system_reference = sub_mosr.orig_system_reference (+)
2456 and cont_int.sub_orig_system = sub_mosr.orig_system (+)
2457 and cont_int.obj_orig_system_reference = obj_sg.party_orig_system_reference (+)
2458 and cont_int.obj_orig_system = obj_sg.party_orig_system (+)
2459 and obj_mosr.owner_table_name (+) = 'HZ_PARTIES'
2463 and cont_int.batch_id = sub_int.batch_id(+)
2460 and obj_mosr.status (+) = 'A'
2461 and cont_int.obj_orig_system_reference = obj_mosr.orig_system_reference (+)
2462 and cont_int.obj_orig_system = obj_mosr.orig_system (+)
2464 and sub_int.interface_status(+) is null
2465 and cont_int.sub_orig_system_reference =sub_int.party_orig_system_reference (+)
2466 and cont_int.sub_orig_system = sub_int.party_orig_system (+)
2467 and cont_int.batch_id = obj_int.batch_id(+)
2468 and obj_int.interface_status(+) is null
2469 and cont_int.obj_orig_system_reference = obj_int.party_orig_system_reference (+)
2470 and cont_int.obj_orig_system = obj_int.party_orig_system (+)
2471 );
2472 end if;
2473 commit;
2474 end match_contacts;
2475
2476
2477 PROCEDURE match_contactroles(
2478 P_BATCH_ID IN NUMBER,
2479 P_OS IN VARCHAR2,
2480 P_FROM_OSR IN VARCHAR2,
2481 P_TO_OSR IN VARCHAR2,
2482 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
2483 P_RERUN IN VARCHAR2,
2484 P_BATCH_MODE_FLAG IN VARCHAR2
2485 ) IS
2486
2487 begin
2488 if(P_RERUN = 'Y') then
2489 INSERT INTO HZ_IMP_CONTACTROLES_SG
2490 (
2491 SUB_ORIG_SYSTEM,
2492 SUB_ORIG_SYSTEM_REFERENCE,
2493 CONTACT_ID,
2494 CONTACT_ROLE_ID,
2495 INT_ROW_ID,
2496 ACTION_FLAG,
2497 ERROR_FLAG,
2498 BATCH_MODE_FLAG, BATCH_ID
2499 )
2500 (
2501 select /*+ leading(conr_w_cid) use_nl(hz_conr) */
2502 sos, sosr,
2503 conr_w_cid.contact_id contact_id,
2504 nvl(hz_conr.org_contact_role_id, hz_org_contact_roles_S.NextVal) conr_id,
2505 conr_w_cid.int_row_id int_row_id,
2506 nvl2(hz_conr.org_contact_role_id, 'U', 'I') action_flag,
2507 decode(conr_w_cid.cont_action_flag,
2508 'I', decode(conr_w_cid.sub_id, conr_w_cid.cont_sub_id, null, 2),
2509 null) error_flag,
2510 P_BATCH_MODE_FLAG, P_BATCH_ID
2511 from hz_org_contact_roles hz_conr,
2512 (
2513 select /*+ no_merge leading(conrole_int) index_asc(conrole_int)
2514 use_nl(cont_sg,con_mosr,party_mosr) use_hash(party_sg) */
2515 conrole_int.sub_orig_system sos,
2516 conrole_int.sub_orig_system_reference sosr,
2517 nvl(cont_sg.contact_id, con_mosr.owner_table_id) contact_id,
2518 conrole_int.rowid int_row_id,
2519 nvl(party_sg.party_id, party_mosr.owner_table_id) sub_id,
2520 cont_sg.sub_id cont_sub_id,
2521 cont_sg.action_flag cont_action_flag,
2522 conrole_int.role_type role_type
2523 from hz_imp_contactroles_int conrole_int,
2524 hz_imp_contacts_sg cont_sg,
2525 hz_orig_sys_references con_mosr,
2526 hz_imp_parties_sg party_sg,
2527 hz_orig_sys_references party_mosr--,
2528 where conrole_int.batch_id = P_BATCH_ID
2529 and conrole_int.sub_orig_system = P_OS
2530 and conrole_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2531 and conrole_int.interface_status = 'C'
2532 and conrole_int.batch_id = party_sg.batch_id(+)
2533 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2534 and party_sg.party_orig_system (+) = P_OS
2535 and party_sg.batch_id(+) = P_BATCH_ID
2536 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2537 and party_sg.action_flag(+)='I'
2538 and conrole_int.batch_id = cont_sg.batch_id(+)
2539 and cont_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2540 and cont_sg.action_flag(+)='I'
2541 and con_mosr.owner_table_name (+) = 'HZ_ORG_CONTACTS'
2542 and con_mosr.status (+) = 'A'
2543 and conrole_int.contact_orig_system_reference = con_mosr.orig_system_reference (+)
2544 and conrole_int.contact_orig_system = con_mosr.orig_system (+)
2545 and conrole_int.contact_orig_system_reference = cont_sg.contact_orig_system_reference (+)
2546 and conrole_int.contact_orig_system = cont_sg.contact_orig_system (+)
2547 and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2548 and party_mosr.status (+) = 'A'
2549 and conrole_int.sub_orig_system_reference = party_mosr.orig_system_reference (+)
2550 and conrole_int.sub_orig_system = party_mosr.orig_system (+)
2551 and conrole_int.sub_orig_system_reference = party_sg.party_orig_system_reference (+)
2552 and conrole_int.sub_orig_system = party_sg.party_orig_system (+)
2553 ) conr_w_cid
2554 where conr_w_cid.contact_id = hz_conr.org_contact_id (+)
2555 and conr_w_cid.role_type = hz_conr.role_type (+)
2556 );
2557 else
2558 INSERT INTO HZ_IMP_CONTACTROLES_SG
2559 (
2560 SUB_ORIG_SYSTEM,
2561 SUB_ORIG_SYSTEM_REFERENCE,
2562 CONTACT_ID,
2563 CONTACT_ROLE_ID,
2564 INT_ROW_ID,
2565 ACTION_FLAG,
2566 ERROR_FLAG,
2567 BATCH_MODE_FLAG, BATCH_ID
2568 )
2569 (
2570 select /*+ leading(conr_w_cid) use_nl(hz_conr) */
2571 sos, sosr,
2572 conr_w_cid.contact_id contact_id,
2573 nvl(hz_conr.org_contact_role_id, hz_org_contact_roles_S.NextVal) conr_id,
2577 'I', decode(conr_w_cid.sub_id, conr_w_cid.cont_sub_id, null, 2),
2574 conr_w_cid.int_row_id int_row_id,
2575 nvl2(hz_conr.org_contact_role_id, 'U', 'I') action_flag,
2576 decode(conr_w_cid.cont_action_flag,
2578 null) error_flag,
2579 P_BATCH_MODE_FLAG, P_BATCH_ID
2580 from hz_org_contact_roles hz_conr,
2581 (
2582 select /*+ no_merge leading(conrole_int) index_asc(conrole_int)
2583 use_nl(cont_sg,con_mosr,party_mosr) use_hash(party_sg) */
2584 conrole_int.sub_orig_system sos,
2585 conrole_int.sub_orig_system_reference sosr,
2586 nvl(cont_sg.contact_id, con_mosr.owner_table_id) contact_id,
2587 conrole_int.rowid int_row_id,
2588 nvl(party_sg.party_id, party_mosr.owner_table_id) sub_id,
2589 cont_sg.sub_id cont_sub_id,
2590 cont_sg.action_flag cont_action_flag,
2591 conrole_int.role_type role_type
2592 from hz_imp_contactroles_int conrole_int,
2593 hz_imp_contacts_sg cont_sg,
2594 hz_orig_sys_references con_mosr,
2595 hz_imp_parties_sg party_sg,
2596 hz_orig_sys_references party_mosr--,
2597 where conrole_int.batch_id = P_BATCH_ID
2598 and conrole_int.sub_orig_system = P_OS
2599 and conrole_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2600 and conrole_int.interface_status is null
2601 and conrole_int.batch_id = party_sg.batch_id(+)
2602 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2603 and party_sg.party_orig_system (+) = P_OS
2604 and party_sg.batch_id(+) = P_BATCH_ID
2605 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2606 and party_sg.action_flag(+)='I'
2607 and conrole_int.batch_id = cont_sg.batch_id(+)
2608 and cont_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2609 and cont_sg.action_flag(+)='I'
2610 and con_mosr.owner_table_name (+) = 'HZ_ORG_CONTACTS'
2611 and con_mosr.status (+) = 'A'
2612 and conrole_int.contact_orig_system_reference = con_mosr.orig_system_reference (+)
2613 and conrole_int.contact_orig_system = con_mosr.orig_system (+)
2614 and conrole_int.contact_orig_system_reference = cont_sg.contact_orig_system_reference (+)
2615 and conrole_int.contact_orig_system = cont_sg.contact_orig_system (+)
2616 and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2617 and party_mosr.status (+) = 'A'
2618 and conrole_int.sub_orig_system_reference = party_mosr.orig_system_reference (+)
2619 and conrole_int.sub_orig_system = party_mosr.orig_system (+)
2620 and conrole_int.sub_orig_system_reference = party_sg.party_orig_system_reference (+)
2621 and conrole_int.sub_orig_system = party_sg.party_orig_system (+)
2622 ) conr_w_cid
2623 where conr_w_cid.contact_id = hz_conr.org_contact_id (+)
2624 and conr_w_cid.role_type = hz_conr.role_type (+)
2625 );
2626 end if;
2627 commit;
2628 end match_contactroles;
2629
2630
2631 PROCEDURE match_addruses(
2632 P_BATCH_ID IN NUMBER,
2633 P_OS IN VARCHAR2,
2634 P_FROM_OSR IN VARCHAR2,
2635 P_TO_OSR IN VARCHAR2,
2636 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
2637 P_RERUN IN VARCHAR2,
2638 P_BATCH_MODE_FLAG IN VARCHAR2
2639 ) IS
2640
2641 begin
2642 if (P_RERUN = 'Y') then
2643 INSERT INTO HZ_IMP_ADDRESSUSES_SG
2644 ( SITE_USE_TYPE,
2645 PARTY_ORIG_SYSTEM,
2646 PARTY_ORIG_SYSTEM_REFERENCE,
2647 PARTY_SITE_USE_ID,
2648 PARTY_SITE_ID,
2649 INT_ROW_ID,
2653 PRIMARY_FLAG
2650 ACTION_FLAG,
2651 ERROR_FLAG,
2652 BATCH_MODE_FLAG, BATCH_ID,
2654 )
2655 (
2656 select /*+ leading(addruse_w_id) use_nl(hz_psuse) */
2657 addruse_w_id.site_use_type, pos, posr,
2658 nvl(hz_psuse.party_site_use_id, hz_party_site_uses_s.nextval)
2659 siteusr_id, addruse_w_id.site_id, addruse_w_id.int_row_id,
2660 nvl2(hz_psuse.party_site_use_id, 'U', 'I') action_flag,
2661 decode(addruse_w_id.addr_action_flag, 'I', decode(
2662 addruse_w_id.party_id, addruse_w_id.addr_party_id, null, 2), null)
2663 error_flag, P_BATCH_MODE_FLAG, P_BATCH_ID, decode(row_number() over
2664 (partition by addruse_w_id.party_id, addruse_w_id.site_use_type
2665 order by nvl2(hz_psuse.party_site_use_id, hz_psuse.primary_per_type,
2666 addruse_w_id.primary_flag) desc nulls last,
2667 hz_psuse.party_site_use_id nulls last), 1, decode((
2668 select count(*)
2669 from hz_party_sites hz_ps,
2670 hz_party_site_uses hz_ps_use
2671 where hz_ps.party_id = addruse_w_id.party_id
2672 and hz_ps.party_site_id = hz_ps_use.party_site_id
2673 and hz_ps_use.site_use_type = addruse_w_id.site_use_type
2674 and hz_ps_use.primary_per_type = 'Y'
2675 and hz_ps_use.status = 'A'
2676 and rownum < 2), 0, 'Y')) primary_flag
2677 from hz_party_site_uses hz_psuse,
2678 (
2679 select /*+ no_merge ordered index_asc(addruse_int) index(addr_sg,HZ_IMP_ADDRESSES_SG_N2)
2680 use_nl(addr_sg,addr_mosr,party_mosr) use_hash(party_sg) */
2681 addruse_int.party_orig_system pos,
2682 addruse_int.party_orig_system_reference posr,
2683 nvl(addr_sg.party_site_id, addr_mosr.owner_table_id)
2684 site_id, addruse_int.rowid int_row_id, nvl(
2685 party_sg.party_id, party_mosr.owner_table_id) party_id,
2686 addr_sg.party_id addr_party_id, addruse_int.site_use_type,
2687 nvl(addr_sg.action_flag, 'U') addr_action_flag,
2688 addruse_int.primary_flag
2689 from hz_imp_addressuses_int addruse_int,
2690 hz_imp_parties_sg party_sg,
2691 hz_imp_addresses_sg addr_sg,
2692 hz_orig_sys_references addr_mosr,
2693 hz_orig_sys_references party_mosr
2694 where addruse_int.batch_id = P_BATCH_ID
2695 and addruse_int.party_orig_system = P_OS
2696 and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2697 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2698 and party_sg.party_orig_system (+) = P_OS
2699 and party_sg.batch_id(+) = P_BATCH_ID
2700 and addruse_int.interface_status = 'C'
2701 and addruse_int.batch_id = party_sg.batch_id(+)
2702 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2703 and party_sg.action_flag(+)='I'
2704 and addruse_int.batch_id = addr_sg.batch_id(+)
2705 and addr_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2706 and addr_sg.action_flag(+)='I'
2707 and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
2708 and addr_mosr.status (+) = 'A'
2709 and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2710 and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2711 and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)
2712 and addruse_int.site_orig_system = addr_sg.site_orig_system (+)
2713 and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2714 and party_mosr.status (+) = 'A'
2715 and addruse_int.party_orig_system_reference = party_mosr.orig_system_reference (+)
2716 and addruse_int.party_orig_system = party_mosr.orig_system (+)
2717 and addruse_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
2718 and addruse_int.party_orig_system = party_sg.party_orig_system (+)
2719 ) addruse_w_id
2720 where addruse_w_id.site_id = hz_psuse.party_site_id (+)
2721 and addruse_w_id.site_use_type = hz_psuse.site_use_type (+)
2722 );
2723 else
2724 INSERT INTO HZ_IMP_ADDRESSUSES_SG
2725 ( SITE_USE_TYPE,
2726 PARTY_ORIG_SYSTEM,
2727 PARTY_ORIG_SYSTEM_REFERENCE,
2728 PARTY_SITE_USE_ID,
2729 PARTY_SITE_ID,
2730 INT_ROW_ID,
2731 ACTION_FLAG,
2732 ERROR_FLAG,
2733 BATCH_MODE_FLAG, BATCH_ID,
2734 PRIMARY_FLAG
2735 )
2736 (
2737 select /*+ leading(addruse_w_id) use_nl(hz_psuse) */
2738 addruse_w_id.site_use_type, pos, posr,
2739 nvl(hz_psuse.party_site_use_id, hz_party_site_uses_s.nextval)
2740 siteusr_id, addruse_w_id.site_id, addruse_w_id.int_row_id,
2741 nvl2(hz_psuse.party_site_use_id, 'U', 'I') action_flag,
2742 decode(addruse_w_id.addr_action_flag, 'I', decode(
2743 addruse_w_id.party_id, addruse_w_id.addr_party_id, null, 2), null)
2747 addruse_w_id.primary_flag) desc nulls last,
2744 error_flag, P_BATCH_MODE_FLAG, P_BATCH_ID, decode(row_number() over
2745 (partition by addruse_w_id.party_id, addruse_w_id.site_use_type
2746 order by nvl2(hz_psuse.party_site_use_id, hz_psuse.primary_per_type,
2748 hz_psuse.party_site_use_id nulls last), 1, decode((
2749 select count(*)
2750 from hz_party_sites hz_ps,
2751 hz_party_site_uses hz_ps_use
2752 where hz_ps.party_id = addruse_w_id.party_id
2753 and hz_ps.party_site_id = hz_ps_use.party_site_id
2754 and hz_ps_use.site_use_type = addruse_w_id.site_use_type
2755 and hz_ps_use.primary_per_type = 'Y'
2756 and hz_ps_use.status = 'A'
2757 and rownum < 2), 0, 'Y')) primary_flag
2758 from hz_party_site_uses hz_psuse,
2759 (
2760 select /*+ no_merge ordered index_asc(addruse_int) index(addr_sg,HZ_IMP_ADDRESSES_SG_N2)
2761 use_nl(addr_sg,addr_mosr,party_mosr) use_hash(party_sg) */
2762 addruse_int.party_orig_system pos,
2763 addruse_int.party_orig_system_reference posr,
2764 nvl(addr_sg.party_site_id, addr_mosr.owner_table_id)
2765 site_id, addruse_int.rowid int_row_id, nvl(
2766 party_sg.party_id, party_mosr.owner_table_id) party_id,
2767 addr_sg.party_id addr_party_id, addruse_int.site_use_type,
2768 nvl(addr_sg.action_flag, 'U') addr_action_flag,
2769 addruse_int.primary_flag
2770 from hz_imp_addressuses_int addruse_int,
2771 hz_imp_parties_sg party_sg,
2772 hz_imp_addresses_sg addr_sg,
2773 hz_orig_sys_references addr_mosr,
2774 hz_orig_sys_references party_mosr
2775 where addruse_int.batch_id = P_BATCH_ID
2776 and addruse_int.party_orig_system = P_OS
2777 and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2778 and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2779 and party_sg.party_orig_system (+) = P_OS
2780 and party_sg.batch_id(+) = P_BATCH_ID
2781 and addruse_int.interface_status is null
2782 and addruse_int.batch_id = party_sg.batch_id(+)
2783 and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2784 and party_sg.action_flag(+)='I'
2785 and addruse_int.batch_id = addr_sg.batch_id(+)
2786 and addr_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2787 and addr_sg.action_flag(+)='I'
2788 and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
2789 and addr_mosr.status (+) = 'A'
2790 and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2791 and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2792 and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)
2793 and addruse_int.site_orig_system = addr_sg.site_orig_system (+)
2794 and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2795 and party_mosr.status (+) = 'A'
2796 and addruse_int.party_orig_system_reference = party_mosr.orig_system_reference (+)
2797 and addruse_int.party_orig_system = party_mosr.orig_system (+)
2798 and addruse_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
2799 and addruse_int.party_orig_system = party_sg.party_orig_system (+)
2800 ) addruse_w_id
2801 where addruse_w_id.site_id = hz_psuse.party_site_id (+)
2802 and addruse_w_id.site_use_type = hz_psuse.site_use_type (+)
2803 );
2804 end if;
2805 commit;
2806 end match_addruses;
2807
2808
2809 END HZ_IMP_LOAD_SSM_MATCHING_PKG;