AR Customer Listing - Detail

SELECT  NULL  null_cbp, 
        RALU10.MEANING    	                         PRIMARY_cbp,
        RALU1.MEANING                                          USAGE_cbp,
        RALU8.MEANING                                          STATUS_cbp,
        RASU1.LOCATION                                        LOCATION_cbp,
        RASU2.LOCATION                                        BILL_TO_LOCATION_cbp,
        ARCPC.NAME                                               CREDIT_PROFILE_CLASS_cbp,
        RASU1.SIC_CODE                                        SIC_CODE_cbp,
        RAT.NAME                                                     PAYMENT_TERM_cbp,
        RALU4.MEANING                                          GSA_INDICATOR_cbp,
        RALU5.MEANING                                          SHIP_PARTIAL_cbp,
        RALU6.MEANING                                          SHIP_VIA_cbp,
        RALU7.MEANING                                          FOB_POINT_cbp,
        SOOT.NAME                                                  ORDER_TYPE_cbp,
       RASU1.FREIGHT_TERM                               FREIGHT_TERM_cbp,
       SOPL.NAME                                                  PRICE_LIST_cbp,
       RASU1.CUST_ACCT_SITE_ID                    ADDRESS_ID_cbp,
       DECODE(RASU1.CONTACT_ID, NULL, NULL,
              SUBSTRB(PARTY.PERSON_LAST_NAME,1,50)||', '|| SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) ) CONTACT_cbp,
       RASU1.TAX_REFERENCE TAX_REFERENCE_cbp ,
       RASU1.TAX_CODE  TAX_CODE_cbp,
       ARVT.TAX_RATE TAX_RATE_cbp,
        SOLU1.MEANING	                          FREIGHT_TERMS_cbp,
        FCLU.MEANING                                            DEMAND_CLASS_cbp,
        RASU1.SITE_USE_ID                                   SITE_USE_ID_cbp,
        null            	        TERRITORY_cbp,
        ORGD.ORGANIZATION_NAME                   WAREHOUSE_cbp,
        ORGF.DESCRIPTION                                   CARRIER_cbp, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_territory', 'AR', 'CT#', 101, NULL, RATT.TERRITORY_ID, 'ALL', 'Y', 'VALUE') D_TERRITORY
FROM   	HZ_CUST_SITE_USES                 RASU1,
       	HZ_CUST_SITE_USES                 RASU2,
       	RA_TERMS                     RAT,
       	SO_ORDER_TYPES               SOOT,
       	SO_PRICE_LISTS               SOPL,
       	AR_VAT_TAX 		ARVT,
       	AR_LOOKUPS                   RALU1,
       	AR_LOOKUPS                   RALU4,
       	AR_LOOKUPS                   RALU5,
       	AR_LOOKUPS                   RALU6,
       	AR_LOOKUPS                   RALU7,
       	AR_LOOKUPS                   RALU8,
       	AR_LOOKUPS                   RALU10,
       	SO_LOOKUPS	               SOLU1,
       	HZ_CUSTOMER_PROFILES         ARCP,
       	HZ_CUST_PROFILE_CLASSES  ARCPC,
      	HZ_CUST_ACCOUNT_ROLES                RACO,
	HZ_RELATIONSHIPS	REL,
	HZ_PARTIES	 PARTY,
       	FND_COMMON_LOOKUPS FCLU,
       	ORG_ORGANIZATION_DEFINITIONS ORGD,
       	ORG_FREIGHT ORGF,
       	RA_TERRITORIES  RATT
WHERE
          RALU1.LOOKUP_CODE = RASU1.SITE_USE_CODE                
AND  RALU1.LOOKUP_TYPE = 'SITE_USE_CODE'
AND  RALU10.LOOKUP_CODE = RASU1.PRIMARY_FLAG                
AND  RALU10.LOOKUP_TYPE = 'YES/NO'
AND  RALU4.LOOKUP_CODE(+) = RASU1.GSA_INDICATOR                
AND  RALU4.LOOKUP_TYPE(+) = 'YES/NO'
AND  RALU5.LOOKUP_CODE(+) = RASU1.SHIP_PARTIAL                 
AND  RALU5.LOOKUP_TYPE(+) = 'YES/NO'
AND  RALU6.LOOKUP_CODE(+) = RASU1.SHIP_VIA                     
AND  RALU6.LOOKUP_TYPE(+) = 'SHIP_VIA'
AND  RALU7.LOOKUP_CODE(+) = RASU1.FOB_POINT                    
AND  RALU7.LOOKUP_TYPE(+) = 'FOB'
AND  RALU8.LOOKUP_CODE = RASU1.STATUS                       
AND  RALU8.LOOKUP_TYPE = 'CODE_STATUS'
AND  RASU2.SITE_USE_ID(+) = RASU1.BILL_TO_SITE_USE_ID
AND  RAT.TERM_ID(+) = RASU1.PAYMENT_TERM_ID
AND  SOOT.ORDER_TYPE_ID(+) = RASU1.ORDER_TYPE_ID
AND  SOPL.PRICE_LIST_ID(+) = RASU1.PRICE_LIST_ID
AND  ARCP.SITE_USE_ID(+) = RASU1.SITE_USE_ID
AND  ARCPC.PROFILE_CLASS_ID(+) = ARCP.PROFILE_CLASS_ID
AND
       RACO.CUST_ACCOUNT_ROLE_ID (+) = RASU1.CONTACT_ID
AND RACO.PARTY_ID = REL.PARTY_ID(+)
AND RACO.ROLE_TYPE(+) = 'CONTACT'
AND REL.SUBJECT_ID = PARTY.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND  ARVT.TAX_CODE(+) = RASU1.TAX_CODE 
AND TRUNC(SYSDATE) BETWEEN NVL(ARVT.START_DATE,
                                                                     TRUNC(SYSDATE))                                                                   AND
                                                           NVL(ARVT.END_DATE,
                                                                    TRUNC(SYSDATE))
AND SOLU1.LOOKUP_CODE(+) = RASU1.FREIGHT_TERM
AND SOLU1.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND  FCLU.LOOKUP_CODE(+) = RASU1.DEMAND_CLASS_CODE 
AND  FCLU.LOOKUP_TYPE(+) = 'DEMAND_CLASS'
AND  ORGD.ORGANIZATION_ID(+) = RASU1.WAREHOUSE_ID
AND  ORGF.FREIGHT_CODE(+) = RASU1.SHIP_VIA   
AND  ORGF.ORGANIZATION_ID(+) =  :ITEM_FLEX_STRUCTURE
AND  RATT.TERRITORY_ID(+) = RASU1.TERRITORY_ID 
 and RASU1.CUST_ACCT_SITE_ID=:ADDRESS_ID_ca
ORDER BY 
	RALU10.MEANING,
	RALU1.MEANING,
                  RALU8.MEANING,
                  RASU1.LOCATION