select hao.name 公司名称,
pha.segment1 订单编号,
to_char(pla.line_num) 订单行编号,
pv.vendor_name 供应商名,
pvs.vendor_site_code 供应商地点,
nvl(msib.segment1, null) 物料编码,
pla.item_description 物料名称,
hao1.name 发运组织,
rec.currency_code 订单币种,
decode(rec.transaction_type,
'RETURN TO VENDOR',
-rec.quantity,
rec.quantity) 数量,
pla.unit_price 单价,
decode(rrs.entered_cr, null, -rrs.entered_dr, rrs.entered_cr) 金额,
rec.currency_conversion_rate 接收汇率,
decode(rrs.accounted_cr, null, -rrs.accounted_dr, rrs.accounted_cr) 本位币金额,
plla.accrue_on_receipt_flag 接收应计标志,
gcc.segment3 科目,
to_char(rec.transaction_date, 'YYYY-MM-DD') 总帐日期,
'采购接收' 来源,
rsh.receipt_num 接收编号,
rec.currency_conversion_date 接收日期,
pl2.displayed_field 接收类型,
pda.recovery_rate 退税率,
apca.name 税码
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_vendors pv,
po_vendor_sites_all pvs,
hr_all_organization_units hao,
hr_all_organization_units hao1,
gl_code_combinations gcc,
rcv_transactions rec,
rcv_shipment_headers rsh,
ap_tax_codes_all apca,
rcv_receiving_sub_ledger rrs,
po_lookup_codes pl2,
mtl_system_items_b msib
where pha.org_id = hao.organization_id
and msib.organization_id = 103
and pha.vendor_id = pv.vendor_id
and pha.vendor_site_id = pvs.vendor_site_id
and plla.ship_to_organization_id = hao1.organization_id
and pha.po_header_id = pla.po_header_id
and plla.po_line_id = pla.po_line_id
and user_entered_flag = 'Y'
and rec.po_line_id = pla.po_line_id
and rec.po_header_id = pha.po_header_id
and rec.po_line_location_id = plla.line_location_id
and pda.line_location_id = plla.line_location_id
and pda.accrual_account_id = gcc.code_combination_id
and rec.shipment_header_id = rsh.shipment_header_id
and plla.tax_code_id = apca.tax_id(+)
and pda.po_distribution_id = rrs.reference3
and rec.transaction_id = rrs.rcv_transaction_id
and gcc.code_combination_id = rrs.code_combination_id
and rrs.set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID')
and rec.transaction_type in ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT')
and pl2.lookup_code = rec.transaction_type
and pl2.lookup_type = 'RCV TRANSACTION TYPE'
and pla.item_id = msib.inventory_item_id
union all
select hao.name 公司名称,
pha.segment1 订单编号,
to_char(pla.line_num) 订单行编号,
pv.vendor_name 供应商名,
pvs.vendor_site_code 供应商地点,
nvl(msib.segment1, null) 物料编码,
pla.item_description 物料名称,
hao1.name 发运组织,
decode(rt.currency_code, null, pha.currency_code, rt.currency_code) 订单币种,
-aida.quantity_invoiced 数量,
aida.unit_price 单价,
nvl(aala.entered_cr, 0) - nvl(aala.entered_dr, 0) 金额,
rt.currency_conversion_rate 接收汇率,
nvl(aala.accounted_cr, 0) - nvl(aala.accounted_dr, 0) 本位币金额,
plla.accrue_on_receipt_flag 接收应计标志,
gcc.segment3 科目,
to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期,
'发票匹配' 来源,
rsh.receipt_num 接收编号,
rt.currency_conversion_date 接收日期,
pl1.displayed_field 接收类型,
pda.recovery_rate 退税率,
apca.name 税码
from ap_invoices_all aia,
ap_invoice_distributions_all aida,
ap_ae_lines_all aala,
hr_all_organization_units hao,
gl_code_combinations gcc,
po_vendors pv,
po_vendor_sites_all pvs,
po_distributions_all pda,
po_line_locations_all plla,
hr_all_organization_units hao1,
po_lines_all pla,
po_headers_all pha,
rcv_shipment_headers rsh,
rcv_transactions rt,
ap_tax_codes_all apca,
po_lookup_codes pl1,
mtl_system_items_b msib
where aia.invoice_id = aida.invoice_id
and msib.organization_id = 103
and aida.org_id = hao.organization_id
and aala.source_table = 'AP_INVOICE_DISTRIBUTIONS'
and aida.invoice_distribution_id = aala.source_id
and aala.code_combination_id = gcc.code_combination_id
and aida.po_distribution_id = pda.po_distribution_id
and pda.line_location_id = plla.line_location_id
and plla.ship_to_organization_id = hao1.organization_id
and plla.po_line_id = pla.po_line_id
and pla.po_header_id = pha.po_header_id
and aia.vendor_id = pv.vendor_id
and aia.vendor_site_id = pvs.vendor_site_id
and aida.po_distribution_id is not null
and aala.ae_line_type_code = 'AP ACCRUAL'
and aida.rcv_transaction_id = rt.transaction_id(+)
and rt.shipment_header_id = rsh.shipment_header_id(+)
and plla.tax_code_id = apca.tax_id(+)
and (rt.transaction_type in ('RECEIVE', 'RETURN TO VENDOR') or
rt.transaction_type is null)
and pl1.lookup_code(+) = rt.transaction_type
and pl1.lookup_type(+) = 'RCV TRANSACTION TYPE'
and pla.item_id = msib.inventory_item_id
union all
select hao.name 公司名称,
aia.invoice_num 订单编号,
to_char(aida.distribution_line_number) 订单行编号,
pv.vendor_name 供应商名,
pvs.vendor_site_code 供应商地点,
null 物料编码,
null 物料名称,
null 发运组织,
aia.invoice_currency_code 订单币种,
null 数量,
null 单价,
aia.invoice_amount * (-1) 金额,
aida.exchange_rate 接收汇率,
nvl(aida.base_amount * (-1), aida.amount * (-1)) 本位币金额,
null 接收应计标志,
gcc.segment3 科目,
to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期,
'发票无匹配' 来源,
null 接收编号,
null 接收日期,
null 接收类型,
null 退税率,
null 税码
from ap_invoice_distributions_all aida,
ap_invoices_all aia,
hr_all_organization_units hao,
gl_code_combinations gcc,
po_vendors pv,
po_vendor_sites_all pvs
where gcc.code_combination_id = aida.dist_code_combination_id
and aia.invoice_id = aida.invoice_id
and pv.vendor_id = aia.vendor_id
and aida.org_id = hao.organization_id
and pv.vendor_id = pvs.vendor_id
and aia.vendor_site_id = pvs.vendor_site_id
and gcc.segment3 like '2121020000'
and aida.po_distribution_id is null
and aida.reversal_flag is null
