[Home] [Help]
3655: --
3656: AND hpr.RELATIONSHIP_CODE IN
3657: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3658: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3659: AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3660: AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3661: --
3662: -- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3663: AND ((hpr.status = 'I')
3656: AND hpr.RELATIONSHIP_CODE IN
3657: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3658: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3659: AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3660: AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3661: --
3662: -- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3663: AND ((hpr.status = 'I')
3664: OR
3763: PARTY.PERSON_FIRST_NAME FIRST_NAME,
3764: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3765: PARTY.PERSON_LAST_NAME LAST_NAME
3766: FROM
3767: HZ_PARTIES PARTY,
3768: HZ_CONTACT_POINTS CT_POINT1
3769: WHERE PARTY.PARTY_ID = l_party_id
3770: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3771: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3766: FROM
3767: HZ_PARTIES PARTY,
3768: HZ_CONTACT_POINTS CT_POINT1
3769: WHERE PARTY.PARTY_ID = l_party_id
3770: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3771: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3772: AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3773: AND CT_POINT1.STATUS (+)= 'A'
3774: AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
3786: PARTY.PERSON_FIRST_NAME FIRST_NAME,
3787: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3788: PARTY.PERSON_LAST_NAME LAST_NAME
3789: FROM
3790: HZ_PARTIES PARTY,
3791: HZ_PARTIES PARTY2,
3792: HZ_PARTIES PARTY3,
3793: HZ_CONTACT_POINTS CT_POINT1,
3794: HZ_RELATIONSHIPS REL
3787: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3788: PARTY.PERSON_LAST_NAME LAST_NAME
3789: FROM
3790: HZ_PARTIES PARTY,
3791: HZ_PARTIES PARTY2,
3792: HZ_PARTIES PARTY3,
3793: HZ_CONTACT_POINTS CT_POINT1,
3794: HZ_RELATIONSHIPS REL
3795: WHERE PARTY.PARTY_ID = l_party_id
3788: PARTY.PERSON_LAST_NAME LAST_NAME
3789: FROM
3790: HZ_PARTIES PARTY,
3791: HZ_PARTIES PARTY2,
3792: HZ_PARTIES PARTY3,
3793: HZ_CONTACT_POINTS CT_POINT1,
3794: HZ_RELATIONSHIPS REL
3795: WHERE PARTY.PARTY_ID = l_party_id
3796: AND (
3810: --
3811: AND REL.RELATIONSHIP_CODE IN
3812: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3813: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3814: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3815: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3816: AND REL.DIRECTIONAL_FLAG = 'F'
3817: AND REL.STATUS = 'A'
3818: --
3811: AND REL.RELATIONSHIP_CODE IN
3812: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3813: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3814: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3815: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3816: AND REL.DIRECTIONAL_FLAG = 'F'
3817: AND REL.STATUS = 'A'
3818: --
3819: AND REL.SUBJECT_ID = PARTY2.PARTY_ID
3820: AND (PARTY2.PARTY_TYPE = 'PERSON'
3821: OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
3822: AND REL.OBJECT_ID = PARTY3.PARTY_ID
3823: AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
3824: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3825: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3826: AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3827: AND CT_POINT1.STATUS (+)= 'A'
3828: AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE'
3903: -- added status flag check 20 nov 2001
3904: AND prt.status = 'A')
3905: WHERE RES.CATEGORY = 'PARTNER'
3906: AND exists (select 'A'
3907: from hz_parties par
3908: where par.party_id = res.source_id
3909: and par.party_type = 'PARTY_RELATIONSHIP');
3910:
3911: EXCEPTION