Hello Oracle family:
My system developer is out of the office and I need to create a materialized view in SQL Developer.
First question: Do I have to create the target table for the materialized view BEFORE I submit the code shown below? I could not find any table build in our library.
Second: I get an error message when I copy the following code into SQL Developer:
(Don't concern yourself about the SELECT statements. They were tested before I inserted them into the script):
I get this message when I try to run the script:
____________________________________________________________
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
____________________________________________________________
Strange... because all the tables work when I run just the select statement:
I just pasted the following into the SQL Developer window and attempted to run it.
Do you see any issues?
CREATE MATERIALIZED VIEW fwsapex.fws_item_activity_mv
NOCACHE
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE ('01-NOV-2016 18:00:00', 'DD-MON-YYYY HH24:MI:SS')
NEXT trunc(sysdate)+ 1 + 270/1440
AS
SELECT
inventory_item_id,
organization_id,
total_quantity,
total_weight,
last_active_date ,
TRUNC(sysdate) - TRUNC(last_active_date) inactive_days
FROM
(SELECT
inv.inventory_item_id,
inv.organization_id,
inv.total_quantity ,
inv.total_weight,
GREATEST (
-- **************************************************
-- Look for the most recent transaction in R11i
-- ic_tran_pend that affects inventory balances
-- **************************************************
NVL(
(SELECT max(trans_date)
from ic_tran_pnd pend
join ic_item_mst_b ic on ic.item_id = pend.item_id
join mtl_system_items_b msib on msib.segment1 = ic.item_no
join ic_whse_mst whse on whse.whse_code = pend.whse_code
where pend.delete_mark = 0
and pend.trans_qty <> 0
and msib.organization_id = (select fws_inv_pkg.master_organization_id() from dual)
and msib.inventory_item_id = inv.inventory_item_id
and whse.organization_id = inv.organization_id
), '31-DEC-1900'),
-- **************************************************
-- Look for the most recent pending transaction
-- that affects inventory balances in R12
-- **************************************************
NVL(
( select max(trx_date)
from fws_pending_trx_v pend
where pend.inventory_item_id = inv.inventory_item_id
and pend.organization_id = inv.organization_id
), '31-DEC-1900'),
-- **************************************************
-- Look for the most recent transaction in
-- R12's material transactions that affect inventory
-- balances
-- **************************************************
NVL(
(select max (transaction_date)
from mtl_material_transactions mtl
where nvl(source_code,'_') <> 'OPM-DXFR-MIGRATION'
and mtl.inventory_item_id = inv.inventory_item_id
and mtl.organization_id = inv.organization_id
), '31-DEC-1900')
) last_active_date
FROM
-- **************************************************
-- Only select inventory items with active dynamic
-- R12 balances greater than zero
-- **************************************************
(SELECT
instock.inventory_item_id,
instock.organization_id,
instock.total_quantity,
(nvl(instock.total_quantity,0) * nvl(msib.unit_weight,0)) total_weight
FROM
(SELECT
onhand.inventory_item_id,
onhand.organization_id,
round(sum(onhand.transaction_quantity),2) total_quantity
FROM mtl_onhand_quantities onhand
WHERE onhand.transaction_quantity != 0
group by
onhand.inventory_item_id,
onhand.organization_id) instock
JOIN mtl_system_items_b msib on msib.inventory_item_id = instock.inventory_item_id
WHERE msib.organization_id = (select fws_inv_pkg.master_organization_id() from dual)
and (nvl(instock.total_quantity,0) * nvl(msib.unit_weight,0)) <> 0
) inv
);
CREATE INDEX fwsapex.fws_item_activity_idx1 ON fwsapex.fws_item_activity_mv (inventory_item_id, organization_id) compute statistics;
GRANT SELECT ON fwsapex.fws_item_activity_mv TO APPS;
END;
/