凭证及明细分类帐设计

最新推荐文章于 2024-12-14 17:31:35 发布

转载最新推荐文章于 2024-12-14 17:31:35 发布 · 153 阅读

本文提供了一段SQL查询代码，用于从应付模块中提取付款相关信息，包括货币代码、会计日期、科目组合等，并通过解码函数处理了不同情况下的金额显示。

      SELECT '应付模块' MODULE
             ,'付款' TYPE
             ,ael.currency_code currency
             ,aeh.accounting_date gl_date
             ,ael.code_combination_id code_id
             ,gl_code.concatenated_segments code_combination
             ,cux_f_getacctdesc(gl_code.chart_of_accounts_id,
                                gl_code.code_combination_id) code_combination_desc
             ,decode(ael.currency_code,
                     'CNY',to_number(NULL),
                     decode(ael.entered_dr,
                            NULL,0,
                            ael.entered_dr)) entered_dr
             ,decode(ael.currency_code,
                     'CNY',to_number(NULL),
                     decode(ael.entered_cr,
                            NULL,0,
                            ael.entered_cr)) entered_cr
             ,decode(ael.currency_code,
                     'CNY',to_number(NULL),
                     decode(ael.entered_dr,
                            NULL,0,
                            ael.entered_dr))
             -
             decode(ael.currency_code,
                     'CNY',to_number(NULL),
                     decode(ael.entered_cr,
                            NULL,0,
                            ael.entered_cr)) bal
             ,decode(ael.currency_conversion_rate,
                     NULL,1,
                     ael.currency_conversion_rate) exchange_rate
             ,decode(ael.accounted_dr,
                     NULL,0,
                     ael.accounted_dr) accounted_dr
             ,decode(ael.accounted_cr,
                     NULL,0,
                     ael.accounted_cr) accounted_cr
             ,decode(ael.accounted_dr,
                     NULL,0,
                     ael.accounted_dr)
             -
             decode(ael.accounted_cr,
                     NULL,0,
                     ael.accounted_cr) func_bal
             ,inv.invoice_id invoice_id
             ,imp.je_header_id jeh_id
             ,imp.je_line_num jel_id
             ,inv.invoice_num invoice_num
    ,gl_vendorname.vendor_name third_party_name
--       ,ael.reference1 third_party_name
--   reference1 是保留最初供应商的名称，合并供应商操作后，该字段值没变，
--   但是THIRD_PARTY_ID 在有合并供应商操作后会变成合并的ID，固连接该字段。

             ,inv.Invoice_Num ||' '||inv.description DESCRIPTION
             ,gl_code.segment1 company_code
             ,gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                    1,gl_code.segment1) company_desc
             ,gl_code.segment2 costcenter_code
             ,gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                    2,gl_code.segment2) costcenter_desc
             ,gl_code.segment3 account_code
             ,gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                    3,gl_code.segment3) account_desc
             ,gl_code.segment4 product_code
             ,gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                    4,gl_code.segment4) product_desc
             ,gl_code.segment5 project_code
             ,gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                    5,gl_code.segment5) project_desc
             ,gl_code.segment6 expense_code
             ,decode(gl_code.chart_of_accounts_id,50181,null,50183,NULL,
                     gl_flexfields_pkg.get_description_sql(gl_code.chart_of_accounts_id,
                                                           6,gl_code.segment6)) expense_desc
             ,ael.org_id org_id
             ,org.NAME org_name
             ,aeh.set_of_books_id sob_id
             ,sob.NAME sob_name
       ,jeb.Default_Period_Name
      FROM ap.ap_ae_headers_all aeh
           ,ap.ap_ae_lines_all ael
           ,gl.gl_import_references imp
           ,ap.ap_invoice_payments_all pay
           ,ap.ap_invoices_all inv
           ,gl.gl_je_batches jeb
           ,gl_code_combinations_kfv gl_code
           ,hr_organization_units org
           ,gl.gl_sets_of_books sob
      ,AP_VENDORS_V gl_vendorname   --取供应商名称
      WHERE aeh.ae_header_id = ael.ae_header_id
      AND   ael.source_table = 'AP_INVOICE_PAYMENTS'
      AND   ael.source_id = pay.invoice_payment_id
      AND   pay.invoice_id = inv.invoice_id
      AND   gl_code.code_combination_id = ael.code_combination_id
      AND   ael.gl_sl_link_id = imp.gl_sl_link_id
--Below line is for solve duplicate line in AP. because gl_import_references
--have the same gl_sl_link_id for differenct gl_sl_link_table.
   And   imp.Gl_Sl_Link_Table='APECL'
      AND   jeb.je_batch_id = imp.je_batch_id
      AND   jeb.status = 'P'
      AND   org.organization_id = ael.Org_Id
      AND   sob.set_of_books_id = aeh.set_of_books_id
      AND   sob.set_of_books_id = v_sob_id
      AND   aeh.accounting_date BETWEEN period_start AND period_end
   And   ael.THIRD_PARTY_ID=gl_vendorname.vendor_id; --根据供应商ID 找供应商