Monday, July 28, 2014

Subledger Accounting Query to download all SLA Setups - for your BR-100 documentation

Here is a SQL that can be used to download all the information on the Subledger Accounting Setups from Application Accounting Definition to Account Derivation Rule




-- This SQL will pull all the details of Application Accounting Definition all the way from AAD name to ADR (Account Derivation Rule).
-- Two of the Oracle Forms are captured in this query


SELECT distinct A.PRODUCT_RULE_CODE     AAD,
                A.EVENT_CLASS_CODE      Event_Class,
                A.EVENT_TYPE_CODE       Event_Type,
                A.HEADER_DESC_NAME,
                B.line_definition_code  JLD_Code,
                B.Line_definition_name  JLD_Name,
                B.line_definition_desc  JLD_Desc,
                A.HEADER_DESC_TYPE_DSP,
                T.line_definition_code  JLD_CODE,
                T.accounting_line_name  Journal_Line_Type,
                T.description_name      Journal_Line_Description,
                T.accounting_line_code,
                N.segment_rule_name     ADR_NAME,
                N.segment_rule_code     ADR_CODE,
                N.segment_rule_owner,
                N.segment_rule_desc     ADR_DESCRIPTION,
                N.flexfield_segment_num
  FROM apps.xla_prod_header_desc_fvl     A,
              apps.xla_aad_line_defn_assgns_f_v B,
              apps.xla_line_defn_jlt_assgns_f_v T, -- To get all the details of Journal Line Type
              apps.xla_line_defn_adr_assgns_f_v N -- To get all the details of ADR
 WHERE (A.APPLICATION_ID = 140) -- Application_id from FND_APPLICATION.
   and (A.AMB_CONTEXT_CODE = 'DEFAULT')
   and (A.PRODUCT_RULE_TYPE_CODE = 'C')
  -- and (A.PRODUCT_RULE_CODE LIKE 'XXFA_ASSET_AAD')-- If you want Product rule code --specific, write the name here
  -- and (A.ENTITY_CODE = 'TRANSACTIONS') -- IF you want to use the query for just a --particular entity,
   and A.product_rule_code = B.product_rule_code
   and A.application_id = B.application_id
   and A.amb_context_code = B.amb_context_code
   and A.event_class_code = B.event_class_code
   and A.event_type_code = B.event_type_code
   and T.application_id = 140 -- Application_id from FND_APPLICATION.
   and T.application_id = B.application_id
   and T.event_class_code = A.event_class_code
   and B.line_definition_code = T.line_definition_code
   and B.event_class_code = T.event_class_code
   and B.line_definition_code = T.line_definition_code
   and B.event_type_code = T.event_type_code
   and T.event_Class_code = N.event_Class_code
   and T.line_definition_code = N.line_definition_code
   and T.application_id = N.application_id
   and T.line_definition_code = N.line_definition_code
   and T.event_type_code = N.event_type_code
   and N.accounting_line_code = T.accounting_line_code
 order by 2, 3, 5, 10, 11, 16;