Hello All

I have created a Materialized view using the following code

CREATE MATERIALIZED VIEW "APPS"."XXBST_UNPACK_PENDING_MV" ("CUST_ACCOUNT_ID", "CUSTOMER_NAME", "SUPPLIER_ID", "SUPPLIER_NAME", "SHIPMENT_ID", "SHIPMENT_NUMBER", "UNPACK_DATE", "DAYS_IN_UNPACK_PENDING_STATUS")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPS_TS_TX_DATA" 
  BUILD DEFERRED
  USING INDEX 
  REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT sysdate + (1/24/60)*30
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING TRUSTED CONSTRAINTS EVALUATE USING CURRENT EDITION  DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS SELECT * FROM xxbst_unpack_pending_v;

This is in a non prod environment which has just been restored from a backup due to the code being changed in prod to this lowest level expanded code

CREATE MATERIALIZED VIEW "APPS"."XXBST_UNPACK_PENDING_MV" ("CUST_ACCOUNT_ID", "CUSTOMER_NAME", "SUPPLIER_ID", "SUPPLIER_NAME", "SHIPMENT_ID", "SHIPMENT_NUMBER", "UNPACK_DATE", "DAYS_IN_UNPACK_PENDING_STATUS")
  SEGMENT CREATION IMMEDIATE
  ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS EVALUATE USING CURRENT EDITION  DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS SELECT "A1"."CUST_ACCOUNT_ID" "CUST_ACCOUNT_ID","A1"."CUSTOMER_NAME" "CUSTOMER_NAME","A1"."SUPPLIER_ID" "SUPPLIER_ID","A1"."SUPPLIER_NAME" "SUPPLIER_NAME","A1"."SHIPMENT_ID" "SHIPMENT_ID","A1"."SHIPMENT_NUMBER" "SHIPMENT_NUMBER","A1"."UNPACK_DATE" "UNPACK_DATE","A1"."DAYS_IN_UNPACK_PENDING_STATUS" "DAYS_IN_UNPACK_PENDING_STATUS" FROM  (SELECT "A7"."CUST_ACCOUNT_ID" "CUST_ACCOUNT_ID","APPS"."XXBST_DEV_UTILS_PKG"."GET_CUST_SHORT_NAME"("A7"."CUST_ACCOUNT_ID") "CUSTOMER_NAME","A3"."VENDOR_ID" "SUPPLIER_ID","A3"."VENDOR_NAME" "SUPPLIER_NAME","A10"."SHIPMENT_ID" "SHIPMENT_ID","A10"."SHIPMENT_NUMBER" "SHIPMENT_NUMBER",MAX("A7"."CREATION_DATE") "UNPACK_DATE",TRUNC(SYSDATE)-TRUNC(MAX("A7"."CREATION_DATE")) "DAYS_IN_UNPACK_PENDING_STATUS" FROM "XXBST"."XXBST_TNT_SHPMNT" "A10","XXBST"."XXBST_TNT_MV_PLAN" "A9","XXBST"."XXBST_TNT_MV" "A8","XXICE"."XXICE_SM_RCV_HEADERS" "A7","XXBST"."XXBST_TNT_HEADERS_ALL" "A6","XXBST"."XXBST_TNT_LINES_ALL" "A5","XXBST"."XXBST_BOM_ORDER_HEADERS_ALL" "A4", (SELECT "A12"."VENDOR_ID" "VENDOR_ID","A12"."VENDOR_NAME" "VENDOR_NAME" FROM "AP"."AP_SUPPLIERS" "A12","AR"."HZ_PARTIES" "A11" WHERE "A12"."PARTY_ID"="A11"."PARTY_ID") "A3","XXBST"."XXBST_TNT_MV_PLAN" "A2" WHERE "A10"."SHIPMENT_ID"="A7"."SHIPMENT_ID" AND "A10"."SHIPMENT_ID"="A9"."SHIPMENT_ID" AND "A9"."PLAN_ID"="A8"."PLAN_ID" AND "A8"."MV_TYPE"='DELIVERY' AND "A8"."MV_STATUS"='UNPACK_PENDING' AND "A6"."SHIPMENT_ID"="A10"."SHIPMENT_ID" AND "A5"."TRACK_ID"="A6"."TRACK_ID" AND "A4"."ORDER_HEADER_ID"="A5"."ORDER_HEADER_ID" AND "A3"."VENDOR_ID"="A4"."VENDOR_ID" AND "A10"."SHIPMENT_ID"="A2"."SHIPMENT_ID" GROUP BY "A7"."CUST_ACCOUNT_ID","A3"."VENDOR_ID","A3"."VENDOR_NAME","A10"."SHIPMENT_ID","A10"."SHIPMENT_NUMBER") "A1";

I need to know how this could have happened as the developers did not change the code.
We did recently apply some patches and there was a suggestion that this could be due to the patches and possibly a refresh running when the patch was applied.

Any help would be hugely appreciated

Nicki

Additional information requested

1) Just to confirm, the first DDL is the one you ran in Production, and then you copied Production back to Non-Prod?
2) Then in Non-Prod, the DDL looked like the second one? If so, how did you extract this DDL?
3) When you say "restore", do you mean an RMAN backup/restore or some other means? (DataPump export/import perhaps?)

1) the first ddl is from a non prod environment which a backup was taken for and then this environment was restored from the backup. It showed me the different source from the backe-up taken in August and the current Prod environment.

2) second ddl is the prod ddl which was retrieved using export ddl in sql developer

3) the restore was done by the DBA's from the backup

In Summary

When we recently applied some patches for EBS some of the materialized view objects we were using were actually lost and only the table structure for the mv was left in the DB. This necessitated us having to restore from an old backup to check differences before and after the patch

I hope that makes sense