[Home] [Help]
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,
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,
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'
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
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,
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,
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'
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
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,
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
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,
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;
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
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 (+)
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'
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
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 (+)
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'
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 (+)
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 (+)
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
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'
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 (+)
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 (+)
782: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
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'
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'
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
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'
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
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
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 (+)
1005: and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
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 (+)
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'
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')
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 (+)
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'
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 ) =
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 (+)
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 */
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'
1211: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1212: /* bug 4079902 */
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'
1216: and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
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')
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 (+)
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'
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 ) =
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
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) =
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
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) =
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
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
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
1782: and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1783: and party_sg.action_flag(+) = 'I'
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
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'
1895: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
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'
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
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'
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
1898: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
1899: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1892: and mosr.status (+) = 'A'
1893: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1894: and mosr2.status (+) = 'A'
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
1898: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
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
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
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'
2000: and mosr2.status (+) = 'A'
2001: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
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'
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'
2003: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1997: where mosr.owner_table_name (+) = 'HZ_PARTIES'
1998: and mosr.status (+) = 'A'
1999: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
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'
2003: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
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
1998: and mosr.status (+) = 'A'
1999: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
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'
2003: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
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
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
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'
2106: and mosr2.status (+) = 'A'
2107: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
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'
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'
2109: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2103: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2104: and mosr.status (+) = 'A'
2105: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
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'
2109: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
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
2104: and mosr.status (+) = 'A'
2105: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
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'
2109: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
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
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
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'
2213: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
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'
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
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'
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
2216: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2217: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2210: and mosr.status (+) = 'A'
2211: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2212: and mosr2.status (+) = 'A'
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
2216: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
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
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;
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(+)
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 (+)
2433: and cont_int.batch_id = sub_int.batch_id(+)
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 (+)
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 (+)
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 (+)
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 (+)