CREATE OR REPLACE VIEW ECP_ERP_PO_LINE_TO_MATCH_V AS

SELECT poh.po_header_id, -- 订单ID

poh.segment1 po_number, -- 订单号

poh.comments, -- 订单说明

poh.vendor_id, -- 供应商ID

poh.vendor_site_id,

poh.org_id, -- 组织ID

poh.agent_id, -- 采购员ID

ppx.last_name agent_name, -- 采购员姓名

pol.po_line_id, -- 订单行ID

pol.line_num, -- 订单行号

plt.line_type, -- 订单行类型

pol.item_id, -- 物料ID

pol.item_description, -- 物料描述

pll.unit_meas_lookup_code, -- 计量单位

pll.ship_to_organization_id, -- 发运组织ID

ood.organization_code, -- 发运组织代码

pll.line_location_id, -- 发运行ID

pll.shipment_num, -- 发运行号

pll.price_override unit_price, -- 单价

pll.match_option, -- 匹配选项（P表示两相匹配，R表示三相）

pll.accrue_on_receipt_flag, -- 接收应计标志（Y表示是，N表示否）

nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0) quantity_ordered, -- 订货数量

nvl(pll.quantity_received, 0) quantity_delivered, -- 已接收数量

nvl(pll.quantity_billed, 0) + nvl(rsv.quantity_billed, 0) quantity_billed, -- 已匹配数量

decode(pll.match_option,

'P',

nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0) - nvl(pll.quantity_billed, 0) - nvl(rsv.quantity_billed, 0),

'R',

nvl(pll.quantity_received, 0) - nvl(pll.quantity_billed, 0)- nvl(rsv.quantity_billed, 0)) quantity_billable -- 可匹配数量

FROM po.po_headers_all poh,

po.po_lines_all pol,

po.po_line_locations_all pll,

po.po_line_types_tl plt,

apps.org_organization_definitions ood,

apps.per_people_x ppx,

(SELECT eyol.line_location_id,

SUM(eyol.paidquantity) quantity_billed

FROM ecp_ynetworkoperat_order_line eyol,

ecp_ynetworkoperat_order eyo,

ecp_ynetworkoperat ey

WHERE ey.operatid = eyo.operatid

AND eyo.matchid = eyol.matchid

GROUP BY eyol.line_location_id) rsv

WHERE poh.approved_flag = 'Y'

AND poh.type_lookup_code = 'STANDARD'

AND nvl(poh.closed_code, 'X') <> 'FINALLY CLOSED'

AND pll.approved_flag = 'Y'

AND nvl(pll.consigned_flag, 'N') <> 'Y'

AND poh.po_header_id = pol.po_header_id

AND pol.po_line_id = pll.po_line_id

AND pol.line_type_id = plt.line_type_id

AND pll.ship_to_organization_id = ood.organization_id

--AND nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0) > 0

--AND ((pll.match_option = 'R' AND (nvl(pll.quantity_received, 0) - nvl(pll.quantity_billed, 0)) > 0) OR

-- (pll.match_option = 'P' AND (nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0) - nvl(pll.quantity_billed, 0)) > 0))

AND plt.LANGUAGE = 'ZHS'

AND poh.agent_id = ppx.person_id

AND pll.line_location_id = rsv.line_location_id(+);

此代码，在只运行红色代码的时候完全没问题。但是加了第一句之后，就报权限不足。后来赋予select any table 的权限之后问题消失，不知道是什么原因。9i的问题？求大家解答

ITPub博客

create or replace view 报权限不足

最新文章