EBS 寄售(VMI)物权转移-冲减数量-开票对账---查询

--初始化环境变量

begin mo_global.set_policy_context('M',82); mo_global.init('PO');end;

 

--查询

1、物权转移与冲减数量

select sum(a.transaction_quantity) -36208 mmt_qty, sum(b.net_qty) net_qty
from (select mmt.transfer_transaction_id,
mmt.transaction_quantity ,
mmt.transaction_id
from mtl_material_transactions mmt
where mmt.transaction_type_id = 74
and mmt.inventory_item_id = 493322
and mmt.organization_id = 124
and mmt.xfr_owning_organization_id = 1738) a,
(select mct.transaction_id, mct.net_qty
from mtl_consumption_transactions mct
where mct.transaction_id in
(select mmt.transfer_transaction_id
from mtl_material_transactions mmt
where mmt.transaction_type_id = 74
and mmt.inventory_item_id = 493322
and mmt.organization_id = 124
and mmt.xfr_owning_organization_id = 1738)) b
where a.transfer_transaction_id = b.transaction_id;

 

 

2、冲减数量与发放数据

select a.*, b.*
from (select mct.consumption_release_id, sum(mct.net_qty) net_qty
from mtl_consumption_transactions mct
where mct.transaction_id in
(select mmt.transfer_transaction_id
from mtl_material_transactions mmt
where mmt.transaction_type_id = 74
and mmt.inventory_item_id = 493322
and mmt.organization_id = 124
and mmt.xfr_owning_organization_id = 1738)
and mct.interface_distribution_ref is not null
group by mct.consumption_release_id) a,
(select pra.po_release_id, sum(plla.quantity) qty
from po_releases_all pra, po_line_locations_all plla
where pra.po_release_id = plla.po_release_id
and plla.PO_LINE_ID =3960035
and plla.shipment_type ='BLANKET'
and pra.po_release_id in
(select mct.consumption_release_id
from mtl_consumption_transactions mct
where mct.transaction_id in
(select mmt.transfer_transaction_id
from mtl_material_transactions mmt
where mmt.transaction_type_id = 74
and mmt.inventory_item_id = 493322
and mmt.organization_id = 124
and mmt.xfr_owning_organization_id = 1738)
and mct.interface_distribution_ref is not null)
group by pra.po_release_id) b
where a.consumption_release_id = b.po_release_id

 

 

 

3、开票数量

--开票数量 688711 731111

select aia.INVOICE_NUM, aila.QUANTITY_INVOICED, aila.*
from ap_invoices_all aia, ap_invoice_lines_all aila
where aia.INVOICE_ID = aila.INVOICE_ID
and aia.VENDOR_ID = 1329
and aia.VENDOR_SITE_ID = 1738
and aila.PO_LINE_ID = 3960035
and ap_invoices_pkg.get_approval_status(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code) ='APPROVED';
 

 

4、未开票的金额

SELECT poh.po_header_id PO_HEADER_ID,
por.po_release_id PO_RELEASE_ID,
pol.po_line_id PO_LINE_ID,
poll.line_location_id LINE_LOCATION_ID,
pod.po_distribution_id PO_DISTRIBUTION_ID,
pv.vendor_id VENDOR_ID,
pvs.pay_on_receipt_summary_code PAY_ON_RECEIPT_SUMMARY_CODE,
poh.vendor_site_id VENDOR_SITE_ID,
NVL(pvs.default_pay_site_id, pvs.vendor_site_id) DEFAULT_PAY_SITE_ID,
pol.item_id ITEM_ID, --bug 7614092
nvl(poll.description, pol.item_description) ITEM_DESCRIPTION, --bug 7614092
poll.price_override UNIT_PRICE,
pod.quantity_ordered QUANTITY,
NVL(pod.quantity_billed, 0) QUANTITY_BILLED,
poh.currency_code CURRENCY_CODE,
poh.rate_type CURRENCY_CONVERSION_TYPE,
poh.rate CURRENCY_CONVERSION_RATE,
poh.rate_date CURRENCY_CONVERSION_DATE,
NVL(pvs.payment_currency_code,
NVL(pvs.invoice_currency_code, poh.currency_code)) PAYMENT_CURRENCY_CODE,
por.creation_date CREATION_DATE,
NVL(NVL(poll.terms_id, poh.terms_id), pvs2.terms_id) PAYMENT_TERMS_ID,
DECODE(poll.taxable_flag, 'Y', poll.tax_code_id, NULL) TAX_CODE_ID,
por.org_id ORG_ID,
poll.unit_meas_lookup_code UNIT_MEAS_LOOKUP_CODE --5100177
FROM PO_VENDORS pv,
PO_VENDOR_SITES pvs,
PO_VENDOR_SITES pvs2,
PO_HEADERS poh,
PO_RELEASES por,
PO_LINES pol,
PO_LINE_LOCATIONS poll,
PO_DISTRIBUTIONS pod
WHERE pv.vendor_id = poh.vendor_id
AND poh.vendor_site_id = pvs.vendor_site_id
AND NVL(pvs.default_pay_site_id, pvs.vendor_site_id) =
pvs2.vendor_site_id
AND poh.po_header_id = por.po_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND por.po_release_id = poll.po_release_id
AND poll.line_location_id = pod.line_location_id
AND por.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
AND DECODE(por.consigned_consumption_flag, -- utilize PO_RELEASES_F1 idx
'Y',
DECODE(por.closed_code, 'FINALLY CLOSED', NULL, 'Y'),
NULL) = 'Y'
AND por.release_type = 'BLANKET'
--AND por.creation_date <= p_cutoff_date
and poh.VENDOR_ID = 1329
and pol.ITEM_ID =493322
AND pvs.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
AND pod.quantity_ordered > NVL(pod.quantity_billed, 0)
AND poll.closed_code <> 'FINALLY CLOSED'
/AND NOT EXISTS ( SELECT 'use invoice is interfaced'FROM ap_invoices_interface aii,ap_invoice_lines_interface ailiWHERE aii.invoice_id = aili.invoice_idAND nvl(aii.status,'PENDING') <> 'PROCESSED'AND aili.po_distribution_id = pod.po_distribution_id )/
AND EXISTS
(SELECT 'po distribution is not fully invoiced'
FROM ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE aida.invoice_id = aia.invoice_id
AND aila.invoice_id = aia.invoice_id
AND aida.invoice_line_number = aila.line_number
AND aida.po_distribution_id = pod.po_distribution_id
AND aia.invoice_type_lookup_code = 'STANDARD'
AND Nvl(aila.discarded_flag, 'N') <> 'Y'
AND Nvl(aila.cancelled_flag, 'N') <> 'Y'
AND Nvl(aida.cancelled_flag, 'N') <> 'Y'
AND aida.quantity_invoiced > 0 HAVING
Nvl(Sum(aida.quantity_invoiced), 0) < pod.quantity_ordered) -- bug 19673985
ORDER BY 6, -- VENDOR_ID
9, -- DEFAULT_PAY_SITE_ID
7, -- PAY_ON_RECEIPT_SUMMARY_CODE
15, -- CURRENCY_CODE
18, -- CURRENCY_CONVERSION_DATE -- bug2786193
16, -- CURRENCY_CONVERSION_TYPE -- bug2786193
17, -- CURRENCY_CONVERSION_RATE -- bug2786193
20, -- PAYMENT_TERMS_ID
-- 19, -- CREATION_DATE -- bug2786193
1, -- PO_HEADER_ID
2, -- PO_RELEASE_ID
3, -- PO_LINE_ID
4, -- LINE_LOCATION_ID
5; -- DISTRIBUTION_ID