1. Using Analytics and Reports for Financials
  2. Oracle Fusion Financials for Latin America Reports

17Oracle Fusion Financials for Latin America Reports

This chapter contains the following:

Oracle Fusion Financials for America Predefined Reports

Oracle Fusion Financials for America provides predefined reports that are used to meet reporting requirements for specific countries and for reconciliation and audit of tax calculated on transactions.

You can schedule and run reports from the Scheduled Processes work area found on the Navigator. To run predefined reports:

  1. Click the Schedule New Process button.

  2. Search on the Process Name.

  3. Enter the appropriate parameters.

  4. Enter the appropriate process options and schedule.

  5. Click Submit.

Oracle Fusion Financials for America provides predefined reports for:

  • Mexico

Reports for Mexico

The tax reports for Mexico are:

Display Name Description

Chart of Accounts for Mexico Report

Companies in Mexico must submit accounting information to the tax authorities. According to the new government regulations, companies must upload accounting information to the Tax Administration Services (SAT) internet portal through a registered email account.

Journals for Mexico Report

Companies in Mexico must submit accounting information to the tax authorities. According to the new government regulations, companies must upload accounting information to the Tax Administration Services (SAT) internet portal through a registered email account.

Auxiliary Report for Mexico

The Auxiliary Report for Mexico helps with an in-depth analysis of all accounting entries for a given account in a given period. The report displays beginning and ending balance, as well as the daily journals entries posted to that account during the period.

Trial Balance Report for Mexico

The trial balance report is an XML file that includes a summary of the initial balances, the sum of total transactions for the period, and the final balances for each account that you report in the Chart of Accounts XML file. The accounts related to assets, liabilities, equity, and results of operations (revenues, costs and expenses) are all included.

Reports for Mexico

Auxiliary Report for Mexico

This topic contains summary information about the Auxiliary Report for Mexico.

Overview

The Auxiliary Report for Mexico helps with an in-depth analysis of all accounting entries for a given account in a given period. The report displays beginning and ending balance, as well as the daily journals entries posted to that account during the period.

Key Insights

To run the report, you must first grant access to the user for the Journals for Mexico process so that you can run it from the Scheduled Processes page. For more information, see Enable the Mexican Reporting Processes.

After generating the report, convert the report to an XML format so that it's ready to upload to the Tax Administration Services (SAT) website.

Frequently Asked Questions

The following table lists frequently asked questions about the Auxiliary Report for Mexico.

FAQs Answer

How do I find this report?

In Oracle Business Intelligence Enterprise Edition, select Analysis from Analysis and Interactive Reporting.

Who uses this report?

  • Financial Accountants

  • Financial Specialists

When do I use this report?

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

What type of report is this?

Oracle Transaction Business Intelligence

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 the following steps before you generate the Auxiliary Report for Mexico:

  1. Sign in to Oracle Business Intelligence Enterprise Edition.

  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 the following 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 
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 the following steps to run the report. Select your ledger and accounting period every time you run the report.

  1. Sign in to Oracle Business Intelligence Enterprise Edition.

  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.

Convert the Auxiliary Report for Mexico to an XML Format

To upload the Auxiliary Report for Mexico to the Tax Administration Services (SAT) website, you must first convert the report into an XML format.

To convert the report, perform these steps:

  1. Run the Auxiliary Report Mexico report in Oracle Business Intelligence Publisher

    Export the report to Microsoft Excel.

  2. Click the Export option, select Data, and then select CSV Format.

  3. On the Opening Balance Journals Details with Prompts dialog box, select the Open with option and click OK.

    The report appears in Microsoft Excel.

  4. Click the View tab.

  5. Click Macros > Record Macros.

    The Record Macros dialog box appears.

  6. Click OK.

  7. Click the View tab.

  8. Click Macros > View Macros.

  9. Click Edit on the Macros dialog box.

    Microsoft Visual Basic editor appears.

  10. Replace the existing code with the following code: 

    Sub MakeXML()
' create an XML file from an Excel table
Dim MyRow As Integer, MyCol As Integer, MyCol2 As Integer, Temp As String, YesNo As Variant, DefFolder As String
Dim XMLFileName As String, XMLRecBuffer As String, XMLRec2Buffer As String, MyLF As String, RTC1 As Integer
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String, CheckFlag As Integer, Arr
Dim Version As String, RFC As String, Mes As String, Anio As String, TipoSolicitud As String, NumOrden As String
Dim NumTramite As String, Sello As String, noCertificado As String, Certificado As String, lastRow As Long
Dim XML As String, MidStr As String, Row As String, XML3 As String, XML4 As String, Pos As String, Pos2 As String

MyLF = Chr(10) & Chr(13)    ' a line feed command

YesNo = MsgBox("This procedure requires the following data:" & MyLF _
 & "1 A filename for the XML file" & MyLF _
 & "2 Version Value" & MyLF _
 & "3 RFC Value" & MyLF _
 & "4 Mes Value" & MyLF _
 & "5 Anio Value" & MyLF _
 & "6 TipoSolicitud Value" & MyLF _
 & "7 NumOrden Value" & MyLF _
 & "8 NumTramite Value" & MyLF _
 & "9 Sello Value" & MyLF _
 & "10 noCertificado Value" & MyLF _
 & "11 Certificado Value" & MyLF _
 & "Are you ready to proceed?", vbQuestion + vbYesNo, "MakeXML CiM")
 
If YesNo = vbNo Then
 Debug.Print "User aborted with 'No'"
 Exit Sub
End If

XMLFileName = FillSpaces(InputBox("1. Enter the name of the XML file:", "MakeXML CiM", "xl_xml_data"))
If Right(XMLFileName, 4) <> ".xml" Then
 XMLFileName = XMLFileName & ".xml"
End If

If Len(Dir("C:\Mexico", vbDirectory)) = 0 Then
   MkDir "C:\Mexico"
End If

If Len(Dir("C:\Mexico\Auxiliary", vbDirectory)) = 0 Then
   MkDir "C:\Mexico\Auxiliary"
End If

DefFolder = ActiveWorkbook.Path
'"c:\Mexico\Auxiliary"
'InputBox("2. Enter the location of the path for the output file", "MakeXML CiM", "Enter the Path")
RangeOne = "A1:K1"
If MyRng(RangeOne, 1) <> MyRng(RangeOne, 2) Then
  MsgBox "Error: names must be on a single row" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
MyRow = MyRng(RangeOne, 1)
For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)
 If Len(Cells(MyRow, MyCol).Value) = 0 Then
  MsgBox "Error: names range contains blank cell" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
 End If
 FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol

'RangeTwo = InputBox("3. Enter the range of cells containing the data table:", "MakeXML CiM", "A2:I2")

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

RangeTwo = "A2:K" & lastRow
If MyRng(RangeOne, 4) - MyRng(RangeOne, 3) <> MyRng(RangeTwo, 4) - MyRng(RangeTwo, 3) Then
  MsgBox "Error: number of field names <> data columns" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
