标签归档:银行省市

获取银行分行所在的省、市信息

    之前在做一个银行抽盘文件的时候,需要获取到供应商对应维护的银行分行所在的省市信息,于是记录下获取的sql命令,如下:

 记录银行信息的界面:

  来源界面

–银行分行的地址行1和城市数据
SELECT HP.ADDRESS1, HP.CITY
  FROM AP_SUPPLIERS            ASP,
       IBY_EXT_BANK_ACCOUNTS_V IEB,
       IBY_EXTERNAL_PAYEES_ALL IEP,
       IBY_PMT_INSTR_USES_ALL  USES,
       IBY_ACCOUNT_OWNERS      IAO,
       AP_SUPPLIER_SITES_ALL   ASS,
       HZ_PARTIES              HP
 WHERE 1 = 1
   AND HP.PARTY_ID = IEB.branch_party_id
   AND IEP.EXT_PAYEE_ID = USES.EXT_PMT_PARTY_ID
   AND IEP.PAYMENT_FUNCTION = 'PAYABLES_DISB'
   AND USES.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
   AND IEP.PAYEE_PARTY_ID = ASP.PARTY_ID
   AND ASP.ENABLED_FLAG = 'Y'
   AND ASP.SUMMARY_FLAG = 'N'
   AND IEP.PARTY_SITE_ID IS NULL
   AND IEP.SUPPLIER_SITE_ID IS NULL
   AND IEP.ORG_ID IS NULL
   AND (USES.END_DATE IS NULL OR USES.END_DATE > SYSDATE)
   AND (IEB.END_DATE IS NULL OR IEB.END_DATE > SYSDATE)
   AND (ASP.END_DATE_ACTIVE IS NULL OR ASP.END_DATE_ACTIVE > SYSDATE)
   AND (ASS.INACTIVE_DATE IS NULL OR ASS.INACTIVE_DATE > SYSDATE)
   AND ASS.VENDOR_ID = ASP.VENDOR_ID
   AND USES.INSTRUMENT_TYPE = 'BANKACCOUNT'
   AND IAO.ACCOUNT_OWNER_PARTY_ID = ASP.PARTY_ID
   AND IAO.EXT_BANK_ACCOUNT_ID(+) = IEB.ext_bank_account_id
   –AND IEB.BANK_ACCOUNT_ID = P_BANK_ACC_ID –银行ID
   –AND ASP.VENDOR_ID = P_VENDOR_ID         –供应商ID
   –AND ASS.VENDOR_SITE_ID = P_VENDOR_SITE_ID –供应商地点ID
 ORDER BY ASP.VENDOR_TYPE_LOOKUP_CODE;