Generate the Auxiliary Report for Mexico

The Auxiliary Report for Mexico provides a detailed analysis of all accounting entries for a given account during a given period. The report displays beginning and ending balances, as well as the daily journal entries posted to an account during a specific period.

Use the Auxiliary Report for Mexico to provide detailed records of your accounts to the fiscal authorities.

Prerequisites

Complete these steps before you generate the Auxiliary Report for Mexico:

1. Sign in to Oracle Transactional Business Intelligence.

2. Click Catalog.

3. Click New > Folder on the catalog tool bar.

4. Create a local folder in My Folders.

5. Enter the Name of the folder as Mexico and click OK.

   The new folder appears in the folder pane in My Folders.

6. Select the Mexico folder and click the Expand icon.

7. Click the New icon that appears on the toolbar.

8. Click the Analysis option that appears in Analysis and Interactive Reporting.

9. Enter this SQL statement in the Create Analysis from Simple Logical SQL option:

   SELECT NumCta saw_0, DesCta saw_2, SaldoIni saw_4, SaldoFin saw_5, Fecha saw_6, NumUnIdenPol saw_7, Concepto saw_8, Debe saw_9, Haber saw_10, Accounting_Period saw_11, Ledger saw_12, Legal_Entity saw_13 FROM (SELECT
      "General Ledger - Transactional Balances Real Time"."Account"."General Ledger Code Combination Identifier" bal_ccid,
      "General Ledger - Transactional Balances Real Time"."Ledger"."Ledger Name" Ledger,
      "General Ledger - Transactional Balances Real Time"."Time"."Fiscal Period"  Accounting_Period,
      "General Ledger - Transactional Balances Real Time"."Natural Account Segment"."Account Code" NumCta,
      "General Ledger - Transactional Balances Real Time"."Natural Account Segment"."Account Description" DesCta,
      "General Ledger - Transactional Balances Real Time"."- Balance"."Beginning Balance" SaldoIni,
      "General Ledger - Transactional Balances Real Time"."- Balance"."Ending Balance" SaldoFin,
      "General Ledger - Transactional Balances Real Time"."- Balance"."Period Net Activity" bal_periodbal
   FROM "General Ledger - Transactional Balances Real Time" where "General Ledger - Transactional Balances Real Time"."Ledger"."Ledger Name"  in ('@{p_ledger}') and "General Ledger - Transactional Balances Real Time"."Time"."Fiscal Period" in ('@{p_period}')  ) balances full outer join (SELECT
      "General Ledger - Journals Real Time"."- Header Details"."Header Default Effective Date" Fecha,
      "General Ledger - Journals Real Time"."- Account"."General Ledger Code Combination Identifier" jrnl_ccid,
      CONCAT(REPLACE("General Ledger - Journals Real Time"."- Header Details"."Header Name",' ',''),CONCAT(REPLACE(CAST("General Ledger - Journals Real Time"."- Header Details"."Header Key" AS CHAR),' ',''),CONCAT(REPLACE("General Ledger - Journals Real Time"."- Batch Details"."Batch Name",' ',''),REPLACE("General Ledger - Journals Real Time"."- Header Details"."Header Description",' ','')))) jrnl_header_desc,
      CONCAT(SUBSTRING(REPLACE("General Ledger - Journals Real Time"."- Header Details"."Header Name",' ','') FROM 1 FOR (50 - CHAR_LENGTH(REPLACE(CAST("General Ledger - Journals Real Time"."- Header Details"."Header Key" AS CHAR),' ','')))),REPLACE(CAST("General Ledger - Journals Real Time"."- Header Details"."Header Key" AS CHAR),' ','')) NumUnIdenPol,
      "General Ledger - Journals Real Time"."- Ledger"."Ledger Name" jrnl_ledger,
      "General Ledger - Journals Real Time"."Time"."Fiscal Period"  jrnl_period,
      "General Ledger - Journals Real Time"."- Line Details"."Line Description" Concepto,
      "General Ledger - Journals Real Time"."- Line Details"."Line Number" jrnl_line_number,
      "General Ledger - Journals Real Time"."- Lines"."Journal Line Accounted Amount Debit" Debe,
      "General Ledger - Journals Real Time"."- Lines"."Journal Line Accounted Amount Credit" Haber,
      "General Ledger - Journals Real Time"."- Header Details"."Journal Legal Entity" Legal_Entity
   FROM "General Ledger - Journals Real Time" 
   where "General Ledger - Journals Real Time"."- Ledger"."Ledger Name" in ('@{p_ledger}') 
   and "General Ledger - Journals Real Time"."Time"."Fiscal Period" in ('@{p_period}') 
   and "General Ledger - Journals Real Time"."Posting Status"."Posting Status Code" = 'P' 
   and "General Ledger - Journals Real Time"."- Header Details"."Journal Legal Entity" in ('@{p_legal_entity}')) journals on journals.jrnl_ledger = balances.Ledger 
   and balances.bal_ccid = journals.jrnl_ccid 
   and journals.jrnl_period = balances.Accounting_Period
   and "General Ledger - Journals Real Time"."Balance Subtype"."Balance Subtype Name" = 'Actual'
   ORDER BY saw_0,saw_4, saw_5, saw_6, saw_9, saw_10, saw_11

10. Click OK.

11. Click the Prompts tab.

    Create two prompts for Ledger and Accounting Period.

12. Click the Add icon > Column Prompts > "Ledger"."Ledger".

    The New Prompt: Ledger dialog box appears.

13. Click Options on the New Prompt: Ledger dialog box.

14. Enter SQL Results in the Choice List Values field.

    A default query appears in the SQL statement box.

15. Replace the default query with the following query:

    SELECT    "General Ledger - Journals Real Time"."- Ledger"."Ledger Name" FROM "General Ledger - Journals Real Time" ORDER BY 1 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY

16. Enter Presentation Variable in the Set a variable field.

17. Enter p_ledger as the presentation variable.

18. Click OK.

19. Click Add > Column Prompt > "Accounting_Period" . "Accounting_Period".

    The New Prompt: Accounting_Period dialog box appears.

20. Click Options on the New Prompt: Accounting_Period dialog box.

21. Enter SQL Results in the Choice List Values field.

    A default query appears in the SQL statement field.

22. Replace the default query with the following query:

    SELECT    "General Ledger - Journals Real Time"."Time"."Fiscal Period" s_1, SORTKEY("General Ledger - Journals Real Time"."Time"."Fiscal Period") s_2 FROM "General Ledger - Journals Real Time" ORDER BY 1,2 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY

23. Enter Presentation Variable in the Set a variable field.

24. Enter p_period as the presentation variable.

25. Click OK.

26. Click Add > Column Prompt > "Legal_Entity" . "Legal_Entity".

    The New Prompt: Legal_Entity dialog box appears.

27. Click Options on the New Prompt: Legal_Entity dialog box.

28. Enter SQL Results in the Choice List Value box.

    A default query appears in the SQL statement box.

29. Remove the default query and enter the following query:

    SELECT    "General Ledger - Journals Real Time"."- Header Details"."Journal Legal Entity" FROM "General Ledger - Journals Real Time" ORDER BY 1 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY

30. Enter Presentation Variable in the Set a Variable box.

31. Enter p_legal_entity as the presentation variable.

32. Click OK.

Generating the Auxiliary Report for Mexico

After completing the prerequisites, perform these steps to run the report. Select your ledger and accounting period every time you run the report.

1. Sign in to Oracle Transactional Business Intelligence.

2. Click Catalog.

3. Navigate and select the Mexico folder in My Folders.

4. Click Open to open the Balance Journals Detail report.

5. Specify the ledger and accounting period in the Ledger and Accounting Period fields.

6. Click OK.

   The report appears in a tabular format.

   The report excludes encumbrance accounting journal entries.