RTC1 = MyRng(RangeTwo, 3)

Version = InputBox("2. Enter the Version Value:", "MakeXML CiM", "1.3")
Version = "Version=" & Chr(34) & Version & Chr(34)

RFC = InputBox("3. Enter the RFC Value:", "MakeXML CiM", "RFC Value")
RFC = "RFC=" & Chr(34) & RFC & Chr(34)

Mes = InputBox("4. Enter the Mes Value:", "MakeXML CiM", "Mes Value")
Mes = "Mes=" & Chr(34) & Mes & Chr(34)

Anio = InputBox("5. Enter the Anio Value:", "MakeXML CiM", "Anio Value")
Anio = "Anio=" & Chr(34) & Anio & Chr(34)

TipoSolicitud = InputBox("6. Enter the TipoSolicitud Value:", "MakeXML CiM", "TipoSolicitud Value")
TipoSolicitud = "TipoSolicitud=" & Chr(34) & TipoSolicitud & Chr(34)

NumOrden = InputBox("7. Enter the NumOrden Value:", "MakeXML CiM", "NumOrden Value")
NumOrden = "NumOrden=" & Chr(34) & NumOrden & Chr(34)

NumTramite = InputBox("8. Enter the NumTramite Value:", "MakeXML CiM", "NumTramite Value")
NumTramite = "NumTramite=" & Chr(34) & NumTramite & Chr(34)

Sello = InputBox("9. Enter the Sello Value:", "MakeXML CiM", "Sello Value")
Sello = "Sello=" & Chr(34) & Sello & Chr(34)

noCertificado = InputBox("10. Enter the Certificado Number Value:", "MakeXML CiM", "noCertificado Value")
noCertificado = "noCertificado=" & Chr(34) & noCertificado & Chr(34)

Certificado = InputBox("11. Enter the Certificado Value:", "MakeXML CiM", "Certificado Value")
Certificado = "Certificado=" & Chr(34) & Certificado & Chr(34)

Arr = Array(Version, RFC, Mes, Anio, TipoSolicitud, NumOrden, NumTramite, Sello, noCertificado, Certificado)

If InStr(1, XMLFileName, ":\") = 0 Then
 XMLFileName = DefFolder & "\" & XMLFileName
End If

XMLRecBuffer = "    <AuxiliarCtas:Cuenta "
CheckFlag = 0

'XML3 = "<AuxiliarCtas  xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas/AuxiliarCtas_1_1.xsd" & Chr(34) & " xmlns=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas" & Chr(34) & " Version=" & Chr(34) & Version & Chr(34) & " RFC=" & Chr(34) & RFC & Chr(34) & " Mes=" & Chr(34) & Mes & Chr(34) & " Anio=" & Chr(34) & Anio & Chr(34) & " TipoSolicitud=" & Chr(34) & TipoSolicitud & Chr(34) & " NumOrden=" & Chr(34) & NumOrden & Chr(34) & " NumTramite=" & Chr(34) & NumTramite & Chr(34) & " Sello=" & Chr(34) & Sello & Chr(34) & " noCertificado=" & Chr(34) & noCertificado & Chr(34) & " Certificado=" & Chr(34) & Certificado & Chr(34) & ">"
XML3 = "<AuxiliarCtas:AuxiliarCtas xmlns:AuxiliarCtas=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas" & Chr(34) & " xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas/AuxiliarCtas_1_1.xsd" & Chr(34)
XML4 = XML3

    Row = ""

Open XMLFileName For Output As #1
    
    For I = 0 To 9
        Pos = InStr(1, Arr(I), Chr(34))
        Pos2 = InStrRev(Arr(I), Chr(34), -1)
        Pos2 = Pos2 - Pos
        
        MidStr = Mid(Arr(I), (Pos + 1), (Pos2 - 1))
				'MsgBox MidStr
        If MidStr <> "" Then
            Row = Row & " " & Arr(I)
        End If
        
    Next I
    
    XML4 = XML4 & Row
    
    XML3 = XML4 & ">"

'Open XMLFileName For Output As #1
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
Print #1, XML3

For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)
  For MyCol = RTC1 To MyRng(RangeTwo, 4)
  If MyCol = 7 And CheckFlag = 0 Then
		'MsgBox XMLRecBuffer
    Print #1, XMLRecBuffer & ">"
    CheckFlag = 1
  End If
  If MyCol = 7 Then
    XMLRec2Buffer = "        <AuxiliarCtas:DetalleAux "
    For MyCol2 = MyCol To MyRng(RangeTwo, 4)
        If RemoveAmpersands(FormChk(MyRow, MyCol2)) <> "" Then
            XMLRec2Buffer = XMLRec2Buffer & " " & FldName(MyCol2 - 1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol2)) & Chr(34)
        ElseIf FldName(MyCol2 - 1) = "Debe" Or FldName(MyCol2 - 1) = "Haber" And RemoveAmpersands(FormChk(MyRow, MyCol2)) = "" Then
                XMLRec2Buffer = XMLRec2Buffer & " " & FldName(MyCol2 - 1) & "=" & Chr(34) & "0" & Chr(34)
        End If
  ' the next line uses the FormChk function to format dates and numbers
  ' the next line does not apply any formatting
  '  Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">"
        Next MyCol2
    Print #1, XMLRec2Buffer & "/>"
    End If
  ' the next line uses the FormChk function to format dates and numbers
  ' the next line does not apply any formatting
  '  Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">"
    If CheckFlag = 0 Then
        If RemoveAmpersands(FormChk(MyRow, MyCol)) <> "" Then
            If FldName(MyCol - RTC1) = "Debe" Or FldName(MyCol - RTC1) = "Haber" Then
                XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & "0" & Chr(34)
            ElseIf FldName(MyCol - RTC1) = "NumCta" Then
                If RemoveAmpersands(FormChk(MyRow, MyCol + 1)) <> "" Then
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & "-" & RemoveAmpersands(FormChk(MyRow, MyCol + 1)) & Chr(34)
                Else
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
                End If
            ElseIf FldName(MyCol - RTC1) = "DesCta" Then
                If RemoveAmpersands(FormChk(MyRow, MyCol + 1)) <> "" Then
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & "-" & RemoveAmpersands(FormChk(MyRow, MyCol + 1)) & Chr(34)
                Else
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
                End If
            ElseIf FldName(MyCol - RTC1) = "SubCta" Or FldName(MyCol - RTC1) = "SubDesCta" Then
                XML3 = "SubCta"
            Else
                XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
								'MsgBox XMLRecBuffer
            End If
        End If
    End If
    Next MyCol
    If FormChk(MyRow, 1) <> FormChk(MyRow + 1, 1) Then
        Print #1, "    </AuxiliarCtas:Cuenta>"
        XMLRecBuffer = "    <AuxiliarCtas:Cuenta "
        CheckFlag = 0
    End If
Next MyRow

Print #1, "</AuxiliarCtas:AuxiliarCtas>"
Close #1
MsgBox XMLFileName & " created." & MyLF & "Process finished", vbOKOnly + vbInformation, "MakeXML CiM"
Debug.Print XMLFileName & " saved"

End Sub
Function MyRng(MyRangeAsText As String, MyItem As Integer) As Integer
' analyse a range, where MyItem represents 1=TR, 2=BR, 3=LHC, 4=RHC

Dim UserRange As Range
Set UserRange = Range(MyRangeAsText)
Select Case MyItem
 Case 1
 MyRng = UserRange.Row
 Case 2
 MyRng = UserRange.Row + UserRange.Rows.Count - 1
 Case 3
 MyRng = UserRange.Column
 Case 4
 MyRng = UserRange.Columns(UserRange.Columns.Count).Column
End Select
Exit Function

End Function
Function FillSpaces(AnyStr As String) As String
' remove any spaces and replace with underscore character
Dim MyPos As Integer
MyPos = InStr(1, AnyStr, " ")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "_"
 MyPos = InStr(1, AnyStr, " ")
Loop
FillSpaces = AnyStr
End Function

Function FormChk(RowNum As Integer, ColNum As Integer) As String
' formats numeric and date cell values to comma 000's and DD MMM YY
FormChk = Cells(RowNum, ColNum).Value
'If IsNumeric(Cells(RowNum, ColNum).Value) Then
' FormChk = Format(Cells(RowNum, ColNum).Value, "#,##0 ;(#,##0)")
'End If
If IsDate(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "yyyy-mm-dd")
End If
End Function

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Integer
' replace Ampersands (&) with plus symbols (+)

MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "+"
 MyPos = InStr(1, AnyStr, "&")
Loop
RemoveAmpersands = AnyStr
End Function

  11. Run the macro.

    The MakeXML CiM dialog box appears containing instructions to provide several details in a series of dialog boxes.

  12. Click Yes and proceed with information entry on each dialog box as they appear one after the other. Enter the following information:

    • Name of the output file

    • Location to save the file

    • Version of the file

    • RFC

    • Mes

    • Anio

    • TipoSolicitud

    • NumOrden

    • NumTramite

    • Seal

    • Certificate number

    • Certificate details

  13. Verify the location where the output files will be saved and click OK.

    Open the XML report that's saved at location you specified.

Chart of Accounts for Mexico Report

This topic includes details about the Chart of Accounts for Mexico report.

Overview

Companies in Mexico must submit accounting information to the tax authorities. According to the new government regulations, companies must upload accounting information to the Tax Administration Services (SAT) internet portal through a registered email account.

Note: You can use your taxpayer identification registry (RFC) to register your email on the SAT portal.

Fiscal authorities in Mexico publish the official chart of accounts that all companies must use to report accounting information. This referential chart of accounts assigns a code to each account, and a level to identify parent/child relationships for reporting purposes. You must set up these codes in Oracle Fusion Applications.

The following figure is an example of the report:

This graphic is a screenshot of a Chart of Accounts report.

Key Insights

You must map accounts from your company's chart of accounts to the SAT grouping codes in the Chart of Accounts Mapping page. Only the accounts that are mapped to the SAT grouping codes are included in the report.

You can define the company's natural account segment as a single level or a hierarchy. If you implement an account hierarchy, a child account can have a single parent account (recommended) or multiple parent accounts. When a child account has multiple parent accounts, only the immediate parent appears on the report. You can map more than one account to a given SAT grouping code.

Before you run the report, ensure the natural accounts appear at the correct level in the account hierarchy. Perform the following steps to update the level of natural account according to the account hierarchy:

  1. In the Setup and Maintenance work area, select these options:

    • Offering: Financials

    • Functional area: Financial Reporting Structure

    • Show: All Tasks

    • Task: Manage Account Hierarchies

  2. Select the account.

  3. From the Actions menu, select Flatten and click Row Flattening.

  4. Click Force Flatten.

  5. From the Actions menu, select Flatten and click Column Flattening.

  6. Click Force Flatten.

Note: To run the Chart of Accounts for Mexico process from the Scheduled Processes page, you must give access to the user you use to manage the electronic accounting reports for Mexico. For more information, see the Enable the Mexican Reporting Processes topic.

Report Parameters

The following table describes selected process parameters.

Parameter Description

Seal

Enter the digital electronic seal information provided by the fiscal partner.

Certificate Number

Enter the certificate number information provided by the fiscal partner.

Certificate

Enter certificate information provided by the fiscal partner.

Format Version

Enter the format version of the file defined by the Government of Mexico. It can change over time, such as 1.1, 1.2, and 1.3. Currently it defaults to 1.3.

Chart of Accounts Mapping

Enter the chart of accounts mapping created for Mexico.

Legal Entity

Enter the legal entity, which determines the RFC number to be stamped on the report.

Frequently Asked Questions

The following table lists frequently asked questions about the Chart of Accounts for Mexico report.

FAQ Answer

How do I find this report?

Schedule and run this report from the Scheduled Processes work area on the Navigator menu.

Who uses this report?

Financial accountant

When do I use this report?

Use this report to provide detailed chart of accounts information to the tax authorities in Mexico.

What type of report is this?

Oracle Business Intelligence Publisher

Enable the Mexican Reporting Processes

To run the Journals for Mexico and Chart of Accounts for Mexico reports, you must enable the processes once to run them from the Scheduled Processes page in Oracle Fusion Applications.

Enable the Process

To enable the processes, perform these steps:

  1. Sign in as a IT_SECURITY_MANAGER user.

  2. Select Tools > Security Console.

  3. Click Create Role on the Roles page.

  4. Enter the new role name with Role Code as Mexico_Reports_Custom.

  5. Click Next to add the required privileges.

  6. Add the required roles:

    1. Click Add Function Security Policy.

    2. Search for a required role.

    3. From the search result, select and add the privilege roles and not the OBI roles.

    4. Click Add Privileges to Role.

  7. Similarly, add all the required privileges.

  8. Verify that all the privileges were added successfully.

  9. Click Next twice to go to the role hierarchy definition page.

  10. Add the roles containing your Mexican Jobs:

    • JL_GENERATE_CHART_OF_ACCOUNTS_FOR_MEXICO_ELECTRONIC_ACCOUNTING_PRIV_OBI

    • JL_GENERATE_JOURNAL_ENTRIES_FOR_MEXICO_ELECTRONIC_ACCOUNTING_PRIV_OBI

  11. Verify that all the roles have been added

  12. Click Next to add the job role to the required users, such as the Mexican Manager.

  13. Once all users are added, click Next and then click Save and Close to complete the job role definition.

  14. Sign in as an Application_Implementation_Consultant user and navigate to the Setup and Maintenance page.

  15. In the Setup and Maintenance work area, select these options:

    • Offering: Financials

    • Functional area: Enterprise Profile

    • Task: Manage Reference Data Set Data Access for Users

  16. Select the Users with Data Access option and identify the user you need to add data access to.

  17. Click Search to see all the existing data access privileges for this user.

  18. Click the Create icon to add the new job role data access.

  19. Add the required security contexts.

    Note: The usual set defined to cover all possible requirements is: Ledger, Data Access Set, Reference Data Set, and Business Unit.

