[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select party_id from hz_purge_gt;
delete from hz_application_trans_gt;
insert into hz_application_trans_gt(app_id,party_id) select 691, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_ENTY_ATTR_VALUES xx where xx.ENTITY_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_ASSIGNMENTS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_ASSIGNMENT_LOGS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_SEARCH_ATTR_VALUES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_GE_PTNR_RESPS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_PSS_LINES xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_GE_PARTY_NOTIFICATIONS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PG_ENRL_REQUESTS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PG_MEMBERSHIPS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_REFERRALS_B xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from PV_REFERRALS_B
yy where yy.CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_ASSIGNMENTS xx where xx.RELATED_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_PARTNER_ACCESSES xx where xx.PARTNER_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_ENTY_ATTR_VALUES xx where xx.ENTITY_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_ASSIGNMENTS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_ASSIGNMENT_LOGS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_SEARCH_ATTR_VALUES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_GE_PTNR_RESPS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_PSS_LINES xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_GE_PARTY_NOTIFICATIONS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PG_ENRL_REQUESTS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PG_MEMBERSHIPS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_REFERRALS_B xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from PV_REFERRALS_B
yy where yy.CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from PV_REFERRALS_B zz where zz.CUSTOMER_ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_LEAD_ASSIGNMENTS xx where xx.RELATED_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PV_PARTNER_ACCESSES xx where xx.PARTNER_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from WSH_CARRIER_SITES
yy where yy.CARRIER_SITE_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from WSH_CARRIER_SITES
yy where yy.CARRIER_SITE_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.SELLING_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_CUSTOMER_PROFILES xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_ORGANIZATION_PROFILES xx where xx.DISPLAYED_DUNS_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F'))
or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F'));
delete from hz_purge_gt temp where
exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.SELLING_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_CUSTOMER_PROFILES xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_ORGANIZATION_PROFILES xx where xx.DISPLAYED_DUNS_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I')))
or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F'))
or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F'));
insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from IBE_ORD_ONECLICK_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IBE_ORD_ONECLICK_ALL
yy where yy.BILL_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IBE_ORD_ONECLICK_ALL
yy where yy.SHIP_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from IBE_MSITE_PRTY_ACCSS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from IBE_ORD_ONECLICK_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IBE_ORD_ONECLICK_ALL
yy where yy.BILL_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IBE_ORD_ONECLICK_ALL
yy where yy.SHIP_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from IBE_MSITE_PRTY_ACCSS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_CLAIMS_ALL xx where xx.BROKER_ID = temp.party_id
)
or exists (select 'Y' from OZF_CLAIMS_ALL xx where xx.CONTACT_ID = temp.party_id
)
or exists (select 'Y' from OZF_ACT_BUDGETS xx where xx.BUDGET_SOURCE_ID = temp.party_id
and (BUDGET_SOURCE_TYPE='PTNR'))
or exists (select 'Y' from OZF_ACT_BUDGETS xx where xx.VENDOR_ID = temp.party_id
)
or exists (select 'Y' from OZF_OFFERS xx where xx.BUYING_GROUP_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from OZF_CODE_CONVERSIONS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_RESALE_BATCHES_ALL xx where xx.PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_CLAIMS_ALL xx where xx.BROKER_ID = temp.party_id
)
or exists (select 'Y' from OZF_CLAIMS_ALL xx where xx.CONTACT_ID = temp.party_id
)
or exists (select 'Y' from OZF_ACT_BUDGETS xx where xx.BUDGET_SOURCE_ID = temp.party_id
and (BUDGET_SOURCE_TYPE='PTNR'))
or exists (select 'Y' from OZF_ACT_BUDGETS xx where xx.VENDOR_ID = temp.party_id
)
or exists (select 'Y' from OZF_OFFERS xx where xx.BUYING_GROUP_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from OZF_CODE_CONVERSIONS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_RESALE_BATCHES_ALL xx where xx.PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 695, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IEX_CASE_CONTACTS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from IEX_CASE_CONTACTS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 661, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
and ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from QP_QUALIFIERS
yy where yy.QUALIFIER_ATTR_VALUE=to_char(xx.PARTY_SITE_ID)
and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
delete from hz_purge_gt temp where
exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
and ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from QP_QUALIFIERS
yy where yy.QUALIFIER_ATTR_VALUE=to_char(xx.PARTY_SITE_ID)
and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from POS_SUPP_PROF_EXT_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from POS_BUS_CLASS_ATTR xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from POS_SUPP_PROF_EXT_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from POS_BUS_CLASS_ATTR xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_T_TXN_LINE_DETAILS
yy where yy.LOCATION_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_T_TXN_LINE_DETAILS
yy where yy.INSTALL_LOCATION_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from CSI_I_PARTIES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.SHIP_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_PARTY_DETAILS xx where xx.PARTY_SOURCE_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.SHIP_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.BILL_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.SERVICE_ADMIN_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.BILL_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.SERVICE_ADMIN_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_SYSTEMS_B
yy where yy.INSTALL_SITE_USE_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_T_TXN_LINE_DETAILS
yy where yy.LOCATION_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_T_TXN_LINE_DETAILS
yy where yy.INSTALL_LOCATION_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from CSI_I_PARTIES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.SHIP_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_PARTY_DETAILS xx where xx.PARTY_SOURCE_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.SHIP_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.BILL_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_SYSTEMS_B xx where xx.SERVICE_ADMIN_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.BILL_TO_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.SERVICE_ADMIN_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CSI_SYSTEMS_B
yy where yy.INSTALL_SITE_USE_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 510, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_CONTACTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_CONTACTS')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT2_ID1 = to_char(temp.party_id)
and (jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT3_ID1 = to_char(temp.party_id)
and (jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT2_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT3_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT2_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT3_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from OKC_K_ITEMS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_K_ITEMS
yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_K_ITEMS zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))));
delete from hz_purge_gt temp where
exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_CONTACTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_CONTACTS')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT2_ID1 = to_char(temp.party_id)
and (jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT3_ID1 = to_char(temp.party_id)
and (jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT2_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_RULES_B
yy where yy.OBJECT3_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT2_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_RULES_B zz where zz.OBJECT3_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
or exists (select 'Y' from OKC_K_ITEMS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKC_K_ITEMS
yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKC_K_ITEMS zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))));
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_SHIPMENTS
yy where yy.SHIP_TO_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.END_CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.END_CUSTOMER_CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_QUOTE_HEADERS_ALL
yy where yy.END_CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from ASO_QUOTE_LINES_ALL xx where xx.END_CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_LINES_ALL xx where xx.END_CUSTOMER_CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_QUOTE_LINES_ALL
yy where yy.END_CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_SHIPMENTS
yy where yy.SHIP_TO_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.END_CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.END_CUSTOMER_CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_QUOTE_HEADERS_ALL
yy where yy.END_CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from ASO_QUOTE_LINES_ALL xx where xx.END_CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from ASO_QUOTE_LINES_ALL xx where xx.END_CUSTOMER_CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from ASO_QUOTE_LINES_ALL
yy where yy.END_CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
)
or exists (select 'Y' from OKE_K_FUNDING_SOURCES xx where xx.K_PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
)
or exists (select 'Y' from OKE_K_FUNDING_SOURCES xx where xx.K_PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AHL_SUPPLIER_DOCUMENTS xx where xx.SUPPLIER_ID = temp.party_id
)
or exists (select 'Y' from AHL_RECIPIENT_DOCUMENTS xx where xx.RECIPIENT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AHL_DOC_REVISIONS_B xx where xx.APPROVED_BY_PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AHL_SUPPLIER_DOCUMENTS xx where xx.SUPPLIER_ID = temp.party_id
)
or exists (select 'Y' from AHL_RECIPIENT_DOCUMENTS xx where xx.RECIPIENT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AHL_DOC_REVISIONS_B xx where xx.APPROVED_BY_PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_ACCESSES_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.PARTNER_CONT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_INTERESTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_INTERESTS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_ACCESSES_ALL
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_LEADS xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_LEADS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEADS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_LEADS_ALL xx where xx.CLOSE_COMPETITOR_ID = temp.party_id
)
or exists (select 'Y' from AS_LEADS_ALL xx where xx.INCUMBENT_PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_LEAD_COMPETITORS xx where xx.COMPETITOR_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS_DENORM
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS_DENORM
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_CURRENT_ENVIRONMENT
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEADS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_LEADS xx where xx.PRIMARY_CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_SALES_LEADS xx where xx.PRIMARY_CNT_PERSON_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEADS
yy where yy.PRIMARY_CONTACT_PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEADS zz where zz.PRIMARY_CONTACT_PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_ACCESSES_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.PARTNER_CONT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_INTERESTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_INTERESTS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_ACCESSES_ALL
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_LEADS xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from AS_LEADS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEADS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_LEADS_ALL xx where xx.CLOSE_COMPETITOR_ID = temp.party_id
)
or exists (select 'Y' from AS_LEADS_ALL xx where xx.INCUMBENT_PARTNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_LEAD_COMPETITORS xx where xx.COMPETITOR_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS_DENORM
yy where yy.PARTNER_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_CREDITS_DENORM
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_CURRENT_ENVIRONMENT
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEADS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from AS_SALES_LEADS xx where xx.PRIMARY_CONTACT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AS_SALES_LEADS xx where xx.PRIMARY_CNT_PERSON_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AS_SALES_LEADS
yy where yy.PRIMARY_CONTACT_PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AS_SALES_LEADS zz where zz.PRIMARY_CONTACT_PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 673, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from IBY_ACCOUNT_OWNERS xx where xx.ACCOUNT_OWNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from IBY_TRXN_SUMMARIES_ALL xx where xx.PAYERID = to_char(temp.party_id)
)
or exists (select 'Y' from IBY_CREDITCARD xx where xx.CARD_OWNER_ID = temp.party_id
)
or exists (select 'Y' from IBY_EXTERNAL_PAYERS_ALL xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from IBY_ACCOUNT_OWNERS xx where xx.ACCOUNT_OWNER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from IBY_TRXN_SUMMARIES_ALL xx where xx.PAYERID = to_char(temp.party_id)
)
or exists (select 'Y' from IBY_CREDITCARD xx where xx.CARD_OWNER_ID = temp.party_id
)
or exists (select 'Y' from IBY_EXTERNAL_PAYERS_ALL xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 206, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
)
or exists (select 'Y' from LNS_PARTICIPANTS xx where xx.HZ_PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
)
or exists (select 'Y' from LNS_PARTICIPANTS xx where xx.HZ_PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 275, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.OWNER_ID = temp.party_id
)
or exists (select 'Y' from PA_PERCENT_COMPLETES xx where xx.PUBLISHED_BY_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_PROJECT_REQUESTS xx where xx.CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_PROJECT_SETS_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_RESOURCE_TXN_ATTRIBUTES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_CI_IMPACTS xx where xx.IMPLEMENTED_BY = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.LAST_MODIFIED_BY_ID = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.CLOSED_BY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.OWNER_ID = temp.party_id
)
or exists (select 'Y' from PA_PERCENT_COMPLETES xx where xx.PUBLISHED_BY_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_PROJECT_REQUESTS xx where xx.CUST_PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_PROJECT_SETS_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_RESOURCE_TXN_ATTRIBUTES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from PA_CI_IMPACTS xx where xx.IMPLEMENTED_BY = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.LAST_MODIFIED_BY_ID = temp.party_id
)
or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.CLOSED_BY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDING_PARTIES xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_ITEM_PRICES xx where xx.BID_TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_AUCTION_HEADERS_ALL xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_AUCTION_EVENTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDERS_LISTS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDERS_LISTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_ATTRIBUTE_LISTS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_ATTRIBUTE_LISTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDING_PARTIES xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_ITEM_PRICES xx where xx.BID_TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_AUCTION_HEADERS_ALL xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_AUCTION_EVENTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDERS_LISTS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_BIDDERS_LISTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_ATTRIBUTE_LISTS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_ATTRIBUTE_LISTS xx where xx.TRADING_PARTNER_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.IST_ID = yy.PARTY_SITE_USE_ID )))
or exists (select 'Y' from OKL_LEASE_APPS_ALL_B xx where xx.PROSPECT_ID = temp.party_id
)
or exists (select 'Y' from OKL_LEASE_OPPS_ALL_B xx where xx.PROSPECT_ID = temp.party_id
)
or exists (select 'Y' from OKL_QUOTE_PARTIES xx where xx.CONTACT_OBJECT1_ID1 = to_char(temp.party_id)
and (CONTACT_JTOT_OBJECT1_CODE = 'OKX_PARTY'))
or exists (select 'Y' from OKL_QUOTE_PARTIES xx where xx.PARTY_OBJECT1_ID1 = to_char(temp.party_id)
and (PARTY_JTOT_OBJECT1_CODE = 'OKX_PARTY'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_TRX_AR_INVOICES_B zz where zz.IBT_ID = yy.CUST_ACCT_SITE_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_CNSLD_AR_HDRS_ALL_B zz where zz.IBT_ID = yy.CUST_ACCT_SITE_ID )))
or exists (select 'Y' from HZ_CUST_ACCOUNTS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_TXL_RCPT_APPS_ALL_B
yy where yy.ILE_ID=xx.CUST_ACCOUNT_ID
))
or exists (select 'Y' from HZ_CUST_ACCOUNTS
xx where xx.SELLING_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_TXL_RCPT_APPS_ALL_B
yy where yy.ILE_ID=xx.CUST_ACCOUNT_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B
yy where yy.PAC_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
delete from hz_purge_gt temp where
exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_PARTY_SITE_USES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.IST_ID = yy.PARTY_SITE_USE_ID )))
or exists (select 'Y' from OKL_LEASE_APPS_ALL_B xx where xx.PROSPECT_ID = temp.party_id
)
or exists (select 'Y' from OKL_LEASE_OPPS_ALL_B xx where xx.PROSPECT_ID = temp.party_id
)
or exists (select 'Y' from OKL_QUOTE_PARTIES xx where xx.CONTACT_OBJECT1_ID1 = to_char(temp.party_id)
and (CONTACT_JTOT_OBJECT1_CODE = 'OKX_PARTY'))
or exists (select 'Y' from OKL_QUOTE_PARTIES xx where xx.PARTY_OBJECT1_ID1 = to_char(temp.party_id)
and (PARTY_JTOT_OBJECT1_CODE = 'OKX_PARTY'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_TRX_AR_INVOICES_B zz where zz.IBT_ID = yy.CUST_ACCT_SITE_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CUST_ACCT_SITES_ALL
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_CNSLD_AR_HDRS_ALL_B zz where zz.IBT_ID = yy.CUST_ACCT_SITE_ID )))
or exists (select 'Y' from HZ_CUST_ACCOUNTS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_TXL_RCPT_APPS_ALL_B
yy where yy.ILE_ID=xx.CUST_ACCOUNT_ID
))
or exists (select 'Y' from HZ_CUST_ACCOUNTS
xx where xx.SELLING_PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_TXL_RCPT_APPS_ALL_B
yy where yy.ILE_ID=xx.CUST_ACCOUNT_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B
yy where yy.PAC_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from AP_SUPPLIER_CONTACTS xx where xx.PER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AP_SUPPLIER_CONTACTS xx where xx.REL_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.RELATIONSHIP_ID=xx.RELATIONSHIP_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS zz where zz.ORG_CONTACT_ID = yy.ORG_CONTACT_ID )))
or exists (select 'Y' from AP_SUPPLIER_CONTACTS xx where xx.PER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AP_SUPPLIER_CONTACTS xx where xx.REL_PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from AP_SUPPLIER_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PREFERRED_VAD_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PRIMARY_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from AMS_EVENT_REGISTRATIONS xx where xx.REGISTRANT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_EVENT_REGISTRATIONS xx where xx.ATTENDANT_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from AMS_CHANNELS_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_IBA_POSTINGS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_IBA_POSTINGS_B xx where xx.AFFILIATE_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_COMPETITOR_PRODUCTS_B xx where xx.COMPETITOR_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_VENUES_B xx where xx.PARTY_ID = temp.party_id
and (OBJECT_TYPE='VENU'))
or exists (select 'Y' from AMS_IMP_SOURCE_LINES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_LIST_ENTRIES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_LIST_ENTRIES xx where xx.PARENT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_RESOURCES xx where xx.RESOURCE_ID = temp.party_id
)
or exists (select 'Y' from AMS_TCOP_CONTACT_SUMMARY xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PARTNER_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PREFERRED_VAD_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_PARTNERS xx where xx.PRIMARY_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from AMS_EVENT_REGISTRATIONS xx where xx.REGISTRANT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_EVENT_REGISTRATIONS xx where xx.ATTENDANT_CONTACT_ID = temp.party_id
)
or exists (select 'Y' from AMS_CHANNELS_B xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_IBA_POSTINGS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_IBA_POSTINGS_B xx where xx.AFFILIATE_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_COMPETITOR_PRODUCTS_B xx where xx.COMPETITOR_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_VENUES_B xx where xx.PARTY_ID = temp.party_id
and (OBJECT_TYPE='VENU'))
or exists (select 'Y' from AMS_IMP_SOURCE_LINES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_LIST_ENTRIES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_LIST_ENTRIES xx where xx.PARENT_PARTY_ID = temp.party_id
)
or exists (select 'Y' from AMS_ACT_RESOURCES xx where xx.RESOURCE_ID = temp.party_id
)
or exists (select 'Y' from AMS_TCOP_CONTACT_SUMMARY xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from CSC_CUSTOMIZED_PLANS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from CSC_CUSTOMIZED_PLANS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASKS_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
and (SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.NEW_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.OLD_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.NEW_SOURCE_OBJECT_ID = temp.party_id
and (NEW_SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.OLD_SOURCE_OBJECT_ID = temp.party_id
and (OLD_SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_CONTACTS xx where xx.CONTACT_ID = temp.party_id
and (CONTACT_TYPE_CODE = 'CUST'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_REFERENCES_B
yy where yy.OBJECT_ID=xx.PARTY_SITE_ID
and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))
or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASKS_B
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_AUDITS_B
yy where yy.NEW_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_AUDITS_B
yy where yy.OLD_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_PHONES
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_NOTES_B
yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_NOTE_CONTEXTS
yy where yy.NOTE_CONTEXT_TYPE_ID=xx.PARTY_SITE_ID
and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
or exists (select 'Y' from JTF_NOTES_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
and (source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES' )))
or exists (select 'Y' from JTF_NOTE_CONTEXTS xx where xx.NOTE_CONTEXT_TYPE_ID = temp.party_id
and (NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES')))
or exists (select 'Y' from JTF_IH_INTERACTIONS xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from JTF_RS_RESOURCE_EXTNS xx where xx.SOURCE_ID = temp.party_id
and (category IN ( 'PARTY' , 'PARTNER')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
and category = 'PARTNER'))
or exists (select 'Y' from JTF_UM_APPROVERS xx where xx.ORG_PARTY_ID = temp.party_id
and (ROWNUM < 2))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TERR_VALUES_ALL
yy where yy.LOW_VALUE_CHAR_ID=xx.PARTY_SITE_ID
and TERR_QUAL_ID in ( select TERR_QUAL_ID from JTF_TERR_QUAL_ALL where QUAL_USG_ID in (-1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002, -1001, -1201, -1039, -1094))))
or exists (select 'Y' from JTF_TERR_VALUES_ALL xx where xx.LOW_VALUE_CHAR_ID = temp.party_id
and (TERR_QUAL_ID in ( select TERR_QUAL_ID from JTF_TERR_QUAL_ALL where QUAL_USG_ID in (-1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002, -1001, -1201, -1039, -1094))))
or exists (select 'Y' from JTF_TASK_ASSIGNMENTS xx where xx.RESOURCE_ID = temp.party_id
and (RESOURCE_TYPE_CODE IN (SELECT object_code FROM jtf_objects_b WHERE LTRIM(RTRIM(UPPER(from_table))) = 'HZ_PARTIES' AND RTRIM(LTRIM(UPPER(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NAME')))
or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NUMBER')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS
yy where yy.SUPPORT_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TTY_NAMED_ACCTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from JTF_TTY_NAMED_ACCTS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASKS_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
and (SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.NEW_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.OLD_CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.NEW_SOURCE_OBJECT_ID = temp.party_id
and (NEW_SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_AUDITS_B xx where xx.OLD_SOURCE_OBJECT_ID = temp.party_id
and (OLD_SOURCE_OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_TASK_CONTACTS xx where xx.CONTACT_ID = temp.party_id
and (CONTACT_TYPE_CODE = 'CUST'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_REFERENCES_B
yy where yy.OBJECT_ID=xx.PARTY_SITE_ID
and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))
or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASKS_B
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_AUDITS_B
yy where yy.NEW_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_AUDITS_B
yy where yy.OLD_ADDRESS_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_CONTACT_POINTS
xx where xx.OWNER_TABLE_ID = temp.party_id
and (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TASK_PHONES
yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_CONTACT_POINTS
yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.OBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_RELATIONSHIPS
xx where xx.SUBJECT_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and exists
(select 'Y' from HZ_ORG_CONTACTS
yy where yy.PARTY_RELATIONSHIP_ID=xx.RELATIONSHIP_ID
and nvl(STATUS, 'A') in ('A','I')
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_NOTES_B
yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_NOTE_CONTEXTS
yy where yy.NOTE_CONTEXT_TYPE_ID=xx.PARTY_SITE_ID
and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
or exists (select 'Y' from JTF_NOTES_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
and (source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES' )))
or exists (select 'Y' from JTF_NOTE_CONTEXTS xx where xx.NOTE_CONTEXT_TYPE_ID = temp.party_id
and (NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES')))
or exists (select 'Y' from JTF_IH_INTERACTIONS xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from JTF_RS_RESOURCE_EXTNS xx where xx.SOURCE_ID = temp.party_id
and (category IN ( 'PARTY' , 'PARTNER')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS
yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
and category = 'PARTNER'))
or exists (select 'Y' from JTF_UM_APPROVERS xx where xx.ORG_PARTY_ID = temp.party_id
and (ROWNUM < 2))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TERR_VALUES_ALL
yy where yy.LOW_VALUE_CHAR_ID=xx.PARTY_SITE_ID
and TERR_QUAL_ID in ( select TERR_QUAL_ID from JTF_TERR_QUAL_ALL where QUAL_USG_ID in (-1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002, -1001, -1201, -1039, -1094))))
or exists (select 'Y' from JTF_TERR_VALUES_ALL xx where xx.LOW_VALUE_CHAR_ID = temp.party_id
and (TERR_QUAL_ID in ( select TERR_QUAL_ID from JTF_TERR_QUAL_ALL where QUAL_USG_ID in (-1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002, -1001, -1201, -1039, -1094))))
or exists (select 'Y' from JTF_TASK_ASSIGNMENTS xx where xx.RESOURCE_ID = temp.party_id
and (RESOURCE_TYPE_CODE IN (SELECT object_code FROM jtf_objects_b WHERE LTRIM(RTRIM(UPPER(from_table))) = 'HZ_PARTIES' AND RTRIM(LTRIM(UPPER(select_id))) = 'PARTY_ID')))
or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NAME')))
or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NUMBER')))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_RS_RESOURCE_EXTNS
yy where yy.SUPPORT_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from JTF_TTY_NAMED_ACCTS
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from JTF_TTY_NAMED_ACCTS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
)
or exists (select 'Y' from OKS_K_DEFAULTS xx where xx.SEGMENT_ID1 = to_char(temp.party_id)
and (JTOT_OBJECT_CODE = 'OKX_PARTY'))
or exists (select 'Y' from OKS_SERV_AVAIL_EXCEPTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
delete from hz_purge_gt temp where
exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
)
or exists (select 'Y' from OKS_K_DEFAULTS xx where xx.SEGMENT_ID1 = to_char(temp.party_id)
and (JTOT_OBJECT_CODE = 'OKX_PARTY'))
or exists (select 'Y' from OKS_SERV_AVAIL_EXCEPTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
insert into hz_application_trans_gt(app_id,party_id) select 0, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from FND_USER xx where xx.PERSON_PARTY_ID = temp.party_id
)
or exists (select 'Y' from UMX_REG_REQUESTS xx where xx.REQUESTED_FOR_PARTY_ID = temp.party_id
and (ROWNUM < 2));
delete from hz_purge_gt temp where
exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from FND_USER xx where xx.PERSON_PARTY_ID = temp.party_id
)
or exists (select 'Y' from UMX_REG_REQUESTS xx where xx.REQUESTED_FOR_PARTY_ID = temp.party_id
and (ROWNUM < 2));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from CS_HZ_SR_CONTACT_POINTS xx where xx.PARTY_ID = temp.party_id
and (CONTACT_TYPE <> 'EMPLOYEE'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CS_ESTIMATE_DETAILS
yy where yy.INVOICE_TO_ORG_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CS_ESTIMATE_DETAILS
yy where yy.SHIP_TO_ORG_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
)
or exists (select 'Y' from CS_HZ_SR_CONTACT_POINTS xx where xx.PARTY_ID = temp.party_id
and (CONTACT_TYPE <> 'EMPLOYEE'))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CS_ESTIMATE_DETAILS
yy where yy.INVOICE_TO_ORG_ID=xx.PARTY_SITE_ID
))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from CS_ESTIMATE_DETAILS
yy where yy.SHIP_TO_ORG_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 660, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
and ((pricing_context = 'ASOPARTYINFO' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE1' OR pricing_context = 'CUSTOMER' AND pricing_attribute ='QUALIFIER_ATTRIBUTE16' OR pricing_context = 'CUSTOMER_GROUP' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE3' OR pricing_context = 'PARTY' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OE_PRICE_ADJ_ATTRIBS
yy where yy.PRICING_ATTR_VALUE_FROM=to_char(xx.PARTY_SITE_ID)
and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
delete from hz_purge_gt temp where
exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
and ((pricing_context = 'ASOPARTYINFO' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE1' OR pricing_context = 'CUSTOMER' AND pricing_attribute ='QUALIFIER_ATTRIBUTE16' OR pricing_context = 'CUSTOMER_GROUP' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE3' OR pricing_context = 'PARTY' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from OE_PRICE_ADJ_ATTRIBS
yy where yy.PRICING_ATTR_VALUE_FROM=to_char(xx.PARTY_SITE_ID)
and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
insert into hz_application_trans_gt(app_id,party_id) select 800, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
delete from hz_purge_gt temp where
exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 9004, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from INL_CHARGE_LINES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from INL_CHARGE_LINES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
));
delete from hz_purge_gt temp where
exists (select 'Y' from INL_CHARGE_LINES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from HZ_PARTY_SITES
xx where xx.PARTY_ID = temp.party_id
and (nvl(STATUS, 'A') in ('A','I'))
and exists
(select 'Y' from INL_CHARGE_LINES
yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
));
insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from XLE_ETB_PROFILES xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
)
or exists (select 'Y' from XLE_ETB_PROFILES xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where
exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
);
delete from hz_purge_gt temp where
exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
);
insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 665 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 222 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.EMPLOYMENT_HISTORY_ID
in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
yy
where nvl(STATUS, 'A') in ('A','I')))
or temp.party_id in (select /*+ parallel (xx)*/ xx.EMPLOYED_BY_PARTY_ID from HZ_EMPLOYMENT_HISTORY
xx
where (nvl(STATUS,'A') in ('A','I'))
and xx.EMPLOYMENT_HISTORY_ID
in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
yy
where nvl(STATUS, 'A') in ('A','I')));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.EMPLOYMENT_HISTORY_ID
in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
yy
where nvl(STATUS, 'A') in ('A','I')))
or temp.party_id in (select /*+ parallel (xx)*/ xx.EMPLOYED_BY_PARTY_ID from HZ_EMPLOYMENT_HISTORY
xx
where (nvl(STATUS,'A') in ('A','I'))
and xx.EMPLOYMENT_HISTORY_ID
in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
yy
where nvl(STATUS, 'A') in ('A','I')));
insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 222 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
where (entity_name = 'HZ_PARTIES'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
where (entity_name = 'HZ_PARTIES'));
insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 222 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 671 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 671 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 435, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 435 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 435, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 435 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 435, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 435 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 435, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 435 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 535, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 535 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 250, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 250 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.OBJECT_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.SUBJECT_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.OBJECT_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.SUBJECT_ID from HZ_RELATIONSHIPS
xx
where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F')
and xx.RELATIONSHIP_ID
in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from POS_PARTY_SITE_UDA_V
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from POS_PARTY_SITE_UDA_V
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AP_SUPPLIERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AP_SUPPLIERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_PS_MERGE_V xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_PS_MERGE_V xx
);
insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_BANK_MERGE_V xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_BANK_MERGE_V xx
);
insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 694 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 694 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 777 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
);
insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 514, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 514 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
where (attribute_name = 'customer ID' and application_id = 514 and perz_data_type in ('CSS_PROFILE_DEFECT_TEMPLATE','CSS_PROFILE_ENH_TEMPLATE') and profile_name like 'CSS_514%'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
where (attribute_name = 'customer ID' and application_id = 514 and perz_data_type in ('CSS_PROFILE_DEFECT_TEMPLATE','CSS_PROFILE_ENH_TEMPLATE') and profile_name like 'CSS_514%'));
insert into hz_application_trans_gt(app_id,party_id) select 514, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 514 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
where (parameter_name in ('CUSTOMER_ID','SR_CUSTOMER_ID') and application_id = 514 and query_type like 'ADV_SEARCH_%' and profile_name like 'CSS_514%' ));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
where (parameter_name in ('CUSTOMER_ID','SR_CUSTOMER_ID') and application_id = 514 and query_type like 'ADV_SEARCH_%' and profile_name like 'CSS_514%' ));
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_HEADERS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_HEADERS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_ALL_B xx
where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_ALL_B xx
where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_ALL_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_ALL_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_ALL_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_ALL_B
yy
where IPY_TYPE = 'THIRD_PARTY_POLICY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_ALL_B xx
where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_ALL_B xx
where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B
yy
where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B
yy
where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B xx
where (JTOT_OBJECT1_CODE = 'OKX_PARTY' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B xx
where (JTOT_OBJECT1_CODE = 'OKX_PARTY' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_OLD from OKL_TRX_CONTRACTS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_OLD from OKL_TRX_CONTRACTS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_OLD from OKL_TRX_CONTRACTS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_OLD from OKL_TRX_CONTRACTS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_APPS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_APPS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_OPPS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_OPPS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_OBJECT_ID1 from OKL_TERMNT_INTF_PTY xx
where (PARTY_OBJECT_CODE = 'OKX_PARTY'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_OBJECT_ID1 from OKL_TERMNT_INTF_PTY xx
where (PARTY_OBJECT_CODE = 'OKX_PARTY'));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_NEW from OKL_TRX_CONTRACTS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_NEW from OKL_TRX_CONTRACTS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_RULES_B
yy
where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_RULES_B
yy
where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_NEW from OKL_TRX_CONTRACTS_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_NEW from OKL_TRX_CONTRACTS_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_CUST_ACCOUNTS
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.CUST_ACCOUNT_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_ID from OKL_EXT_SELL_INVS_ALL_B
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.SELLING_PARTY_ID from HZ_CUST_ACCOUNTS
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.CUST_ACCOUNT_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_ID from OKL_EXT_SELL_INVS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_CUST_ACCOUNTS
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.CUST_ACCOUNT_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_ID from OKL_EXT_SELL_INVS_ALL_B
yy
))
or temp.party_id in (select /*+ parallel (xx)*/ xx.SELLING_PARTY_ID from HZ_CUST_ACCOUNTS
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.CUST_ACCOUNT_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_ID from OKL_EXT_SELL_INVS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT_ALL xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT_ALL xx
);
insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 200 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PO_VENDORS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PO_VENDORS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 200 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from PO_VENDOR_SITES_ALL
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from PO_VENDOR_SITES_ALL
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
);
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 511 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 716, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 716 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 690 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
);
insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 690 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
);
insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 515 and appl.party_id=temp.party_id) and
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
where ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
where ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))));
insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 515 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS
yy
where (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and to_char(xx.PARTY_SITE_ID)
in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS
yy
where (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
);
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 174 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS
yy
));
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
xx
where (nvl(STATUS, 'A') in ('A','I'))
and xx.PARTY_SITE_ID
in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS
yy
));
insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 666 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
);
insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 666 and appl.party_id=temp.party_id) and
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
);
delete /*+ parallel(temp) */ from hz_purge_gt temp where
temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
);
delete from hz_purge_gt temp where temp.party_id in (select appl.party_id from hz_application_trans_gt appl) ;