VIEW: APPS.PA_CUSTOMER_LOV_V

Source

View Text - Preformatted

SELECT /*+ cardinality(100) first_rows */ pc.customer_name, pc.customer_number, pc.customer_id, r1.location, r.address1, r.address2, r.address3, r.address4,r.details,pc.status,r1.site_use_code, pc.party_id, pc.party_type, pc.customer_name || '('|| pc.customer_number || ')', decode(r1.site_use_code, 'BILL_TO', r1.location, NULL) bill_to_location FROM pa_customers_v pc, (SELECT hz_cs.cust_account_id cust_account_id, hz_cs.cust_acct_site_id address_id, hz_l.address1 address1, hz_l.address2 address2, hz_l.address3 address3, hz_l.address4 address4, hz_l.city||', ' ||nvl(hz_l.state, hz_l.province)||', ' || hz_l.county||', '||hz_l.country||','||hz_l.postal_code as details FROM hz_cust_acct_sites hz_cs, hz_party_sites hz_ps, hz_locations hz_l where NVL(hz_cs.status,'I')='A' and hz_cs.party_site_id = hz_ps.party_site_id and hz_ps.location_id = hz_l.location_id ) r, (SELECT cust_acct_site_id address_id, location,site_use_code FROM hz_cust_site_uses su WHERE ( su.site_use_code ='BILL_TO' or su.site_use_code='SHIP_TO') AND NVL(su.status,'I')='A') r1 WHERE r.cust_account_id(+) = pc.customer_id AND r.address_id = r1.address_id(+)

View Text - HTML Formatted

SELECT /*+ CARDINALITY(100) FIRST_ROWS */ PC.CUSTOMER_NAME
, PC.CUSTOMER_NUMBER
, PC.CUSTOMER_ID
, R1.LOCATION
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.DETAILS
, PC.STATUS
, R1.SITE_USE_CODE
, PC.PARTY_ID
, PC.PARTY_TYPE
, PC.CUSTOMER_NAME || '('|| PC.CUSTOMER_NUMBER || ')'
, DECODE(R1.SITE_USE_CODE
, 'BILL_TO'
, R1.LOCATION
, NULL) BILL_TO_LOCATION
FROM PA_CUSTOMERS_V PC
, (SELECT HZ_CS.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HZ_CS.CUST_ACCT_SITE_ID ADDRESS_ID
, HZ_L.ADDRESS1 ADDRESS1
, HZ_L.ADDRESS2 ADDRESS2
, HZ_L.ADDRESS3 ADDRESS3
, HZ_L.ADDRESS4 ADDRESS4
, HZ_L.CITY||'
, ' ||NVL(HZ_L.STATE
, HZ_L.PROVINCE)||'
, ' || HZ_L.COUNTY||'
, '||HZ_L.COUNTRY||'
, '||HZ_L.POSTAL_CODE AS DETAILS
FROM HZ_CUST_ACCT_SITES HZ_CS
, HZ_PARTY_SITES HZ_PS
, HZ_LOCATIONS HZ_L
WHERE NVL(HZ_CS.STATUS
, 'I')='A'
AND HZ_CS.PARTY_SITE_ID = HZ_PS.PARTY_SITE_ID
AND HZ_PS.LOCATION_ID = HZ_L.LOCATION_ID ) R
, (SELECT CUST_ACCT_SITE_ID ADDRESS_ID
, LOCATION
, SITE_USE_CODE
FROM HZ_CUST_SITE_USES SU
WHERE ( SU.SITE_USE_CODE ='BILL_TO' OR SU.SITE_USE_CODE='SHIP_TO')
AND NVL(SU.STATUS
, 'I')='A') R1
WHERE R.CUST_ACCOUNT_ID(+) = PC.CUSTOMER_ID
AND R.ADDRESS_ID = R1.ADDRESS_ID(+)