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的問題？求大家解答

create or replace view 報許可權不足

相關文章