This discussion is archived
8 Replies Latest reply: Jan 16, 2013 6:53 PM by 806269 RSS

Data did not show up after running execution plan for financial module

Joni Newbie
Currently Being Moderated
Hi Gurus,

I have problem when loading financial module, after I ran the execution plan, execution plan succeed (complete) without warning but there was no data in these table:
- W_AP_BALANCE_F
- W_AR_BALANCE_F
- W_GL_BALANCE_FW_AP_XACT_F
- W_AR_XACT_R

I have already set the data source number for the ebs version as in page 56 document e19039-Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users. I already followed reference from metalink (ID 746902.1, ID 865215.1, ID 1256585.1) but still did not find resolution. Any idea where could I start to debug?


Regards,


Joni
  • 1. Re: Data did not show up after running execution plan for financial module
    Benoît Journeyer
    Currently Being Moderated
    Hi Joni,

    Have you data in the staging table ? :

    - W_AP_BALANCE_FS
    - W_AR_BALANCE_FS
    - W_GL_BALANCE_FW_AP_XACT_FS
    - W_AR_XACT_FS

    If not could you view the query executed by informatica (Worflow Monitor - Folder ORA_SDE-...) and see if all parameters are correctly used.

    Hope it helps,
  • 2. Re: Data did not show up after running execution plan for financial module
    Joni Newbie
    Currently Being Moderated
    Hi Benoit,

    Thanks for your quick reply. After I followed document on metalink ID 1366309.1 and did patching for Patch 9739315 and Patch 9255542 here is the result after I ran the execution plan:

    No data for those tables:

    W_AP_XACT_FS
    W_AP_XACT_FS
    W_AP_BALANCE_F
    W_AR_BALANCE_F


    Data available for those tables:
    W_AP_XACT_F
    W_AP_XACT_F
    W_GL_BALANCE_F
    W_GL_BALANCE_FS


    There were No table
    W_AP_BALANCE_FS
    W_AR_BALANCE_FS
    Any idea where should I start to investigate?

    Thanks

    Joni
  • 3. Re: Data did not show up after running execution plan for financial module
    Benoît Journeyer
    Currently Being Moderated
    Hi Joni,

    the best way is to use Informatica with dependencies tools to see what mappings are used to load data in your table and see with Worklfow monitor the query used for these mappings.
    For example :
    - table W_AP_BALANCE_F is loaded by some mappings (PLP folder):
    - PLP_APBalanceFutureActivityLoad
    - PLP_APBalanceDateUpdate
    - PLP_APBalanceCurrentActivityLoad

    - PLP_APBalanceCurrentActivityLoad uses as source
    - W_AP_BALANCE_F
    - W_AP_BALANCE_F_TMP

    - table W_AP_BALANCE_F_TMP is loaded by some mappings (PLP folder):
    - PLP_APBalanceFutureActivityUpdate
    - PLP_APIncrActivityLoad

    - PLP_APIncrActivityLoad uses as source
    - W_AP_XACT_F
    - W_GL_ACCOUNT_D
    - W_STATUS_D
    with inner join between these tables

    So if you have data in the table W_AP_XACT_F, maybe your dimension W_GL_ACCOUNT_D has a bad configuration or maybe you have no record with a status equals to 'POSTED' or 'REVERSED'.
    The only way is to see the query runs by Informatica in your environment and find your issue.

    Hope it helps,

    Benoît
  • 4. Re: Data did not show up after running execution plan for financial module
    Joni Newbie
    Currently Being Moderated
    Hi Benoit,

    Thanks for your help. Yes it is true something bad for my configuration on file_group_acct_codes_ora.csv. I have fixed it already and data for W_AP_BALANCE_F and W_AR_BALANCE_F already show up. But for w_gl_cogs_f and w_gl_cogs_fs still did not show the result. Did you have documentation for mapping involve for each subject area such as for Financial - General Ledger, Financials - Cost of Goods Sold, Financials - General Ledger, Financials - Payable, etc. If you have any documentation for the mapping involve please send to my email siauan@yahoo.com. I will award you for helpful answers.

    Really appreciate for you help.

    Thanks,

    Joni
  • 5. Re: Data did not show up after running execution plan for financial module
    Benoît Journeyer
    Currently Being Moderated
    Hi Joni,

    the documentation for mapping Financial module is provided in the Oracle documentation (Configuration Guide for Informatica PowerCenter Users).
    You can find this documentation on the URL with your BI Apps version : http://www.oracle.com/technetwork/middleware/bi-foundation/documentation/bi-apps-098545.html

    Hope it helps,

    Benoit
  • 6. Re: Data did not show up after running execution plan for financial module
    JV123 Journeyer
    Currently Being Moderated
    Hi Joni,

    check the dependencies for the tables w_gl_cogs_f and w_gl_cogs_fs in informatica mappings and trace data/dependencies from target to source starting from SIL mapping for these tables. it is the best way to trace the issue. you can also find target to source dependencis in data lineage document provided by oracle for biapps. biapps document doesn't tell you anything at the details level for a specific mapping. plzz follow my advise.

    Thanks
    Jay.
  • 7. Re: Data did not show up after running execution plan for financial module
    Joni Newbie
    Currently Being Moderated
    Hi Jay,

    Thanks for your input. I will follow your advice


    Regards,


    Joni
  • 8. Re: Data did not show up after running execution plan for financial module
    806269 Newbie
    Currently Being Moderated
    Hi Joni,

    I've got the same problem as you had previously. What kind of configurations your have done in the file_group_acct_codes_ora.csv to make the tables W_AP_BALANCE_F and W_AR_BALANCE_F load with data? Many Many Many thanks!

    Best Regards,

    Eddy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points