[Home] [Help]
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 (+)
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
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 (+)
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
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
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
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,
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
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,
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
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,
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,
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
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,
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,
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
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
894: cr_int.rated_as_of_date, cr_int.rating_organization
895: rating_org, cr_int.rowid int_row_id
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
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
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
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
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
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
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
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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
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'
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,
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
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
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
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'
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
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
2658: addruse_int.primary_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
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
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
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