This discussion is archived
6 Replies Latest reply: May 2, 2013 2:44 AM by User538247 RSS

Import GL DFF in OBIA Finacials

User538247 Newbie
Currently Being Moderated
Hi,

I want to import all 20 DFF from gl_je_lines into OBIA 7.9.6.3. Is there is any way we can import all 20 DFF in OBIA with 1 custom mapping OR pls let me know if this can be achieved by modifying OOTB mappings & How?

Thanks,
  • 1. Re: Import GL DFF in OBIA Finacials
    Veeresh Rayan Journeyer
    Currently Being Moderated
    Check this..

    Introducing New Flex Fields

    Mark correct or helpful if it helps..

    Regards,
    Veeresh Rayan
  • 2. Re: Import GL DFF in OBIA Finacials
    User538247 Newbie
    Currently Being Moderated
    Hi Veeresh,

    I know how to customize OBIA mappings. I want to import all 20 dff from gl_je_lines into warehouse.There are two possible approaches.

    1. Modify OOTB mappings. How to add & decode attribute column values in OOTB mappings.Do we have to add these values in W_CODE_D?
    If someone did it then pls explain you did it. Which mappings needs to be modified.

    2. Create custom mappings to directly populate decoded Attribute values in DW tables. Can you pls provide Source Qualifier for this kind of mappings?

    Which approach is more suitable?

    Thanks,
  • 3. Re: Import GL DFF in OBIA Finacials
    Veeresh Rayan Journeyer
    Currently Being Moderated
    Hi,

    I have done DFF customization 3-4 times. When you are bring attribute columns you need not have to change the mappings for loading W_CODE_D table.

    DFF's are just extra informations and they can be brought in simply extending dimension tables/fact tables.

    I would suggest you to modify OOTB mappings rather creating your own design in loading and integrating with the complex BI APPS module.


    As you already know how to customize the mappings it would re right if you following your 1st approach.

    Mark helpful or correctif it helps.,

    regards,
    Veeresh Rayan
  • 4. Re: Import GL DFF in OBIA Finacials
    User538247 Newbie
    Currently Being Moderated
    Hi,

    In current EBS envirment, DFF attributes columns are not storing actual values.They are storing some ids from LOV. To decode those values i need to hit fnd tables in ebs. below sql i received from EBS team to get description for attribute 1.

    SELECT DISTINCT gljl.attribute1, fndfvl.flex_value_meaning,
    fndfvl.description
    FROM gl_je_lines gljl,
    fnd_descr_flex_contexts_tl t,
    fnd_descr_flex_contexts fnddfc,
    fnd_descr_flex_col_usage_tl t1,
    fnd_descr_flex_column_usages b,
    fnd_flex_value_sets fndfv,
    fnd_flex_values_tl fndfvl,
    fnd_flex_values b1
    WHERE fnddfc.application_id = t.application_id
    AND fnddfc.descriptive_flexfield_name =
    t.descriptive_flexfield_name
    AND fnddfc.descriptive_flex_context_code =
    t.descriptive_flex_context_code
    AND b.application_id = t1.application_id
    AND b.descriptive_flexfield_name = t1.descriptive_flexfield_name
    AND b.descriptive_flex_context_code =
    t1.descriptive_flex_context_code
    AND b.application_column_name = t1.application_column_name
    AND gljl.CONTEXT = t.descriptive_flex_context_name
    AND fnddfc.descriptive_flexfield_name =
    b.descriptive_flexfield_name
    AND b.flex_value_set_id = fndfv.flex_value_set_id
    AND fndfv.flex_value_set_id = b1.flex_value_set_id
    AND gljl.attribute1 = b1.flex_value
    AND b1.flex_value_id = fndfvl.flex_value_id
    AND gljl.CONTEXT = 'Employee Information'
    ORDER BY gljl.attribute1

    My question is do we have to use similar SQL for each attribute column. If we do so this will be huge mapping & might give performance issue.

    Pls let me know your thoughts on this. Any simple approach is there to do this. If possible let me what approach you followed.

    Thanks,
  • 5. Re: Import GL DFF in OBIA Finacials
    Veeresh Rayan Journeyer
    Currently Being Moderated
    Check if this make sense.

    1)In the SQL which you provided ,get all the attribute ID's and corresponidng meaning by putting IN statement like ('Employee Information',org Information).

    2)Create one table where in you can load the details which u get in the first step.

    3)Use this table as lookup(Use lookup Transformation) whenever you are looking for the value. Like for example I am getting 100 as ID from the GL_JE_LINES and for this description is Emp ID.So when you pass 100 as ID your lookup should give you as EMP ID as description.Have this Lookup transformation between after your source qualifier and before your target(may be stage in your instance).When it come to target from stage you just pass it on to target using simple source qualifier(or existing source qualifier).

    I hope this is most simplest and easy way to implement your solutiion.

    Let me know if it helps you..

    mark correct or helpful if it helps,

    Regards,
    Veeresh Rayan
  • 6. Re: Import GL DFF in OBIA Finacials
    User538247 Newbie
    Currently Being Moderated
    Hi Veeresh,

    Looks a good solution ..i wl try & let u know.

    Thanks,
    Paresh

Legend

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