1. Tables and Views for Sales and Fusion Service
  2. ZCQ_ORG_CONTACT_ADDR_VIEW

ZCQ_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

PARTYNAMEID

CONTACTNAME

CONTACTIDENTIFIER

RELATIONSHIPID

Query

SQL_Statement

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,

pn.party_name_id PartyNameID,

per.person_title

||' '

||per.person_pre_name_adjunct

||' '

||per.person_first_name

||' '

||per.person_middle_name

|| ' '

||per.preferred_name

||' '

||per.person_last_name

||' '

||per.person_name_suffix ContactName,

per.party_id ContactIdentifier,

hr.relationship_id RelationshipId

FROM hz_parties P,

hz_parties per,

hz_organization_profiles opr,

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,

hz_party_sites ps

WHERE P.party_type = 'ORGANIZATION'

AND p.party_id = opr.party_id

AND p.status = 'A'

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 pn.status_flag = 'A'

AND p.party_id = ps.party_id(+)

AND ps.status(+) = 'A'

AND ( Nvl(ps.start_date_active(+), SYSDATE) <= SYSDATE

AND Nvl(ps.end_date_active(+), SYSDATE + 1) >= SYSDATE )

AND ps.location_id = loc.location_id(+)

AND p.party_id = ct1.owner_table_id(+)

AND ct1.owner_table_name(+) = 'HZ_PARTIES'

AND ct1.contact_point_type(+) = 'PHONE'

AND ct1.status(+) = 'A'

AND ct1.primary_flag(+) = 'Y'

