I tried creating materialized view and the following error is thrown.
ORA-00942: table or view does not exist
I have specified all the schemas. Please help me to resolve.
CREATE MATERIALIZED VIEW XXMO_LCM_MATCH
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND START WITH SYSDATE+0 NEXT SYSDATE + 1/24
AS
SELECT SUM(A.AMOUNT) AMOUNT,
SUM(A.ESTIMATED_ALLOCATED_AMT) ESTIMATED_ALLOCATED_AMT,
A.SHIP_DATE,
A.SHIP_NUM,
A.VENDOR_NAME,
A.CHARGE_TYPE,
A.NAME,
A.PO_NUM,
A.ORG_ID,
A.SHIP_TO_ORGANIZATION_ID
FROM
(
SELECT
SH.SHIP_DATE,
SH.SHIP_NUM,
SUM(D.AMOUNT) AMOUNT,
LC.ESTIMATED_ALLOCATED_AMT ESTIMATED_ALLOCATED_AMT,
PHA.SEGMENT1 PO_NUM,
POV.VENDOR_NAME,
PE.PRICE_ELEMENT_CODE CHARGE_TYPE,
OU.NAME,
SH.ORG_ID,
SL.SHIP_TO_ORGANIZATION_ID
FROM PO.RCV_TRANSACTIONS RT,
AP.AP_INVOICE_DISTRIBUTIONS_ALL D,
AP.AP_INVOICE_LINES_ALL L,
APPS.INL_DET_LANDED_COSTS_V LC,
INL.INL_SHIP_LINES_ALL SL,
INL.INL_SHIP_HEADERS_ALL SH,
HR.HR_ALL_ORGANIZATION_UNITS OU,
PO.PO_LINE_LOCATIONS_ALL PLL,
PO.PO_LINES_ALL PLA,
PO.PO_HEADERS_ALL PHA,
APPS.PO_VENDORS POV,
PON.PON_PRICE_ELEMENT_TYPES PE
WHERE
D.INVOICE_ID = L.INVOICE_ID
AND d.invoice_line_number = l.line_number
AND L.LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS', 'FREIGHT', 'ITEM')
AND rt.lcm_shipment_line_id = sl.SHIP_LINE_ID-- IS NOT NULL
AND rt.transaction_id = NVL(l.rcv_transaction_id,d.rcv_transaction_id)
AND SL.SHIP_LINE_ID = RT.LCM_SHIPMENT_LINE_ID
AND LC.charge_line_type_id = l.cost_factor_id
AND sl.ship_header_id = sh.ship_header_id
AND NVL(lc.parent_ship_line_id, lc.ship_line_id) = NVL(sl.parent_ship_line_id, sl.ship_line_id)
AND LC.ADJUSTMENT_NUM = SH.ADJUSTMENT_NUM
AND SL.SHIP_LINE_SOURCE_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_LINE_ID = PLA.PO_LINE_ID
AND PLL.PO_HEADER_ID = PHA.PO_HEADER_ID
AND L.ORG_ID = OU.ORGANIZATION_ID
AND POV.VENDOR_ID = PHA.VENDOR_ID
AND lc.CHARGE_LINE_TYPE_ID = pe.price_element_type_id
GROUP BY
SH.SHIP_DATE,
SH.SHIP_NUM,
LC.ESTIMATED_ALLOCATED_AMT,
PHA.SEGMENT1 ,
POV.VENDOR_NAME,
PE.PRICE_ELEMENT_CODE ,
OU.NAME,
SH.ORG_ID,
SL.SHIP_TO_ORGANIZATION_ID
) A
GROUP BY
A.SHIP_DATE,
A.SHIP_NUM,
A.VENDOR_NAME,
A.CHARGE_TYPE,
A.NAME,
A.PO_NUM,
A.ORG_ID,
A.SHIP_TO_ORGANIZATION_ID;