现金流量报表开发

一、创建一个视图用于存放现金、银行相关科目的信息。为更有扩展性，可以在LOOKUP中定义现金、银行相关科目。

CREATE VIEW CUX_GL_CASH_ACCOUNT_V AS  SELECT gcc.code_combination_id code_combination_id,        ff.flex_value || '.' || ff2.flex_value   cash_account,        ff.description || '.' || ff2.description cash_account_desc   FROM fnd_flex_values_vl   ff,        fnd_flex_value_sets  ffv,        gl_code_combinations gcc,        fnd_flex_values_vl   ff2,        fnd_flex_value_sets  ffv2,        fnd_lookup_values_vl flv  WHERE ffv.flex_value_set_name =‘科目’    AND ff.flex_value_set_id = ffv.flex_value_set_id    AND ff.flex_value = gcc.segment3    AND ffv2.flex_value_set_name = '子科目'    AND ff2.flex_value_set_id = ffv2.flex_value_set_id    AND ff2.flex_value = gcc.segment4    AND flv.lookup_type = 'CUX_GL_CASH_ACCOUNT'    AND gcc.segment3 LIKE flv.lookup_code || '%';

二、总帐

SELECT gjh.default_effective_date gl_date         , --记帐日期        gjh.name                   doc_name        , -- 日记帐名        to_char(gjl.je_line_num)   doc_number      , -- 行号         gjl.description            description     , -- 说明        gca.cash_account_desc      bank_account    , --银行帐户        decode(p_currency_code,               l_accounted_currency,               gjl.accounted_dr,               gjl.entered_dr)      dr             , --借项        decode(p_currency_code,               l_accounted_currency,               gjl.accounted_cr,               gjl.entered_cr)      cr             , -- 贷项        gjl.attribute2              cash_code        -- 现金流量表项代码   FROM gl_je_headers         gjh,        gl_je_lines           gjl,        gl_je_categories      gjc,        cux_gl_cash_account_v gca  WHERE gjh.je_header_id = gjl.je_header_id    AND gjl.code_combination_id = gca.code_combination_id    AND gjh.je_source = 'Manual'                  -- 手工    AND gjh.status = 'P'                          -- 过帐    AND gjh.je_category = gjc.je_category_name    AND gjc.user_je_category_name = '类别'    --类别    AND gjh.default_effective_date >= p_date_from    AND gjh.default_effective_date < p_date_to + 1

三、应收

SELECT crh.gl_date           gl_date         , ----记帐日期        cr.receipt_number     doc_name        , -- 收款编号        cr.attribute1         doc_number      , -- 进帐单号        party.party_name      customer_vendor , -- 客户        cr.comments           description     , -- 说明        gca.cash_account_desc bank_account    , -- 银行帐户        decode(p_currency_code,               l_accounted_currency,               ad.acctd_amount_dr,               ad.amount_dr)  dr              , --已转换借项        decode(p_currency_code,               l_accounted_currency,               ad.acctd_amount_cr,               ad.amount_cr)  cr              , -- 已转换贷项        cr.attribute2         cash_code         -- 现金流量表项代码   FROM ar_distributions           ad,        ar_receivable_applications ra,        ar_cash_receipt_history    crh,        ar_cash_receipts           cr,        hz_cust_accounts           cust,        hz_parties                 party,        ar_receipt_method_accounts amc,        cux_gl_cash_account_v      gca  WHERE decode(ad.source_table, 'RA', ad.source_id, NULL) =        ra.receivable_application_id(+)    AND decode(ad.source_table, 'CRH', ad.source_id, NULL) =        crh.cash_receipt_history_id(+)    AND decode(ad.source_table,               'RA',ra.cash_receipt_id,               'CRH',crh.cash_receipt_id,               NULL) = cr.cash_receipt_id    AND ad.source_type = 'CASH'    AND cr.pay_from_customer = cust.cust_account_id(+)    AND cust.party_id = party.party_id(+)    AND cr.receipt_method_id = amc.receipt_method_id    AND cr.remittance_bank_account_id = amc.bank_account_id    AND gca.code_combination_id = ad.code_combination_id    AND decode(crh.posting_control_id, -3, 'N', 'Y') = 'Y'    AND crh.gl_date >= p_date_from    AND crh.gl_date < p_date_to + 1

四、应付

SELECT aah.accounting_date      gl_date      , ----记帐日期         to_char(ac.check_number) doc_name     , -- 付款编号         ac.attribute1            doc_number   , -- 付款单据号         ac.current_vendor_name   vendor_name  , -- 供应商名称         ac.description           description  , -- 说明         gca.cash_account_desc    bank_account , -- 银行帐户         decode(p_currency_code,                l_accounted_currency,                aal.accounted_dr,                aal.entered_dr)   dr           , -- 借项         decode(p_currency_code,                l_accounted_currency,                aal.accounted_cr,                aal.entered_cr)   cr           , -- 贷项         ac.attribute2            cash_code      -- 现金流量表项代码    FROM ap_checks_v           ac,         ap_ae_lines           aal,         ap_ae_headers         aah,         cux_gl_cash_account_v gca   WHERE ac.check_id = aal.reference3     AND ac.check_number = aal.reference4     AND aal.source_table = 'AP_CHECKS'     AND aal.ae_line_type_code = 'CASH'        -- 现金(行类型)     AND aal.ae_header_id = aah.ae_header_id     AND aah.gl_transfer_flag = 'Y'                AND aal.code_combination_id = gca.code_combination_id     AND aah.accounting_date >= p_date_from     AND aah.accounting_date < p_date_to + 1