应付数据脚本

??

--27663

/*select count(1) from ap_invoice_distributions_all;*/

SELECT h.batch_id 批id,

       aba.batch_name 批名,

       h.org_id ou,

       ood.organization_name 业务实体,

       h.invoice_type_lookup_code INVOICE类型,

       pha.segment1 po编号,

       ass.vendor_name 贸易伙伴,

       ass.segment1 供应商编号,

       assa.vendor_site_code 供应商地点编码,

       assa.address_line1 供应商地点名称,

       h.invoice_date INVOICE日期,

       h.invoice_num INVOICE编号,

       h.invoice_currency_code INVOICE币种,

       h.invoice_amount INVOICE额,

       h.amount_paid 已付金额,

       h.total_tax_amount 税额,

       h.gl_date gl日期,

       h.attribute3 成本中心编码,

       department.description 成本中心,

       cux_flex_pkg.get_gl_flexfields(p_ccid   => h.accts_pay_code_combination_id,

                                      p_return => ‘A‘) 负债账户,

       

       d.line_type_lookup_code 行类型代码,

       alc.description 行类型说明,

       d.amount,

       cux_flex_pkg.get_gl_flexfields(p_ccid   => d.dist_code_combination_id,

                                      p_return => ‘A‘) 账户,

       d.created_by,

       us.user_name,

       d.creation_date

 

  FROM ap_invoices_all h,

       ap_invoice_distributions_all d,

       ap_batches_all aba,

       org_organization_definitions ood,

       po_headers_all pha,

       ap_suppliers ass,

       ap_supplier_sites_all assa,

       ap_lookup_codes alc,

       fnd_user us,

       (SELECT ffvt.description, ffv.flex_value

          FROM fnd_flex_value_sets ffvs,

               fnd_flex_values     ffv,

               fnd_flex_values_tl  ffvt

         WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id

           AND ffv.flex_value_id = ffvt.flex_value_id

           AND ffvt.language = ‘ZHS‘

           AND ffv.summary_flag = ‘N‘

           AND ffv.enabled_flag = ‘Y‘

           AND ffvs.flex_value_set_name = ‘BZ_DEPARTMENT‘) department

 

 WHERE h.invoice_id = d.invoice_id

   AND h.batch_id = aba.batch_id

   AND ood.organization_id = h.org_id

   AND h.po_header_id = pha.po_header_id(+)

   AND ass.vendor_id = assa.vendor_id

   AND ass.vendor_id = h.vendor_id

   AND assa.vendor_site_id = h.vendor_site_id

   AND h.attribute3 = department.flex_value

   AND alc.lookup_type = ‘INVOICE DISTRIBUTION TYPE‘

   AND alc.lookup_code = d.line_type_lookup_code

   and us.user_id = d.created_by

      

   --AND h.gl_date >= to_date(‘2014-01-01‘, ‘YYYY-MM-DD‘)

   AND h.gl_date < to_date(‘2014-02-01‘, ‘YYYY-MM-DD‘)

 

 

 

SELECT ood.organization_name 公司,

       vendor.segment1 供应商,

       vendor.address_line1 供应商地址,

       vendor.vendor_site_code 供应商地点,

       h.invoice_type_lookup_code INVOICE类型,

       SUM(nvl(h.invoice_amount, 0)) INVOICE金额,

       SUM(nvl(h.amount_paid, 0)) 已付款金额,

       SUM(nvl(h.invoice_amount, 0)) - SUM(nvl(h.amount_paid, 0)) 应付金额

 

  FROM ap_invoices_all h,

       org_organization_definitions ood,

       (SELECT h.segment1,

               l.address_line1,

               l.vendor_site_id,

               l.vendor_site_code

          FROM ap_supplier_sites_all l, ap_suppliers h

         WHERE l.vendor_id = h.vendor_id) vendor

 WHERE h.org_id = ood.organization_id

   AND h.vendor_site_id = vendor.vendor_site_id

 GROUP BY ood.organization_name,

          vendor.segment1,

          vendor.vendor_site_code,

          vendor.address_line1,

          h.invoice_type_lookup_code;

/*\* AND ass.vendor_id = assa.vendor_id

AND ass.vendor_id = h.vendor_id

AND assa.vendor_site_id = h.vendor_site_id*
 

\*   minus

 

select l.invoice_id

from ap_invoices_all l*
 

\* select *from ap_invoices_all l where \*l.vendor_id is null or*\ l.vendor_site_id is null*
 

SELECT COUNT(1)

  FROM ap_invoices_all h

 WHERE h.vendor_site_id > 0

 GROUP BY h.org_id,

          h.vendor_id,

          h.vendor_site_id,

          h.invoice_type_lookup_code*/

应付数据脚本

原文：http://blog.csdn.net/cai_xingyun/article/details/19701423