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博客 ” ,链接:http://blog.itpub.net/21416913/viewspace-750737/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21416913/viewspace-750737/