ZCQ_SYNC_ORG_CONTACT_ADDR_VIEW

Details

• Schema: FUSION

• Object owner: ZCQ

• Object type: VIEW

Columns

Name

ORGANIZATIONIDENTIFIER REGISTRYIDENTIFIER ORGANIZATIONPROFILEID ORGANIZATIONNAME ORGANIZATIONTYPE DUNSNUMBER YEARINCORPORATED YEARESTABLISHED TRADINGPARTNERIDENTIFIER STOCKSYMBOL SICCODETYPE SICCODE TAXREGISTRATIONNUMBER EMAILDOMAINID EMAILDOMAIN LOCATIONIDENTIFIER PARTYSITEID ADDRESSLINE1 ADDRESSLINE2 ADDRESSLINE3 ADDRESSLINE4 PHONETICADDRESSLINES CITY COUNTY COUNTRYSUBENTITY PROVINCE COUNTRY POSTALCODE POSTALCODEEXT ADDRESS PARTYIDENTIFIERID1 PARTYIDENTIFIER1 PARTYIDENTIFIERID2 PARTYIDENTIFIER2 PARTYIDENTIFIERID3 PARTYIDENTIFIER3 PHONECONTACTPOINTID PHONENUMBER PHONEEXTENSION PHONELINETYPE WEBCONTACTPOINTID URL CUSTOMATTRIBUTE1 CUSTOMATTRIBUTE2 CUSTOMATTRIBUTE3 CUSTOMATTRIBUTE4 CUSTOMATTRIBUTE5 CUSTOMATTRIBUTE6 CUSTOMATTRIBUTE7 CUSTOMATTRIBUTE8 CUSTOMATTRIBUTE9 CUSTOMATTRIBUTE10 PARTYDATE ORGANIZATIONDATE PATYSITEDATE LOCATIONDATE CONTACTPOINTDATE URLDATE ADI1DATE ADI2DATE ADI3DATE EMAILDOMAINDATE OPERATION PARTYSTATUS PARTYSITESTATUS CONTACTPOINTSTATUS URLSTATUS EMAILDOMAINSTATUS ORGANIZATIONPROFILESTATUS LOCATIONSTATUS ADI1STATUS ADI2STATUS ADI3STATUS IDT_NAME ENTITY_TYPE_CODE PARTYNAMEID ADDTNLNAMEDATE ADDTNLNAMESTATUS CONTACTNAME CONTACTIDENTIFIER CONTACTDATE CONTACTSTATUS RELATIONSHIPID

Query

SQL_Statement

