导出系统内的应付结算单数据(包含会计科目)

注意:不具备通用性。

目前代码只能找到预付结算单的借方科目(YUF),改为费用、负债(FY、FZ)可以看到应付结算单的。

SELECT h.settle_header_id AS settle_header_id,
       h.org_id AS org_id,
       (SELECT nvl(SUM(l.tax_amount),
                   0)
          FROM xxfin_fin_settle_line_all l
         WHERE l.settle_header_id = h.settle_header_id) AS total_line_taxamount, --added by yaxu wu
       h.tax_amount AS tax_amount, --added by yaxu wu
       h.description AS description, --added by yaxu wu
       h.transaction_type AS transaction_type,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_TRANSACTION_TYPE',
                                         p_lookup_code => h.transaction_type) AS transaction_type_desc,
       h.currency_code AS currency_code,
       h.payment_amount AS payment_amount,
       h.remain_amount AS remain_amount,
       h.vendor_id AS vendor_id,
       h.pay_term AS pay_term,
       h.application_person_id AS application_person_id,
       h.payment_status AS payment_status,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_PAY_STATUS',
                                         p_lookup_code => h.payment_status) AS payment_status_desc,
       to_char(h.application_date,
               'yyyy/mm/dd') AS application_date,
       to_char(h.approved_date,
               'yyyy/mm/dd') AS approved_date,
       (SELECT ppf.full_name
          FROM per_all_people_f ppf
         WHERE (SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
           AND ppf.person_id = h.application_person_id) AS application_person_name,
       h.approved_person_id AS approved_person_id,
       (SELECT ppf.full_name
          FROM per_all_people_f ppf
         WHERE (SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
           AND ppf.person_id = h.approved_person_id) AS approved_person_name,
       (SELECT tv.name
          FROM cux_fin_terms_v tv
         WHERE tv.term_id = h.pay_term) AS pay_term_desc,
       to_char(h.pay_term_date,
               'yyyy/mm/dd') AS pay_term_date,
       (SELECT pv.vendor_name
          FROM po_vendors pv
         WHERE pv.vendor_id = h.vendor_id) AS vendor_name,
       h.vendor_site_id AS vendor_site_id,
       (SELECT nvl(pvs.vendor_site_code_alt,
                   pvs.vendor_site_code)
          FROM po_vendor_sites_all pvs
         WHERE pvs.vendor_site_id = h.vendor_site_id) AS vendor_site_name,
       (SELECT hou.name
          FROM hr_operating_units hou
         WHERE hou.organization_id = h.org_id) AS org_name,
       h.write_off_remain_amount AS write_off_remain_amount,
       to_char(h.settle_end_date,
               'yyyy/mm/dd') AS settle_end_date,
       h.settle_type AS settle_type,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_SETTLEMENT_TYPE',
                                         p_lookup_code => h.settle_type) AS settle_type_desc,
       h.operation_type AS operation_type,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_BUSINESS_TYPE',
                                         p_lookup_code => h.operation_type) AS operation_type_desc,
       nvl(h.internal_settle_flag,
           'N') AS internal_settle_flag,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'ZX_YES_NO',
                                         p_lookup_code => h.internal_settle_flag) AS internal_settle_flag_desc,
       h.settle_num AS settle_num,
       h.tax_code AS tax_code,
       h.status AS status,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_SET_STATUS',
                                         p_lookup_code => h.status) AS status_desc,
       h.invoice_match AS invoice_match,
       h.exchange_rate AS exchange_rate,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_INVOICE_STATUS',
                                         p_lookup_code => h.invoice_match) AS invoice_match_desc,
       cux_fin_gl_account_pub.get_gl_status_code(h.settle_header_id,
                                                 'SETTLEMENT_DOCUMENT') AS billing_status,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_GL_STATUS',
                                         p_lookup_code => cux_fin_gl_account_pub.get_gl_status_code(h.settle_header_id,
                                                                                                    'SETTLEMENT_DOCUMENT')) AS billing_status_desc,
       h.pendding_status AS pendding_status,
       cux_common_utl.get_lookup_meaning(p_lookup_type => 'SET_PENDING_STATUS',
                                         p_lookup_code => h.pendding_status) AS pendding_status_desc,
       h.amount AS amount,
       cux_common_utl.get_account_segements(p_ccid   => (SELECT jl.code_combination_id
                                                           FROM cux_gl_je_lines jl
                                                          WHERE jl.je_header_id = jh.je_header_id
                                                            AND jl.line_type_code = 'FZ'),
                                            p_org_id => h.org_id) AS fz_account_segements,
       cux_common_utl.get_account_segements_desc(p_ccid        => (SELECT jl.code_combination_id
                                                                     FROM cux_gl_je_lines jl
                                                                    WHERE jl.je_header_id = jh.je_header_id
                                                                      AND jl.line_type_code = 'FZ'),
                                                 p_org_id      => h.org_id,
                                                 p_segment_num => 4) AS fz_four_account_segements_desc,
       cux_common_utl.get_account_segements(p_ccid   => (SELECT jl.code_combination_id
                                                           FROM cux_gl_je_lines jl
                                                          WHERE jl.je_header_id = jh.je_header_id
                                                            AND jl.line_type_code = 'YUF'),
                                            p_org_id => h.org_id) AS fy_account_segements,
       cux_common_utl.get_account_segements_desc(p_ccid        => (SELECT jl.code_combination_id
                                                                     FROM cux_gl_je_lines jl
                                                                    WHERE jl.je_header_id = jh.je_header_id
                                                                      AND jl.line_type_code = 'YUF'),
                                                 p_org_id      => h.org_id,
                                                 p_segment_num => 4) AS fy_four_account_segements_desc
  FROM xxfin_fin_settle_header_all h,
       cux_gl_je_headers           jh
 WHERE 1 = 1
   AND h.org_id = 102
   AND h.settle_type IN (
                         'ADVANCE')
   AND jh.source_code = 'SETTLEMENT_DOCUMENT'
   AND jh.source_header_id = h.settle_header_id;


本文标题:导出系统内的应付结算单数据(包含会计科目)

本文链接:http://catonisland.cn/post/120.html

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章