[Home] [Help]
3204: --
3205: AND hpr.RELATIONSHIP_CODE IN
3206: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3207: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3208: AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3209: AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3210: --
3211: -- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3212: AND ((hpr.status = 'I')
3205: AND hpr.RELATIONSHIP_CODE IN
3206: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3207: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3208: AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3209: AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3210: --
3211: -- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3212: AND ((hpr.status = 'I')
3213: OR
3312: PARTY.PERSON_FIRST_NAME FIRST_NAME,
3313: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3314: PARTY.PERSON_LAST_NAME LAST_NAME
3315: FROM
3316: HZ_PARTIES PARTY,
3317: HZ_CONTACT_POINTS CT_POINT1
3318: WHERE PARTY.PARTY_ID = l_party_id
3319: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3320: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3315: FROM
3316: HZ_PARTIES PARTY,
3317: HZ_CONTACT_POINTS CT_POINT1
3318: WHERE PARTY.PARTY_ID = l_party_id
3319: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3320: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3321: AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3322: AND CT_POINT1.STATUS (+)= 'A'
3323: AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
3335: PARTY.PERSON_FIRST_NAME FIRST_NAME,
3336: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3337: PARTY.PERSON_LAST_NAME LAST_NAME
3338: FROM
3339: HZ_PARTIES PARTY,
3340: HZ_PARTIES PARTY2,
3341: HZ_PARTIES PARTY3,
3342: HZ_CONTACT_POINTS CT_POINT1,
3343: HZ_RELATIONSHIPS REL
3336: PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3337: PARTY.PERSON_LAST_NAME LAST_NAME
3338: FROM
3339: HZ_PARTIES PARTY,
3340: HZ_PARTIES PARTY2,
3341: HZ_PARTIES PARTY3,
3342: HZ_CONTACT_POINTS CT_POINT1,
3343: HZ_RELATIONSHIPS REL
3344: WHERE PARTY.PARTY_ID = l_party_id
3337: PARTY.PERSON_LAST_NAME LAST_NAME
3338: FROM
3339: HZ_PARTIES PARTY,
3340: HZ_PARTIES PARTY2,
3341: HZ_PARTIES PARTY3,
3342: HZ_CONTACT_POINTS CT_POINT1,
3343: HZ_RELATIONSHIPS REL
3344: WHERE PARTY.PARTY_ID = l_party_id
3345: AND (
3359: --
3360: AND REL.RELATIONSHIP_CODE IN
3361: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3362: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3363: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3364: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3365: AND REL.DIRECTIONAL_FLAG = 'F'
3366: AND REL.STATUS = 'A'
3367: --
3360: AND REL.RELATIONSHIP_CODE IN
3361: ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3362: 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3363: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3364: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3365: AND REL.DIRECTIONAL_FLAG = 'F'
3366: AND REL.STATUS = 'A'
3367: --
3368: AND REL.SUBJECT_ID = PARTY2.PARTY_ID
3369: AND (PARTY2.PARTY_TYPE = 'PERSON'
3370: OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
3371: AND REL.OBJECT_ID = PARTY3.PARTY_ID
3372: AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
3373: AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3374: AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3375: AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3376: AND CT_POINT1.STATUS (+)= 'A'
3377: AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE'
3452: -- added status flag check 20 nov 2001
3453: AND prt.status = 'A')
3454: WHERE RES.CATEGORY = 'PARTNER'
3455: AND exists (select 'A'
3456: from hz_parties par
3457: where par.party_id = res.source_id
3458: and par.party_type = 'PARTY_RELATIONSHIP');
3459:
3460: EXCEPTION