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