Skip to main content

Appendix F: Miscellaneous Tasks

This appendix lists the miscellaneous tasks that the administrator may need to perform while setting up and using Transaction.

Backtracking Transactions from SAP S/4HANA

This section describes how to backtrack the transactions from SAP S/4HANA to your source system.

JD Edwards EnterpriseOne

To backtrack the transactions in JD Edwards EnterpriseOne, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    F0911.GLICUT||'-'[If reversal transaction] || ‘D|| (sequence number) (If document splits into multiple documents) ||F0411.RPDOC (last 4 characters) ||F0911.GLCO (last 4 characters) ||F0911.GLDGJ (MMDD format)

    For example, the value of the AWKEY column for a payable transaction is V 4258 D1 0001 0225.

  • Accounts Receivable

    F03B11.RPDCT||'-'[If reversal transaction] || ‘D|| (sequence number)’ (If document splits into multiple documents) ||F03B11.RPDOC (last 4 characters) ||F0911.GLICU (last 4 characters) ||F0911.GLCO (last 4 characters) ||F03B11.RPDGJ (MMDD format)

    For example, the value of the AWKEY column for a receivables transaction is RI 9619 9725 0001 0801.

  • General Ledger

    ‘JE’||'-'[If reversal transaction] || ‘D|| (sequence number)’ (If document splits into multiple documents) ||F0911.GLDOC ||F0911.GLCO (last 4 characters) ||F03B11.GLDGJ (MMDD format)

    For example, the value of the AWKEY column for a general ledger transaction is JE 18000173 0001 1101.

PeopleSoft

To backtrack the transactions in Oracle PeopleSoft, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    D:|| (sequence number) (If document splits into multiple documents) || PS_VOUCHER.VOUCHER_ID/PS_PAYMENT_TBL.PYMNT_ID (last 8 characters for payments)||PS_JRNL_HEADER.BUSINESS_UNIT||PS_JRNL_HEADER.JOURNAL_ID(last 3 digits)

    For example, the value of the AWKEY column for a payables transaction is 00000420 US001 727.

  • Accounts Receivable

    D:|| (sequence number) (If document splits into multiple documents) || PS_ITEM_ACTIVITY.payment_id/PS_ITEM_ACTIVITY.draft_id(last 10 characters for payments) or PS_ITEM_ACTIVITY.item (last 10 characters for other documents after removing non alpha-numeric characters)||PS_JRNL_HEADER.journal_id (last 4 characters)||PS_ITEM_ACTIVITY.entry_type (last 2 characters)

    For example, the value for a receivables transaction is DR1 0610 DR.

  • General Ledger

    D:|| (sequence number) (If document splits into multiple documents) ||PS_JRNL_HEADER.JOURNAL_ID(last 6 digits) ||PS_JRNL_HEADER.BUSINESS_UNIT||PS_JRNL_HEADER.JOURNAL_DATE(MMDD)

    For example, the value of the AWKEY column for a general ledger transaction is 000621 US001 0522.

Oracle E-Business Suite

To backtrack the transactions in Oracle E-Business Suite, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable/Accounts Receivable

    AP_INVOICES_ALL.INVOICE_ID /RA_CUSTOMER_TRX_ALL. CUSTOMER_TRX_ID (last 5 numbers) || GL_JE_HEADERS.JE_HEADER_ID (last 5 numbers) || ‘D|| (sequence number) (If document splits into multiple documents) || HR_ORGANIZATION_INFORMATION. ORG_INFORMATION2 (last 4 digits) ||GL_PERIODS.PERIOD_YEAR

    For example, the value of the AWKEY column for a payables transaction is 07099 04106 D1 204 2018, and the value for a receivables transaction is 69235 05094 D1 204 2018.

  • General Ledger

    GL_JE_HEADERS.JE_HEADER_ID (last 10 numbers If it has greater than 10 digits else zeros will be added as suffix) || ‘D|| (sequence number) (If document splits into multiple documents) || HR_ORGANIZATION_INFORMATION. ORG_INFORMATION2 (last 4 digits) || GL_PERIODS.PERIOD_YEAR

    For example, the value of the AWKEY column for a general ledger transaction is 0709604100 204 2018.