AND ( Nvl(ct1.start_date(+), SYSDATE) <= SYSDATE

AND Nvl(ct1.end_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 Nvl(ct2.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.status_flag(+) = 'A'

AND adi1.party_identifier_type(+) = 'DUMMY1'

AND ( Nvl(adi1.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi1.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi2.party_id(+)

AND adi2.status_flag(+) = 'A'

AND adi2.party_identifier_type(+) = 'DUMMY2'

AND ( Nvl(adi2.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi2.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi3.party_id(+)

AND adi3.status_flag(+) = 'A'

AND adi3.party_identifier_type(+) = 'DUMMY3'

AND ( Nvl(ADI3.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(ADI3.expiration_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 HR.status(+)='A'

AND PER.party_id = HR.subject_id

AND PER.status = 'A'

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,

PER_LOC.location_id LOCATIONIDENTIFIER,

PER_ps.party_site_id ContactPersonPartySiteID,

PER_LOC.address1 AddressLine1,

PER_LOC.address2 AddressLine2,

PER_LOC.address3 AddressLine3,

PER_LOC.address4 AddressLine4,

PER_LOC.address_lines_phonetic PhoneticAddressLines,

PER_LOC.city City,

PER_LOC.county County,

PER_LOC.state CountrySubEntity,

PER_LOC.province Province,

PER_LOC.country Country,

PER_LOC.postal_code PostalCode,

PER_LOC.postal_plus4_code PostalCodeExt,

( PER_LOC.address1

||' '

||PER_LOC.address2

||' '

||PER_LOC.address3

||' '

||PER_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,

pn.party_name_id PartyNameId,

per.person_title

||' '

||per.person_pre_name_adjunct

||' '

||per.person_first_name

||' '

||per.person_middle_name

|| ' '

||per.preferred_name

||' '

||per.person_last_name

||' '

||per.person_name_suffix ContactName,

per.party_id ContactIdentifier,

hr.relationship_id RelationshipId

FROM hz_parties P,

hz_parties per,

hz_organization_profiles opr,

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,

hz_locations per_loc,

hz_party_sites per_ps

WHERE P.party_type = 'ORGANIZATION'

AND p.party_id = opr.party_id

AND p.status = 'A'

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 pn.status_flag = 'A'

AND p.party_id = ct1.owner_table_id(+)

AND ct1.owner_table_name(+) = 'HZ_PARTIES'

AND ct1.contact_point_type(+) = 'PHONE'

AND ct1.status(+) = 'A'

AND ct1.primary_flag(+) = 'Y'

AND ( Nvl(ct1.start_date(+), SYSDATE) <= SYSDATE

AND Nvl(ct1.end_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 Nvl(ct2.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.status_flag(+) = 'A'

AND adi1.party_identifier_type(+) = 'DUMMY1'

AND ( Nvl(adi1.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi1.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi2.party_id(+)

AND adi2.status_flag(+) = 'A'

AND adi2.party_identifier_type(+) = 'DUMMY2'

AND ( Nvl(adi2.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi2.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi3.party_id(+)

AND adi3.status_flag(+) = 'A'

AND adi3.party_identifier_type(+) = 'DUMMY3'

AND ( Nvl(ADI3.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(ADI3.expiration_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 HR.status(+)='A'

AND PER.party_id = HR.subject_id

AND PER_PS.location_id = PER_LOC.location_id(+)

AND PER.party_id = PER_PS.party_id(+)

AND ( Nvl(PER_PS.start_date_active(+), SYSDATE) <= SYSDATE

AND trunc(Nvl(PER_PS.end_date_active(+), SYSDATE + 1)) >= trunc(SYSDATE))

AND PER.status = 'A'

AND NVL(PER_ps.status,'A') = 'A'

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,

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,

NULL PartyNameId,

per.person_title

||' '

||per.person_pre_name_adjunct

||' '

||per.person_first_name

||' '

||per.person_middle_name

|| ' '

||per.preferred_name

||' '

||per.person_last_name

||' '

||per.person_name_suffix ContactName,

per.party_id ContactIdentifier,

hr.relationship_id RelationshipId

FROM hz_parties P,

hz_parties PER,

hz_organization_profiles opr,

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,

hz_party_sites PS

WHERE P.party_type = 'ORGANIZATION'

AND p.party_id = opr.party_id

AND p.status = 'A'

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.status(+) = 'A'

AND ( Nvl(ps.start_date_active(+), SYSDATE) <= SYSDATE

AND Nvl(ps.end_date_active(+), SYSDATE + 1) >= SYSDATE )

AND ps.location_id = loc.location_id(+)

AND p.party_id = ct1.owner_table_id(+)

AND ct1.owner_table_name(+) = 'HZ_PARTIES'

AND ct1.contact_point_type(+) = 'PHONE'

AND ct1.status(+) = 'A'

AND ct1.primary_flag(+) = 'Y'

AND ( Nvl(ct1.start_date(+), SYSDATE) <= SYSDATE

AND Nvl(ct1.end_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 Nvl(ct2.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.status_flag(+) = 'A'

AND adi1.party_identifier_type(+) = 'DUMMY1'

AND ( Nvl(adi1.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi1.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi2.party_id(+)

AND adi2.status_flag(+) = 'A'

AND adi2.party_identifier_type(+) = 'DUMMY2'

AND ( Nvl(adi2.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi2.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi3.party_id(+)

AND adi3.status_flag(+) = 'A'

AND adi3.party_identifier_type(+) = 'DUMMY3'

AND ( Nvl(ADI3.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(ADI3.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND HR.status(+)='A'

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.status = 'A'

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,

PER_LOC.location_id LOCATIONIDENTIFIER,

PER_ps.party_site_id ContactPersonPartySiteID,

PER_LOC.address1 AddressLine1,

PER_LOC.address2 AddressLine2,

PER_LOC.address3 AddressLine3,

PER_LOC.address4 AddressLine4,

PER_LOC.address_lines_phonetic PhoneticAddressLines,

PER_LOC.city City,

PER_LOC.county County,

PER_LOC.state CountrySubEntity,

PER_LOC.province Province,

PER_LOC.country Country,

PER_LOC.postal_code PostalCode,

PER_LOC.postal_plus4_code PostalCodeExt,

( PER_LOC.address1

||' '

||PER_LOC.address2

||' '

||PER_LOC.address3

||' '

||PER_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,

NULL PartyNameId,

per.person_title

||' '

||per.person_pre_name_adjunct

||' '

||per.person_first_name

||' '

||per.person_middle_name

|| ' '

||per.preferred_name

||' '

||per.person_last_name

||' '

||per.person_name_suffix ContactName,

per.party_id ContactIdentifier,

hr.relationship_id RelationshipId

FROM hz_parties P,

hz_parties PER,

hz_organization_profiles opr,

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,

hz_locations PER_LOC,

hz_party_sites PER_PS

WHERE P.party_type = 'ORGANIZATION'

AND p.party_id = opr.party_id

AND p.status = 'A'

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 = ct1.owner_table_id(+)

AND ct1.owner_table_name(+) = 'HZ_PARTIES'

AND ct1.contact_point_type(+) = 'PHONE'

AND ct1.status(+) = 'A'

AND ct1.primary_flag(+) = 'Y'

AND ( Nvl(ct1.start_date(+), SYSDATE) <= SYSDATE

AND Nvl(ct1.end_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 Nvl(ct2.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.status_flag(+) = 'A'

AND adi1.party_identifier_type(+) = 'DUMMY1'

AND ( Nvl(adi1.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi1.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi2.party_id(+)

AND adi2.status_flag(+) = 'A'

AND adi2.party_identifier_type(+) = 'DUMMY2'

AND ( Nvl(adi2.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(adi2.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND p.party_id = adi3.party_id(+)

AND adi3.status_flag(+) = 'A'

AND adi3.party_identifier_type(+) = 'DUMMY3'

AND ( Nvl(ADI3.issue_date(+), SYSDATE) <= SYSDATE

AND Nvl(ADI3.expiration_date(+), SYSDATE + 1) >= SYSDATE )

AND HR.status(+)='A'

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 PER_PS.location_id = PER_LOC.location_id(+)

AND PER.party_id = PER_PS.party_id(+)

AND ( Nvl(PER_PS.start_date_active(+), SYSDATE) <= SYSDATE

AND trunc(Nvl(PER_PS.end_date_active(+), SYSDATE + 1)) >= trunc(SYSDATE ))

AND PER.status = 'A'

AND NVL(PER_ps.status,'A') = 'A'

AND PER.party_type = 'PERSON'