Subledger Accounting (XLA) in R12 Techical and Functional

All accounting performed before transfer to the GL. Accounting data generated and stored in "Accounting Events" tables prior to transfer to GL

2) Run "Create Accounting" to populate accounting events (SLA) tables. User can "View Accounting" only after "Create Accounting" is run. Create Accounting process
– Applies accounting rules
– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA "distribution links" table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah
XLA_AE_LINES xal
XLA_EVENTS xae
XLA_TRANSACTION_ENTITIES xte
XLA_DISTRIBUTION_LINKS xdl
GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id
xah.entity_id = xte.entity_id
xae.entity_id = xte.entity_id
xah.event_id = xlae.event_id
xah.ae_header_id = xdl.ae_header_id
xal.ae_line_num = xdl.ae_line_num
xah.event_id = xdl.event_id
xal.gl_sl_link_id = gir.gl_sl_link_id
xal.gl_sl_link_table = gir.gl_sl_link_table
xah.application_id = xal.application_id
xal.application_id = xte.application_id
xte.application_id = xdl.application_id
xae.application_id = xte.application_id
xah.application_id = xae.application_id
xah.application_id = (Different value based on MODULE LIKE AR/AP/OM/PO/INV/WIP)

xte.entity_code = 'TRANSACTIONS' OR 'RECEIPTS' OR 'ADJUSTMENTS' OR 'PURCHASE_ORDER' OR 'AP_INVOICES' OR 'AP_PAYMENTS' OR 'MTL_ACCOUNTING_EVENTS' OR 'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 ='INVOICE_ID' OR 'CHECK_ID' OR 'CUSTOMER_TRX_ID' OR 'TRANSACTION_ID'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types
xdl.source_distribution_type = 'AP_PMT_DIST '
and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id
---------------
xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id
---------------
xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id
and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
---------------
xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id
---------------
xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id
---------------
xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id
---------------
xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Independent value set in r12

SELECT ffvs.flex_value_set_id,
       ffvs.flex_value_set_name,
       ffvs.description set_description,
       ffvs.validation_type,
       ffv.flex_value_id,
       ffv.flex_value,
       ffvt.flex_value_meaning,
       ffvt.description value_description
  FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv, fnd_flex_values_tl ffvt
 WHERE     ffvs.flex_value_set_id = ffv.flex_value_set_id
       AND ffv.flex_value_id = ffvt.flex_value_id
       AND ffvt.language = USERENV ('LANG');

Value Sets based on table

SELECT ffvs.flex_value_set_id,
       ffvs.flex_value_set_name,
       ffvs.description set_description,
       ffvs.validation_type,
       ffvt.value_column_name,
       ffvt.meaning_column_name,
       ffvt.id_column_name,
       ffvt.application_table_name,
       ffvt.additional_where_clause
  FROM fnd_flex_value_sets ffvs, fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;

Oracle Applications AOL Objects Table Names

FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users

Query to find Value Sets Details?

SELECT ffvs.flex_value_set_id,
  ffvs.flex_value_set_name,
  ffvs.description set_description ,
  fl.meaning validation_type,
  ffvt.value_column_name ,
  ffvt.meaning_column_name ,
  ffvt.id_column_name ,
  ffvt.application_table_name ,
  ffvt.additional_where_clause
FROM apps.fnd_flex_value_sets ffvs ,
  apps.fnd_flex_validation_tables ffvt,
  apps.fnd_lookups fl
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id(+)
AND ffvs.flex_value_set_name LIKE NVL('&VALUE_SET_NAME_CASE_SENSITIVE','%')
and fl.lookup_type = 'SEG_VAL_TYPES'
AND fl.lookup_code = ffvs.validation_type

How to get Values at All Levels for a Profile Option

SELECT fpot.user_profile_option_name  "Profile"
       ,fpov.profile_option_value     "Value"
       ,decode(fpov.level_id
             ,10001,'Site'
             ,10002,'Application'
             ,10003,'Responsibility'
             ,10004,'User'
             ,'UNKNOWN')              "Level"
       ,decode(fpov.level_id
             ,10002,fat.application_name
             ,10003,frt.responsibility_name
             ,10004,fu.user_name
             ,'n/a')                  "Level value"
FROM  applsys.fnd_application_tl         fat
      ,applsys.fnd_user                  fu
      ,applsys.fnd_responsibility_tl     frt
      ,applsys.fnd_profile_option_values fpov
      ,applsys.fnd_profile_options       fpo
      ,applsys.fnd_profile_options_tl    fpot
WHERE    UPPER(fpot.user_profile_option_name) LIKE '%&profile_name_case_sensitive%'
  AND    fpo.hierarchy_type = 'SECURITY'
  AND    fpot.profile_option_name   = fpo.profile_option_name
  AND    fpot.LANGUAGE              = 'US'
  AND    fpo.application_id         = fpov.application_id(+)
  AND    fpo.profile_option_id      = fpov.profile_option_id(+)
  AND    fpov.level_value           = frt.responsibility_id(+)
  AND    frt.LANGUAGE(+)            = 'US'
  AND    fpov.level_value           = fu.user_id(+)
  AND    fpov.level_value           = fat.application_id(+)
  AND    fat.LANGUAGE(+)            = 'US'
ORDER  BY "Profile"
          ,"Level"
          ,"Level value"
          ,"Value";

Query to find all responsibilities of a user ?

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",      
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  :p_user_name  
 ORDER BY frt.responsibility_name;
  

XLA TO FA Query

SELECT 'FA' module,
       transaction_type_code,
       asset_number,
       transaction_date_entered,
       xal.accounting_date gl_date,
       xal.description,
       (NVL (xal.accounted_dr, 0)) "DR_AMOUNT",
       (NVL (xal.accounted_cr, 0)) "CR_AMOUNT",
       gcc.segment4
  FROM xla_ae_lines xal,
       xla_ae_headers xah,
       fa_transaction_headers fth,
       xla_transaction_entities xte,
       gl_code_combinations gcc,
       fa_additions fa
 WHERE     xal.ae_header_id = xah.ae_header_id
       AND xah.event_id = fth.event_id
       AND xte.entity_id = xah.entity_id
       AND xte.entity_id = xah.entity_id
       AND gcc.code_combination_id = xal.code_combination_id
       AND gcc.segment4 = p_account
       AND fth.asset_id = fa.asset_id
        

How to get Query Order number for particular AR Invoice?

How to get Query Order number for particular AR Invoice?

SELECT ooh.order_number
              ,ool.line_number
              ,ool.ordered_item
              ,ool.ordered_quantity * ool.unit_selling_price
              ,rct.trx_number invoice_number
              ,rct.trx_date
              ,rcta.line_number
              ,rcta.unit_selling_price
              ,ooh.org_id
   FROM  oe_order_headers_all ooh
               ,oe_order_lines_all ool
               ,ra_customer_trx_all rct
               ,ra_customer_trx_lines_all rcta
   WHERE ooh.header_id=ool.header_Id
        AND  rcta.interface_line_attribute1=to_char(ooh.order_number)
        AND  rcta.interface_line_attribute6=to_char(ool.line_id)
        AND  rcta.customer_trx_id=rct.customer_trx_id
        AND  ooh.order_number=nvl(:p_order_number,ooh.order_number)
        AND  ooh.org_id=nvl(:p_org_id,ooh.org_id)