AR Customer Listing - Detail

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Customer Listing - Detail (XML)
Short Name: RAXCUSLR_XML
DB package: AR_RAXCUSLR_XMLP_PKG
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
Parameter Name SQL text Validation
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Order By
 
LOV Oracle
Ask a question