It is advisable to run a final process whenever there is a change in job roles. This is accessed from the Setup and Maintenance page. Just select and click Submit. Follow its progress from the Tools > Scheduled Processes page.

Proving Access to Users

After the job role is created and you can add it to a user.

To do this, perform these steps:

  1. Open the User Accounts page.

  2. Select the job role and click Edit.

  3. Keep clicking Next till you see the Add User page.

  4. Add the user to the role.

    Note: Alternatively, you can select the user on the User Accounts page, click Edit, and add the role.

  5. Click Save and Close to complete the setup.

You will also need to add the required data access for this user.

Configure the General Ledger Segments for Reporting in OTBI

Before you generate the Auxiliary Report for Mexico, you need to configure and enable the general ledger segments for reporting in OTBI.

Perform the following tasks to configure and enable general ledger segments for reporting:

  1. Enable the chart of accounts segments for BI reporting.

  2. Map the accounting segments with the corresponding BI Object Name.

  3. Deploy flexfields.

  4. Run the Import Oracle Fusion Data Extensions for Transactional Business Intelligence process.

Enable the Chart of Accounts Segments

You must enable your accounting segments for OTBI reporting before using them. Perform the following steps to use the accounting segments in OTBI:

  1. Navigate to Setup and Maintenance work area.

  2. Select an implementation project.

  3. In the Task Lists and Tasks section, select Financials > Define Common Applications Configurations for Financials > Define Common Reference Objects for Financials > Manage Key Flexfields .

    The Manage Key Flexfields page appears.

  4. Enter GL# in the Key Flexfield Code field and click Search.

  5. Click Manage Structure Instances.

    The Manage Key Flexfield Structure Instances page appears.

  6. Use the search fields to search for your key flexfield structure instances.

  7. Select your key flexfield structure instances from the Search Results.

  8. Click the Actions menu and then click Edit.

    The Edit Key Flexfield Structure Instance page appears.

  9. Select the segment instance that you want to use in OTBI from the Segment Instances table.

  10. Click the Actions menu and then click Edit.

    The Edit Key Flexfield Segment Instance dialog box appears.

  11. Select the BI enabled check box and click the OK\ button.

  12. Click Save.

  13. Repeat the steps for all the segment instances that you want to use in OTBI.

Map the Accounting Segments and BI Object Name

Populate the BI Object Name for each of the Segment Labels that want to use in OTBI for reporting and analysis. The BI metadata has ten predefined BI Objects for the different GL segments. These BI Objects are used as dimensions in OTBI for the selected GL segments.

Perform the following steps to map the different chart of account segments to the predefined BI objects:

  1. Navigate to Setup and Maintenance work area.

  2. Select an implementation project.

  3. In the Task Lists and Tasks section, select Financials > Define Common Applications Configurations for Financials > Define Common Reference Objects for Financials > Manage Key Flexfields .

    The Manage Key Flexfields page appears.

  4. Enter GL# in the Key Flexfield Code field and click Search.

  5. Click the Actions menu and then click the Manage Segment Labels option.

    The Manage Segment Labels page appears.

  6. Update the BI Object Name for all the segment labels that are required. For the following three segment label codes, specify the BI Object name as specified in the table. Do not modify the BI object name; use the BI object name as mentioned in the following table.

    Segment Label Code BI Object Name

    FA_COST_CTR

    Dim - Cost Center

    GL_BALANCING

    Dim - Balancing Segment

    GL_ACCOUNT

    Dim - Natural Account Segment

  7. For the other non-qualified segment labels that you want to use in OTBI, use any BI object names from Dim - GL Segment1 to Dim - GL Segment10.

    For the Auxiliary report for Mexico, map the segment label MX_SUBACCOUNT to Dim - GL Segment9.

Deploy Flexfields

Perform the following steps to deploy the flexfields:

  1. Navigate to Setup and Maintenance work area.

  2. In the Setup and Maintenance work area, go to the following:

    • Offering: Financials

    • Functional Area: Financial Reporting Structures

    • Task: Manage Chart of Accounts Structure

  3. On the Manage Chart of Accounts Structure page, enter GL# in the Key Flexfield Code field and click Search.

  4. Click Deploy Flexfield.

    The GL#: Processing dialog box appears indicating the deployment progress.

  5. Click the OK button after the deployment completes.

Run the Import Oracle Fusion Data Extensions for Transactional Business Intelligence Process

Perform the following steps to run the Import Oracle Fusion Data Extensions for Transactional Business Intelligence process:

  1. Navigate to the Scheduled Processes work area.

  2. Click the Schedule New Process button.

    The Schedule New Process dialog box appears.

  3. Click Name.

  4. Click Search.

    The Search and Select dialog box appears.

  5. Enter Import Oracle Fusion Data Extensions for Transactional Business Intelligence in the Name field.

  6. Click Search.

  7. Select the process name from the Search Results area and click the OK button.

  8. Click the OK button on the Schedule New Process dialog box.

    The Process Details dialog box appears.

  9. Update the details and click the Submit button.

  10. Note the Process ID and click OK to acknowledge that your process was submitted.

  11. Note the status of your process:

    • If the status of the process is Succeeded, the process executed successfully. Proceed and generate the Auxiliary Report for Mexico.

    • If the status of the process is failed, check the log file, make corrections, and submit the process again.

Journals for Mexico Report

This topic includes details about the Journals for Mexico report.

Overview

Companies in Mexico must submit accounting information to the tax authorities. Companies must upload accounting information to the Tax Administration Services (SAT) internet portal through a registered e-mail account.

Note: You can use your taxpayer identification registry (RFC) to register your e-mail address on the SAT portal.

The Journals for Mexico report collects information on all transactions that are accounted and posted in different subledgers. The report also captures information related to electronic invoices generated for sales or received from suppliers for purchases, as well as manually entered journals. However, you must update the global descriptive flexfields when entering and posting transactions in Oracle Fusion Payables, Oracle Fusion Receivables, and Oracle Fusion General Ledger.

The following figure is an example of the report.

This graphic is a screenshot of the Journals for Mexico report.

Key Insights

To run the report, you must first enable the Journals for Mexico process so that you can run it from the Scheduled Processes page. For more information, see Enable the Mexican Reporting Processes.

When you run the Journals for Mexico process, detailed transaction information is extracted from Oracle Fusion General Ledger for the specified period.

The report is generated in an XML format and includes the following sections:

  • Heading: Includes layout version, RFC number, and the date on which the report is generated.

  • Details: Provides accounting information from the journal entries. Note that the UUID information depicted in the GL journals is extracted directly from the AR and AP transactions. Any change in these at the subledger level, doesn't need additional transfer actions to be included in the journals report.

Note: Records in the journal entries are grouped based on your Payables and Receivables settings. The information in the expense reports are tracked from the Payables transactions, while the GL journal entries are depicted as they're entered in GL.

Report Parameters

The following table describes selected process parameters.

Parameter Description

Request Type

Select the audit request type indicated by the Government.

  • AF - Audit Event

  • FC - On-site Audit

  • DE - Return

  • CO - Compensation

Audit Request Number

Specify the audit request number, when the request type is audit event (AF) or on-site audit (FC).

Audit Process Number

Specify the audit process number, when the request type is return (DE) or compensation (CO).

Journal Version

Enter the format version of the file defined by the Government of Mexico. It can change over time, such as 1.1, 1.2, and 1.3. Currently, the default value is 1.3.

Seal Number

Specify the digital electronic seal provided by the Government for the accounting file.

Certificate Number

Specify the digital certificate seal that protects the electronic accounting file.

Digital Seal Certificate

Specify digital seal certificate that protects the electronic accounting file.

Journal Source

Specify the name of the journal source. When you specify the source, the journal lines from that source are extracted. When you specify ALL, journal lines from all the subledgers are extracted.

Show Adjustment Periods

Specify whether data during the adjustment period must be extracted and reported.

Ledger

Specify the ledger from which you want to extract data. When you specify a ledger, only posted journal lines in that ledger are extracted.

From Period and To Period

Specify the range of subledger periods to be included for extracting data.

Posting Status

Specify whether posted data must be included in the report.

Include Zero Amount Lines

Specify whether zero amount lines must be included in the report.

Frequently Asked Questions

The following table lists frequently asked questions about the Journals for Mexico report.

FAQ Answer

How do I find this report?

Schedule and run this report from the Scheduled Processes work area on the Navigator menu.

Who uses this report?

Financial accountant

When do I use this report?

Use this report to provide detailed accounting information to the tax authorities in Mexico.

What type of report is this?

Oracle Business Intelligence Publisher

How You Capture Electronic Invoice Information for Mexico

The Journals for Mexico report collects information on all transactions that are accounted and posted in different subledgers. The report also captures information related to electronic invoices generated for sales, received from suppliers for purchases, or from manually entered journals. However you must update the global descriptive flexfields when entering and posting transactions in Oracle Fusion Payables, Oracle Fusion Receivables, and Oracle Fusion General Ledger.

Global descriptive flexfields store the unique identifier and invoice number issued either by the Tax Administration Services (SAT) or by an authorized service provider (PAC).

The following table summarizes the flexfields you must update for electronic invoices when you create an invoice or transaction in Payables, Receivables, Expense Reports or manual General Ledger entries.

Global Descriptive Flexfields Used in Description

Digital Tax Receipt using Internet Unique ID

  • AP Invoices

  • AP Payments

  • AR Invoices

  • AR Receipts

  • Manual GL Entries

Enter the unique identifier of the Comprobante Fiscal Digital por Internet. CFDI is an electronic billing schema that simplifies the process of issuing and receiving invoices and involves engaging an authorized certification provider.

Digital Tax Receipt Serial Number

  • AP Invoices

  • AR Invoices

Enter the serial number on the Digital Fiscal Document or Fiscal Document with Bidirectional Bar Code (CFD/CBB). CFD/CBB is a paper-based fiscal document that contains an authorized bidirectional bar code. This type of fiscal document is issued to taxpayers whose annual income is less than four million Mexican Pesos.

Digital Tax Receipt Invoice Number

  • AP Invoices

  • AR Invoices

Enter the invoice number on the CFD/CBB.

Foreign Invoice Number

  • AP Invoices

Enter the foreign invoice number, if it exists.

Payment Method

  • AP Payments

Select the payment type from the given list of values

Receipt Number

  • Expense Reports

Enter the unique identifier of CFDI. CFDI is an electronic billing schema that simplifies the process of issuing and receiving invoices and involves engaging an authorized certification provider.

Tax Payer ID

  • Manual GL Entries

Enter the tax payer ID of the transaction corresponding the manually entered journal in GL.

To capture all the Mexican transactions correctly:

  • In Payables, select Invoices for Mexico from the Regional Information list on the Additional Information tab on the Show More page of the invoice header. Ignore the Routing Attributes 1-5 for now.

  • For Payables Payments, select Payments for Mexico from the Regional Information list on the Additional Information tab of the payment header.

  • In Receivables, select Transactions for Mexico from the Regional Information list on the Miscellaneous tab of the transaction header.

  • In Receivables Receipts, select Receipt for Mexico from the Regional Information list on the Additional Information section of the AR Receipt header.

  • For Expense Reports, define the Receipt Number field to identify the UUID code on the Detail page of the expense item definition. The page can be accessed directly by clicking the Create New Expense Item button or from the detail section of an expense report by adding (and defining) a new expense item. Apart from the UUID entered at the Receipt Number field, Merchant Name and Taxpayer ID fields are also mandatory for extracting the information.

    Note: If you provide a valid UUID in the Receipt Number field, the transaction is considered as an expense report with a UUID. Otherwise, it's treated as a foreign invoice reported under NumFactExt. The Valid UUID format is: ad662d33-6934-459c-a188-bdf03930f444.

  • For manual General Ledger transactions, select Journals for Mexico from the Regional Information list on the Journal tab of the Show More page of the journals header.

If an invoice is paid in multiple installments, capture each payment separately. Update the GDFs with the details of your payments.You can access the GDFs from the:

  • Payment Installments dialog box in Payables

  • Review Installments dialog box in Receivables

Trial Balance Report for Mexico

This topic contains summary information about the Trial Balance Report for Mexico.

Overview

The trial balance report is an XML file that includes a summary of the initial balances, the sum of total transactions for the period, and the final balances for each account that you report in the Chart of Accounts XML file. The accounts related to assets, liabilities, equity, and results of operations (revenues, costs and expenses) are all included.

Key Insights

After generating the report from your folder in the General Accounting: Financial Reporting Center, convert the report to an XML format so that it's ready to upload to the Tax Administration Services (SAT) website.

You can create the report once and use it periodically. The report can also be modified based on the requirements from the Federal Government.

Frequently Asked Questions

The following table lists frequently asked questions about the Trial Balance Report for Mexico.

FAQs Answer

How do I find this report?

Run this report from your folder in the General Accounting: Financial Reporting Center.

Who uses this report?

  • Financial Accountants

  • Financial Specialists

When do I use this report?

Use the Trial Balance Report for Mexico to know the summary of the initial balances, the sum of total transactions for the period, and the final balances for each account that you report in the Chart of Accounts XML file.

What type of report is this?

Oracle Business Intelligence Publisher

Create a Trial Balance Report for Mexico

The trial balance report is an XML file that includes a summary of the initial balances, the sum of total transactions for the period, and the final balances for each account in the Chart of Accounts XML file. The report includes information on accounts related to assets, liabilities, equity, and the results of operations (revenues, costs and expenses).

Use the Oracle Hyperion Financial Reporting Studio to create the Trial Balance Report for Mexico. You can create the report once and use it periodically. The report can also be modified based on the requirements from the Federal Government.

Perform the following steps to create the report:

  1. Access the Financial Reporting Studio on your desktop.

  2. Click File > New > Report or click the New Report icon.

  3. Use the Report Pallet page as the container to build your report.

  4. Click Insert > Grid... or click the New Grid icon.

  5. Click and hold the left mouse button to select a large area of the grid to create the report.

    The Select a Database Connection dialog box appears when you release the mouse after setting the size and position of the grid.

  6. Click the Database Connection list.

  7. Select your database. A unique cube exists for each combination of chart of accounts and accounting calendar.

    Note: The user name and password are the login credentials you used to access the Financial Reporting Studio.

  8. Click OK.

    The Dimension Layout dialog box appears. Set the point of view dimensions and accounts for the rows, columns, and page levels.

  9. Use the Dimension Layout dialog box to arrange your dimensions. Move:

    • Accounting Period and Balance Amount to Columns.

    • Account to Rows.

      Tip: Click the icon in front of the name to select the dimension to move to the designed area.

      The following figure illustrates how the dimensions appear on the Dimension Layout dialog box after you made your selection.

      This is the Dimension Layout dialog box.

  10. Click OK.

    You return to the grid which has the selected dimensions.

  11. Add additional rows and columns to the grid according to your requirement.

    • Columns: Copy column A and paste it four times to see columns A to E.

    • Rows: Select row 1, right-click and select Insert Row > Text. Insert ten rows.

  12. Enter the row text in each row cell. The following table lists the row text for each row.

    Row Text

    1

    		 
    Version="1.3"
    Note: You can update the version number anytime.

    2

    		 
    RFC="SYS11103"
    Note: You can update the RFC anytime.

    3

    		 
    Mes="<<MemberName("Grid1", "AccountingPeriod")>>"

    4

    		 
    Anio="<<MemberName("Grid1", E, "AccountingPeriod")>>"

    5

    		 
    TipoEnvio="N" (User needs to fill value)

    6

    		 
    FechaModBal=" " (User needs to fill value)

    7

    		 
    Sello="Seal" (User needs to fill value)

    8

    		 
    noCertificado ="13"(User needs to fill value)

    9

    		 
    Certificado="cERT"(User needs to fill value)

    10

    Update the row cell and columns for row 10 with the following values:

    • Row cell with NumCta

    • Row 10, Column A with Saldo Ini

    • Row 10, Column B with Debe

    • Row 10, Column C with Haber

    • Row 10, Column D with SaldoFin

    The following figure illustrates how the grid should appear:

    This graphic is a screenshot of the report grid.

  13. In the first column on the grid (Column A), double-click the AccountingPeriod cell.

    The Select Members dialog box appears.

  14. Remove the default selection from the Selected pane and assign a user-defined period selection.

  15. Select the AccountingPeriod in the Selected pane and click Remove.

  16. Select Current Point of View for Accounting Period from Available pane and click Move to place it in the Selected pane.

  17. Select the Place selection into separate columns option.

  18. Click OK.

  19. Repeat the steps for Columns B, C, and D.

  20. In the last column on the grid (Column E), double-click the AccountingPeriod cell to open the Select Members dialog box.

  21. Select the AccountingPeriod in the Selected pane and click Remove.

  22. Select Current Point of View for Accounting Period from Available pane and click Move to place it in the Selected pane.

  23. Click the Functions tab and select RelativeMember from the list.

  24. Click Add.

  25. Use the Edit RelativeMember Function dialog box to add the parameter values. The following table lists the values you can enter.

    Parameter Value

    Member

    Current Point of View for Accounting Period

    Offset

    0

    Hierarchy

    AccountingPeriod

    RelativeMemberList

    Fiscal Year

  26. Select the Place selection into separate columns option.

  27. Click OK.

  28. In the first column on the grid (Column A), double-click the BalanceAmount cell.

    The Select Members dialog box appears.

  29. Remove the default selection from the Selected pane and assign a user-defined selection.

  30. Expand the Balance Amount node in the Available pane and select Beginning Balance.

  31. Click Move.

  32. Select the Place selection into separate columns option.

  33. Click OK.

  34. Repeat the steps and assign the user-defined properties as shown in the table for the BalanceAmount cell in the other columns.

    Column Parent Node Custom Selection

    B

    Period Activity

    Period Activity Dr

    C

    Period Activity

    Period Activity Cr

    D

    Ending Balance

    Ending Balance

    E

    Ending Balance

    Ending Balance

    The following figure illustrates how your grid appears when you finish updating user-defined values for all the dimensions.

    This graphic shows what the Trial Balance Report for Mexico looks like.

  35. Select column E.

    The Custom Properties dialog box appears.

  36. Select the Hide Always check box.

  37. Select the first two rows (row 1 and 2).

  38. Select the Hide Always check box in the Custom Properties dialog box.

  39. Select row 11, right-click and click Insert Row > Data. Row 12 is added.

    Note: For each account in the SAT chart of accounts mapping, add a row.

  40. Double-click the Account cell in row 11 to open the Select Members dialog box.

  41. Select Account in the Selected pane and click Remove.

  42. Click the Members tab.

  43. Expand the Account > All Account Values nodes that appear in the Available pane.

  44. Select 1110 and click Move. The account appears in the Selected pane.

  45. Select the Place selection into separate columns option.

  46. Click OK.

    You can follow the similar steps to add all your natural accounts.

  47. Update the dimensions that appear in the grid with the values specified in the following table.

    Note: To update the dimensions, double-click the respective dimension buttons to open the Select Members dialog box. Select the values as specified in the table in the Select Members dialog box and click OK.
    Dimension Value

    AccountingPeriod

    Qtr1-00 for the year 2000

    Note: Run the report either for a year or for any quarter of a year.

    Scenario

    Actual

    Ledger

    Vision Operation

    Note: Run the report for any ledger.

    Amount Type

    Base

    Currency

    MXN

    Currency Type

    Entered

  48. Set the grid properties. Select the grid to open the Grid Properties dialog box. In the Suppression region, check the following check boxes:

    • Basic Options

    • If Zero, Suppress

    • If Missing, Suppress

    • If Error, Suppress

  49. Save the report.

Example of the final output of the report:

This graphic is a screenshot of a completed Trial Balance Report for Mexico.

Convert the Trial Balance Report for Mexico to XML Format

You can view the Trail Balance Report for Mexico from the Financial Reporting Center. After you have viewed the report, you can convert it to an XML format to upload it to the Tax Administration Services (SAT) internet portal.