Microsoft Dynamics GP

To backtrack the transactions in Microsoft Dynamics GP, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable/Accounts Receivable

    D|| (sequence number) (If document splits into multiple documents) ||COMPANY ID|| GL20000.JRNENTRY (Last 10 characters) || (PM20000/PM30200). VCHRNMBR / GL20000.ORDOCNUM (Last 6 characters)

    For example, the value of the AWKEY column for a payables transaction is TWO 0000011990 000376, and the value for a receivables transaction is TWO 0000003481 IVC26.

  • General Ledger

    D|| (sequence number) (If document splits into multiple documents) ||COMPANY ID || GL20000.JRNENTRY

    For example, the value of the AWKEY column for a general ledger transaction is TWO 3451.

IFS Applications

To backtrack the transactions in IFS Applications, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

GEN_LED_VOUCHER_ROW_TAB.VOUCHER_NO|| ‘D|| (sequence number) (If document splits into multiple documents) || GEN_LED_VOUCHER_ROW_TAB.COMPANY_CODE|| GEN_LED_VOUCHER_ROW_TAB.VOUCHER_TYPE|| GEN_LED_VOUCHER_ROW_TAB.ACCOUNTING_YEAR

For example, the value of the AWKEY column for a payables transaction is 2009000012 C1 I 2009, and the value for a receivables transaction is 2008000102 C1 F 2008, and general ledger transaction is 2017040009 C1 M 2017.

Epicor iScala

To backtrack the transactions in Epicor iScala, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    D+ (sequence number) (If document splits into multiple documents) + GL06.GL06002+’:’+GL06.GL06023(last 6 chars) +’:’+(PL03.PL03079(for CreditMemo) or GL06.GL06012(other docs))

    For example, the value of the AWKEY column for a payables transaction is 040050023:000421:6.

  • Accounts Receivable

    D+ (sequence number) (If document splits into multiple documents) + GL06.GL06002+':'+GL06.GL06023(last 6 chars) +’:’+(SL03.SL03088(Invoices) or GL06.GL06012(other docs))

    For example, the value of the AWKEY column for a receivables transaction is 207000017:200010:4.

  • General Ledger

    D+ (sequence number) (If document splits into multiple documents) +GL06.GL06002+':'+GL06.GL06023(last 6 chars) +’:’+GL06.GL06012

    For example, the value of the AWKEY column for a general ledger transaction is 900000000:000001:0.

Microsoft Dynamics AX

To backtrack the transactions in Microsoft Dynamics AX, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

D+ (sequence number) (If document splits into multiple documents) +GENERALJOURNALENTRY.RECID

For example, the value of the AWKEY column is D1 35637304420.

Microsoft Dynamics NAV

To backtrack the transactions in Microsoft Dynamics NAV, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

D+ (sequence number) (If document splits into multiple documents) +G_L Register. No (last 3 chars) + (G_L Entry. [Document No_]) (last 4 chars) +G_L Entry. [Document Type] +G_L Entry. [Posting Date]

For example, the value of the AWKEY column is 335 8066 2 20180220.

Microsoft Dynamics SL

To backtrack the transactions in Microsoft Dynamics SL, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    D+ (sequence number) (If document splits into multiple documents) + APDoc.RefNbr(last 8 chars) +’:’+Batch.BatNbr (last 6 chars) +’:’+Batch.Module

    For example, the value of the AWKEY column for a payables transaction is 000115:000140:AP.

  • Accounts Receivable

    D+(sequence number) (If document splits into multiple documents) +ARDoc.RefNbr(last 8 chars) +’:’+Batch.BatNbr(last 6 chars) +’:’+Batch.Module

    For example, the value of the AWKEY column for a receivables transaction is 000001:000001:AR.

  • General Ledger

    D+(sequence number) (If document splits into multiple documents) +GLTran.RefNbr(last 8 chars) +’:’+Batch.BatNbr(last 6 chars) +’:’+Batch.Module

    For example, the value of the AWKEY column for a general ledger transaction is SA2525:000129:GL.

