This appendix covers the following topics:
During the Refresh Collection Snapshots request, run during the Collections process, the snapshots for all the selected data objects are updated before the data is pulled to the VCP Planning Server.
The following SQL reveals the list of snapshots that are available in the release on the system.
select owner, name, master from all_snapshots
where name like '%SN'
and name not in ('MSC_ATP_PLAN_SN','WSH_DELIVERY_DATES_SN')
order by name;
This list changes with each release or may change depending on the level of code in the release. See My Oracle Support Note 179522.1 -- "Data Collection Use and Parameters" for the list of snapshots being maintained for each release.
List of snapshots for Value Chain Planning Applications. This list can be generated by SQL #22 -- Show MLOGs and Related Snapshots and Base Tables for Data Collections.
In addition, the list includes snapshots that share the same MLOG as Data Collections snapshots. These other snapshots cannot be truncated using automatic refresh and must be truncated and refreshed manually. Marked by an asterisk (*) in the table, these include:
ENI - Product Intelligence
OZF - Trade Management
Multiple rows for a snapshot mean a complex snapshot where the query is for the multiple base tables and MLOGs shown in the table.
The snapshots are as follows:
| Snapshot | Snapshot Owner | Base Table Name | Base Table Owner | MLOG$ |
|---|---|---|---|---|
| AHL_SCH_MTLS_SN | APPS | AHL_SCHEDULE_MATERIALS | AHL | MLOG$_AHL_SCHEDULE_MATERIA |
| BOM_BOMS_SN | APPS | BOM_STRUCTURES_B | BOM | MLOG$_BOM_STRUCTURES_B |
| BOM_CTO_ORDER_DMD_SN | BOM | BOM_CTO_ORDER_DEMAND | BOM | MLOG$_BOM_CTO_ORDER_DEMAND |
| BOM_INV_COMPS_SN | APPS | BOM_COMPONENTS_B BOM_STRUCTURES_B |
BOM | MLOG$_BOM_COMPONENTS_B MLOG$_BOM_STRUCTURES_B |
| BOM_OPR_NETWORKS_SN | APPS | BOM_OPERATION_SEQUENCES BOM_OPERATIONAL_ROUTINGS BOM_OPERATION_NETWORKS |
BOM | MLOG$_BOM_OPERATION_SEQUEN MLOG$_BOM_OPERATIONAL_ROUT MLOG$_BOM_OPERATION_NETWOR |
| BOM_OPR_RESS_SN | BOM_OPERATION_SEQUENCES BOM_OPERATION_RESOURCES BOM_OPERATIONAL_ROUTINGS |
MLOG$_BOM_OPERATION_SEQUEN MLOG$_BOM_OPERATION_RESOUR MLOG$_BOM_OPERATIONAL_ROUT |
||
| BOM_OPR_RTNS_SN | APPS | BOM_OPERATIONAL_ROUTINGS | BOM | MLOG$_BOM_OPERATIONAL_ROUT |
| BOM_OPR_SEQS_SN | APPS | BOM_OPERATION_SEQUENCES BOM_OPERATIONAL_ROUTINGS |
BOM | MLOG$_BOM_OPERATION_SEQUEN MLOG$_BOM_OPERATIONAL_ROUT |
| BOM_RES_CHNGS_SN | APPS | BOM_RESOURCE_CHANGES BOM_DEPARTMENTS | BOM | MLOG$_BOM_RESOURCE_CHANGES MLOG$_BOM_DEPARTMENTS |
| BOM_RES_INST_CHNGS_SN | APPS | BOM_DEPARTMENTS BOM_RES_INSTANCE_CHANGES |
BOM | MLOG$_BOM_DEPARTMENTS MLOG$_BOM_RES_INSTANCE_CHA |
| BOM_SUB_COMPS_SN | APPS | BOM_STRUCTURES_B BOM_COMPONENTS_B BOM_SUBSTITUTE_COMPONENTS |
BOM | MLOG$_BOM_STRUCTURES_B MLOG$_BOM_COMPONENTS_B MLOG$_BOM_SUBSTITUTE_COMPO |
| BOM_SUB_OPR_RESS_SN | APPS | BOM_OPERATION_SEQUENCES BOM_SUB_OPERATION_RESOURCES BOM_OPERATIONAL_ROUTINGS |
BOM | MLOG$_BOM_OPERATION_SEQUEN MLOG$_BOM_SUB_OPERATION_RE MLOG$_BOM_OPERATIONAL_ROUT |
| CSP_REPAIR_PO_HEADERS_SN | APPS | CSP_REPAIR_PO_HEADERS | CSP | MLOG$_CSP_REPAIR_PO_HEADER |
| EAM_WO_RELATIONSHIPS_SN | APPS | EAM_WO_RELATIONSHIPS | EAM | MLOG$_EAM_WO_RELATIONSHIPS |
| ENI_DBI_BOM_COMPONENTS_MV1* | APPS | BOM_COMPONENTS_B | BOM | MLOG$_BOM_COMPONENTS_B |
| ENI_DBI_BOM_COMPONENTS_MV2* | APPS | BOM_COMPONENTS_B | BOM | MLOG$_BOM_COMPONENTS_B |
| ENI_DBI_MFG_STEPS_JOIN_MV* | APPS | BOM_OPERATIONAL_ROUTINGS | BOM | MLOG$_BOM_OPERATIONAL_ROUT |
| ENI_RES_1_MV* | APPS | BOM_DEPARTMENTS | BOM | MLOG$_BOM_DEPARTMENTS |
| MRP_FORECAST_DATES_SN | APPS | MRP_FORECAST_DATES | MRP | MLOG$_MRP_FORECAST_DATES |
| MRP_FORECAST_DSGN_SN | APPS | MRP_FORECAST_DESIGNATORS | MRP | MLOG$_MRP_FORECAST_DESIGNA |
| MRP_FORECAST_ITEMS_SN | APPS | MRP_FORECAST_ITEMS | MRP | MLOG$_MRP_FORECAST_ITEMS |
| MRP_SCHD_DATES_SN | APPS | MRP_SCHEDULE_DATES | MRP | MLOG$_MRP_SCHEDULE_DATES |
| MTL_DEMAND_SN | APPS | MTL_DEMAND | INV | MLOG$_MTL_DEMAND |
| MTL_ITEM_CATS_SN | APPS | MTL_ITEM_CATEGORIES | INV | MLOG$_MTL_ITEM_CATEGORIES |
| MTL_ITEM_RELATIONSHIPS_SN | MTL_RELATED_ITEMS | MLOG$_MTL_RELATED_ITEMS | ||
| MTL_MTRX_TMP_SN | APPS | MTL_MATERIAL_TRANSACTIONS_TEMP | INV | MLOG$_MTL_MATERIAL_TRANSAC |
| MTL_OH_QTYS_SN | APPS | MTL_ONHAND_QUANTITIES_DETAIL | INV | MLOG$_MTL_ONHAND_QUANTITIE |
| MTL_RESERVATIONS_SN | APPS | MTL_RESERVATIONS | INV | MLOG$_MTL_RESERVATIONS |
| MTL_SUPPLY_SN | APPS | MTL_SUPPLY | INV | MLOG$_MTL_SUPPLY |
| MTL_SYS_ITEMS_SN | APPS | MTL_SYSTEM_ITEMS_B | INV | MLOG$_MTL_SYSTEM_ITEMS_B |
| MTL_TXN_REQUEST_LINES_SN | APPS | MTL_TXN_REQUEST_LINES | INV | MLOG$_MTL_TXN_REQUEST_LINE |
| MTL_U_DEMAND_SN | APPS | MTL_USER_DEMAND | INV | MLOG$_MTL_USER_DEMAND |
| MTL_U_SUPPLY_SN | APPS | MTL_USER_SUPPLY | INV | MLOG$_MTL_USER_SUPPLY |
| OE_ODR_LINES_SN | APPS | OE_ORDER_LINES_ALL | ONT | MLOG$_OE_ORDER_LINES_ALL |
| OZF_EARNING_SUMMARY_MV* | APPS | MTL_ITEM_CATEGORIES | INV | MLOG$_MTL_ITEM_CATEGORIES |
| PO_ACCEPTANCES_SN | APPS | PO_ACCEPTANCES | PO | MLOG$_PO_ACCEPTANCES |
| PO_CHANGE_REQUESTS_SN | APPS | PO_CHANGE_REQUESTS | PO | MLOG$_PO_CHANGE_REQUESTS |
| PO_SI_CAPA_SN | APPS | PO_SUPPLIER_ITEM_CAPACITY | PO | MLOG$_PO_SUPPLIER_ITEM_CAP |
| WIP_DSCR_JOBS_SN | APPS | WIP_DISCRETE_JOBS | WIP | MLOG$_WIP_DISCRETE_JOBS |
| WIP_FLOW_SCHDS_SN | APPS | WIP_FLOW_SCHEDULES | WIP | MLOG$_WIP_FLOW_SCHEDULES |
| WIP_OPR_RES_INSTS_SN | APPS | WIP_OP_RESOURCE_INSTANCES | WIP | MLOG$_WIP_OP_RESOURCE_INST |
| WIP_REPT_ITEMS_SN | APPS | WIP_REPETITIVE_ITEMS | WIP | MLOG$_WIP_REPETITIVE_ITEMS |
| WIP_REPT_SCHDS_SN | APPS | WIP_REPETITIVE_SCHEDULES | WIP | MLOG$_WIP_REPETITIVE_SCHED |
| WIP_WLINES_SN | APPS | WIP_LINES | WIP | MLOG$_WIP_LINES |
| WIP_WOPRS_SN | APPS | WIP_MSC_OPEN_JOB_STATUSES WIP_DISCRETE_JOBS WIP_OPERATIONS |
WIP | MLOG$_WIP_MSC_OPEN_JOB_STA MLOG$_WIP_DISCRETE_JOBS MLOG$_WIP_OPERATIONS |
| WIP_WOPR_NETWORKS_SN | APPS | WIP_OPERATION_NETWORKS | WIP | MLOG$_WIP_OPERATION_NETWOR |
| WIP_WOPR_RESS_SN | APPS | WIP_DISCRETE_JOBS WIP_MSC_OPEN_JOB_STATUSES WIP_OPERATION_RESOURCES |
WIP | MLOG$_WIP_DISCRETE_JOBS MLOG$_WIP_MSC_OPEN_JOB_STA MLOG$_WIP_OPERATION_RESOUR" |
| WIP_WOPR_SUB_RESS_SN | APPS | WIP_SUB_OPERATION_RESOURCES | WIP | MLOG$_WIP_SUB_OPERATION_RE |
| WIP_WREQ_OPRS_SN | APPS | WIP_MSC_OPEN_JOB_STATUSES WIP_DISCRETE_JOBS WIP_REQUIREMENT_OPERATIONS |
WIP | MLOG$_WIP_MSC_OPEN_JOB_STA MLOG$_WIP_DISCRETE_JOBS MLOG$_WIP_REQUIREMENT_OPER |
| WSH_DELIVERY_DETAILS_SN | WSH | WSH_DELIVERY_DETAILS | WSH | MLOG$_WSH_DELIVERY_DETAILS |
| WSH_TRIP_SN | APPS | WSH_TRIPS | WSH | MLOG$_WSH_TRIPS |
| WSH_TRIP_STOP_SN | APPS | WSH_TRIP_STOPS | WSH | MLOG$_WSH_TRIP_STOPS |
| WSM_LJ_OPRS_SN | APPS | WSM_COPY_OPERATIONS | WSH | MLOG$_WSM_COPY_OPERATIONS |
| WSM_LJ_OPR_NWK_SN | APPS | WSM_COPY_OP_NETWORKS | WSM | MLOG$_WSM_COPY_OP_NETWORKS |
| WSM_LJ_OPR_RESS_INSTS_SN | APPS | WSM_COPY_OP_RESOURCE_INSTANCES | WSM | MLOG$_WSM_COPY_OP_RESOURCE1 |
| WSM_LJ_OPR_RESS_SN | APPS | WSM_COPY_OP_RESOURCES | WSM | MLOG$_WSM_COPY_OP_RESOURCE |
| WSM_LJ_REQ_OPRS_SN | APPS | WSM_COPY_REQUIREMENT_OPS | WSM | MLOG$_WSM_COPY_REQUIREMENT |
Note: This information relates to Global ATP data collection as well as Advanced Supply Chain Planning data collection.
Do not be concerned if you run the following SQL query and find that %SN has a status = INVALID using the following SQL script:
select distinct object_name, status from all_objects
where object_name like '%SN'
and object_name not in ('MSC_ATP_PLAN_SN','WSH_DELIVERY_DATES_SN')
and object_type in ('MATERIALIZED VIEW')
order by object_name;
Seeing an invalid snapshot is normal and of no concern. The object will be rebuilt and validated when Collections is run. Source snapshots may have become invalid in either their status or in the data contained therein. This could occur for many reasons. If you encounter the following error:
ORA-12034: snapshot log on"<schema>"."<snapshot_name>" younger than last refresh
Oracle recommends that you run refresh of that snapshot via the application. Alternatively, the following code can be run as the APPS user on the source instance to manually execute the code in the request:
SELECT MRP_AP_REFRESH_S.NEXTVAL FROM DUAL;
exec dbms_snapshot.refresh('<owner>.<snapshot_name>','COMPLETE');
-- note you can refresh one or more snapshots with one NEXTVAL
exec dbms_snapshot.refresh('<owner>.<snapshot_name>','COMPLETE');
exit;
Note: The select statement for NEXTVAL is only for Collection Snapshots.