之前在做一个银行抽盘文件的时候，需要获取到供应商对应维护的银行分行所在的省市信息，于是记录下获取的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;

select ai.org_id,
       ai.invoice_id,
       aps.vendor_name,
       ai.invoice_num,
       –ail.attribute1, –标准发票中存储预付款发票编号
       nvl(sum(ail.amount + nvl((select sum(zl.tax_amt)
                                  from zx_lines zl
                                 where ail.invoice_id = zl.trx_id
                                   and ail.line_number = zl.trx_line_id
                                   and (zl.tax_amt_included_flag is null or
                                       zl.tax_amt_included_flag = 'N')),
                                0)),
           0) invoice_amount_remaining,
       nvl(sum(ap_prepay_utils_pkg.get_invoice_unpaid_amount(ai.invoice_id) +
               nvl(ai.total_tax_amount, 0)),
           0) invoice_unpaid, –未付或未核销金额
       ai.gl_date,
       ai.vendor_id,
       ai.vendor_site_id,
       ai.invoice_currency_code,
       ai.set_of_books_id,
       ail.line_number

  from ap_invoices_all      ai,
       ap_invoice_lines_all ail,
       gl_code_combinations gcc,
       ap_suppliers         aps
 where 1 = 1
   and ai.invoice_id = ail.invoice_id
   and aps.vendor_id = ai.vendor_id
   and ai.accts_pay_code_combination_id = gcc.code_combination_id
   and ail.line_type_lookup_code(+) = 'ITEM'
      –只获取已付金额小于总金额的发票
   and nvl(ai.amount_paid, 0) < ai.invoice_amount
      –根据需要是否需要验证或者不验证核销（排除取消过的发票）
      –APPROVED已验证,NEVER APPROVED从未验证
   and ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,
                                                   ai.invoice_amount,
                                                   ai.payment_status_flag,
                                                   ai.invoice_type_lookup_code) <>
       'CANCELLED'
   and ai.invoice_type_lookup_code = 'STANDARD'
— and ail.attribute1 is not null
— and ai.org_id = p_org_id
— and (ai.vendor_id = p_vendor_id or p_vendor_id is null)
 group by ai.invoice_id,
          ai.invoice_num,
          ai.org_id,
          ai.gl_date,
          ai.vendor_id,
          aps.vendor_name,
          ai.vendor_site_id,
          ai.invoice_currency_code,
          ai.set_of_books_id,
          ail.line_number,
          ai.creation_date
          –ail.attribute1
 order by ai.creation_date;

–此段sql主要是用来获取“分机构获取发票付款金额”，当然可根据自己的需求，取出其他字段，比如付款时间，待付款金额等信息

SELECT HR.NAME 机构名称, /*AIA.INVOICE_NUM 发票号*/ SUM(AIP.AMOUNT) 金额
  FROM AP.AP_INVOICES_ALL      AIA,
       AP_INVOICE_PAYMENTS_ALL AIP,
       HR_OPERATING_UNITS      HR
 WHERE 1 = 1
   and AIA.INVOICE_ID = AIP.INVOICE_ID
   AND HR.ORGANIZATION_ID = AIA.ORG_ID
   AND TO_CHAR(AIP.CREATION_DATE, 'YYYY-MM-DD') BETWEEN '2015-03-07' AND
       '2015-03-17'
 GROUP BY HR.NAME /*, AIA.INVOICE_NUM*/
 ORDER BY HR.NAME;

说明：发票的付款信息可以从机构、发票编号等维度去获取付款的金额。但是需要注意的是，由于付款信息是针对供应商付款，而供应商只分配给某个或某几个机构来使用，并未明细分配到部门段来使用，因此无法准确的从成本中心的维度来展示付款信息。

select ai.invoice_id 发票ID,
       ai.invoice_num 发票编号,
       ail.line_number 发票行号,
       ap_prepay_utils_pkg.get_line_prepay_amt_remaining(ail.invoice_id,
                                                         ail.line_number) 待核销金额,
       aps.vendor_name 供应商名称

  from ap_invoices_all              ai,
       ap_invoice_lines_all         ail,
       ap_invoice_distributions_all aid,
       gl_code_combinations         gcc,
       ap_suppliers                 aps
 where 1 = 1
   and ai.invoice_id = ail.invoice_id
   and aps.vendor_id = ai.vendor_id
   and ail.invoice_id = aid.invoice_id
   and ail.line_number = aid.invoice_line_number
   and aps.enabled_flag = 'Y'
   and (aps.end_date_active is null or aps.end_date_active > sysdate)
   and ai.invoice_type_lookup_code = 'PREPAYMENT'
      –获取为可用状态的预付款发票,即已经对预付款系统内付款完成
   and ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,
                                                   ai.invoice_amount,
                                                   ai.payment_status_flag,
                                                   ai.invoice_type_lookup_code) =
       'AVAILABLE'
   and decode(ai.invoice_type_lookup_code,
              'PREPAYMENT',
              decode(ai.earliest_settlement_date,
                     '',
                     'PERMANENT',
                     'TEMPORARY')) = 'TEMPORARY'
   and ai.accts_pay_code_combination_id = gcc.code_combination_id;

    今天给客户提取所有银行账户的机构分配信息，写了以下一段SQL命令，记录下来以备后用：