Dynamics 365 Finance and Operations

To backtrack the transactions in Dynamics 365 Finance and Operations, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    OT_D365FNO_AP_STG.JE_HEADER_ID||':'||OT_D365FNO_AP_STG.COMPANY_CODE

    For example, 68719706062:USMF where 68719706062 is RECID and USMF is SUBLEDGERVOUCHERDATAAREAID.

  • Accounts Receivable

    OT_D365FNO_AR_STG.JE_HEADER_ID||':'||OT_D365FNO_AR_STG.COMPANY_CODE

    For example, 68719706062:USMF where 68719706062 is RECID and USMF is SUBLEDGERVOUCHERDATAAREAID.

  • General Ledger

    OT_D365FNO_GL_STG.JE_HEADER_ID||':'||OT_D365FNO_GL_STG.COMPANY_CODE

    For example, 68719706062:USMF where 68719706062 is RECID and USMF is SUBLEDGERVOUCHERDATAAREAID.

Dynamics 365 Business Central (On-premises and Cloud)

To backtrack the transactions in Dynamics 365 Business Central, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    OT_BC_AP_STG.JE_HEADER_ID||':'||OT_BC_AP_STG.COMPANY_CODE||':'||OT_BC_AP_STG.FISCAL_YEAR

    AWKEY in the BKPF table is concatenated value of JE_HEADER_ID,COMPANY_CODE, and FISCAL_YEAR columns.

  • Accounts Receivable

    OT_BC_AR_STG.JE_HEADER_ID||':'||OT_BC_AR_STG.COMPANY_CODE||':'||OT_BC_AR_STG.FISCAL_YEAR

    AWKEY in the BKPF table is concatenated value of JE_HEADER_ID,COMPANY_CODE, and FISCAL_YEAR columns.

  • General Ledger

    OT_BC_GL_STG.JE_HEADER_ID||':'||OT_BC_GL_STG.COMPANY_CODE||':'||OT_BC_GL_STG.FISCAL_YEAR

    AWKEY in the BKPF table is concatenated value of JE_HEADER_ID,COMPANY_CODE, and FISCAL_YEAR columns.

Infor CloudSuite Industrial (SyteLine)

To backtrack the transactions in Infor CloudSuite Industrial (SyteLine), you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable

    "D+ (sequence number) (If document splits into multiple documents) +LEDGER_MST.JOURNAL_BATCH_ID (last 5 chars) + LEDGER_MST.TRANS_DATE (mmdd) + APTRXP_MST.type + LEDGER_MST.CHECK_NUM (payments) or LEDGER_MST.VOUCHER(for other docs) last 2 chars +LEDGER_MST.SITE_REF (last 4 chars)"

    For example, the value of the AWKEY column for a payables transaction is 100 0205 O 24 DALS.

  • Accounts Receivable

    "D+(sequence number) (If document splits into multiple documents) +LEDGER_MST.JOURNAL_BATCH_ID (last 5 chars) + LEDGER_MST.TRANS_DATE (mmdd) + artran_mst.type + LEDGER_MST.CHECK_NUM(payments) or LEDGER_MST.VOUCHER(for other docs) last 2 chars +LEDGER_MST.SITE_REF(last 4 chars)"

    For example, the value of the AWKEY column for a receivables transaction is 110 0403 I 36 DALS.

  • General Ledger

    "D+(sequence number) (If document splits into multiple documents) +LEDGER_MST.JOURNAL_BATCH_ID (last 5 chars) + LEDGER_MST.TRANS_DATE (mmdd) +LEDGER_MST.control_number (last 3 chars) + LEDGER_MST.SITE_REF (last 4 chars)"

    For example, the value of the AWKEY column for a general ledger transaction is 950 0205 P 24 DALS.

