ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 现金流量报表开发

现金流量报表开发

原创 Linux操作系统 作者:smallnavy 时间:2008-12-16 23:17:01 0 删除 编辑

一、创建一个视图用于存放现金、银行相关科目的信息。为更有扩展性,可以在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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/57020/viewspace-512145/,如需转载,请注明出处,否则将追究法律责任。

上一篇: BOM导入及更新
下一篇: APPWORX安装指南
请登录后发表评论 登录
全部评论

注册时间:2008-07-29

  • 博文量
    20
  • 访问量
    175671