[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
492: (partition by ps_int_w_psosr.pid order by
493: ps_int_w_psosr.primary_flag
494: desc nulls last),
495: 1, 'Y', null)) primary_flag
496: from hz_orig_sys_references psosr_mosr,
497: hz_party_sites hz_ps,
498: (
499: select /*+ no_merge leading(ps_int) index_asc(ps_int) use_hash(psg) use_nl(mosr) */
500: ps_int.rowid int_row_id,
518: ps_int.primary_flag primary_flag,
519: nvl2(nullif(ps_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
520: from hz_imp_addresses_int ps_int,
521: hz_imp_parties_sg psg,
522: hz_orig_sys_references mosr,
523: hz_imp_osr_change osr_change_tbl
524: where mosr.owner_table_name (+) = 'HZ_PARTIES'
525: and mosr.status (+) = 'A'
526: and ps_int.batch_id = P_BATCH_ID
614: cpi_cosr.contact_point_type
615: from hz_imp_addresses_sg asg,
616: hz_imp_osr_change addr_osr_ch_tbl,
617: hz_contact_points hz_cpt_pri,
618: hz_orig_sys_references ps_mosr,
619: hz_orig_sys_references cp_mosr,
620: (
621: select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
622: cp_int.party_orig_system_reference posr,
615: from hz_imp_addresses_sg asg,
616: hz_imp_osr_change addr_osr_ch_tbl,
617: hz_contact_points hz_cpt_pri,
618: hz_orig_sys_references ps_mosr,
619: hz_orig_sys_references cp_mosr,
620: (
621: select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
622: cp_int.party_orig_system_reference posr,
623: cp_int.rowid int_row_id, cp_int.cp_orig_system cos,
631: cp_int.batch_id, cp_int.contact_point_type,
632: nvl2(nullif(cp_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
633: from hz_imp_contactpts_int cp_int,
634: hz_imp_parties_sg psg,
635: hz_orig_sys_references mosr,
636: hz_imp_osr_change party_osr_ch_tbl
637: where cp_int.interface_status = 'C'
638: and cp_int.batch_id = P_BATCH_ID
639: and cp_int.party_orig_system = P_OS
727: cpi_cosr.contact_point_type
728: from hz_imp_addresses_sg asg,
729: hz_imp_osr_change addr_osr_ch_tbl,
730: hz_contact_points hz_cpt_pri,
731: hz_orig_sys_references ps_mosr,
732: hz_orig_sys_references cp_mosr,
733: (
734: select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
735: cp_int.party_orig_system_reference posr,
728: from hz_imp_addresses_sg asg,
729: hz_imp_osr_change addr_osr_ch_tbl,
730: hz_contact_points hz_cpt_pri,
731: hz_orig_sys_references ps_mosr,
732: hz_orig_sys_references cp_mosr,
733: (
734: select /*+ no_merge leading(cp_int) index_asc(cp_int) use_hash(psg) use_nl(mosr) */
735: cp_int.party_orig_system_reference posr,
736: cp_int.rowid int_row_id, cp_int.cp_orig_system cos,
744: cp_int.batch_id, cp_int.contact_point_type,
745: nvl2(nullif(cp_int.party_id,mosr.owner_table_id),'Y',null) owning_pty_chg_flag
746: from hz_imp_contactpts_int cp_int,
747: hz_imp_parties_sg psg,
748: hz_orig_sys_references mosr,
749: hz_imp_osr_change party_osr_ch_tbl
750: where cp_int.interface_status is null
751: and cp_int.batch_id = P_BATCH_ID
752: and cp_int.party_orig_system = P_OS
842: cr_int.rated_as_of_date, cr_int.rating_organization
843: rating_org, cr_int.rowid int_row_id
844: from hz_imp_creditrtngs_int cr_int,
845: hz_imp_parties_sg party_sg,
846: hz_orig_sys_references mosr,
847: hz_imp_osr_change osr_ch_tbl
848: where cr_int.interface_status = 'C'
849: and cr_int.batch_id = P_BATCH_ID
850: and cr_int.party_orig_system = P_OS
901: cr_int.rated_as_of_date, cr_int.rating_organization
902: rating_org, cr_int.rowid int_row_id
903: from hz_imp_creditrtngs_int cr_int,
904: hz_imp_parties_sg party_sg,
905: hz_orig_sys_references mosr,
906: hz_imp_osr_change osr_ch_tbl
907: where cr_int.interface_status is null
908: and cr_int.batch_id = P_BATCH_ID
909: and cr_int.party_orig_system = P_OS
995: then 'SIC' else ca_int.class_category end class_category,
996: ca_int.primary_flag
997: from hz_imp_classifics_int ca_int,
998: hz_imp_parties_sg party_sg,
999: hz_orig_sys_references mosr,
1000: hz_imp_osr_change osr_ch_tbl
1001: where ca_int.interface_status = 'C'
1002: and ca_int.batch_id = p_batch_id
1003: and ca_int.party_orig_system = p_os
1091: then 'SIC' else ca_int.class_category end class_category,
1092: ca_int.primary_flag
1093: from hz_imp_classifics_int ca_int,
1094: hz_imp_parties_sg party_sg,
1095: hz_orig_sys_references mosr,
1096: hz_imp_osr_change osr_ch_tbl
1097: where ca_int.interface_status = 'C'
1098: and ca_int.batch_id = p_batch_id
1099: and ca_int.party_orig_system = p_os
1186: then 'SIC' else ca_int.class_category end class_category,
1187: ca_int.primary_flag
1188: from hz_imp_classifics_int ca_int,
1189: hz_imp_parties_sg party_sg,
1190: hz_orig_sys_references mosr,
1191: hz_imp_osr_change osr_ch_tbl
1192: where ca_int.interface_status is null
1193: and ca_int.batch_id = p_batch_id
1194: and ca_int.party_orig_system = p_os
1282: then 'SIC' else ca_int.class_category end class_category,
1283: ca_int.primary_flag
1284: from hz_imp_classifics_int ca_int,
1285: hz_imp_parties_sg party_sg,
1286: hz_orig_sys_references mosr,
1287: hz_imp_osr_change osr_ch_tbl
1288: where ca_int.interface_status is null
1289: and ca_int.batch_id = p_batch_id
1290: and ca_int.party_orig_system = p_os
1408: REPORT_START_DATE,
1409: fr_int.rowid int_row_id
1410: from hz_imp_finreports_int fr_int,
1411: hz_imp_parties_sg party_sg,
1412: hz_orig_sys_references mosr,
1413: hz_imp_osr_change osr_ch_tbl
1414: where fr_int.interface_status = 'C'
1415: and fr_int.batch_id = P_BATCH_ID
1416: and fr_int.party_orig_system = P_OS
1504: REPORT_START_DATE,
1505: fr_int.rowid int_row_id
1506: from hz_imp_finreports_int fr_int,
1507: hz_imp_parties_sg party_sg,
1508: hz_orig_sys_references mosr,
1509: hz_imp_osr_change osr_ch_tbl
1510: where fr_int.interface_status is null
1511: and fr_int.batch_id = P_BATCH_ID
1512: and fr_int.party_orig_system = P_OS
1642: fn_int.rowid int_row_id,
1643: fn_int.batch_id
1644: from hz_imp_finnumbers_int fn_int,
1645: hz_imp_parties_sg party_sg,
1646: hz_orig_sys_references mosr,
1647: hz_imp_osr_change osr_ch_tbl
1648: where fn_int.interface_status = 'C'
1649: and fn_int.batch_id = P_BATCH_ID
1650: and fn_int.party_orig_system = P_OS
1778: fn_int.rowid int_row_id,
1779: fn_int.batch_id
1780: from hz_imp_finnumbers_int fn_int,
1781: hz_imp_parties_sg party_sg,
1782: hz_orig_sys_references mosr,
1783: hz_imp_osr_change osr_ch_tbl
1784: where fn_int.interface_status is null
1785: and fn_int.batch_id = P_BATCH_ID
1786: and fn_int.party_orig_system = P_OS
1896: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change, /* Bug 5383200 */
1897: sub_int.party_type sub_type,
1898: obj_int.party_type obj_type
1899: from hz_imp_RELSHIPS_int ri,
1900: hz_orig_sys_references mosr,
1901: hz_orig_sys_references mosr2,
1902: HZ_IMP_PARTIES_SG PARTY_SGA,
1903: HZ_IMP_PARTIES_SG PARTY_SGB,
1904: hz_imp_osr_change sub_osr_ch_tbl,
1897: sub_int.party_type sub_type,
1898: obj_int.party_type obj_type
1899: from hz_imp_RELSHIPS_int ri,
1900: hz_orig_sys_references mosr,
1901: hz_orig_sys_references mosr2,
1902: HZ_IMP_PARTIES_SG PARTY_SGA,
1903: HZ_IMP_PARTIES_SG PARTY_SGB,
1904: hz_imp_osr_change sub_osr_ch_tbl,
1905: hz_imp_osr_change obj_osr_ch_tbl,
2006: sub_int.party_type sub_type,
2007: obj_int.party_type obj_type,
2008: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2009: from hz_imp_RELSHIPS_int ri,
2010: hz_orig_sys_references mosr,
2011: hz_orig_sys_references mosr2,
2012: HZ_IMP_PARTIES_SG PARTY_SGA,
2013: HZ_IMP_PARTIES_SG PARTY_SGB,
2014: hz_imp_osr_change sub_osr_ch_tbl,
2007: obj_int.party_type obj_type,
2008: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2009: from hz_imp_RELSHIPS_int ri,
2010: hz_orig_sys_references mosr,
2011: hz_orig_sys_references mosr2,
2012: HZ_IMP_PARTIES_SG PARTY_SGA,
2013: HZ_IMP_PARTIES_SG PARTY_SGB,
2014: hz_imp_osr_change sub_osr_ch_tbl,
2015: hz_imp_osr_change obj_osr_ch_tbl,
2116: sub_int.party_type sub_type,
2117: obj_int.party_type obj_type,
2118: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2119: from hz_imp_RELSHIPS_int ri,
2120: hz_orig_sys_references mosr,
2121: hz_orig_sys_references mosr2,
2122: HZ_IMP_PARTIES_SG PARTY_SGA,
2123: HZ_IMP_PARTIES_SG PARTY_SGB,
2124: hz_imp_osr_change sub_osr_ch_tbl,
2117: obj_int.party_type obj_type,
2118: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2119: from hz_imp_RELSHIPS_int ri,
2120: hz_orig_sys_references mosr,
2121: hz_orig_sys_references mosr2,
2122: HZ_IMP_PARTIES_SG PARTY_SGA,
2123: HZ_IMP_PARTIES_SG PARTY_SGB,
2124: hz_imp_osr_change sub_osr_ch_tbl,
2125: hz_imp_osr_change obj_osr_ch_tbl,
2225: sub_int.party_type sub_type,
2226: obj_int.party_type obj_type,
2227: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2228: from hz_imp_RELSHIPS_int ri,
2229: hz_orig_sys_references mosr,
2230: hz_orig_sys_references mosr2,
2231: HZ_IMP_PARTIES_SG PARTY_SGA,
2232: HZ_IMP_PARTIES_SG PARTY_SGB,
2233: hz_imp_osr_change sub_osr_ch_tbl,
2226: obj_int.party_type obj_type,
2227: NULLIF(sub_osr_ch_tbl.entity_id, PARTY_SGB.party_id) subject_osr_change /* Bug 5383200 */
2228: from hz_imp_RELSHIPS_int ri,
2229: hz_orig_sys_references mosr,
2230: hz_orig_sys_references mosr2,
2231: HZ_IMP_PARTIES_SG PARTY_SGA,
2232: HZ_IMP_PARTIES_SG PARTY_SGB,
2233: hz_imp_osr_change sub_osr_ch_tbl,
2234: hz_imp_osr_change obj_osr_ch_tbl,
2341: nvl2(cont_mosr.owner_table_id, 'U', 'I') action_flag,
2342: nvl(obj_sg.action_flag,'U') PARTY_ACTION_FLAG,
2343: P_BATCH_MODE_FLAG, P_BATCH_ID
2344: from hz_imp_contacts_int cont_int,
2345: hz_orig_sys_references cont_mosr,
2346: hz_imp_parties_sg sub_sg,
2347: hz_orig_sys_references sub_mosr,
2348: hz_imp_parties_sg obj_sg,
2349: hz_imp_parties_int sub_int,
2343: P_BATCH_MODE_FLAG, P_BATCH_ID
2344: from hz_imp_contacts_int cont_int,
2345: hz_orig_sys_references cont_mosr,
2346: hz_imp_parties_sg sub_sg,
2347: hz_orig_sys_references sub_mosr,
2348: hz_imp_parties_sg obj_sg,
2349: hz_imp_parties_int sub_int,
2350: hz_imp_parties_int obj_int, /*6913856 */
2351: hz_orig_sys_references obj_mosr
2347: hz_orig_sys_references sub_mosr,
2348: hz_imp_parties_sg obj_sg,
2349: hz_imp_parties_int sub_int,
2350: hz_imp_parties_int obj_int, /*6913856 */
2351: hz_orig_sys_references obj_mosr
2352: where cont_int.batch_id = P_BATCH_ID
2353: and cont_int.sub_orig_system = P_OS
2354: and cont_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2355: and cont_int.interface_status = 'C'
2423: nvl2(cont_mosr.owner_table_id, 'U', 'I') action_flag,
2424: nvl(obj_sg.action_flag,'U') PARTY_ACTION_FLAG,
2425: P_BATCH_MODE_FLAG, P_BATCH_ID
2426: from hz_imp_contacts_int cont_int,
2427: hz_orig_sys_references cont_mosr,
2428: hz_imp_parties_sg sub_sg,
2429: hz_orig_sys_references sub_mosr,
2430: hz_imp_parties_sg obj_sg,
2431: hz_imp_parties_int sub_int,
2425: P_BATCH_MODE_FLAG, P_BATCH_ID
2426: from hz_imp_contacts_int cont_int,
2427: hz_orig_sys_references cont_mosr,
2428: hz_imp_parties_sg sub_sg,
2429: hz_orig_sys_references sub_mosr,
2430: hz_imp_parties_sg obj_sg,
2431: hz_imp_parties_int sub_int,
2432: hz_imp_parties_int obj_int,
2433: hz_orig_sys_references obj_mosr
2429: hz_orig_sys_references sub_mosr,
2430: hz_imp_parties_sg obj_sg,
2431: hz_imp_parties_int sub_int,
2432: hz_imp_parties_int obj_int,
2433: hz_orig_sys_references obj_mosr
2434: where cont_int.batch_id = P_BATCH_ID
2435: and cont_int.sub_orig_system = P_OS
2436: and cont_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2437: and cont_int.interface_status is null
2521: cont_sg.action_flag cont_action_flag,
2522: conrole_int.role_type role_type
2523: from hz_imp_contactroles_int conrole_int,
2524: hz_imp_contacts_sg cont_sg,
2525: hz_orig_sys_references con_mosr,
2526: hz_imp_parties_sg party_sg,
2527: hz_orig_sys_references party_mosr--,
2528: where conrole_int.batch_id = P_BATCH_ID
2529: and conrole_int.sub_orig_system = P_OS
2523: from hz_imp_contactroles_int conrole_int,
2524: hz_imp_contacts_sg cont_sg,
2525: hz_orig_sys_references con_mosr,
2526: hz_imp_parties_sg party_sg,
2527: hz_orig_sys_references party_mosr--,
2528: where conrole_int.batch_id = P_BATCH_ID
2529: and conrole_int.sub_orig_system = P_OS
2530: and conrole_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2531: and conrole_int.interface_status = 'C'
2590: cont_sg.action_flag cont_action_flag,
2591: conrole_int.role_type role_type
2592: from hz_imp_contactroles_int conrole_int,
2593: hz_imp_contacts_sg cont_sg,
2594: hz_orig_sys_references con_mosr,
2595: hz_imp_parties_sg party_sg,
2596: hz_orig_sys_references party_mosr--,
2597: where conrole_int.batch_id = P_BATCH_ID
2598: and conrole_int.sub_orig_system = P_OS
2592: from hz_imp_contactroles_int conrole_int,
2593: hz_imp_contacts_sg cont_sg,
2594: hz_orig_sys_references con_mosr,
2595: hz_imp_parties_sg party_sg,
2596: hz_orig_sys_references party_mosr--,
2597: where conrole_int.batch_id = P_BATCH_ID
2598: and conrole_int.sub_orig_system = P_OS
2599: and conrole_int.sub_orig_system_reference between P_FROM_OSR and P_TO_OSR
2600: and conrole_int.interface_status is null
2688: addruse_int.primary_flag
2689: from hz_imp_addressuses_int addruse_int,
2690: hz_imp_parties_sg party_sg,
2691: hz_imp_addresses_sg addr_sg,
2692: hz_orig_sys_references addr_mosr,
2693: hz_orig_sys_references party_mosr
2694: where addruse_int.batch_id = P_BATCH_ID
2695: and addruse_int.party_orig_system = P_OS
2696: and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2689: from hz_imp_addressuses_int addruse_int,
2690: hz_imp_parties_sg party_sg,
2691: hz_imp_addresses_sg addr_sg,
2692: hz_orig_sys_references addr_mosr,
2693: hz_orig_sys_references party_mosr
2694: where addruse_int.batch_id = P_BATCH_ID
2695: and addruse_int.party_orig_system = P_OS
2696: and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2697: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR
2769: addruse_int.primary_flag
2770: from hz_imp_addressuses_int addruse_int,
2771: hz_imp_parties_sg party_sg,
2772: hz_imp_addresses_sg addr_sg,
2773: hz_orig_sys_references addr_mosr,
2774: hz_orig_sys_references party_mosr
2775: where addruse_int.batch_id = P_BATCH_ID
2776: and addruse_int.party_orig_system = P_OS
2777: and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2770: from hz_imp_addressuses_int addruse_int,
2771: hz_imp_parties_sg party_sg,
2772: hz_imp_addresses_sg addr_sg,
2773: hz_orig_sys_references addr_mosr,
2774: hz_orig_sys_references party_mosr
2775: where addruse_int.batch_id = P_BATCH_ID
2776: and addruse_int.party_orig_system = P_OS
2777: and addruse_int.party_orig_system_reference between P_FROM_OSR and P_TO_OSR
2778: and party_sg.party_orig_system_reference (+) between P_FROM_OSR and P_TO_OSR