Post date: Feb 13, 2013 8:17:44 AM
/*Customer
===========================*/
SELECT distinct hca.CUST_ACCOUNT_ID CUSTOMER_ID
--, Get_Usage_Cust(hcasa.CUST_ACCT_SITE_ID,'BILL_TO') "BILL_TO_SITE_USE_ID"
--, Get_Usage_Cust(hcasa.CUST_ACCT_SITE_ID,'SHIP_TO') "SHIP_TO_SITE_USE_ID"
, hca.ACCOUNT_NUMBER CUSTOMER_NUMBER
, hcasa.cust_acct_site_id address_id
, hp.PARTY_NAME "NAMA PELANGGAN"
, hl.ADDRESS1 "ALAMAT 1"
, hl.ADDRESS2 "ALAMAT 2"
, hl.ADDRESS3 "ALAMAT 3"
, hl.ADDRESS4 "ALAMAT 4"
, hl.CITY "KOTA"
, hl.POSTAL_CODE "KODE POS"
, hl.PROVINCE "PROPINSI"
, ftt.TERRITORY_SHORT_NAME "NEGARA"
, DECODE(hps.STATUS,'A','ACT','UNKNOWN') "STATUS PELANGGAN (AKTIF/NON)"
, hcasa.ORG_ID
--, rt.name "PAYMENT TERMS"
, lv.MEANING cust_class
, l_sc.MEANING sales_chanel
, hca.STATUS ACCT_STATUS
, hca.CUSTOMER_TYPE
, hp.PARTY_TYPE
, hp.PARTY_NUMBER
, hps.IDENTIFYING_ADDRESS_FLAG
, hps.STATUS PARTY_STATUS
, hps.PARTY_SITE_NUMBER
, rt.description
FROM hz_cust_accounts hca
, hz_parties hp
, HZ_CUST_ACCT_SITES_all hcasa
, HZ_PARTY_SITES hps
, hz_locations hl
, FND_TERRITORIES_TL ftt
, RA_TERMS_TL rt
, AR_LOOKUPS LV
, SO_LOOKUPS L_SC
WHERE hca.CUST_ACCOUNT_ID=hcasa.CUST_ACCOUNT_ID
AND hca.PARTY_ID=hp.PARTY_ID
AND hcasa.PARTY_SITE_ID=hps.PARTY_SITE_ID
AND hps.LOCATION_ID=hl.LOCATION_ID
AND hl.COUNTRY=ftt.TERRITORY_CODE
and hca.PAYMENT_TERM_ID = rt.term_id(+)
--AND Get_Usage_Cust(hcasa.CUST_ACCT_SITE_ID,'SHIP_TO') IS NOT NULL
-- AND hcasa.ORG_ID=82
--and hca.CUST_ACCOUNT_ID = 1207
and hca.CUSTOMER_CLASS_CODE = lv.LOOKUP_CODE(+)
and hca.SALES_CHANNEL_CODE = l_sc.LOOKUP_CODE(+)
order by 6, 4, 1;
----and hp.PARTY_NAME like '%TOYOTA%MOTOR%';
++++++++++++++++++++++++++++++++++++
Tabel utk cek ORG_ID > ganti ORG_ID sesuai nama Company????
hr_organization_units