By default, E-Business Suite only has the 'PRIMARY' relationship code in the PA_PROJECT_CUSTOMERS table. Therefore, the value is included in the ODI filter used in the source extract mapping for the Project dimension to get the customer for a project.
Customers can define an additional value such as 'OVERRIDE CUSTOMER' as the relationship value. In this case, the filter must be edited to include any additional values.
- In ODI Designer Navigator, connect to your ODI repository.
- Open the folder appropriate to your source system (for example, SDE_ORA_11510_Adaptor for Oracle V11.5.10, or SDE_ORA_R12_Adaptor for Oracle V12).
- Expand the SDE_ORA_ProjectDimension folder and open the interface SDE_ORA_Project.W_PROJECT_DS.LKP_PROJ_CUST and click on the Quick-Edit tab.
- Expand the Filters tab and edit the expression column for the second filter.
- Remove the existing SQL and add the following sample SQL where it is assumed the values are 'PRIMARY' and 'OVERRIDE CUSTOMER'.
Modify it according to your configuration. If you want it to be independent of any relationships, then just remove the filters on PROJECT_RELATIONSHIP_CODE - UPPER(PA_PROJECT_CUSTOMERS.PROJECT_RELATIONSHIP_CODE (+)) IN ('PRIMARY' . 'OVERRIDE CUSTOMER').
Note:
If the lookup returns more than one customer, then apply a MAX function on the id so that it always returns one row.
- Review the mapping to ensure it is valid then click OK and save the interface.
- Regenerate the scenario by expanding the Packages folder and right-click the scenario to regenerate.