Infor Baan

To backtrack the transactions in Infor Baan, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

  • Accounts Payable/Accounts Receivable

    ttfgld106222.T_OTYP||':'||ttfgld106222.T_ODOC

    For example, the value of the AWKEY column for a receivables transaction is CB1:11000001.

  • General Ledger

    ttfgld106222.T_CATG||':'||ttfgld106222.T_ODOC

    For example, the value of the AWKEY column for a general ledger transaction is JV:17000050.

NetSuite

To backtrack the transactions in NetSuite, you can use the concatenated value of the AWKEY column in the BKPF table in SAP S/4HANA as follows:

D+ (sequence number) (If document splits into multiple documents)|| TRANSACTIONS.TRANSACTION_ID||TRANSACTION_LINES.SUBSIDIARY_ID

For example, the value of the AWKEY column is 512096:7.

Validation of Transactions

This section describes the list of validations that are performed on the FIN% tables and lists the recommended solutions if the validations fail. The section also lists the scenarios in which the documents are updated with the P status in the FIN% tables.

List of Validations on FIN% Tables

After the transformations are applied on the data, the following set of validations are performed on the FIN% tables:

  • Sum of credit and debit amounts in documents must be equal to zero.
  • The following mandatory columns must not be null:
    • Effective date
    • Posted date
    • Vendor number/customer number
    • Fiscal year
    • Currency code
    • Document type
    • Accounting document number
    • Accounting logical system
    • GL account number
    • Company Code
  • Documents with lines in FIN_PRDTAX must contain the corresponding tax codes in FIN_ACCT.
  • The tax amounts in documents should be less than the taxable amounts.
  • Documents with lines in FIN_WHTAX must contain the corresponding lines in FIN_CREDIT.
  • Documents with lines in FIN_CREDIT and FIN_PRDTAX must contain the corresponding lines in FIN_ACCT.

Documents with E Status

When any of the validations fail, the Status column of the FIN_HEADER table is set to E, and information about the failed documents are moved to a log file. In such cases, we recommend that you perform the following:

  1. In the source system, verify the data in the affected columns based on the error details in the log file. If the data is missing, correct the source data, and then run the reprocess job for the failed documents.
  2. If the source data is correct, compare the output of extraction views and source data. Based on the difference, raise a bug or enhancement request with insightsoftware Support.

Documents with P Status

After the data is loaded into the FIN% tables, if any transformations are applied on the data, the Status column of the FIN% tables is set to P. Some of the example are as follows:

  • A document with amounts greater than 2 decimals will be rounded off because SAP S/4HANA allows only 2 decimal values, and the status will be updated to P.
  • If the document contains withholding tax amounts, the tax amount will be adjusted to the credit line, and the status will be updated to P.

Count Mismatch of Documents, Lines

The number of lines or documents in the source system may not match with the number of lines or documents in the FIN% tables (except FIN_HEADER) or SAP S/4HANA in scenarios, such as the following:

  • When the transactions are posted to General Ledger at the summary level in a batch, each transaction will be split into one document and posted into SAP S/4HANA.
  • When the documents with transaction amounts exceed 11 digits, the number of documents or lines may not match between the source and target systems.

CDC Process

The high-level process for the change data capture (CDC) in Transaction is as follows:

  1. Based on the native CDC or BryteFlow, the changes to the General Ledger tables of any enterprise resource planning (ERP) system are captured in the CDC table.
  2. The records from the CDC table will be moved into a temporary CDC table, which is created in the schema containing extraction views during the installation of Transaction.
  3. The records from the temporary CDC table will be joined with other ERP tables to obtain the document details pertaining to the current transaction by the CDC view. Then, the transactions will be loaded into the FIN% tables in the staging database through the ETL jobs.
  4. A set of validations will be performed on the transactions in the FIN% tables, and then, the transactions will be loaded into SLT Replication Server through the ETL jobs.
  5. The documents will be posted from SLT into SAP S/4HANA through the SAP process.

    Important: If the transaction is already loaded into SAP S/4HANA, no changes will be made to the transaction.

