--初始化环境变量
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标签：寄售,VMI,--,mmt,ID,---,PO,po,id From： https://www.cnblogs.com/ivenlin/p/18109771