SELECT CBA.BANK_ACCOUNT_NAME 账户名,
       BANK_INFO.BANK_NAME 银行名称,
       BANK_INFO.BRANCH_NAME 分行名称,
       BANK_INFO.BANK_ACCOUNT_NUM 银行账户,
       BANKACCTUSEEO.ORG_ID 机构ID,
       DECODE(BANKACCTUSEEO.LEGAL_ENTITY_ID,
              NULL,
              FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(BANKACCTUSEEO.ORG_ID),
              (SELECT XLE.NAME
                 FROM XLE_ENTITY_PROFILES XLE
                WHERE XLE.LEGAL_ENTITY_ID = BANKACCTUSEEO.LEGAL_ENTITY_ID)) 机构描述,
       BANKACCTUSEEO.AP_USE_ENABLE_FLAG 应付可用性,
       BANKACCTUSEEO.AR_USE_ENABLE_FLAG 应收可用性,
       BANKACCTUSEEO.END_DATE 失效日期
  FROM CE_BANK_ACCT_USES_ALL BANKACCTUSEEO,
       AR_RECEIVABLES_TRX ARRECEIVABLESTRXEARNED,
       AR_RECEIVABLES_TRX ARRECEIVABLESTRXUNEARNED,
       AR_RECEIVABLES_TRX ARRECEIVABLESTRXCLAIMINV,
       CE_LOOKUPS CL,
       FND_LOOKUPS FL,
       CE_RECEIVABLE_ACTIVITIES_V RA,
       CE_BANK_ACCOUNTS CBA,
       (SELECT BANKPARTY.PARTY_NAME   BANK_NAME,
               BRANCHPARTY.PARTY_NAME BRANCH_NAME,
               TERRITORY_SHORT_NAME   COUNTRY,
               BA.BANK_ACCOUNT_NUM,
               BRANCHPARTY.PARTY_ID   BANK_BRANCH_ID,
               BA.BANK_ACCOUNT_ID,
               CL.MEANING             ACCOUNT_CLASS_MEANING
          FROM HZ_PARTIES               BANKPARTY,
               HZ_PARTIES               BRANCHPARTY,
               HZ_ORGANIZATION_PROFILES BANKORGPROFILE,
               HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,
               HZ_RELATIONSHIPS         BRREL,
               HZ_CODE_ASSIGNMENTS      BANKCA,
               HZ_CODE_ASSIGNMENTS      BRANCHCA,
               CE_LOOKUPS               CL,
               FND_TERRITORIES_VL       FT,
               CE_BANK_ACCOUNTS         BA
         WHERE BANKPARTY.PARTY_TYPE = 'ORGANIZATION'
           AND BANKPARTY.STATUS = 'A'
           AND BANKPARTY.PARTY_ID = BANKORGPROFILE.PARTY_ID
           AND SYSDATE BETWEEN TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE) AND
               NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE), SYSDATE + 1)
           AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
           AND BANKCA.CLASS_CODE = 'BANK'
           AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
           AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID
           AND NVL(BANKCA.STATUS, 'A') = 'A'
           AND BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION'
           AND BRANCHPARTY.STATUS = 'A'
           AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID
           AND SYSDATE BETWEEN
               TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE(+)) AND
               NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE(+)),
                   SYSDATE + 1)
           AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
           AND BRANCHCA.CLASS_CODE = 'BANK_BRANCH'
           AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
           AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID
           AND NVL(BRANCHCA.STATUS, 'A') = 'A'
           AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID
           AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID
           AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
           AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF'
           AND BRREL.STATUS = 'A'
           AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
           AND BRREL.SUBJECT_TYPE = 'ORGANIZATION'
           AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
           AND BRREL.OBJECT_TYPE = 'ORGANIZATION'
           AND CL.LOOKUP_TYPE = 'ACCOUNT_CLASSIFICATION'
           AND CL.LOOKUP_CODE = BA.ACCOUNT_CLASSIFICATION
           AND BANKORGPROFILE.HOME_COUNTRY = TERRITORY_CODE
           AND BA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID) BANK_INFO
 WHERE 1 = 1
   AND BANK_INFO.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
   AND CBA.BANK_ACCOUNT_ID = BANKACCTUSEEO.BANK_ACCOUNT_ID
   AND BANKACCTUSEEO.EDISC_RECEIVABLES_TRX_ID =
       ARRECEIVABLESTRXEARNED.RECEIVABLES_TRX_ID(+)
   AND BANKACCTUSEEO.UNEDISC_RECEIVABLES_TRX_ID =
       ARRECEIVABLESTRXUNEARNED.RECEIVABLES_TRX_ID(+)
   AND BANKACCTUSEEO.AR_CLAIM_INV_ACT_ID =
       ARRECEIVABLESTRXCLAIMINV.RECEIVABLES_TRX_ID(+)
   AND CL.LOOKUP_TYPE = 'ORGANIZATION_TYPE'
   AND CL.LOOKUP_CODE = 'LEGAL_ENTITY'
   AND FL.LOOKUP_TYPE(+) = 'XTR_DEAL_PRICE_MODELS'
   AND FL.LOOKUP_CODE(+) = BANKACCTUSEEO.PRICING_MODEL
   AND BANKACCTUSEEO.NEW_AR_RCPTS_RECEIVABLE_TRX_ID =
       RA.RECEIVABLES_TRX_ID(+);