AR Customer Relationships Listing

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Customer Relationships Listing (XML)
Short Name: ARXCURLL_XML
DB package: AR_ARXCURLL_XMLP_PKG
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
Parameter Name SQL text Validation
Relationship Type High
 
LOV Oracle
Relationship Type Low
 
LOV Oracle
Registry ID High
 
LOV Oracle
Registry ID Low
 
LOV Oracle
Account Number High
 
LOV Oracle
Account Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle