Example : How to Generate Code Combination ID's (CCID) from Backend in Oracle R12

DECLARE
  l_code_combination_id       NUMBER;
  l_boolean                   BOOLEAN;
  l_segment_array             APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_structure_number          APPS.FND_ID_FLEX_STRUCTURES.id_flex_num%TYPE;
  l_id_flex_code              APPS.FND_ID_FLEX_STRUCTURES.id_flex_code%TYPE;
BEGIN
  APPS.FND_GLOBAL.APPS_INITIALIZE(USER_ID => FND_PROFILE.VALUE('USER_ID'),
                                  RESP_ID => FND_PROFILE.VALUE('RESP_ID'),
                                  RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'));
                                 
SELECT fifs.id_flex_num ,
       fifs.id_flex_code
  INTO l_structure_number ,
       l_id_flex_code
  FROM APPS.FND_ID_FLEX_STRUCTURES fifs
 WHERE fifs.id_flex_code         = 'GL#'
   AND fifs.id_flex_structure_code = 'XX_ACCOUNTING_FLEXFIELD';                                
 
  l_segment_array(1) := '00000';
  l_segment_array(2) := '10000';
  l_segment_array(3) := '10324';
  l_segment_array(4) := '00000';
  l_segment_array(5) := '00000';
  l_segment_array(6) := '00000';
 
  l_boolean          := APPS.FND_FLEX_EXT.GET_COMBINATION_ID( APPLICATION_SHORT_NAME =>'SQLGL',
                                                              KEY_FLEX_CODE => l_id_flex_code,
                                                              STRUCTURE_NUMBER => l_structure_number,
                                                              VALIDATION_DATE => SYSDATE,
                                                              N_SEGMENTS => 6,
                                                              SEGMENTS => l_segment_array,
                                                              COMBINATION_ID => l_code_combination_id,
                                                              DATA_SET => -1);
 
  x_ccid             := l_code_combination_id;
 
  IF l_code_combination_id IS NULL THEN
    RAISE;
  ELSE
    dbms_output.put_line(l_code_combination_id);
  END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More

Mandatory Column For GL_Interface

Column	Purpose
SEGMENT1 through SEGMENT30	Identifies the Accounting Flexfield segment values of the journal
CODE_COMBINATION_ID	Is used as an alternative to the SEGMENT values
ACTUAL_FLAG	A for actual amounts
REFERENCE1	Used to create a journal batch name of the format REFERENCE1Request IDActual FlagGroup ID
REFERENCE4	Used to format the journal-entry name
ACCOUNTED_DR	A debit amount
ACCOUNTED_CR	A credit amount
ACCOUNTING_DATE	Determines in which accounting period the journal appears
STATUS	The value NEW
LEDGER_ID	Determines in which ledger the journal appears
USER_JE_SOURCE_NAME	Determines which source the journal has
USER_JE_CATEGORY_NAME	Determines which category the journal has
CURRENCY_CODE	Determines which currency the journal has

Read More

Accounting FlexFields of Bank Account in R12

alter session set current_schema=apps;

SELECT cba.Bank_Account_Name,
  cbbv.Bank_Name,
  cbbv.Bank_Branch_Name,
  cba.Bank_Account_Num,
  gcc_asset.segment1||'.'||gcc_asset.segment2||'.'||gcc_asset.segment3||'.'||gcc_asset.segment4||'.'||gcc_asset.segment5||'.'||gcc_asset.segment6||'.'||gcc_asset.segment7||'.'||gcc_asset.segment8||'.'||gcc_asset.segment9 CASH_ACCOUNT,
  gcc_gain.segment1||'.'||gcc_gain.segment2||'.'||gcc_gain.segment3||'.'||gcc_gain.segment4||'.'||gcc_gain.segment5||'.'||gcc_gain.segment6||'.'||gcc_gain.segment7||'.'||gcc_gain.segment8||'.'||gcc_gain.segment9 GAIN_ACCOUNT,
  gcc_loss.segment1||'.'||gcc_loss.segment2||'.'||gcc_loss.segment3||'.'||gcc_loss.segment4||'.'||gcc_loss.segment5||'.'||gcc_loss.segment6||'.'||gcc_loss.segment7||'.'||gcc_loss.segment8||'.'||gcc_loss.segment9 LOSS_ACCOUNT,
  gcc_cashclear.segment1||'.'||gcc_cashclear.segment2||'.'||gcc_cashclear.segment3||'.'||gcc_cashclear.segment4||'.'||gcc_cashclear.segment5||'.'||gcc_cashclear.segment6||'.'||gcc_cashclear.segment7||'.'||gcc_cashclear.segment8||'.'||gcc_cashclear.segment9 CASH_CLEARING_ACCOUNT,
  gcc_bank.segment1||'.'||gcc_bank.segment2||'.'||gcc_bank.segment3||'.'||gcc_bank.segment4||'.'||gcc_bank.segment5||'.'||gcc_bank.segment6||'.'||gcc_bank.segment7||'.'||gcc_bank.segment8||'.'||gcc_bank.segment9 BANK_CHARGES_ACCOUNT
FROM ce_bank_accounts cba,
ce_bank_branches_v cbbv,
gl_code_combinations gcc_gain,
gl_code_combinations gcc_loss,
gl_code_combinations gcc_cashclear,
gl_code_combinations gcc_bank,
gl_code_combinations gcc_asset
where cba.bank_branch_id=cbbv.branch_party_id
and cba.bank_id=cbbv.bank_party_id
and cba.Gain_code_combination_id=gcc_gain.code_combination_id(+)
and cba.loss_code_combination_id=gcc_loss.code_combination_id(+)
and cba.cash_clearing_ccid=gcc_cashclear.code_combination_id(+)
and cba.bank_charges_ccid=gcc_bank.code_combination_id(+)
and cba.asset_code_combination_id=gcc_asset.code_combination_id(+);

Read More

AR Receipts to Transaction Drill Down

 select acra.receipt_number,
           acra.amount,
           rcta.trx_number,
           acra.receipt_date,
           apsa.amount_applied
     from ar_cash_receipts_all acra,
          ar_receivable_applications_all araa,
          ra_customer_trx_all rcta,
          ar_payment_schedules_all apsa
    where acra.receipt_number = 'BACS170211MORETON'
      and  apsa.customer_trx_id=rcta.customer_trx_id
      and araa.cash_receipt_id = acra.cash_Receipt_id
      and rcta.customer_trx_id=araa.applied_customer_trx_id;

Read More

Join GL tables with XLA (SubLedger Accounting) tables

Foreign Keys

GL_JE_BATCHES (je_batch_id)                              => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                            => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)             => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)                    => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)


The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.
The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

Read More

Drill Down from AR Cash Receipts to GL Ledger R12

Query:

select distinct t.* from
(select gl.name Ledger_Name,
       (gcc.segment1||'-'||
       gcc.segment2||'-'||
       gcc.segment3||'-'||
       gcc.segment4||'-'||
       gcc.segment5||'-'||
       gcc.segment6)Account,
       araa.gl_date Gl_Date,
       xal.accounting_class_code Class,
       xal.accounted_dr,
       xal.accounted_cr
from GL_LEDGERS gl,
     XLA_DISTRIBUTION_LINKS  xdl,
     XLA_AE_LINES            xal,
     gl_code_combinations    gcc,
     ar_cash_receipts_all    acra,
     ar_payment_schedules_all apsa,
     AR_RECEIVABLE_APPLICATIONS_ALL araa,
     AR_DISTRIBUTIONS_ALL           ada
where xal.ledger_id = gl.ledger_id
and      acra.cash_receipt_id = apsa.cash_receipt_id
and      apsa.payment_schedule_id = araa.payment_schedule_id
and      araa.payment_schedule_id = apsa.payment_schedule_id
and      ada.source_id = araa.receivable_application_id -----------
--and      ada.source_type = 'CASH'
--and      ada.code_combination_id = gcc.code_combination_id
and      ada.third_party_id = acra.pay_from_customer
and      ada.third_party_sub_id = acra.customer_site_use_id
and      xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =  ada.line_id
and      ada.source_table = 'RA'
and      xdl.event_id = araa.event_id
--and      xdl.event_class_code like 'RECEIPT' ---------
--and      xdl.event_type_code like 'RECEIPT_ALL'------------
and      xdl.source_distribution_type like 'AR_DISTRIBUTIONS_ALL'
--and      xdl.rounding_class_code = 'RECEIVABLE'
--and      xdl.application_id = 222
and      xal.ae_header_id = xdl.ae_header_id
and      xal.code_combination_id = gcc.code_combination_id
and      acra.receipt_number = &Recipt_Number ) t


select * from ar_distributions_all;

select * from XLA_DISTRIBUTION_LINKS where source_distribution_type LIKE 'AR_DISTRIBUTIONS_ALL';

Read More