View the Trial Balance Report for Mexico

To view the report, perform the following steps:

  1. Sign in the Oracle Fusion Applications.

  2. On the home page of General Account Manager, select General Accounting: Financial Reporting Center > My Folders > Trial Balance Report for Mexico.

  3. View the report in HTML.

  4. Click the HTML Preview list at the beginning of the page and select Export to Excel.

    The report opens in Microsoft Excel.

  5. Save the report in your local folder as an Excel Macro-Enabled Workbook.

Convert the Trial Balance Report for Mexico to XML Format

Perform the following steps to convert your report to an XML format that can be uploaded to the SAT portal:

  1. Open the report in Microsoft Excel and click the View tab.

  2. Click Macros > View Macros.

  3. Enter GenerateXML in the Macro name field and click Create.

    The Microsoft Visual Basic editor opens.

  4. Copy and paste the following report conversion code in the Microsoft Visual Basic editor. 

    Sub Generate_XML()
'
' GenerateXML Macro
    Dim Version As String
    Dim RFC As String
    Dim Mes As String
    Dim Anio As String
    Dim TipoEnvio As String
    Dim FechaModBal As String
    Dim Sello As String
    Dim noCertificado As String
    Dim BalanzaCertificado As String
    Dim XML As String
    Dim XML2 As String
    Dim Row As String
    Dim Row2 As String
    Dim ColumnName As String
    Dim ColumnValue As String
    Dim MesP1 As String
    Dim MesP2 As String
			Dim MesPr As String
    Dim Pos As Integer
    Dim Pos2 As Integer
    Dim Pos3 As Integer
    Dim MidStr As String
    Dim MidStr2 As String
    Dim Arr

    Sheets(1).Select
    Version = Replace(ActiveSheet.Cells(3, 1).Value, " ", "")
    RFC = Replace(ActiveSheet.Cells(4, 1).Value, " ", "")
    Mes = Replace(ActiveSheet.Cells(3, 1).Value, " ", "")
    Anio = Replace(ActiveSheet.Cells(6, 1).Value, " ", "")
    TipoEnvio = Replace(ActiveSheet.Cells(7, 1).Value, " ", "")
    FechaModBal = Replace(ActiveSheet.Cells(8, 1).Value, " ", "")
    Sello = Replace(ActiveSheet.Cells(9, 1).Value, " ", "")
    noCertificado = Replace(ActiveSheet.Cells(10, 1).Value, " ", "")
    BalanzaCertificado = Replace(ActiveSheet.Cells(11, 1).Value, " ", "")
    
    Arr = Array("Version", "RFC", "Mes", "Anio", "TipoEnvio", "FechaModBal", "Sello", "noCertificado", "Certificado")
    
    MesP1 = Mid(Mes, 6, 3)
    'MsgBox MesP1
				MesPr = Mid(Mes, 6, 2)
    If ((MesP1 = "Jan") Or (MesP1 = "Ene") Or (MesP1 = "ENE") Or (MesP1 = "JAN") Or (MesPr = "01")) Then
        MesP2 = "Mes=" & Chr(34) & "01" & Chr(34)
					'MsgBox Mes
    ElseIf ((MesP1 = "Feb") Or (Mes = "FEB") Or (MesPr = "02")) Then
        MesP2 = "Mes=" & Chr(34) & "02" & Chr(34)
    ElseIf ( (MesP1 = "Mar") Or (Mes = "MAR") Or (MesPr = "03")) Then
        MesP2 = "Mes=" & Chr(34) & "03" & Chr(34)
    ElseIf ((MesP1 = "Apr") Or (MesP1 = "Abr") Or (MesP1 = "ABR") Or (MesP1 = "APR") Or (MesPr = "04")) Then
        MesP2 = "Mes=" & Chr(34) & "04" & Chr(34)
    ElseIf ((MesP1 = "May") Or (MesP1 = "MAY") Or (MesPr = "05")) Then
        MesP2 = "Mes=" & Chr(34) & "05" & Chr(34)
    ElseIf ((MesP1 = "Jun") Or (MesP1 = "JUN") Or (MesPr = "06")) Then
        MesP2 = "Mes=" & Chr(34) & "06" & Chr(34)
    ElseIf ((Mes = "Jul") Or (MesP1 = "JUL") Or (MesPr = "07")) Then
        MesP2 = "Mes=" & Chr(34) & "07" & Chr(34)
    ElseIf ((MesP1 = "Aug") Or (MesP1 = "AGO") Or (MesP1 = "Ago") Or (MesP1 = "AUG") Or (MesPr = "08")) Then
        MesP2 = "Mes=" & Chr(34) & "08" & Chr(34)
    ElseIf ((MesP1 = "Sep") Or (MesP1 = "SEP") Or (MesPr = "09")) Then
        MesP2 = "Mes=" & Chr(34) & "09" & Chr(34)
    ElseIf ((MesP1 = "Oct") Or (MesP1 = "OCT") Or (MesPr = "10")) Then
        MesP2 = "Mes=" & Chr(34) & "10" & Chr(34)
    ElseIf ((MesP1 = "Nov") Or (MesP1 = "NOV") Or (MesPr = "11")) Then
        MesP2 = "Mes=" & Chr(34) & "11" & Chr(34)
    ElseIf ((MesP1 = "Dec") Or (MesP1 = "DEC") Or (MesP1 = "Dic") Or (MesP1 = "DIC") Or (MesPr = "12")) Then
        MesP2 = "Mes=" & Chr(34) & "12" & Chr(34)
    Else
        MesP2 = "Mes=" & Chr(34) & "13" & Chr(34)
    End If
    'MsgBox MesP2
    'XML = XML & "<BCE:Balanza xmlns:BCE=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/BalanzaComprobacion" & Chr(34) & " xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/BalanzaComprobacion http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/BalanzaComprobacion/BalanzaComprobacion_1_1.xsd" & Chr(34) & " " & Version & " " & RFC & " " & Mes & " " & Anio & " " & TipoEnvio & " " & FechaModBal & " " & Sello & " " & noCertificado & " " & BalanzaCertificado & ">" & vbNewLine
    XML = XML & "<BCE:Balanza xmlns:BCE=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/BalanzaComprobacion" & Chr(34) & " xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/BalanzaComprobacion http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/BalanzaComprobacion/BalanzaComprobacion_1_1.xsd" & Chr(34)
    XML2 = XML
    Row2 = ""
    
    
    'Pos is Position of Inverted Commas from Start
    'Pos2 is Length of String between Inverted Commas
    'Pos3 is Length of Field Name

    For I = 1 To 9
        Pos = (InStr(1, Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Chr(34))) + 1
        Pos2 = InStr(Pos, Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Chr(34))
        Pos2 = Pos2 - Pos
        Pos3 = Pos - 3
        'MsgBox Pos2
				' MsgBox I
        MidStr = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Pos, Pos2)
        MidStr2 = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), 1, Pos3)
        ' MsgBox MidStr2
        If MidStr <> "" Then
            If MidStr2 = "Mes" Then
                Row2 = Row2 & MesP2 & " " 
              Else
                Row2 = Row2 & MidStr2 & "=" & Chr(34) & MidStr & Chr(34) & "  "
            
              End If
              'Row2 = Row2 & " " & Replace(ActiveSheet.Cells(I, 1).Value, " ", "")
              'MsgBox Row2
                    
        End If
                           
    Next I
    'MsgBox XML2
    'MsgBox Row2
    
    XML2 = XML2 & "  " & Row2
    
    XML = XML2 & ">" & vbNewLine
    
    ActiveSheet.Range("A10").Select
    Selection.End(xlDown).Select
    SheetLastRow = ActiveCell.Row
    
    ActiveSheet.Range("A10").Select
    Selection.End(xlToRight).Select
    SheetLastColumn = ActiveCell.Column
    
    For I = 11 To SheetLastRow
        Row = vbTab & "<BCE:Ctas "
        For J = 1 To SheetLastColumn
            ColumnName = Replace(ActiveSheet.Cells(10, J).Value, " ", "")
            If ColumnName = "SaldoIni" Or ColumnName = "Debe" Or ColumnName = "Haber" Or ColumnName = "SaldoFin" Then
							'MsgBox ColumnValue
							If ActiveSheet.Cells(I, J).Value = "" Or ActiveSheet.Cells(I, J).Value = "0" Then
								ColumnValue = 0
								'MsgBox ColumnValue
							Else
            ActiveSheet.Cells(I, J).Value = Format(ActiveSheet.Cells(I, J).Value, "#,###")
                ColumnValue = ActiveSheet.Cells(I, J).Value
                'Selection.NumberFormat = "#,###"
            Else
                ColumnValue = ActiveSheet.Cells(I, J).Value
            End If
            Row = Row & ColumnName & "=" & Chr(34) & ColumnValue & Chr(34) & " "
        Next J
        Row = Row & "/>" & vbNewLine
        
        XML = XML & Row
    Next I
    
    XML = XML & "</BCE:Balanza>"
    
    fileSaveName = Application.GetSaveAsFilename(ActiveSheet.Name, fileFilter:="XML Files (*.xml), *.xml")
        
    Open fileSaveName For Output As #1
    Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
    Print #1, XML
    Close #1