SELECT p.PARTY_ID OrganizationIdentifier , p.PARTY_NUMBER RegistryIdentifier, opr.ORGANIZATION_PROFILE_ID OrganizationProfileID, opr.Organization_Name OrganizationName, opr.ORGANIZATION_TYPE OrganizationType, opr.DUNS_NUMBER_C DUNSNumber, opr.INCORP_YEAR YearIncorporated, opr.YEAR_ESTABLISHED YearEstablished, opr.TRADING_PARTNER_IDENTIFIER TradingPartnerIdentifier, opr.STOCK_SYMBOL StockSymbol, opr.SIC_CODE_TYPE SICCodeType, opr.SIC_CODE SICCode, opr.JGZZ_FISCAL_CODE TaxRegistrationNumber, ed.email_domain_id eMailDomainID, ed.domain_name eMailDomain, loc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, loc.ADDRESS1 AddressLine1, loc.ADDRESS2 AddressLine2, loc.ADDRESS3 AddressLine3, loc.ADDRESS4 AddressLine4, loc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, loc.CITY City, loc.COUNTY County, loc.STATE CountrySubEntity, loc.PROVINCE Province, loc.COUNTRY Country, loc.POSTAL_CODE PostalCode, loc.POSTAL_PLUS4_CODE PostalCodeExt, (loc.ADDRESS1 ||' ' ||loc.ADDRESS2 ||' ' ||loc.ADDRESS3 ||' ' ||loc.ADDRESS4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PartyIdentifier1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PartyIdentifier2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PartyIdentifier3, ct1.CONTACT_POINT_ID PhoneContactPointID, ct1.RAW_PHONE_NUMBER PhoneNumber, ct1.PHONE_EXTENSION PhoneExtension, ct1.PHONE_LINE_TYPE PhoneLineType, ct2.contact_point_id WebContactPointID, ct2.url Url, p.ATTRIBUTE1 CustomAttribute1, p.ATTRIBUTE2 CustomAttribute2, p.ATTRIBUTE3 CustomAttribute3, p.ATTRIBUTE4 CustomAttribute4, p.ATTRIBUTE5 CustomAttribute5, p.ATTRIBUTE6 CustomAttribute6, p.ATTRIBUTE7 CustomAttribute7, p.ATTRIBUTE8 CustomAttribute8, p.ATTRIBUTE9 CustomAttribute9, p.ATTRIBUTE10 CustomAttribute10, p.LAST_UPDATE_DATE PartyDate, opr.LAST_UPDATE_DATE OrganizationDate, ps.LAST_UPDATE_DATE PatySiteDate, loc.LAST_UPDATE_DATE LocationDate, ct1.LAST_UPDATE_DATE ContactPointDate, ct2.LAST_UPDATE_DATE URLDate, adi1.LAST_UPDATE_DATE Adi1Date, adi2.LAST_UPDATE_DATE Adi2Date, adi3.LAST_UPDATE_DATE Adi3Date, ed.LAST_UPDATE_DATE EmailDomainDate, DECODE((NVL(ps.status,'A') ||NVL(p.status,'A') || NVL(per.status, 'A') || NVL(hr.status, 'A')),'AAAA','ADD','DELETE') Operation, p.status PartyStatus, ps.status PartySiteStatus, ct1.STATUS ContactPointStatus, ct2.STATUS URLStatus, ed.status EmailDomainStatus, opr.status OrganizationProfileStatus, loc.status_flag LocationStatus, adi1.status_flag Adi1Status, adi2.status_flag Adi2Status, adi3.status_flag Adi3Status, sync.idt_name, sync.entity_type_code, NULL PartyNameID, NULL AddtnlNameDate, NULL AddtnlNameStatus, per.person_first_name || ' ' || per.person_last_name ContactName, per.party_id ContactIdentifier, per.LAST_UPDATE_DATE ContactDate, per.Status ContactStatus, hr.relationship_id RelationshipId FROM zcq_sync_ids sync, hz_parties p, hz_parties per, hz_organization_profiles opr, hz_party_sites ps, hz_locations loc, hz_contact_points ct1, hz_contact_points ct2, hz_email_domains ed, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3, hz_relationships hr WHERE sync.entity_id = p.party_id AND sync.entity_type_code = 'ORGANIZATION' AND p.party_type = 'ORGANIZATION' AND p.party_id = opr.PARTY_ID AND trunc(sysdate) BETWEEN opr.effective_start_date AND opr.effective_end_date AND (opr.ACTUAL_CONTENT_SOURCE = 'SST' AND opr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND p.party_id = ps.party_id(+) AND ps.location_id = loc.location_id(+) AND (NVL(ps.START_DATE_ACTIVE(+),sysdate) <=sysdate AND trunc(NVL(ps.END_DATE_ACTIVE(+),sysdate+1)) >=trunc(sysdate)) AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct1.PRIMARY_FLAG(+) = 'Y' AND ct1.STATUS(+) = 'A' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ed.party_id(+) AND ed.STATUS(+) = 'A' AND p.party_id = adi1.party_id(+) AND adi1.party_identifier_type(+) ='DUMMY1' AND adi1.STATUS_FLAG(+) = 'A' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.party_identifier_type(+) ='DUMMY2' AND adi2.STATUS_FLAG(+) = 'A' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.party_identifier_type(+) ='DUMMY3' AND adi3.STATUS_FLAG(+) = 'A' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = ct2.owner_table_id(+) AND ct2.owner_table_name(+) = 'HZ_PARTIES' AND ct2.contact_point_type(+) = 'WEB' AND ct2.status(+) = 'A' AND ct2.primary_flag(+) = 'Y' AND ( Nvl(ct2.start_date(+), SYSDATE) <= SYSDATE AND trunc(Nvl(ct2.end_date(+), SYSDATE + 1)) >= trunc(SYSDATE )) AND HR.object_id = P.party_id AND HR.subject_type = 'PERSON' AND HR.relationship_code = 'CONTACT_OF' AND HR.relationship_type = 'CONTACT' AND Trunc(SYSDATE) BETWEEN Nvl(HR.start_date, SYSDATE - 1) AND Nvl(HR.end_date, SYSDATE + 1) AND PER.party_id = HR.subject_id AND PER.party_type = 'PERSON' UNION ALL SELECT p.PARTY_ID OrganizationIdentifier , p.PARTY_NUMBER RegistryIdentifier, opr.ORGANIZATION_PROFILE_ID OrganizationProfileID, pn.party_name OrganizationName, opr.ORGANIZATION_TYPE OrganizationType, opr.DUNS_NUMBER_C DUNSNumber, opr.INCORP_YEAR YearIncorporated, opr.YEAR_ESTABLISHED YearEstablished, opr.TRADING_PARTNER_IDENTIFIER TradingPartnerIdentifier, opr.STOCK_SYMBOL StockSymbol, opr.SIC_CODE_TYPE SICCodeType, opr.SIC_CODE SICCode, opr.JGZZ_FISCAL_CODE TaxRegistrationNumber, ed.email_domain_id eMailDomainID, ed.domain_name eMailDomain, loc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, loc.ADDRESS1 AddressLine1, loc.ADDRESS2 AddressLine2, loc.ADDRESS3 AddressLine3, loc.ADDRESS4 AddressLine4, loc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, loc.CITY City, loc.COUNTY County, loc.STATE CountrySubEntity, loc.PROVINCE Province, loc.COUNTRY Country, loc.POSTAL_CODE PostalCode, loc.POSTAL_PLUS4_CODE PostalCodeExt, (loc.ADDRESS1 ||' ' ||loc.ADDRESS2 ||' ' ||loc.ADDRESS3 ||' ' ||loc.ADDRESS4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PartyIdentifier1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PartyIdentifier2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PartyIdentifier3, ct1.CONTACT_POINT_ID PhoneContactPointID, ct1.RAW_PHONE_NUMBER PhoneNumber, ct1.PHONE_EXTENSION PhoneExtension, ct1.PHONE_LINE_TYPE PhoneLineType, ct2.contact_point_id WebContactPointID, ct2.url Url, p.ATTRIBUTE1 CustomAttribute1, p.ATTRIBUTE2 CustomAttribute2, p.ATTRIBUTE3 CustomAttribute3, p.ATTRIBUTE4 CustomAttribute4, p.ATTRIBUTE5 CustomAttribute5, p.ATTRIBUTE6 CustomAttribute6, p.ATTRIBUTE7 CustomAttribute7, p.ATTRIBUTE8 CustomAttribute8, p.ATTRIBUTE9 CustomAttribute9, p.ATTRIBUTE10 CustomAttribute10, p.LAST_UPDATE_DATE PartyDate, opr.LAST_UPDATE_DATE OrganizationDate, ps.LAST_UPDATE_DATE PatySiteDate, loc.LAST_UPDATE_DATE LocationDate, ct1.LAST_UPDATE_DATE ContactPointDate, ct2.LAST_UPDATE_DATE URLDate, adi1.LAST_UPDATE_DATE Adi1Date, adi2.LAST_UPDATE_DATE Adi2Date, adi3.LAST_UPDATE_DATE Adi3Date, ed.LAST_UPDATE_DATE EmailDomainDate, DECODE((NVL(ps.status,'A') ||NVL(p.status,'A')||NVL(pn.status_flag,'A') || NVL(per.status, 'A') || NVL(hr.status, 'A')),'AAAAA','ADD','DELETE') Operation, p.status PartyStatus, ps.status PartySiteStatus, ct1.STATUS ContactPointStatus, ct2.STATUS URLStatus, ed.status EmailDomainStatus, opr.status OrganizationProfileStatus, loc.status_flag LocationStatus, adi1.status_flag Adi1Status, adi2.status_flag Adi2Status, adi3.status_flag Adi3Status, sync.idt_name, sync.entity_type_code, pn.party_name_id PartyNameID, pn.last_update_date AddtnlNameDate, pn.status_flag AddtnlNameStatus, per.person_first_name || ' ' || per.person_last_name ContactName, per.party_id ContactIdentifier, per.LAST_UPDATE_DATE ContactDate, per.Status ContactStatus, hr.relationship_id RelationshipId FROM zcq_sync_ids sync, hz_parties p, hz_parties per, hz_organization_profiles opr, hz_party_sites ps, hz_locations loc, hz_contact_points ct1, hz_contact_points ct2, hz_email_domains ed, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3, hz_addtnl_party_names pn, hz_relationships hr WHERE sync.entity_id = p.party_id AND sync.entity_type_code = 'ORGANIZATION' AND p.party_type = 'ORGANIZATION' AND p.party_id = opr.PARTY_ID AND trunc(sysdate) BETWEEN opr.effective_start_date AND opr.effective_end_date AND (opr.ACTUAL_CONTENT_SOURCE = 'SST' AND opr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND p.party_id = pn.party_id AND p.party_id = ps.party_id(+) AND ps.location_id = loc.location_id(+) AND (NVL(ps.START_DATE_ACTIVE(+),sysdate) <=sysdate AND trunc(NVL(ps.END_DATE_ACTIVE(+),sysdate+1)) >=trunc(sysdate)) AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct1.PRIMARY_FLAG(+) = 'Y' AND ct1.STATUS(+) = 'A' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ed.party_id(+) AND ed.STATUS(+) = 'A' AND p.party_id = adi1.party_id(+) AND adi1.party_identifier_type(+) ='DUMMY1' AND adi1.STATUS_FLAG(+) = 'A' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.party_identifier_type(+) ='DUMMY2' AND adi2.STATUS_FLAG(+) = 'A' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.party_identifier_type(+) ='DUMMY3' AND adi3.STATUS_FLAG(+) = 'A' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = ct2.owner_table_id(+) AND ct2.owner_table_name(+) = 'HZ_PARTIES' AND ct2.contact_point_type(+) = 'WEB' AND ct2.status(+) = 'A' AND ct2.primary_flag(+) = 'Y' AND ( Nvl(ct2.start_date(+), SYSDATE) <= SYSDATE AND trunc(Nvl(ct2.end_date(+), SYSDATE + 1)) >= (SYSDATE )) AND HR.object_id = P.party_id AND HR.subject_type = 'PERSON' AND HR.relationship_code = 'CONTACT_OF' AND HR.relationship_type = 'CONTACT' AND Trunc(SYSDATE) BETWEEN Nvl(HR.start_date, SYSDATE - 1) AND Nvl(HR.end_date, SYSDATE + 1) AND PER.party_id = HR.subject_id AND PER.party_type = 'PERSON' UNION ALL SELECT p.PARTY_ID OrganizationIdentifier , p.PARTY_NUMBER RegistryIdentifier, opr.ORGANIZATION_PROFILE_ID OrganizationProfileID, opr.Organization_Name OrganizationName, opr.ORGANIZATION_TYPE OrganizationType, opr.DUNS_NUMBER_C DUNSNumber, opr.INCORP_YEAR YearIncorporated, opr.YEAR_ESTABLISHED YearEstablished, opr.TRADING_PARTNER_IDENTIFIER TradingPartnerIdentifier, opr.STOCK_SYMBOL StockSymbol, opr.SIC_CODE_TYPE SICCodeType, opr.SIC_CODE SICCode, opr.JGZZ_FISCAL_CODE TaxRegistrationNumber, ed.email_domain_id eMailDomainID, ed.domain_name eMailDomain, perloc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, perloc.ADDRESS1 AddressLine1, perloc.ADDRESS2 AddressLine2, perloc.ADDRESS3 AddressLine3, perloc.ADDRESS4 AddressLine4, perloc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, perloc.CITY City, perloc.COUNTY County, perloc.STATE CountrySubEntity, perloc.PROVINCE Province, perloc.COUNTRY Country, perloc.POSTAL_CODE PostalCode, perloc.POSTAL_PLUS4_CODE PostalCodeExt, (perloc.ADDRESS1 ||' ' ||perloc.ADDRESS2 ||' ' ||perloc.ADDRESS3 ||' ' ||perloc.ADDRESS4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PartyIdentifier1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PartyIdentifier2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PartyIdentifier3, ct1.CONTACT_POINT_ID PhoneContactPointID, ct1.RAW_PHONE_NUMBER PhoneNumber, ct1.PHONE_EXTENSION PhoneExtension, ct1.PHONE_LINE_TYPE PhoneLineType, ct2.contact_point_id WebContactPointID, ct2.url Url, p.ATTRIBUTE1 CustomAttribute1, p.ATTRIBUTE2 CustomAttribute2, p.ATTRIBUTE3 CustomAttribute3, p.ATTRIBUTE4 CustomAttribute4, p.ATTRIBUTE5 CustomAttribute5, p.ATTRIBUTE6 CustomAttribute6, p.ATTRIBUTE7 CustomAttribute7, p.ATTRIBUTE8 CustomAttribute8, p.ATTRIBUTE9 CustomAttribute9, p.ATTRIBUTE10 CustomAttribute10, p.LAST_UPDATE_DATE PartyDate, opr.LAST_UPDATE_DATE OrganizationDate, ps.LAST_UPDATE_DATE PatySiteDate, perloc.LAST_UPDATE_DATE LocationDate, ct1.LAST_UPDATE_DATE ContactPointDate, ct2.LAST_UPDATE_DATE URLDate, adi1.LAST_UPDATE_DATE Adi1Date, adi2.LAST_UPDATE_DATE Adi2Date, adi3.LAST_UPDATE_DATE Adi3Date, ed.LAST_UPDATE_DATE EmailDomainDate, DECODE((NVL(ps.status,'A') ||NVL(p.status,'A') || NVL(per.status, 'A') || NVL(hr.status, 'A')),'AAAA','ADD','DELETE') Operation, p.status PartyStatus, ps.status PartySiteStatus, ct1.STATUS ContactPointStatus, ct2.STATUS URLStatus, ed.status EmailDomainStatus, opr.status OrganizationProfileStatus, perloc.status_flag LocationStatus, adi1.status_flag Adi1Status, adi2.status_flag Adi2Status, adi3.status_flag Adi3Status, sync.idt_name, sync.entity_type_code, NULL PartyNameID, NULL AddtnlNameDate, NULL AddtnlNameStatus, per.person_first_name || ' ' || per.person_last_name ContactName, per.party_id ContactIdentifier, per.LAST_UPDATE_DATE ContactDate, per.Status ContactStatus, hr.relationship_id RelationshipId FROM zcq_sync_ids sync, hz_parties p, hz_parties per, hz_organization_profiles opr, hz_party_sites ps, hz_locations perloc, hz_contact_points ct1, hz_contact_points ct2, hz_email_domains ed, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3, hz_relationships hr WHERE sync.entity_id = p.party_id AND sync.entity_type_code = 'ORGANIZATION' AND p.party_type = 'ORGANIZATION' AND p.party_id = opr.PARTY_ID AND trunc(sysdate) BETWEEN opr.effective_start_date AND opr.effective_end_date AND (opr.ACTUAL_CONTENT_SOURCE = 'SST' AND opr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND ps.location_id = perloc.location_id(+) AND (NVL(ps.START_DATE_ACTIVE(+),sysdate) <=sysdate AND trunc(NVL(ps.END_DATE_ACTIVE(+),sysdate+1)) >=trunc(sysdate)) AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct1.PRIMARY_FLAG(+) = 'Y' AND ct1.STATUS(+) = 'A' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ed.party_id(+) AND ed.STATUS(+) = 'A' AND p.party_id = adi1.party_id(+) AND adi1.party_identifier_type(+) ='DUMMY1' AND adi1.STATUS_FLAG(+) = 'A' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.party_identifier_type(+) ='DUMMY2' AND adi2.STATUS_FLAG(+) = 'A' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.party_identifier_type(+) ='DUMMY3' AND adi3.STATUS_FLAG(+) = 'A' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = ct2.owner_table_id(+) AND ct2.owner_table_name(+) = 'HZ_PARTIES' AND ct2.contact_point_type(+) = 'WEB' AND ct2.status(+) = 'A' AND ct2.primary_flag(+) = 'Y' AND ( Nvl(ct2.start_date(+), SYSDATE) <= SYSDATE AND trunc(Nvl(ct2.end_date(+), SYSDATE + 1)) >= trunc(SYSDATE )) AND HR.object_id = P.party_id AND HR.subject_type = 'PERSON' AND HR.relationship_code = 'CONTACT_OF' AND HR.relationship_type = 'CONTACT' AND Trunc(SYSDATE) BETWEEN Nvl(HR.start_date, SYSDATE - 1) AND Nvl(HR.end_date, SYSDATE + 1) AND PER.party_id = HR.subject_id AND PER.party_id = PS.party_id(+) AND ( Nvl(PS.start_date_active(+), SYSDATE) <= SYSDATE AND trunc(Nvl(PS.end_date_active(+), SYSDATE + 1)) >= trunc(SYSDATE )) AND PER.party_type = 'PERSON' UNION ALL SELECT p.PARTY_ID OrganizationIdentifier , p.PARTY_NUMBER RegistryIdentifier, opr.ORGANIZATION_PROFILE_ID OrganizationProfileID, pn.party_name OrganizationName, opr.ORGANIZATION_TYPE OrganizationType, opr.DUNS_NUMBER_C DUNSNumber, opr.INCORP_YEAR YearIncorporated, opr.YEAR_ESTABLISHED YearEstablished, opr.TRADING_PARTNER_IDENTIFIER TradingPartnerIdentifier, opr.STOCK_SYMBOL StockSymbol, opr.SIC_CODE_TYPE SICCodeType, opr.SIC_CODE SICCode, opr.JGZZ_FISCAL_CODE TaxRegistrationNumber, ed.email_domain_id eMailDomainID, ed.domain_name eMailDomain, perloc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, perloc.ADDRESS1 AddressLine1, perloc.ADDRESS2 AddressLine2, perloc.ADDRESS3 AddressLine3, perloc.ADDRESS4 AddressLine4, perloc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, perloc.CITY City, perloc.COUNTY County, perloc.STATE CountrySubEntity, perloc.PROVINCE Province, perloc.COUNTRY Country, perloc.POSTAL_CODE PostalCode, perloc.POSTAL_PLUS4_CODE PostalCodeExt, (perloc.ADDRESS1 ||' ' ||perloc.ADDRESS2 ||' ' ||perloc.ADDRESS3 ||' ' ||perloc.ADDRESS4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PartyIdentifier1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PartyIdentifier2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PartyIdentifier3, ct1.CONTACT_POINT_ID PhoneContactPointID, ct1.RAW_PHONE_NUMBER PhoneNumber, ct1.PHONE_EXTENSION PhoneExtension, ct1.PHONE_LINE_TYPE PhoneLineType, ct2.contact_point_id WebContactPointID, ct2.url Url, p.ATTRIBUTE1 CustomAttribute1, p.ATTRIBUTE2 CustomAttribute2, p.ATTRIBUTE3 CustomAttribute3, p.ATTRIBUTE4 CustomAttribute4, p.ATTRIBUTE5 CustomAttribute5, p.ATTRIBUTE6 CustomAttribute6, p.ATTRIBUTE7 CustomAttribute7, p.ATTRIBUTE8 CustomAttribute8, p.ATTRIBUTE9 CustomAttribute9, p.ATTRIBUTE10 CustomAttribute10, p.LAST_UPDATE_DATE PartyDate, opr.LAST_UPDATE_DATE OrganizationDate, ps.LAST_UPDATE_DATE PatySiteDate, perloc.LAST_UPDATE_DATE LocationDate, ct1.LAST_UPDATE_DATE ContactPointDate, ct2.LAST_UPDATE_DATE URLDate, adi1.LAST_UPDATE_DATE Adi1Date, adi2.LAST_UPDATE_DATE Adi2Date, adi3.LAST_UPDATE_DATE Adi3Date, ed.LAST_UPDATE_DATE EmailDomainDate, DECODE((NVL(ps.status,'A') ||NVL(p.status,'A')||NVL(pn.status_flag,'A') || NVL(per.status, 'A') || NVL(hr.status, 'A')),'AAAAA','ADD','DELETE') Operation, p.status PartyStatus, ps.status PartySiteStatus, ct1.STATUS ContactPointStatus, ct2.STATUS URLStatus, ed.status EmailDomainStatus, opr.status OrganizationProfileStatus, perloc.status_flag LocationStatus, adi1.status_flag Adi1Status, adi2.status_flag Adi2Status, adi3.status_flag Adi3Status, sync.idt_name, sync.entity_type_code, pn.party_name_id PartyNameID, pn.last_update_date AddtnlNameDate, pn.status_flag AddtnlNameStatus, per.person_first_name || ' ' || per.person_last_name ContactName, per.party_id ContactIdentifier, per.LAST_UPDATE_DATE ContactDate, per.Status ContactStatus, hr.relationship_id RelationshipId FROM zcq_sync_ids sync, hz_parties p, hz_parties per, hz_organization_profiles opr, hz_party_sites ps, hz_locations perloc, hz_contact_points ct1, hz_contact_points ct2, hz_email_domains ed, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3, hz_addtnl_party_names pn, hz_relationships hr WHERE sync.entity_id = p.party_id AND sync.entity_type_code = 'ORGANIZATION' AND p.party_type = 'ORGANIZATION' AND p.party_id = opr.PARTY_ID AND trunc(sysdate) BETWEEN opr.effective_start_date AND opr.effective_end_date AND (opr.ACTUAL_CONTENT_SOURCE = 'SST' AND opr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND p.party_id = pn.party_id AND ps.location_id = perloc.location_id(+) AND (NVL(ps.START_DATE_ACTIVE(+),sysdate) <=sysdate AND trunc(NVL(ps.END_DATE_ACTIVE(+),sysdate+1)) >=trunc(sysdate)) AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct1.PRIMARY_FLAG(+) = 'Y' AND ct1.STATUS(+) = 'A' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ed.party_id(+) AND ed.STATUS(+) = 'A' AND p.party_id = adi1.party_id(+) AND adi1.party_identifier_type(+) ='DUMMY1' AND adi1.STATUS_FLAG(+) = 'A' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.party_identifier_type(+) ='DUMMY2' AND adi2.STATUS_FLAG(+) = 'A' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.party_identifier_type(+) ='DUMMY3' AND adi3.STATUS_FLAG(+) = 'A' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = ct2.owner_table_id(+) AND ct2.owner_table_name(+) = 'HZ_PARTIES' AND ct2.contact_point_type(+) = 'WEB' AND ct2.status(+) = 'A' AND ct2.primary_flag(+) = 'Y' AND ( Nvl(ct2.start_date(+), SYSDATE) <= SYSDATE AND trunc(Nvl(ct2.end_date(+), SYSDATE + 1)) >= trunc(SYSDATE )) AND HR.object_id = P.party_id AND HR.subject_type = 'PERSON' AND HR.relationship_code = 'CONTACT_OF' AND Trunc(SYSDATE) BETWEEN Nvl(HR.start_date, SYSDATE - 1) AND Nvl(HR.end_date, SYSDATE + 1) AND HR.relationship_type = 'CONTACT' AND PER.party_id = HR.subject_id AND PS.location_id = PERLOC.location_id(+) AND PER.party_id = PS.party_id(+) AND ( Nvl(PS.start_date_active(+), SYSDATE) <= SYSDATE AND trunc(Nvl(PS.end_date_active(+), SYSDATE + 1)) >= trunc(SYSDATE )) AND PER.party_type = 'PERSON'