客户和供应商账户信息

1.客户账户信息：

SELECT hp.party_name "客户名称", hca.account_number "客户编号", hcsua.location "帐户用途", hl.location_id, DECODE(hl.address1, NVL(hl.address1, 'NULL'), hl.address1, '') || DECODE(hl.address2, NVL(hl.address2, 'NULL'), ','||hl.address2, '') || DECODE(hl.address3, NVL(hl.address3, 'NULL'), ','||hl.address3, '') || DECODE(hl.address4, NVL(hl.address4, 'NULL'), ','||hl.address4 , '') || DECODE(hl.city, NVL(hl.city, 'NULL'), ','||hl.city , '') || DECODE(hl.postal_code, NVL(hl.postal_code, 'NULL'), ','||hl.postal_code, '') || DECODE(hl.state, NVL(hl.state, 'NULL'), ','||hl.state , '') || DECODE(hl.province, NVL(hl.province, 'NULL'), ','||hl.province, '') "地址", hcsua.gl_id_rec "应收款账户ID", hcsua.gl_id_rev "收入账户ID", DECODE(gccc.segment1, NVL(gccc.segment1, 'NULL'), '.'||gccc.segment1, '') || DECODE(gccc.segment2, NVL(gccc.segment2, 'NULL'), '.'||gccc.segment2, '') || DECODE(gccc.segment3, NVL(gccc.segment3, 'NULL'), '.'||gccc.segment3, '') || DECODE(gccc.segment4, NVL(gccc.segment4, 'NULL'), '.'||gccc.segment4, '') || DECODE(gccc.segment5, NVL(gccc.segment5, 'NULL'), '.'||gccc.segment5, '') || DECODE(gccc.segment6, NVL(gccc.segment6, 'NULL'), '.'||gccc.segment6, '') "应收款账户", DECODE(gccr.segment1, NVL(gccr.segment1, 'NULL'), '.'||gccr.segment1, '') || DECODE(gccr.segment2, NVL(gccr.segment2, 'NULL'), '.'||gccr.segment2, '') || DECODE(gccr.segment3, NVL(gccr.segment3, 'NULL'), '.'||gccr.segment3, '') || DECODE(gccr.segment4, NVL(gccr.segment4, 'NULL'), '.'||gccr.segment4, '') || DECODE(gccr.segment5, NVL(gccr.segment5, 'NULL'), '.'||gccr.segment5, '') || DECODE(gccr.segment6, NVL(gccr.segment6, 'NULL'), '.'||gccr.segment6, '') "收入账户" FROM ar.hz_cust_accounts hca, hz_parties hp, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, hz_party_sites hps, hz_locations hl, gl_code_combinations gccc, gl_code_combinations gccr WHERE hca.cust_account_id = hcasa.cust_account_id AND hca.party_id = hp.party_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND hcasa.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id AND hcsua.gl_id_rec = gccc.code_combination_id(+) AND hcsua.gl_id_rev = gccr.code_combination_id(+) AND hcasa.org_id = XX AND hca.account_number='XX' ORDER BY hp.party_name

 

2. 供应商信息

SELECT ass.vendor_name "供应商名称", ass.segment1 "供应商编号", assa.vendor_site_code "账户用途", DECODE(assa.address_line1, NVL(assa.address_line1, 'NULL'), assa.address_line1, '') || DECODE(assa.address_line2, NVL(assa.address_line2, 'NULL'), ',' || assa.address_line2, '') || DECODE(assa.address_line3, NVL(assa.address_line3, 'NULL'), ',' || assa.address_line3, '') || DECODE(assa.address_line4, NVL(assa.address_line4, 'NULL'), ',' || assa.address_line4, '') || DECODE(assa.city, NVL(assa.city, 'NULL'), ',' || assa.city, '') || DECODE(assa.state, NVL(assa.state, 'NULL'), ',' || assa.state, '') || DECODE(assa.zip, NVL(assa.zip, 'NULL'), ',' || assa.zip, '') || DECODE(assa.province, NVL(assa.province, 'NULL'), ',' || assa.province, '') "地址", DECODE(gccc.segment1, NVL(gccc.segment1, 'NULL'), gccc.segment1, '') || DECODE(gccc.segment2, NVL(gccc.segment2, 'NULL'), '.' || gccc.segment2, '') || DECODE(gccc.segment3, NVL(gccc.segment3, 'NULL'), '.' || gccc.segment3, '') || DECODE(gccc.segment4, NVL(gccc.segment4, 'NULL'), '.' || gccc.segment4, '') || DECODE(gccc.segment5, NVL(gccc.segment5, 'NULL'), '.' || gccc.segment5, '') || DECODE(gccc.segment6, NVL(gccc.segment6, 'NULL'), '.' || gccc.segment6, '') "负债帐户", DECODE(gccr.segment1, NVL(gccr.segment1, 'NULL'), gccr.segment1, '') || DECODE(gccr.segment2, NVL(gccr.segment2, 'NULL'), '.' || gccr.segment2, '') || DECODE(gccr.segment3, NVL(gccr.segment3, 'NULL'), '.' || gccr.segment3, '') || DECODE(gccr.segment4, NVL(gccr.segment4, 'NULL'), '.' || gccr.segment4, '') || DECODE(gccr.segment5, NVL(gccr.segment5, 'NULL'), '.' || gccr.segment5, '') || DECODE(gccr.segment6, NVL(gccr.segment6, 'NULL'), '.' || gccr.segment6, '') "预付帐户" FROM ap_suppliers ass, ap_supplier_sites_all assa, gl_code_combinations gccc, gl_code_combinations gccr WHERE ass.vendor_id = assa.vendor_id AND assa.accts_pay_code_combination_id = gccc.code_combination_id(+) AND assa.prepay_code_combination_id = gccr.code_combination_id(+) AND assa.org_id = XX AND ass.segment1 ='XX' ORDER BY ass.vendor_name