'
End Sub

  5. Click File > Save.

    Save the file in your local folder and close Microsoft Visual Basic.

  6. Select the GenerateXML macro and click Run that appears on the Macros dialog box in Microsoft Excel.

  7. Save the XML file in your local folder.

  8. Open the XML file and verify the output.

FAQs for Reports of Mexico

How do parent accounts and subaccounts appear in the Mexico reports?

If you have a combination of account and subaccount, the SubCtaDe field displays the immediate natural account preceding a subaccount. This happens because account hierarchy considers only the immediate natural account preceding the subaccount as the parent. The original parent account may not always be the immediate parent for a subaccount.

Consider these parent and child natural accounts, and the associated subaccounts, for an example:

Natural Account - Parent Natural Account - Child Subaccount SubCtaDe displays

100

 

100

100

10001

100 10001

100

10002

100 10002

10002

001

10002 001

10002

002

10002 002

Here, the parent account for the natural child accounts 10001 and 10002 is 100. However, the immediate parent account for subaccount 001 is 10002 and not 100. Since it takes precedence before the natural parent account, the report displays 10002 001, instead of 100 001.

Why doesn't my report display details of foreign fiscal documents?

You can see details of foreign fiscal documents (CompExt) only on foreign invoices. These details aren't going to show on your report.

Why doesn't my RFC appear on the Mexican reports?

If your Taxpayer Identification Registry (RFC) doesn't appear on the Mexican report, try one of these methods:

  • To make it appear at the header level:

    • Specify it at your legal entity level while you create the legal entity.

  • To make it appear at the national fiscal documents of type CFDI (CompNal) level and payment levels:

    • Specify it at the supplier level.

Other Latin America Reports

Purchase Withholding Certificate for Chile

This topic contains summary information about the Purchase Withholding Certificate Report for Chile.

Overview

The Purchase Withholding Certificate Report for Chile helps with an in-depth analysis of all the information for transactions in the ledger currency that have transaction year within the certificate that is selected. The report displays the calendar periods, taxable service fee, as well as the payments on which amounts were withheld.

Key Insights

To generate the report, the role must be LAD Financial Reporting role, and it can be assigned to any job role. This do not need any job role exclusively.

Frequently Asked Questions

The following table lists frequently asked questions about the Purchase Withholding Certificate for Chile.

FAQs Answer

How do I find this report?

In Oracle Business Intelligence Enterprise Edition, select Analysis from Analysis and Interactive Reporting.

Who uses this report?

  • Financial Accountants

  • Financial Specialists

When do I use this report?

Use the Purchase Withholding Certificate for Chile when you need to provide detailed records of your accounts to the fiscal authorities.

What type of report is this?

Oracle Transaction Business Intelligence

Purchase Withholding Certificate for Argentina

This topic contains summary information about the Purchase Withholding Certificate for Argentina.

Overview

The Purchase Withholding Certificate for Argentina provides all the report information for transactions in the ledger currency that have the transaction date within the date range that is selected.

Key Insights

To generate the report, the role must be LAD Financial Reporting role, and it can be assigned to any job role. This do not need any job role exclusively.

Frequently Asked Questions

The following table lists frequently asked questions about the Purchase Withholding Certificate for Argentina.

FAQs Answer

How do I find this report?

In Oracle Business Intelligence Enterprise Edition, select Analysis from Analysis and Interactive Reporting.

Who uses this report?

  • Financial Accountants

  • Financial Specialists

When do I use this report?

Use the Purchase Withholding Certificate for Argentina when you need to extract information which electronically reports withholding VAT information.

What type of report is this?

Oracle Transaction Business Intelligence

Purchase Withholding Certificate for Colombia

This topic contains summary information about the Purchase Withholding Certificate for Colombia.

Overview

The Purchase Withholding Certificate for Colombia helps with an in-depth analysis of all the report information for the transactions in the ledger currency that have the transaction date within the date range that is selected.

Key Insights

To generate the report, the role must be LAD Financial Reporting role, and it can be assigned to any job role. This do not need any job role exclusively.

Frequently Asked Questions

The following table lists frequently asked questions about the Purchase Withholding Certificate for Colombia.

FAQs Answer

How do I find this report?

In Oracle Business Intelligence Enterprise Edition, select Analysis from Analysis and Interactive Reporting.

Who uses this report?

  • Financial Accountants

  • Financial Specialists

When do I use this report?

Use the Purchase Withholding Certificate for Colombia when you need to extracts information which electronically reports withholding VAT information for Colombia.

What type of report is this?

Oracle Transaction Business Intelligence