6 Replies Latest reply: May 2, 2013 4:44 AM by User538247 RSS

    Import GL DFF in OBIA Finacials


      I want to import all 20 DFF from gl_je_lines into OBIA 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?

        • 1. Re: Import GL DFF in OBIA Finacials
          Veeresh Rayan
          Check this..

          Introducing New Flex Fields

          Mark correct or helpful if it helps..

          Veeresh Rayan
          • 2. Re: Import GL DFF in OBIA Finacials
            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?

            • 3. Re: Import GL DFF in OBIA Finacials
              Veeresh Rayan

              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.,

              Veeresh Rayan
              • 4. Re: Import GL DFF in OBIA Finacials

                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,
                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 =
                AND fnddfc.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 =
                AND b.application_column_name = t1.application_column_name
                AND gljl.CONTEXT = t.descriptive_flex_context_name
                AND fnddfc.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.

                • 5. Re: Import GL DFF in OBIA Finacials
                  Veeresh Rayan
                  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,

                  Veeresh Rayan
                  • 6. Re: Import GL DFF in OBIA Finacials
                    Hi Veeresh,

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