- Functional Configuration Reference for Business Intelligence Applications
- Functional Configuration Task Reference
- Configuring Projects GL Reconciliation Solution for E-Business Suite 11.5.10
Configuring Projects GL Reconciliation Solution for E-Business Suite 11.5.10
Projects GL Reconciliation solution is supported by default for E-Business Suite V12 and PeopleSoft V90 adaptors. To support this solution for E-Business Suite V11510 adaptor, you must perform the steps below. These steps include adding a join in the PLP GL reconciliation ODI interfaces
Note:
The additional join is required for E-Business Suite V11510 adaptor because sub-ledger accounting was introduced from E-Business Suite V12 onwards and the joins between Projects and GL source tables in E-Business Suite V11510 and V12 are different.To Configure Projects GL Reconciliation Solution for E-Business Suite 11.5.10:
- In ODI Designer Navigator, connect to your ODI repository.
- Navigate to BI Apps Project, then Mappings, then PLP, then PLP_Project_GLReconciliationFact.
- Before making any changes, right-click the PLP_Project_GLReconciliationFact folder and create a version of existing folder.
- Enter description: Before adding GL Account join for 11510 source, then click OK.
- Modify the following three temp interfaces:
-
PLP_Project_GLReconciliationFact.SQ_AGG_CDL_AMOUNTS
-
PLP_Project_GLReconciliationFact.SQ_W_PROJ_GL_RECNCLIATION_F_A ("Cost Distributions with mismatch" dataset)
-
PLP_Project_GLReconciliationFact.SQ_W_PROJ_GL_RECNCLIATION_F_U
- Navigate to BI Apps Project, then Mappings, then PLP, then PLP_Project_GLReconciliationFact.
- Open the temp interface, then go to Quick-Edit tab.
- Expand Sources, then click Add Sources.
Source Wizard dialog box will open up. Click on Interfaces tab and search for LKP_W_GL_ACCOUNT_D. Select the lookup interface under PLP folder, give it an alias: LKP_W_GL_ACCOUNT_D, check the Use Temporary Interface as Derived Table (Sub-Select) option and click OK. Press NO when ODI prompts "Do you want to perform Automatic Mapping?"
Source Wizard dialog box will open up. - Click on "Interfaces" tab and search for LKP_W_GL_ACCOUNT_D. Select the lookup interface under PLP folder, give it an alias: LKP_W_GL_ACCOUNT_D, check the "Use Temporary Interface as Derived Table (Sub-Select)" option and Click Ok. Press NO when ODI prompts "Do you want to perform Automatic Mapping?"
- Add two joins:
- A join between W_GL_LINKAGE_INFORMATION_G and LKP_W_GL_ACCOUNT_D.
- A join between W_PROJ_COST_LINE_F and LKP_W_GL_ACCOUNT_D.
-
Expand Joins section on Quick-Edit tab and click on Add Joins.
-
Select General Ledger Linkage Information as Left Source and LKP_W_GL_ACCOUNT_D as Right Source. Join on fields:
W_GL_LINKAGE_INFORMATION_G.GL_ACCOUNT_ID = LKP_W_GL_ACCOUNT_D.INTEGRATION_ID AND W_GL_LINKAGE_INFORMATION_G.DATASOURCE_NUM_ID = LKP_W_GL_ACCOUNT_D.DATASOURCE_NUM_ID
-
Click Add Joins. Select W_PROJ_COST_LINE_Fas Left Source and LKP_W_GL_ACCOUNT_D as Right Source. Join on fields:
W_PROJ_COST_LINE_F.COST_GL_ACCOUNT_WID=LKP_W_GL_ACCOUNT_D.ROW_WID AND W_PROJ_COST_LINE_F.CR_GL_ACCOUNT_WID=LKP_W_GL_ACCOUNT_D.ROW_WID
-
In the "Joins" section, scroll to right and "Edit" the newly added join between W_PROJ_COST_LINE_F and LKP_W_GL_ACCOUNT_D.
-
Edit the join condition to:
W_PROJ_COST_LINE_F.COST_GL_ACCOUNT_WID = COALESCE(LKP_W_GL_ACCOUNT_D.ROW_WID,#ETL_UNSPEC_NUM) OR W_PROJ_COST_LINE_F.CR_GL_ACCOUNT_WID = COALESCE(LKP_W_GL_ACCOUNT_D.ROW_WID,#ETL_UNSPEC_NUM)
-
Make sure you modify the join condition to OR apart from adding the COALESCE function. Click OK.
-
Check the Ordered check box for the Left Outer join between GL Linkage and GL Account lookup.
-
-
- Save the interface.
Repeat the steps a. to e. for all the following three temp interfaces:
-
PLP_Project_GLReconciliationFact.SQ_AGG_CDL_AMOUNTS
-
PLP_Project_GLReconciliationFact.SQ_W_PROJ_GL_RECNCLIATION_F_A ("Cost Distributions with mismatch" dataset only. Do not add joins in "Journal Lines with mismatch" dataset)
-
PLP_Project_GLReconciliationFact.SQ_W_PROJ_GL_RECNCLIATION_F_U
-
- Regenerate the scenario.
- After all three temp interfaces are modified and saved, navigate to PLP_Project_GLReconciliationFact, then Packages, then PLP_Project_GLReconciliationFact, then Scenarios. Right-click scenario PLP_PLP_PROJECT_GLRECONCILIATIONFACT and click Regenerate.
- On the Regenerate Scenario dialog box, click OK.
- On Scenario Variables dialog, select Use All in the Startup Parameters drop down list, then click OK.