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:

  1. In ODI Designer Navigator, connect to your ODI repository.
  2. Navigate to BI Apps Project, then Mappings, then PLP, then PLP_Project_GLReconciliationFact.
  3. Before making any changes, right-click the PLP_Project_GLReconciliationFact folder and create a version of existing folder.
  4. Enter description: Before adding GL Account join for 11510 source, then click OK.
  5. 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

    1. Navigate to BI Apps Project, then Mappings, then PLP, then PLP_Project_GLReconciliationFact.
    2. Open the temp interface, then go to Quick-Edit tab.
    3. Expand Sources, then click Add Sources.
      Description of GUID-23638AB5-54D7-4FC9-A076-C5A3C3D2414A-default.gif follows
      Description of the illustration GUID-23638AB5-54D7-4FC9-A076-C5A3C3D2414A-default.gif

      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?"

      Description of GUID-282EA0F0-CD9F-419E-95D1-9239CB6D1D24-default.gif follows
      Description of the illustration GUID-282EA0F0-CD9F-419E-95D1-9239CB6D1D24-default.gif
      Source Wizard dialog box will open up.
    4. 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?"
    5. 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.

      1. Expand Joins section on Quick-Edit tab and click on Add Joins.
        Description of GUID-B11EB0A2-E683-4970-A3D9-09EA2FB00A03-default.gif follows
        Description of the illustration GUID-B11EB0A2-E683-4970-A3D9-09EA2FB00A03-default.gif
      2. 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
      3. Select Join Type as Left Outer Join and Click OK.
        Description of GUID-3F6553A9-D98D-4F4C-96B3-7BB7A87C584F-default.gif follows
        Description of the illustration GUID-3F6553A9-D98D-4F4C-96B3-7BB7A87C584F-default.gif
      4. 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
      5. Select join Type as Inner Join and Click OK.
        Description of GUID-7A973587-8878-413D-BA1F-AC8816F8B0A5-default.gif follows
        Description of the illustration GUID-7A973587-8878-413D-BA1F-AC8816F8B0A5-default.gif
      6. 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.
        Description of GUID-6F9AC7E5-9302-4B33-8234-17E7808B77EC-default.gif follows
        Description of the illustration GUID-6F9AC7E5-9302-4B33-8234-17E7808B77EC-default.gif
      7. 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)
      8. Make sure you modify the join condition to OR apart from adding the COALESCE function. Click OK.
        Description of GUID-00985EF3-FE13-4157-9E3F-62BAADD0901B-default.gif follows
        Description of the illustration GUID-00985EF3-FE13-4157-9E3F-62BAADD0901B-default.gif
      9. Check the Ordered check box for the Left Outer join between GL Linkage and GL Account lookup.
        Description of GUID-BD5EA9DF-04B9-448C-98CF-B47B0A806B61-default.gif follows
        Description of the illustration GUID-BD5EA9DF-04B9-448C-98CF-B47B0A806B61-default.gif
  6. 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

  7. Regenerate the scenario.
    1. 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.
    2. On the Regenerate Scenario dialog box, click OK.
    3. On Scenario Variables dialog, select Use All in the Startup Parameters drop down list, then click OK.