Including Non-booked Orders in Order Line and Schedule Line Tables

This task applies to E-Business Suite source systems, such as SDE_ORA11510_Adaptor, and SDE_ORAR12Version_Adaptor. By default, only booked orders are extracted from the E-Business Suite.

This diagram is described in surrounding text.
Description of the illustration GUID-0491AB66-E2AA-4AE3-8E71-2CB69A470AD4-default.gif

Therefore, all orders loaded into the Sales Order Lines, Sales Schedule Lines, and Sales Booking Lines tables are booked.

However, you can also load non-booked orders in Sales Order Lines (W_SALES_ORDERS_LINES_F) and Sales Schedule Lines (W_SALES_SCHEDULE_LINE_F), while loading only booked orders in Sales Booking Lines (W_SALES_BOOKING_LINE_F).

If you want to load non-booked orders into the Sales Order Lines and Sales Schedule Lines tables, you have to configure the extract so that it does not filter out non-booked orders. The OE_ORDER_LINES_ALL.BOOKED_FLAG = 'Y' condition indicates that an order is booked; therefore, this statement is used to filter out non-booked orders. So, to load all orders, including non-booked orders, remove the filter condition from the temp interfaces of the following mappings:

  • SDE_ORA_SalesOrderLinesFact

  • SDE_ORA_SalesOrderLinesFact_Primary

Also, if you include non-booked orders in the Sales Order Lines and Sales Schedule Lines tables, you have to exclude non-booked orders when you populate the Sales Booking Lines table from the Sales Order Lines or from the Sales Schedule Lines. You can do this by adding the W_SALES_ORDER_LINE_F.BOOKING_FLG = 'Y' or W_SALES_SCHEDULE_LINE_F.BOOKING_FLG = 'Y' condition to the interfaces of the following mappings:

  • SIL_SalesBookingLinesFact_Load_OrderLine_Credit

  • SIL_SalesBookingLinesFact_Load_OrderLine_Debit

  • SIL_SalesBookingLinesFact_Load_ScheduleLine_Credit

  • SIL_SalesBookingLinesFact_Load_ScheduleLine_Debit

To include non-booked orders in the Sales Order Lines and Sales Schedule Lines tables (for both full and Incremental load):

  1. In ODI Designer Navigator, open the SDE_ORA11510_Adaptor, or SDE_ORAR12Version _Adaptor.
  2. Find SDE_ORA_SalesOrderLinesFact and SDE_ORA_SalesOrderLinesFact_Primary.

    • SDE_ORA_SalesOrderLinesFact.W_SALES_ORDER_LINE_FS_SQ_BCI_SALES_ORDLNS

    • SDE_ORA_SalesOrderLinesFact_Primary.W_SALES_ORDER_LINE_F_PE_SQ_BCI_SALES_ORDLS

    Then open these temp interfaces.

  3. Find and delete the filter condition OE_ORDER_LINES_ALL.BOOKED_FLAG='Y' from the temp interfaces mentioned above.
  4. Save your changes to the repository.

Follow the steps below to make changes for Booking Lines table.

Including Only Booked Orders in the Sales Booking Lines Table

You can include only booked orders in the Sales Booking Lines table.

  1. In ODI Designer Navigator, open the SILOS folder.
  2. Open the following interfaces then add the filter to Filters section.

    • SIL_SalesBookingLinesFact_Load_OrderLine_Credit folder: Open Quick-Edit tab of the SIL_SalesBookingLinesFact_Load_OrderLine_Credit.W_SALES_BOOKING_LINE_F_SQ_W_SALES_ORDER_LINE_F interface, and add W_SALES_ORDER_LINE_F.BOOKING_FLG = 'Y' to the Filters section.

    • SIL_SalesBookingLinesFact_Load_OrderLine_Debt folder : Open Quick-Edit tab of the SIL_SalesBookingLinesFact_Load_OrderLine_Debt.W_SALES_BOOKING_LINE_F interface, and add SQ_W_SALES_ORDER_LINE_F.BOOKING_FLG = 'Y' to the Filters section.

    • SIL_SalesBookingLinesFact_Load_ScheduleLine_Credit folder : Open Quick-Edit tab of the SIL_SalesBookingLinesFact_Load_ScheduleLine_Credit.W_SALES_BOOKING_LINE_F_SQ_W_SALES_SCHEDULE_LINE_F interface, and add W_SALES_SCHEDULE_LINE_F.BOOKING_FLG = 'Y' to the Filters section.

    • SIL_SalesBookingLinesFact_Load_ScheduleLine_Debt folder : Open Quick-Edit tab of the SIL_SalesBookingLinesFact_Load_ScheduleLine_Debt.W_SALES_BOOKING_LINE_F interface, and add SQ_W_SALES_SCHEDULE_LINE_F.BOOKING_FLG = 'Y' to the Filters section.

  3. Save your changes to the repository.