Detailed Integration Flows

This appendix covers the following topics:

• Forecast to ASCP Data Flow
• MDS to VCP Data Flow
• Purchasing Supply to ASCP Data Flow
• Sales Orders to ASCP Data Flow
• Sales Orders to MRP Data Flow
• WIP to ASCP Data Flow

Forecast to ASCP Data Flow

Data Flow Description:

1. New sales order entered with visible_demand_flag = Y and scheduled_ship_date not null.

2. Snapshot does not contain that new entered sales order.

3. The MRP_AP_xxxxx_V views do not contain that new sales order.

4. Internal rdbms trigger populates the newly entered sales order RowID into Snapshot Log (MLOG$ objects).

5. Launch ASCP Data Collections.

6. Refresh Snapshot reads row id from MLOG$.

7. Refresh Snapshot gets record from base table using row id.

8. Refresh Snapshot Populates/Updates the snapshot with current table record information.

9. As the snapshot is updated, the MRP_SN_xxxxx_T triggers fire.

10. The tirggers update/insert into MRP_AD_SALES_ORDERS table.

11. The MRP_AP_xxxxx_V views now have the new sales order.

12. Planning Data Pull reads the MRP_AP_xxxxx_V views.

13. Planning Data Pull validates and calculates the sales order's data.

14. Planning Data Pull pulls data from the EBS Source nstance over the database links.

    • ERP-based programs look in mrp_ap_apps_instances_all on source instance to get the link name to VCP.

    • VCP-based programs look in msc_apps_instances planning instances to get the link name to ERP.

15. Planning Data Pull inserts that pulled data into the MSC_ST_xxxxx Staging Tables.

16. ODS Load reads the MSC_ST_xxxxx Staging Tables.

17. ODS Load validates and calculates the sales order's data.

18. ODS Load purges exisitng ODS data and loads new/change records using either Complete or Net Change methods.

19. ODS Load data is hard coded with PLAN_ID = -1 for Collected Raw Source Data.

20. ODS Data is read by the MBP Memory Based Planner.

21. ODS Data is read by Production Scheduler.

22. ODS Data is read by Strategic Network Optimization.

23. ODS Data is read by Demand Planning.

See My Oracle Support Note #421787.1 -- "MDS, Forecast, SO to ASCP Data Flow Diagnostics" for more information about troubleshooting and diagnostics.

MDS to VCP Data Flow

See My Oracle Support Note #421787.1 -- "MDS, Forecast, SO to ASCP Data Flow Diagnostics" for more information about troubleshooting and diagnostics.

Purchasing Supply to ASCP Data Flow

Description of Data Flow:

1. New purchase orders or requisitions in approved status.

2. Snapshot does not contain that newly entered purchase order or requisition.

3. The MRP_AP_xxxxx_V views do not contain the new purchase order or requisition.

4. Internal rdbms trigger populates the newly entered purchase order or requisition RowID into Snapshot Log (MLOG$ objects).

5. Launch ASCP Data Collections.

6. Refresh Snapshot reads row id from MLOG$.

7. Refresh Snapshot gets record from base table using row id.

8. Refresh Snapshot populates/updates the snapshot with current table record information.

9. As the snapshot is updated, the MRP_SN_xxxxx_T triggers fire.

10. The tirggers update/insert into the MRP_AD_SUPPLY table.

11. The MRP_AP_xxxxx_V views now have the new purchase order or requisition.

12. Planning Data Pull reads the MRP_AP_xxxxx_V views.

13. Planning Data Pull validates and calculates the purchase order or requisition data.

14. Planning Data Pull pulls data from EBS source instance over the database links:

    • ERP-based programs look in mrp_ap_apps_instances_all on the source instance to get the link name to VCP.

    • VCP-based programs look in msc_apps_instances planning instances to get the link name to ERP.

15. Planning Data Pull inserts that pulled data into the MSC_ST_xxxxx staging tables.

16. ODS Load reads the MSC_ST_xxxxx staging tables.

17. ODS Load validates and calculates the purchase order or requisition data.

18. ODS Load purges existing ODS data and loads new/change records using either the Complete or Net Change methods.

19. ODS Load data is hard coded with PLAN_ID = -1 for Collected Raw Source Data.

20. ODS data is read by the MBP Memory Based Planner.

21. ODS data is read by Production Scheduler.

