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