[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: and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
414: ) ps_int_w_psosr,
415: hz_parties p
411: and osr_change_tbl.entity_name (+) = 'HZ_PARTIES'
412: and osr_change_tbl.batch_id (+) = P_BATCH_ID
413: and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
414: ) ps_int_w_psosr,
415: hz_parties p
416: where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
417: and ps_int_w_psosr.final_psosr =
418: psosr_mosr.orig_system_reference (+)
419: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
520: from hz_imp_addresses_int ps_int,
521: hz_imp_parties_sg psg,
522: hz_orig_sys_references mosr,
523: hz_imp_osr_change osr_change_tbl
524: where mosr.owner_table_name (+) = 'HZ_PARTIES'
525: and mosr.status (+) = 'A'
526: and ps_int.batch_id = P_BATCH_ID
527: and ps_int.party_orig_system = P_OS
528: and ps_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
537: and ps_int.party_orig_system_reference = mosr.orig_system_reference (+)
538: and ps_int.party_orig_system = mosr.orig_system (+)
539: and ps_int.interface_status is null
540: and ps_int.party_orig_system_reference = osr_change_tbl.new_orig_system_reference (+)
541: and osr_change_tbl.entity_name (+) = 'HZ_PARTIES'
542: and osr_change_tbl.batch_id (+) = P_BATCH_ID
543: and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
544: ) ps_int_w_psosr,
545: hz_parties p
541: and osr_change_tbl.entity_name (+) = 'HZ_PARTIES'
542: and osr_change_tbl.batch_id (+) = P_BATCH_ID
543: and osr_change_tbl.entity_id (+) = ps_int.party_id /* Bug 5383200 */
544: ) ps_int_w_psosr,
545: hz_parties p
546: where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
547: and ps_int_w_psosr.final_psosr =
548: psosr_mosr.orig_system_reference (+)
549: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
640: and cp_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
641: and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
642: and psg.party_orig_system (+) = P_OS
643: and psg.batch_id(+) = P_BATCH_ID
644: and party_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
645: and party_osr_ch_tbl.batch_id (+) = P_BATCH_ID
646: and party_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cp_int.party_orig_system_reference
647: and party_osr_ch_tbl.entity_id (+) = cp_int.party_id /* Bug 5383200 */
648: and cp_int.party_orig_system_reference = psg.party_orig_system_reference (+)
651: and psg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
652: and psg.action_flag (+) = 'I'
653: and cp_int.party_orig_system_reference = mosr.orig_system_reference (+)
654: and cp_int.party_orig_system = mosr.orig_system (+)
655: and mosr.owner_table_name (+) = 'HZ_PARTIES'
656: and mosr.status (+) = 'A'
657: ) cpi_cosr
658: where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
659: and cpi_cosr.los = asg.site_orig_system (+)
675: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
676: and ps_mosr.status (+) = 'A'
677: and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
678: and cp_mosr.status (+) = 'A'
679: and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'
680: and hz_cpt_pri.owner_table_id (+) = cpi_cosr.pid
681: and hz_cpt_pri.contact_point_type (+) = cpi_cosr.contact_point_type
682: and hz_cpt_pri.primary_flag (+) = 'Y'
683: and hz_cpt_pri.status (+) = 'A'
753: and cp_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
754: and psg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
755: and psg.party_orig_system (+) = P_OS
756: and psg.batch_id(+) = P_BATCH_ID
757: and party_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
758: and party_osr_ch_tbl.batch_id (+) = P_BATCH_ID
759: and party_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cp_int.party_orig_system_reference
760: and party_osr_ch_tbl.entity_id (+) = cp_int.party_id /* Bug 5383200 */
761: and cp_int.party_orig_system_reference = psg.party_orig_system_reference (+)
764: and psg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
765: and psg.action_flag (+) = 'I'
766: and cp_int.party_orig_system_reference = mosr.orig_system_reference (+)
767: and cp_int.party_orig_system = mosr.orig_system (+)
768: and mosr.owner_table_name (+) = 'HZ_PARTIES'
769: and mosr.status (+) = 'A'
770: ) cpi_cosr
771: where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
772: and cpi_cosr.los = asg.site_orig_system (+)
788: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
789: and ps_mosr.status (+) = 'A'
790: and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
791: and cp_mosr.status (+) = 'A'
792: and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'
793: and hz_cpt_pri.owner_table_id (+) = cpi_cosr.pid
794: and hz_cpt_pri.contact_point_type (+) = cpi_cosr.contact_point_type
795: and hz_cpt_pri.primary_flag (+) = 'Y'
796: and hz_cpt_pri.status (+) = 'A'
848: where cr_int.interface_status = 'C'
849: and cr_int.batch_id = P_BATCH_ID
850: and cr_int.party_orig_system = P_OS
851: and cr_int.batch_id = party_sg.batch_id(+)
852: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
853: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
854: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cr_int.party_orig_system_reference
855: and osr_ch_tbl.entity_id (+) = cr_int.party_id /* Bug 5383200 */
856: and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
860: and party_sg.party_orig_system (+) = P_OS
861: and party_sg.batch_id(+) = P_BATCH_ID
862: and cr_int.party_orig_system_reference = mosr.orig_system_reference (+)
863: and cr_int.party_orig_system = mosr.orig_system (+)
864: and mosr.owner_table_name (+) = 'HZ_PARTIES'
865: and mosr.status (+) = 'A'
866: and cr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
867: and cr_int.party_orig_system = party_sg.party_orig_system (+)
868: ) cr_int_w_pid
907: where cr_int.interface_status is null
908: and cr_int.batch_id = P_BATCH_ID
909: and cr_int.party_orig_system = P_OS
910: and cr_int.batch_id = party_sg.batch_id(+)
911: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
912: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
913: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cr_int.party_orig_system_reference
914: and osr_ch_tbl.entity_id (+) = cr_int.party_id /* Bug 5383200 */
915: and party_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
919: and party_sg.party_orig_system (+) = P_OS
920: and party_sg.batch_id(+) = P_BATCH_ID
921: and cr_int.party_orig_system_reference = mosr.orig_system_reference (+)
922: and cr_int.party_orig_system = mosr.orig_system (+)
923: and mosr.owner_table_name (+) = 'HZ_PARTIES'
924: and mosr.status (+) = 'A'
925: and cr_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
926: and cr_int.party_orig_system = party_sg.party_orig_system (+)
927: ) cr_int_w_pid
1002: and ca_int.batch_id = p_batch_id
1003: and ca_int.party_orig_system = p_os
1004: and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1005: and ca_int.batch_id = party_sg.batch_id (+)
1006: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1007: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1008: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1009: and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1010: and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1012: and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1013: and ca_int.party_orig_system = party_sg.party_orig_system (+)
1014: and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1015: and ca_int.party_orig_system = mosr.orig_system (+)
1016: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1017: and mosr.status (+) = 'A'
1018: ) ca_int_w_pid
1019: where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1020: --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1027: --wawong: ignore start data for DNB data
1028: --and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1029: and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1030:
1031: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1032: /* Bug 4979902 */
1033: --and hz_ca.content_source_type (+) = p_actual_content_src
1034: and hz_ca.actual_content_source (+) = p_actual_content_src
1035: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1031: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1032: /* Bug 4979902 */
1033: --and hz_ca.content_source_type (+) = p_actual_content_src
1034: and hz_ca.actual_content_source (+) = p_actual_content_src
1035: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1036: and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1037: and nvl(hz_ca3.end_date_active (+),c_end_date ) = c_end_date
1038: and (case when hz_ca3.class_category (+)
1039: in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1098: and ca_int.batch_id = p_batch_id
1099: and ca_int.party_orig_system = p_os
1100: and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1101: and ca_int.batch_id = party_sg.batch_id (+)
1102: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1103: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1104: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1105: and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1106: and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1108: and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1109: and ca_int.party_orig_system = party_sg.party_orig_system (+)
1110: and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1111: and ca_int.party_orig_system = mosr.orig_system (+)
1112: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1113: and mosr.status (+) = 'A'
1114: ) ca_int_w_pid
1115: where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1116: --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1123: --wawong: ignore start data for DNB data
1124: and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1125: --and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1126:
1127: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1128: /* Bug 4979902 */
1129: --and hz_ca.content_source_type (+) = p_actual_content_src
1130: and hz_ca.actual_content_source (+) = p_actual_content_src
1131: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1127: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1128: /* Bug 4979902 */
1129: --and hz_ca.content_source_type (+) = p_actual_content_src
1130: and hz_ca.actual_content_source (+) = p_actual_content_src
1131: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1132: and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1133: and (case when hz_ca3.class_category (+)
1134: in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1135: then 'SIC' else hz_ca3.class_category (+) end ) =
1193: and ca_int.batch_id = p_batch_id
1194: and ca_int.party_orig_system = p_os
1195: and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1196: and ca_int.batch_id = party_sg.batch_id (+)
1197: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1198: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1199: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1200: and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1201: and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1203: and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1204: and ca_int.party_orig_system = party_sg.party_orig_system (+)
1205: and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1206: and ca_int.party_orig_system = mosr.orig_system (+)
1207: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1208: and mosr.status (+) = 'A'
1209: ) ca_int_w_pid
1210: where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1211: --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1218: --wawong: ignore start data for DNB data
1219: --and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1220: and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1221:
1222: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1223: /* bug 4079902 */
1224: --and hz_ca.content_source_type (+) = p_actual_content_src
1225: and hz_ca.actual_content_source (+) = p_actual_content_src
1226: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1222: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1223: /* bug 4079902 */
1224: --and hz_ca.content_source_type (+) = p_actual_content_src
1225: and hz_ca.actual_content_source (+) = p_actual_content_src
1226: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1227: and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1228: and nvl(hz_ca3.end_date_active (+),c_end_date ) = c_end_date
1229: and (case when hz_ca3.class_category (+)
1230: in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1289: and ca_int.batch_id = p_batch_id
1290: and ca_int.party_orig_system = p_os
1291: and ca_int.party_orig_system_reference between p_from_osr and p_to_osr
1292: and ca_int.batch_id = party_sg.batch_id (+)
1293: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1294: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1295: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ca_int.party_orig_system_reference
1296: and osr_ch_tbl.entity_id (+) = ca_int.party_id /* Bug 5383200 */
1297: and party_sg.batch_mode_flag (+) = p_batch_mode_flag
1299: and ca_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
1300: and ca_int.party_orig_system = party_sg.party_orig_system (+)
1301: and ca_int.party_orig_system_reference = mosr.orig_system_reference (+)
1302: and ca_int.party_orig_system = mosr.orig_system (+)
1303: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1304: and mosr.status (+) = 'A'
1305: ) ca_int_w_pid
1306: where ca_int_w_pid.pid = hz_ca.owner_table_id (+)
1307: --and ca_int_w_pid.class_category = hz_ca.class_category (+)
1314: --wawong: ignore start data for DNB data
1315: and trunc(ca_int_w_pid.start_date_active) = trunc(hz_ca.start_date_active (+))
1316: --and nvl(hz_ca.end_date_active (+),c_end_date ) = c_end_date
1317:
1318: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1319: /* Bug 4979902 */
1320: --and hz_ca.content_source_type (+) = p_actual_content_src
1321: and hz_ca.actual_content_source (+) = p_actual_content_src
1322: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1318: and hz_ca.owner_table_name (+) = 'HZ_PARTIES'
1319: /* Bug 4979902 */
1320: --and hz_ca.content_source_type (+) = p_actual_content_src
1321: and hz_ca.actual_content_source (+) = p_actual_content_src
1322: and hz_ca3.owner_table_name (+) = 'HZ_PARTIES'
1323: and hz_ca3.owner_table_id (+) = ca_int_w_pid.pid
1324: and (case when hz_ca3.class_category (+)
1325: in ('1972 SIC', '1977 SIC', '1987 SIC', 'NAICS_1997')
1326: then 'SIC' else hz_ca3.class_category (+) end ) =
1414: where fr_int.interface_status = 'C'
1415: and fr_int.batch_id = P_BATCH_ID
1416: and fr_int.party_orig_system = P_OS
1417: and fr_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1418: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1419: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1420: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fr_int.party_orig_system_reference
1421: and osr_ch_tbl.entity_id (+) = fr_int.party_id /* Bug 5383200 */
1422: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1428: and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1429: and party_sg.action_flag(+) = 'I'
1430: and fr_int.party_orig_system_reference = mosr.orig_system_reference (+)
1431: and fr_int.party_orig_system = mosr.orig_system (+)
1432: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1433: and mosr.status (+) = 'A'
1434: ) fr_int_w_pid
1435: where fr_int_w_pid.pid = hz_fr1.PARTY_ID (+)
1436: and nvl(trunc(fr_int_w_pid.DATE_REPORT_ISSUED), c_end_date) =
1510: where fr_int.interface_status is null
1511: and fr_int.batch_id = P_BATCH_ID
1512: and fr_int.party_orig_system = P_OS
1513: and fr_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1514: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1515: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1516: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fr_int.party_orig_system_reference
1517: and osr_ch_tbl.entity_id (+) = fr_int.party_id /* Bug 5383200 */
1518: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1524: and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1525: and party_sg.action_flag(+) = 'I'
1526: and fr_int.party_orig_system_reference = mosr.orig_system_reference (+)
1527: and fr_int.party_orig_system = mosr.orig_system (+)
1528: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1529: and mosr.status (+) = 'A'
1530: ) fr_int_w_pid
1531: where fr_int_w_pid.pid = hz_fr1.PARTY_ID (+)
1532: and nvl(trunc(fr_int_w_pid.DATE_REPORT_ISSUED), c_end_date) =
1648: where fn_int.interface_status = 'C'
1649: and fn_int.batch_id = P_BATCH_ID
1650: and fn_int.party_orig_system = P_OS
1651: and fn_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1652: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1653: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1654: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fn_int.party_orig_system_reference
1655: and osr_ch_tbl.entity_id (+) = fn_int.party_id /* Bug 5383200 */
1656: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1662: and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1663: and party_sg.action_flag(+) = 'I'
1664: and fn_int.party_orig_system_reference = mosr.orig_system_reference (+)
1665: and fn_int.party_orig_system = mosr.orig_system (+)
1666: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1667: and mosr.status (+) = 'A'
1668: ) fi_pid,
1669: hz_imp_finreports_sg frsg,
1670: hz_financial_reports fr
1784: where fn_int.interface_status is null
1785: and fn_int.batch_id = P_BATCH_ID
1786: and fn_int.party_orig_system = P_OS
1787: and fn_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
1788: and osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1789: and osr_ch_tbl.batch_id (+) = P_BATCH_ID
1790: and osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = fn_int.party_orig_system_reference
1791: and osr_ch_tbl.entity_id (+) = fn_int.party_id /* Bug 5383200 */
1792: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
1798: and party_sg.batch_mode_flag(+) = P_BATCH_MODE_FLAG
1799: and party_sg.action_flag(+) = 'I'
1800: and fn_int.party_orig_system_reference = mosr.orig_system_reference (+)
1801: and fn_int.party_orig_system = mosr.orig_system (+)
1802: and mosr.owner_table_name (+) = 'HZ_PARTIES'
1803: and mosr.status (+) = 'A'
1804: ) fi_pid,
1805: hz_imp_finreports_sg frsg,
1806: hz_financial_reports fr
1904: hz_imp_osr_change sub_osr_ch_tbl,
1905: hz_imp_osr_change obj_osr_ch_tbl,
1906: hz_imp_parties_int sub_int,
1907: hz_imp_parties_int obj_int
1908: where mosr.owner_table_name (+) = 'HZ_PARTIES'
1909: and mosr.status (+) = 'A'
1910: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1911: and mosr2.status (+) = 'A'
1912: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1906: hz_imp_parties_int sub_int,
1907: hz_imp_parties_int obj_int
1908: where mosr.owner_table_name (+) = 'HZ_PARTIES'
1909: and mosr.status (+) = 'A'
1910: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1911: and mosr2.status (+) = 'A'
1912: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1913: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1914: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1908: where mosr.owner_table_name (+) = 'HZ_PARTIES'
1909: and mosr.status (+) = 'A'
1910: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1911: and mosr2.status (+) = 'A'
1912: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1913: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1914: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1915: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
1916: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1909: and mosr.status (+) = 'A'
1910: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
1911: and mosr2.status (+) = 'A'
1912: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1913: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
1914: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1915: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
1916: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
1917: and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
1954: and ri_ids.relationship_code = hz_rel.relationship_code (+)
1955: -- for DNB, ignore start date, filter out end-dated records
1956: and nvl(hz_rel.END_DATE (+),l_no_end_date ) = l_no_end_date
1957: and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
1958: and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
1959: and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
1960:
1961: );
1962: elsif (P_ACTUAL_CONTENT_SRC = 'USER_ENTERED' and P_RERUN = 'Y') then
2014: hz_imp_osr_change sub_osr_ch_tbl,
2015: hz_imp_osr_change obj_osr_ch_tbl,
2016: hz_imp_parties_int sub_int,
2017: hz_imp_parties_int obj_int
2018: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2019: and mosr.status (+) = 'A'
2020: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2021: and mosr2.status (+) = 'A'
2022: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2016: hz_imp_parties_int sub_int,
2017: hz_imp_parties_int obj_int
2018: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2019: and mosr.status (+) = 'A'
2020: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2021: and mosr2.status (+) = 'A'
2022: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2023: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2024: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2018: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2019: and mosr.status (+) = 'A'
2020: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2021: and mosr2.status (+) = 'A'
2022: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2023: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2024: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2025: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2026: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2019: and mosr.status (+) = 'A'
2020: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2021: and mosr2.status (+) = 'A'
2022: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2023: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2024: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2025: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2026: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2027: and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2064: and ri_ids.relationship_code = hz_rel.relationship_code (+)
2065: -- use start date as key for non-DNB data
2066: and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2067: and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2068: and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2069: and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2070:
2071: );
2072: elsif (P_ACTUAL_CONTENT_SRC <> 'USER_ENTERED' and P_RERUN <> 'Y') then
2124: hz_imp_osr_change sub_osr_ch_tbl,
2125: hz_imp_osr_change obj_osr_ch_tbl,
2126: hz_imp_parties_int sub_int,
2127: hz_imp_parties_int obj_int
2128: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2129: and mosr.status (+) = 'A'
2130: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2131: and mosr2.status (+) = 'A'
2132: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2126: hz_imp_parties_int sub_int,
2127: hz_imp_parties_int obj_int
2128: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2129: and mosr.status (+) = 'A'
2130: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2131: and mosr2.status (+) = 'A'
2132: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2133: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2134: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2128: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2129: and mosr.status (+) = 'A'
2130: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2131: and mosr2.status (+) = 'A'
2132: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2133: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2134: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2135: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2136: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2129: and mosr.status (+) = 'A'
2130: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2131: and mosr2.status (+) = 'A'
2132: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2133: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2134: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2135: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2136: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2137: and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2174: and ri_ids.relationship_code = hz_rel.relationship_code (+)
2175: --and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2176: and nvl(hz_rel.END_DATE (+),l_no_end_date ) = l_no_end_date
2177: and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2178: and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2179: and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2180: );
2181: else
2182: -- first run/non-DNB
2233: hz_imp_osr_change sub_osr_ch_tbl,
2234: hz_imp_osr_change obj_osr_ch_tbl,
2235: hz_imp_parties_int sub_int,
2236: hz_imp_parties_int obj_int
2237: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2238: and mosr.status (+) = 'A'
2239: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2240: and mosr2.status (+) = 'A'
2241: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2235: hz_imp_parties_int sub_int,
2236: hz_imp_parties_int obj_int
2237: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2238: and mosr.status (+) = 'A'
2239: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2240: and mosr2.status (+) = 'A'
2241: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2242: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2243: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2237: where mosr.owner_table_name (+) = 'HZ_PARTIES'
2238: and mosr.status (+) = 'A'
2239: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2240: and mosr2.status (+) = 'A'
2241: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2242: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2243: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2244: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2245: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2238: and mosr.status (+) = 'A'
2239: and mosr2.owner_table_name (+) = 'HZ_PARTIES'
2240: and mosr2.status (+) = 'A'
2241: and sub_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2242: and obj_osr_ch_tbl.entity_name (+) = 'HZ_PARTIES'
2243: and sub_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2244: and sub_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.sub_orig_system_reference
2245: and obj_osr_ch_tbl.batch_id (+) = P_BATCH_ID
2246: and obj_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = ri.obj_orig_system_reference
2282: and ri_ids.relationship_type = hz_rel.relationship_type (+)
2283: and ri_ids.relationship_code = hz_rel.relationship_code (+)
2284: and trunc(ri_ids.START_DATE) = trunc(hz_rel.START_DATE (+))
2285: and hz_rel.ACTUAL_CONTENT_SOURCE (+) = P_ACTUAL_CONTENT_SRC
2286: and hz_rel.subject_table_name (+) = 'HZ_PARTIES'
2287: and ri_ids.subject_osr_change IS NULL /* Bug 5383200 */
2288: );
2289: end if;
2290: commit;
2364: and cont_int.contact_orig_system_reference = cont_mosr.orig_system_reference (+)
2365: and cont_int.contact_orig_system = cont_mosr.orig_system (+)
2366: and cont_int.sub_orig_system_reference = sub_sg.party_orig_system_reference (+)
2367: and cont_int.sub_orig_system = sub_sg.party_orig_system (+)
2368: and sub_mosr.owner_table_name (+) = 'HZ_PARTIES'
2369: and sub_mosr.status (+) = 'A'
2370: and sub_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2371: and sub_sg.party_orig_system (+) = P_OS
2372: and sub_sg.batch_id(+) = P_BATCH_ID
2373: and cont_int.sub_orig_system_reference = sub_mosr.orig_system_reference (+)
2374: and cont_int.sub_orig_system = sub_mosr.orig_system (+)
2375: and cont_int.obj_orig_system_reference = obj_sg.party_orig_system_reference (+)
2376: and cont_int.obj_orig_system = obj_sg.party_orig_system (+)
2377: and obj_mosr.owner_table_name (+) = 'HZ_PARTIES'
2378: and obj_mosr.status (+) = 'A'
2379: and cont_int.obj_orig_system_reference = obj_mosr.orig_system_reference (+)
2380: and cont_int.obj_orig_system = obj_mosr.orig_system (+)
2381: and cont_int.batch_id = sub_int.batch_id(+)
2446: and cont_int.contact_orig_system_reference = cont_mosr.orig_system_reference (+)
2447: and cont_int.contact_orig_system = cont_mosr.orig_system (+)
2448: and cont_int.sub_orig_system_reference = sub_sg.party_orig_system_reference (+)
2449: and cont_int.sub_orig_system = sub_sg.party_orig_system (+)
2450: and sub_mosr.owner_table_name (+) = 'HZ_PARTIES'
2451: and sub_mosr.status (+) = 'A'
2452: and sub_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2453: and sub_sg.party_orig_system (+) = P_OS
2454: and sub_sg.batch_id(+) = P_BATCH_ID
2455: and cont_int.sub_orig_system_reference = sub_mosr.orig_system_reference (+)
2456: and cont_int.sub_orig_system = sub_mosr.orig_system (+)
2457: and cont_int.obj_orig_system_reference = obj_sg.party_orig_system_reference (+)
2458: and cont_int.obj_orig_system = obj_sg.party_orig_system (+)
2459: and obj_mosr.owner_table_name (+) = 'HZ_PARTIES'
2460: and obj_mosr.status (+) = 'A'
2461: and cont_int.obj_orig_system_reference = obj_mosr.orig_system_reference (+)
2462: and cont_int.obj_orig_system = obj_mosr.orig_system (+)
2463: and cont_int.batch_id = sub_int.batch_id(+)
2543: and conrole_int.contact_orig_system_reference = con_mosr.orig_system_reference (+)
2544: and conrole_int.contact_orig_system = con_mosr.orig_system (+)
2545: and conrole_int.contact_orig_system_reference = cont_sg.contact_orig_system_reference (+)
2546: and conrole_int.contact_orig_system = cont_sg.contact_orig_system (+)
2547: and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2548: and party_mosr.status (+) = 'A'
2549: and conrole_int.sub_orig_system_reference = party_mosr.orig_system_reference (+)
2550: and conrole_int.sub_orig_system = party_mosr.orig_system (+)
2551: and conrole_int.sub_orig_system_reference = party_sg.party_orig_system_reference (+)
2612: and conrole_int.contact_orig_system_reference = con_mosr.orig_system_reference (+)
2613: and conrole_int.contact_orig_system = con_mosr.orig_system (+)
2614: and conrole_int.contact_orig_system_reference = cont_sg.contact_orig_system_reference (+)
2615: and conrole_int.contact_orig_system = cont_sg.contact_orig_system (+)
2616: and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2617: and party_mosr.status (+) = 'A'
2618: and conrole_int.sub_orig_system_reference = party_mosr.orig_system_reference (+)
2619: and conrole_int.sub_orig_system = party_mosr.orig_system (+)
2620: and conrole_int.sub_orig_system_reference = party_sg.party_orig_system_reference (+)
2709: and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2710: and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2711: and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)
2712: and addruse_int.site_orig_system = addr_sg.site_orig_system (+)
2713: and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2714: and party_mosr.status (+) = 'A'
2715: and addruse_int.party_orig_system_reference = party_mosr.orig_system_reference (+)
2716: and addruse_int.party_orig_system = party_mosr.orig_system (+)
2717: and addruse_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)
2790: and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2791: and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2792: and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)
2793: and addruse_int.site_orig_system = addr_sg.site_orig_system (+)
2794: and party_mosr.owner_table_name (+) = 'HZ_PARTIES'
2795: and party_mosr.status (+) = 'A'
2796: and addruse_int.party_orig_system_reference = party_mosr.orig_system_reference (+)
2797: and addruse_int.party_orig_system = party_mosr.orig_system (+)
2798: and addruse_int.party_orig_system_reference = party_sg.party_orig_system_reference (+)