应付数据脚本



--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*/

<pre name="code" class="sql">SELECT ai.invoice_id,
       ai.org_id,
       hou.name org_name,
       ai.expenditure_organization_id,
       ai.invoice_num,
       ai.vendor_id,
       ai.vendor_site_id,
       nvl(pv.vendor_name, hp.party_name) vendor_name,
       decode(ai.invoice_type_lookup_code,
              'PAYMENT REQUEST',
              NULL,
              pv.segment1) vendor_number,
       pv.num_1099 num_1099,
       pvs.vendor_site_code vendor_site_code,
       alc1.description invoice_type,
       ph.segment1 po_number,
       rsh.receipt_num receipt_number,
       ail.inventory_item_id inventory_item_id,
       ail.item_description item_description,
       ail.unit_price,
       ail.amount,
       ail.accounting_date,
       ail.line_type_lookup_code,
       allc1.description line_type
  FROM ap_invoices_all       ai,
       ap_invoice_lines_all  ail,
       hr_organization_units hou,
       po_vendors            pv,
       po_vendor_sites_all   pvs,
       ap_lookup_codes       alc1,
       ap_lookup_codes       allc1,
       po_headers_all        ph,
       po_lines_all          pl,
       rcv_transactions      rtxns,
       rcv_shipment_headers  rsh,
       rcv_shipment_lines    rsl,
       hz_parties            hp
 WHERE pv.vendor_id(+) = ai.vendor_id /* bug:7366363 */
   AND ai.approval_ready_flag <> 'S'
   AND ai.org_id = hou.organization_id(+)
      --AND ai.expenditure_organization_id = hou.organization_id(+)
   AND ai.vendor_site_id = pvs.vendor_site_id(+)
   AND ai.party_id = hp.party_id
   AND alc1.lookup_type(+) = 'INVOICE TYPE'
   AND alc1.lookup_code(+) = ai.invoice_type_lookup_code
   AND ail.invoice_id = ai.invoice_id
   AND allc1.lookup_type(+) = 'INVOICE LINE TYPE'
   AND allc1.lookup_code(+) = ail.line_type_lookup_code
   AND ail.po_header_id = ph.po_header_id(+)
   AND ail.po_line_id = pl.po_line_id(+)
   AND ail.rcv_transaction_id = rtxns.transaction_id(+)
   AND rsl.shipment_header_id = rsh.shipment_header_id(+)
   AND rsl.shipment_line_id(+) = ail.rcv_shipment_line_id;

/*   SELECT COUNT(1) FROM ap_invoices_all;
*/

/*SELECT COUNT(1) FROM ap_invoice_lines_all;*/