22. ODS data is read by Strategic Network Optimization.

Sales Orders to ASCP Data Flow

Description of Data Flow:

1. New sales order entered with visible_demand_flat = Y and scheduled_ship_date not null.

2. Snapshot does not contain that newly entered sales order.

3. The MRP_AP_xxxxx_V views do not contain the new sales order.

4. Internal rdbms trigger populates the newly entered sales order RowID into Snapshot Log (MLOG$ objects).

5. Launch ASCP Data Collections.

6. Refresh Snapshot reads row id from MLOG$.

7. Refresh Snapshot gets record from base table using row id.

8. Refresh Snapshot populates/updates the snapshot with current table record information.

9. As the snapshot is updated, the MRP_SN_xxxxx_T triggers fire.

10. The tirggers update/insert into the MRP_AD_SALES_ORDERS table.

11. The MRP_AP_xxxxx_V views now have the new sales order.

12. Planning Data Pull reads the MRP_AP_xxxxx_V views.

13. Planning Data Pull validates and calculates the sales order's data.

14. Planning Data Pull pulls data from EBS source instance over the database links:

    • ERP-based programs look in mrp_ap_apps_instances_all on the source instance to get the link name to VCP.

    • VCP-based programs look in msc_apps_instances planning instances to get the link name to ERP.

15. Planning Data Pull inserts that pulled data into the MSC_ST_xxxxx staging tables.

16. ODS Load reads the MSC_ST_xxxxx staging tables.

17. ODS Load validates and calculates the sales order's data.

18. ODS Load purges existing ODS data and loads new/change records using either the Complete or Net Change methods.

19. ODS Load data is hard coded with PLAN_ID = -1 for Collected Raw Source Data.

20. ODS data is read by the MBP Memory Based Planner.

21. ODS data is read by Production Scheduler.

22. ODS data is read by Strategic Network Optimization.

23. ODS data is read by Demand Planning.

See My Oracle Support Note #412375.1 -- "Sales Orders to ASCP Data Flow Diagram" for diagnostics, major profiles, dependencies and diagnostic scripts.

Sales Orders to MRP Data Flow

Description of Data Flow:

1. OM code inserts header info into OE_ORDER_HEADERS_ALL.

2. OM code inserts line info into OE_ORDER_LINES_ALL with:

   • • VISIBLE_DEMAND_FLAG = Y

   • • SHIP_FROM_ORG_ID is NOT NULL

   • • SHIP_TO_ORG_ID is NOT NULL

   • • SOLD_TO_ORG_ID is NOT NULL

   • • INVOICE_TO_ORG_ID is NOT NULL

3. OM inserts Sales Order data into MTL_SALES_ORDERS.

   • • OEXUSCHB.pls: Procedure Insert_Into_Mtl_Sales_Orders

4. Sales Order data is in MTL_DEMAND_OMOE view.

   • • Used to link reservations between oe_order_lines_all and mtl_demand

5. MRP code Inserts Line Info into MRP_SO_LINES_TEMP .

   • • MRPOAPIB.pls: MRP_OM_API_PK INSERT INTO mrp_so_lines_temp

6. Planning Manager processes records in MRP_SO_LINES_TEMP with process_status 2.

7. Planning Manager reads the Line_Ids from MRP_SO_LINES_TEMP, queries those lines from OE_ORDER_LINES_ALL, inserts into MRP_SALES_ORDER_UPDATES with process_status 2, and then sets the record in MRP_SO_LINES_TEMP to process_status 5 if successful.

8. Planning Manager processes the records in MRP_SALES_ORDER_UPDATES with status 2, set the status to 5 if successful, else errors the record with error messages.

9. Planning Manager then populates MRP_FORECAST_DATES and MRP_FORECAST_UPDATES.

10. Load/Copy/Merge MDS reads MRP_SALES_ORDER_UPDATES for valid demand records in MRP, then pulls the sales order line from OE_ORDER_LINES_ALL into MRP_SCHEDULE_DATES for that MDS name.

See My Oracle Support Note #414544.1 -- "Sales Orders to MRP Data Flow Diagram" for more information about profiles involved, major files, major related objects, diagnostic scripts, and common issues.

WIP to ASCP Data Flow

EBS side:

VCP side:

See My Oracle Support Note #413539.1 -- "WIP to ASCP Data Flow Diagram" for more information about troubleshooting and diagnostic scripts.