The diagram showing the flow of data from the source system into the target system during the CDC process is as follows.

Handling Planned, Unplanned Outages

This section explains how to handle planned or unplanned outages in the source or target system.

Planned Outages

During planned outages, if any updates are made to the source or target system while the CDC jobs are running, stop the CDC jobs before the services are down. Restart the CDC jobs after the services are running.

Unplanned Outages

For unplanned outages in your source or target system, perform one or more of the following:

  • If any job fails due to an outage in SLT, identify the documents with the status N in the FIN_HEADER table in the staging database and then verify whether the unique identifiers of these document lines exist in the other FIN% tables with the status Y. If exists, update the status to N in those tables and delete those document lines from the following corresponding tables in SLT:
    • /1LT/CF_E_HEADER
    • /1LT/CF_E_ACCT
    • /1LT/CF_E_DEBIT
    • /1LT/CF_E_CREDIT
    • /1LT/CF_E_PRDTAX
    • /1LT/CF_E_WHTAX
    • /1LT/CF_E_CLRITM
    • /1LT/CF_E_EXT
    • /1LT/CF_E_EXT_IT
    • /1LT/CF_E_COPA
  • If any job fails due to an outage in the staging database, identify whether the document lines with unique identifiers exist in all the FIN% tables except the FIN_HEADER table, and then do one of the following:
    • If exists, delete the lines of the documents from all the FIN% tables.
    • If such documents do not exist, perform the following:
      1. List the documents with the status N and check if the amounts are balanced with the credit and debit entries using the following query:

        SELECT unique_id

        FROM (SELECT SUM(Trunc(amt_document_curr, 2)) AMTDCR,

        SUM(Trunc(amt_comp_code_curr, 2)) ACCR,

        unique_id

        FROM fin_acct

        WHERE (CASE

        WHEN status IN ('N', 'P') THEN status

        ELSE NULL

        END) IN ('N', 'P')

        GROUP BY unique_id

        UNION ALL

        SELECT SUM(Trunc(amt_document_curr, 2)),

        SUM(Trunc(amt_comp_code_curr, 2)),

        unique_id

        FROM fin_debit

        WHERE (CASE

        WHEN status IN ('N', 'P') THEN status

        ELSE NULL

        END) IN ('N', 'P')

        GROUP BY unique_id

        UNION ALL

        SELECT SUM(Trunc(amt_document_curr, 2)),

        SUM(Trunc(amt_comp_code_curr, 2)),

        unique_id

        FROM fin_credit

        WHERE (CASE

        WHEN status IN ('N', 'P') THEN status

        ELSE NULL

        END) IN ('N', 'P')

        GROUP BY unique_id

        UNION ALL

        SELECT SUM(Trunc(amt_prdtax_document_curr, 2)),

        SUM(Trunc(amt_prdtax_comp_code_curr, 2)),

        unique_id

        FROM fin_prdtax

        WHERE (CASE

        WHEN status IN ('N', 'P') THEN status

        ELSE NULL

        END) IN ('N', 'P')

        GROUP BY unique_id) S

        GROUP BY unique_id

        HAVING SUM(amtdcr) <> 0

        OR SUM(accr) <> 0;

      2. If amounts are not balanced, delete the document related lines from the FIN% tables and run the reprocess job for the required documents.

Creating Read-Only Access for Drilldown and Reconciliation

Perform the following steps to create a read-only access for drilldown and reconciliation options for SQL Server as the staging database:

  1. Update the SDA_ACCESS.sql file placed in the Sourceconnect/SCT/Stage/MSSQL folder to create a user login with read-only access, in addition to the usual sa login. The scripts provide access to drilldown and reconciliation specific scripts.
  2. After updating the SDA_ACCESS.sql file with the required information, trigger the SDA_ACCESS.bat file from the command prompt to run the updated SQL file.

Was this article helpful?

We're sorry to hear that.