AR Customer Relationships Listing

SELECT SUB.PARtY_NAME "PrimaryCustomer",
       sub.party_number "PrimaryRegistryId",
       lrt.meaning "RelationshipType",
       lrc.MEANING || ' / ' || lrcb.MEANING "RelationshipPhrase",
       obj.party_name "RelatedCustomerName",
       obj.PARTY_NUMBER "RelatedRegistryId",
       rel.START_DATE "From Date",
       rel.end_date "To Date",
       to_char(rel.START_DATE,'DD-MON-YY') FROM_DATE_DISP,
       to_char(rel.end_date,'DD-MON-YY') TO_DATE_DISP
--From Date",
--       rel.end_date "To Date"
  FROM hz_relationships rel
       ,hz_parties sub
       ,hz_relationship_types rt
       ,hz_parties obj
       ,AR_LOOKUPS lrt
       ,ar_lookups lrc
       ,ar_lookups lrcb
 WHERE rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
   AND rel.SUBJECT_TYPE = 'ORGANIZATION'
   AND rel.subject_id = sub.party_id
   AND rel.object_id = obj.party_id
   AND lrt.lookup_type  = 'HZ_RELATIONSHIP_TYPE'
   AND lrt.lookup_code  = rel.relationship_type
   AND lrc.lookup_type = 'PARTY_RELATIONS_TYPE'
   AND lrc.LOOKUP_code = rel.relationship_code
   AND rt.relationship_type = rel.relationship_type
   AND rt.SUBJECT_TYPE = rel.subject_type
   AND rt.OBJECT_TYPE = rel.object_type
   AND rt.FORWARD_REL_CODE = rel.RELATIONSHIP_CODE
   AND rt.BACKWARD_REL_CODE = lrcb.lookup_code
   AND lrcb.lookup_type = 'PARTY_RELATIONS_TYPE'
   AND rt.hierarchical_flag = 'Y'
   AND exists
   (
     SELECT 1
	   FROM hz_cust_accounts subacc
      WHERE subacc.party_id = rel.subject_id
   )
   AND exists
   (
     SELECT 1
	   FROM hz_cust_accounts objacc
      WHERE objacc.party_id = rel.object_id
   )
 &lp_customer_name
&lp_registry_id
&lp_rel_type
--ORDER BY 1 ASC,2 ASC,3 ASC , sub.party_number , rel.relationship_type , rel.relationship_code
order by 1,2,3,4,5,6,7,8