Predefined Views

Oracle Blockchain Platform Digital Assets Edition provides the following predefined database views.

ACCOUNT_TRANSACTION_VIEW

This view provides a unified transaction history per account, capturing both debit (from_account_id) and credit (to_account_id) sides of a transaction. You can use this view to query all transactions linked to an account without scanning raw chaincode state data.

Confidential SQL Query, System Owner:

CREATE VIEW ACCOUNT_TRANSACTION_VIEW as
        SELECT
            tx."VALUEJSON".from_account_id AS "KEY",
            tx."VALUEJSON".transaction_id AS "TRANSACTION_ID",
            tx."VALUEJSON" AS "TRANSACTION_DETAILS",
            COALESCE(CAST(tx."VALUEJSON".blockNo AS NUMBER), tx."BLOCKNO") AS ORDER_BLOCKNO,
            COALESCE(CAST(tx."VALUEJSON".txnNo AS NUMBER), tx."TXNNO") AS ORDER_TXNNO
        FROM "<obpInstanceName>_<channelName>_state" tx
        WHERE tx."CHAINCODEID" = '<chaincodeName>'
        AND tx."VALUEJSON".assetType = 'otransaction'
        AND tx.KEY NOT LIKE '_obp2pc_'
        AND tx."VALUEJSON".transaction_type NOT IN ('EXECUTE_HOLD_RECEIVER', 'CREATE_ACCOUNT')
        AND tx."VALUEJSON".from_account_id IS NOT NULL
         
        UNION ALL
        SELECT
            tx."VALUEJSON".to_account_id AS "KEY",
            tx."VALUEJSON".transaction_id AS "TRANSACTION_ID",
            tx."VALUEJSON" AS "TRANSACTION_DETAILS",
            COALESCE(CAST(tx."VALUEJSON".blockNo AS NUMBER), tx."BLOCKNO") AS ORDER_BLOCKNO,
            COALESCE(CAST(tx."VALUEJSON".txnNo AS NUMBER), tx."TXNNO") AS ORDER_TXNNO
        FROM "<obpInstanceName>_<channelName>_state" tx
        WHERE tx."CHAINCODEID" = '<chaincodeName>'
        AND tx."VALUEJSON".assetType = 'otransaction'
        AND tx.KEY NOT LIKE '_obp2pc_'
        AND tx."VALUEJSON".transaction_type NOT IN ('EXECUTE_HOLD_SENDER', 'CREATE_ACCOUNT')
        AND tx."VALUEJSON".to_account_id IS NOT NULL

Data Sources: <obpInstanceName>_<channelName>_state, the primary state table holding chaincode data in JSON format

Key Logic:

• Includes only rows where the following statements are true.
  • CHAINCODEID = <chaincodeName>
  • assetType = 'otransaction'
  • KEY does not start with _obp2pc_
  • transaction_type is not EXECUTE_HOLD_RECEIVER, EXECUTE_HOLD_SENDER, or CREATE_ACCOUNT
• Extracts account involvement from both outgoing transactions (from_account_id) and incoming transactions (to_account_id).
• Uses COALESCE to ensure consistent ordering of columns (ORDER_BLOCKNO, ORDER_TXNNO) even if JSON values are missing.
• Combines both outgoing and incoming transactions with a UNION ALL.

Output Columns:

• KEY: Account ID associated with the transaction (sender or receiver).
• TRANSACTION_ID: Unique identifier of the transaction.
• TRANSACTION_DETAILS: Full JSON object of the transaction.
• ORDER_BLOCKNO: Block number used for ordering (from JSON if available, else block metadata).
• ORDER_TXNNO: Transaction number in the block (from JSON if available, else block metadata).

Data SourcesConfidential SQL Query, Participant Bank:

CREATE VIEW ACCOUNT_TRANSACTION_VIEW AS
        SELECT
            tx."VALUEJSON".from_account_id AS "KEY",
            tx."VALUEJSON".transaction_id AS "TRANSACTION_ID",
            tx."VALUEJSON" AS "TRANSACTION_DETAILS",
            COALESCE(CAST(tx."VALUEJSON".blockNo AS NUMBER), tx."BLOCKNO") AS ORDER_BLOCKNO,
            COALESCE(CAST(tx."VALUEJSON".txnNo AS NUMBER), tx."TXNNO") AS ORDER_TXNNO
        FROM "<obpInstanceName>_<channelName>_state" tx
        WHERE tx."CHAINCODEID" = '<chaincodeName>'
        AND tx."VALUEJSON".assetType = 'otransaction'
        AND tx.KEY NOT LIKE '_obp2pc_'
        AND tx."VALUEJSON".transaction_type NOT IN ('EXECUTE_HOLD_RECEIVER', 'CREATE_ACCOUNT')
        AND tx."VALUEJSON".from_account_id IS NOT NULL
         
        UNION ALL
        SELECT
            tx."VALUEJSON".to_account_id AS "KEY",
            tx."VALUEJSON".transaction_id AS "TRANSACTION_ID",
            tx."VALUEJSON" AS "TRANSACTION_DETAILS",
            COALESCE(CAST(tx."VALUEJSON".blockNo AS NUMBER), tx."BLOCKNO") AS ORDER_BLOCKNO,
            COALESCE(CAST(tx."VALUEJSON".txnNo AS NUMBER), tx."TXNNO") AS ORDER_TXNNO
        FROM "<obpInstanceName>_<channelName>_state" tx
        WHERE tx."CHAINCODEID" = '<chaincodeName>'
        AND tx."VALUEJSON".assetType = 'otransaction'
        AND tx.KEY NOT LIKE '_obp2pc_'
        AND tx."VALUEJSON".transaction_type NOT IN ('EXECUTE_HOLD_SENDER', 'CREATE_ACCOUNT')
        AND tx."VALUEJSON".to_account_id IS NOT NULL

Data Sources: <obpInstanceName>_<channelName>_state, the primary state table holding chaincode data in JSON format

Key Logic:

• Includes only rows where the following statements are true.
  • CHAINCODEID = <chaincodeName>
  • assetType = 'otransaction'
  • KEY does not start with _obp2pc_
  • transaction_type is not EXECUTE_HOLD_RECEIVER, EXECUTE_HOLD_SENDER, or CREATE_ACCOUNT
• Extracts account involvement from both outgoing transactions (from_account_id) and incoming transactions (to_account_id).
• Uses COALESCE to ensure consistent ordering of columns (ORDER_BLOCKNO, ORDER_TXNNO) even if JSON values are missing.
• Combines both outgoing and incoming transactions with a UNION ALL.

Output Columns:

• KEY: Account ID associated with the transaction (sender or receiver).
• TRANSACTION_ID: Unique identifier of the transaction.
• TRANSACTION_DETAILS: Full JSON object of the transaction.
• ORDER_BLOCKNO: Block number used for ordering (from JSON if available, else block metadata).
• ORDER_TXNNO: Transaction number in the block (from JSON if available, else block metadata).

TRANSACTION_PDC_VIEW

This view provides transaction-level balance and amount details from the private data collection. It ensures that only the latest record per transaction is retrieved.

Confidential SQL Query, System Owner:

CREATE VIEW TRANSACTION_PDC_VIEW as
        SELECT
            sub."VALUEJSON".transaction_id AS transaction_id,
            sub."CHAINCODEID",
            sub."VALUEJSON".amount AS quantity,
            sub."VALUEJSON".from_account_balance AS from_account_balance,
            sub."VALUEJSON".from_account_onhold_balance AS from_account_onhold_balance,
            sub."VALUEJSON".to_account_balance AS to_account_balance,
            sub."VALUEJSON".to_account_onhold_balance AS to_account_onhold_balance
        FROM (
            SELECT
                hist."VALUEJSON",
                hist."CHAINCODEID",
                ROW_NUMBER() OVER (
                    PARTITION BY hist."VALUEJSON".transaction_id
                    ORDER BY hist.TXNTIMESTAMP DESC
                ) AS rn
            FROM "<obpInstanceName>_<channelName>_hist" hist
            WHERE hist."CHAINCODEID" = '${implicitPDCName}'
            AND hist."VALUEJSON".assetType = 'otransactionpdc'
            AND hist."KEY" NOT LIKE '_obp2pc_staged_%'
        ) sub
        where rn = 1
UNION ALL
SELECT "TRANSACTION_ID","CHAINCODEID","QUANTITY","FROM_ACCOUNT_BALANCE","FROM_ACCOUNT_ONHOLD_BALANCE","TO_ACCOUNT_BALANCE","TO_ACCOUNT_ONHOLD_BALANCE" FROM "$TRANSACTION_PDC_VIEW"@${org1Name}_LINK
UNION ALL
SELECT "TRANSACTION_ID","CHAINCODEID","QUANTITY","FROM_ACCOUNT_BALANCE","FROM_ACCOUNT_ONHOLD_BALANCE","TO_ACCOUNT_BALANCE","TO_ACCOUNT_ONHOLD_BALANCE" FROM "$TRANSACTION_PDC_VIEW"@${org2Name}_LINK

Purpose: Aggregates data across the organization through database links.

Data Sources:

• <obpInstanceName>_<channelName>_hist: the historical chaincode data table containing private data collection records
• $TRANSACTION_PDC_VIEW@${org1Name}_LINK: Remote view from organization 1 accessed via database link.
• $TRANSACTION_PDC_VIEW@${org2Name}_LINK: Remote view from organization 2 accessed via database link.

Key Logic:

• Filters only records where the following statements are true.
  • CHAINCODEID = ${implicitPDCName}
  • assetType = 'otransactionpdc'
  • KEY does not start with _obp2pc_staged_.
• Uses ROW_NUMBER() window function.
  • Partitions by transaction_id.
  • Orders by TXNTIMESTAMP DESC.
  • Keeps only the latest record (rn = 1).
• Combines data from the local private data collection history table, organization 1's private data collection view (via database link), and organization 2's private data collection view (via database link).

Output Columns:

• TRANSACTION_ID: Unique identifier of the transaction.
• CHAINCODEID: Identifier of the chaincode associated with the PDC record.
• QUANTITY: Transaction amount or quantity transferred.
• FROM_ACCOUNT_BALANCE: Balance of the sender account after the transaction.
• FROM_ACCOUNT_ONHOLD_BALANCE: On-hold balance of the sender account after the transaction.
• TO_ACCOUNT_BALANCE: Balance of the receiver account after the transaction.
• TO_ACCOUNT_ONHOLD_BALANCE: On-hold balance of the receiver account after the transaction.

Confidential SQL Query, Participant Bank:

CREATE VIEW TRANSACTION_PDC_VIEW as
        SELECT
            sub."VALUEJSON".transaction_id AS transaction_id,
            sub."CHAINCODEID",
            sub."VALUEJSON".amount AS quantity,
            sub."VALUEJSON".from_account_balance AS from_account_balance,
            sub."VALUEJSON".from_account_onhold_balance AS from_account_onhold_balance,
            sub."VALUEJSON".to_account_balance AS to_account_balance,
            sub."VALUEJSON".to_account_onhold_balance AS to_account_onhold_balance
        FROM (
            SELECT
                hist."VALUEJSON",
                hist."CHAINCODEID",
                ROW_NUMBER() OVER (
                    PARTITION BY hist."VALUEJSON".transaction_id
                    ORDER BY hist.TXNTIMESTAMP DESC
                ) AS rn
            FROM "<obpInstanceName>_<channelName>_hist" hist
            WHERE hist."CHAINCODEID" = '<implicitPDCName>'
            AND hist."VALUEJSON".assetType = 'otransactionpdc'
            AND hist."KEY" NOT LIKE '_obp2pc_staged_%'
        ) sub
        where rn = 1

Data Sources:

• <obpInstanceName>_<channelName>_hist: the historical chaincode data table containing private data collection records

Key Logic:

• Filters only records where the following statements are true.
  • CHAINCODEID = ${implicitPDCName}
  • assetType = 'otransactionpdc'
  • KEY does not start with _obp2pc_staged_.
• Uses ROW_NUMBER() window function.
  • Partitions by transaction_id.
  • Orders by TXNTIMESTAMP DESC.
  • Keeps only the latest record (rn = 1).

Output Columns:

• TRANSACTION_ID: Unique identifier of the transaction.
• CHAINCODEID: Identifier of the chaincode associated with the PDC record.
• QUANTITY: Transaction amount or quantity transferred.
• FROM_ACCOUNT_BALANCE: Balance of the sender account after the transaction.
• FROM_ACCOUNT_ONHOLD_BALANCE: On-hold balance of the sender account after the transaction.
• TO_ACCOUNT_BALANCE: Balance of the receiver account after the transaction.
• TO_ACCOUNT_ONHOLD_BALANCE: On-hold balance of the receiver account after the transaction.

ACCOUNTS_VIEW

This view provides a consolidated view of accounts across organizations.

Confidential SQL Query, System Owner:

CREATE VIEW ACCOUNTS_VIEW as
          WITH hist_ranked AS (
      SELECT
          hist.valuejson,
          hist."VALUEJSON".account_id AS account_id,
          ROW_NUMBER() OVER (
              PARTITION BY hist."VALUEJSON".account_id
              ORDER BY hist.TXNTIMESTAMP DESC
          ) AS rn
      FROM "<obpInstanceName>_<channelName>_hist" hist
      WHERE hist."CHAINCODEID" = '<implicitPDCName>'
        AND hist."VALUEJSON".assetType = 'oaccountpdc'
      ),
      state_filtered AS (
          SELECT
              state.key,
              state.valueJSON
          FROM "<obpInstanceName>_<channelName>_state" state
          WHERE state."CHAINCODEID" = '${chaincodeName}'
          AND state."VALUEJSON".assetType = 'oaccount'
      ),
      token_details AS (
          SELECT
              state.key,
              state.valueJson,
              COALESCE ( TO_NUMBER(JSON_VALUE(valuejson, '$.divisible.decimal' RETURNING VARCHAR2)), 0) AS decimal_value
          FROM "<obpInstanceName>_<channelName>_state" state
          WHERE state."CHAINCODEID" = '<chaincodeName>'
          AND state."VALUEJSON".assetType = 'otoken'
      )
      SELECT
          s.key as account_id,
          s.valueJSON.org_id AS org_id,
          h.valuejson.user_id AS user_id,
          h.valuejson.custom_account_id AS custom_account_id,
          s.valueJSON.token_id AS token_id,
          t.decimal_value
      FROM state_filtered s
      LEFT JOIN hist_ranked h
          ON h.account_id = s.key
      AND h.rn = 1
      LEFT JOIN token_details t
          ON s.valueJSON.token_id = t.key
      WHERE h.valuejson is not null
UNION ALL
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID", "TOKEN_ID", "DECIMAL_VALUE" FROM "ACCOUNTS_VIEW"@<org1Name>_LINK WHERE "USER_ID" IS NOT NULL
UNION ALL
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID", "TOKEN_ID", "DECIMAL_VALUE" FROM "ACCOUNTS_VIEW"@$<org2Name>_LINK WHERE "USER_ID" IS NOT NULL

Purpose: This view provides a consolidated view of accounts across organizations, combining the following information.

• Current account state
• Latest account-related private data (private data collection)
• Token metadata such as decimal precision
• Cross-organization account details via database links

This information makes it possible to query account-level metadata (organization, user, token, decimals) in a single view.

Data Sources:

• <obpInstanceName>_<channelName>_hist: historical chaincode data, used to fetch the most recent account private data collection entry (oaccountpdc).
• <obpInstanceName>_<channelName>_state: Current state data for accounts (oaccount) and tokens (otoken).
• ACCOUNTS_VIEW@<org1Name>_LINK: Remote view from organization 1.
• ACCOUNTS_VIEW@<org2Name>_LINK: Remote view from organization 2.

Key Logic:

• hist_ranked CTE: Gets the latest private data collection record per account_id using ROW_NUMBER() with ORDER BY TXNTIMESTAMP DESC.
• state_filtered CTE: Selects accounts (oaccount) from the state table.
• token_details CTE: Retrieves token details (otoken) with decimal precision (default 0 if missing).
• Final selection joins: Account state (state_filtered) with latest private data collection details (hist_ranked) and token metadata (token_details).
• Ensures only accounts with associated private data collection (WHERE h.valuejson IS NOT NULL).
• Extends account data with database link unions from other organizations.

Output Columns:

• ACCOUNT_ID: Unique identifier of the account.
• ORG_ID: Organization ID associated with the account.
• USER_ID: User ID linked to the account (from private data collection).
• CUSTOM_ACCOUNT_ID: Custom user-defined account identifier.
• TOKEN_ID: Token identifier associated with the account.
• DECIMAL_VALUE: Token decimal precision (defaults to 0 if missing).

Confidential SQL Query, Participant Bank:

CREATE VIEW ACCOUNTS_VIEW AS
        WITH hist_ranked AS (
            SELECT
                hist.valuejson,
                hist."VALUEJSON".account_id AS account_id,
                ROW_NUMBER() OVER (
                    PARTITION BY hist."VALUEJSON".account_id
                    ORDER BY hist.TXNTIMESTAMP DESC
                ) AS rn
            FROM "<obpInstanceName>_<channelName>_hist" hist
            WHERE hist."CHAINCODEID" = '<implicitPDCName>'
            AND hist."VALUEJSON".assetType = 'oaccountpdc'
        ),
        state_filtered AS (
            SELECT
                state.key,
                state.valueJSON
            FROM "<obpInstanceName>_<channelName>_state" state
            WHERE state."CHAINCODEID" = '<chaincodeName>'
            AND state."VALUEJSON".assetType = 'oaccount'
        ),
        token_details AS (
            SELECT
                state.key,
                COALESCE (TO_NUMBER(JSON_VALUE(valuejson, '$.divisible.decimal' RETURNING VARCHAR2)), 0) AS decimal_value
            FROM "<obpInstanceName>_<channelName>_state" state
            WHERE state."CHAINCODEID" = '<chaincodeName>'
            AND state."VALUEJSON".assetType = 'otoken'
        )
        SELECT
            s.key AS account_id,
            s.valueJSON.org_id AS org_id,
            h.valuejson.user_id AS user_id,
            h.valuejson.custom_account_id AS custom_account_id,
            s.valueJSON.token_id AS token_id,
            t.decimal_value
        FROM state_filtered s
        LEFT JOIN hist_ranked h
            ON h.account_id = s.key
        AND h.rn = 1
        LEFT JOIN token_details t
            ON s.valueJSON.token_id = t.key

Purpose: This view provides a consolidated view of accounts across organizations, combining the following information.

• Current account state
• Latest account-related private data (private data collection)
• Token metadata such as decimal precision

This information makes it possible to query account-level metadata (organization, user, token, decimals) in a single view.

Data Sources:

• <obpInstanceName>_<channelName>_hist: historical chaincode data, used to fetch the most recent account private data collection entry (oaccountpdc).
• <obpInstanceName>_<channelName>_state: Current state data for accounts (oaccount) and tokens (otoken).

Key Logic:

• hist_ranked CTE: Gets the latest private data collection record per account_id using ROW_NUMBER() with ORDER BY TXNTIMESTAMP DESC.
• state_filtered CTE: Selects accounts (oaccount) from the state table.
• token_details CTE: Retrieves token details (otoken) with decimal precision (default 0 if missing).
• Final selection joins: Account state (state_filtered) with latest private data collection details (hist_ranked) and token metadata (token_details).
• Ensures only accounts with associated private data collection (WHERE h.valuejson IS NOT NULL).

Output Columns:

• ACCOUNT_ID: Unique identifier of the account.
• ORG_ID: Organization ID associated with the account.
• USER_ID: User ID linked to the account (from private data collection).
• CUSTOM_ACCOUNT_ID: Custom user-defined account identifier.
• TOKEN_ID: Token identifier associated with the account.
• DECIMAL_VALUE: Token decimal precision (defaults to 0 if missing).

ACCOUNTS_MOD

This view provides a structured representation of fungible token registry data, mapping organizations and users to their associated fungible token accounts and tokens. It simplifies access to account–token relationships stored in the chaincode state by flattening nested JSON arrays, making it easier for applications and analytics queries to consume.

Generic SQL Query, TypeScript:

`create view ACCOUNTS_MOD as SELECT s.blockno, s.txnno, s.key,
                s.valuejson.assetType, s.valueJson.org_id, s.valueJson.user_id,
                ft.account_id, ft.token_id
     
                FROM "<obpInstanceName>_<channelName>_state" s,
     
                json_table(s.valuejson, '$.associated_ft_accounts[*]'
                Columns(lng_number FOR ORDINALITY,
                account_id varchar2 (1000) Path '$.account_id' null on empty,
                token_id varchar2 (16) Path '$.token_id' null on empty)) ft
     
                where
                s.key like '%oftregistry%' AND s.chaincodeid = '<chaincodeName>'
     
                order by s.blockno DESC, s.txnno DESC`

Data Sources:

• <obpInstanceName>_<channelName>_state: The primary state table holding chaincode data in JSON format.

Key Logic:

• Filter by registry key: Includes only records where the key contains oftregistry.
• Filter by chaincode: Restricts results to the specified chaincode (<chaincodeName>).
• Flatten nested JSON: Uses JSON_TABLE to expand the associated_ft_accounts array into separate rows for each account-token pair.
  • account_id: Fungible token account identifier.
  • token_id: Token linked to the account.
  • lng_number: Ordinality (used internally to track array order).
• Ordering: Results are sorted by block number (blockno) and transaction number (txnno) in descending order, ensuring that the most recent associations are displayed first.

Output Columns:

• BLOCKNO: Block number where the registry update was recorded.
• TXNNO: Transaction number in the block.
• KEY: State key representing the fungible token registry entry.
• ASSETTYPE: Type of asset (expected: oftregistry).
• ORG_ID: Organization identifier from the JSON.
• USER_ID: User identifier associated with the registry record.
• ACCOUNT_ID: Extracted fungible token account ID from the nested array.
• TOKEN_ID: Extracted token ID linked to the fungible token account.

Generic SQL Query, Go:

`create view ACCOUNTS_MOD as SELECT s.blockno, s.txnno, s.key,
                s.valuejson.AssetType, s.valueJson.OrgId as org_id, s.valueJson.UserId as user_id,
                ft.account_id, ft.token_id
     
                FROM "<obpInstanceName>_<channelName>_state" s,
     
                json_table(s.valuejson, '$.AssociatedFtAccounts[*]'
                Columns(lng_number FOR ORDINALITY,
                account_id varchar2 (1000) Path '$.AccountId' null on empty,
                token_id varchar2 (16) Path '$.TokenId' null on empty)) ft
     
                where
                s.key like '%oftregistry%' AND s.chaincodeid = '<chaincodeName>'
     
                order by s.blockno DESC, s.txnno DESC`

This view is the same as the Generic SQL Query, TypeScript view. The only difference is in the JSON field naming convention, which uses Pascal case instead of lowercase.

• AssetType: previously assetType
• OrgId: previously org_id
• UserId: previously user_id
• AssociatedFtAccounts: previously associated_ft_accounts
• AccountId: previously account_id
• TokenId: previously token_id

Aliasing is applied (for example, OrgId AS org_id) to keep output column names consistent across views.

Confidential SQL Query, System Owner:

CREATE VIEW ACCOUNTS_MOD AS
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID","TOKEN_ID","DECIMAL_VALUE"
FROM (
    WITH hist_ranked AS (
        SELECT
            hist.valuejson,
            hist."VALUEJSON".account_id AS account_id,
            ROW_NUMBER() OVER (
                PARTITION BY hist."VALUEJSON".account_id
                ORDER BY hist.TXNTIMESTAMP DESC
            ) AS rn
        FROM "<obpInstanceName>_<channelName>_hist" hist
        WHERE hist."CHAINCODEID" = '<implicitPDCName>'
          AND hist."VALUEJSON".assetType = 'oaccountpdc'
    ),
    state_filtered AS (
        SELECT
            state.key,
            state.valueJSON
        FROM "<obpInstanceName>_<channelName>_state" state
        WHERE state."CHAINCODEID" = '<chaincodeName>'
          AND state."VALUEJSON".assetType = 'oaccount'
    ),
    token_details AS (
        SELECT
            state.key,
            state.valueJson,
            COALESCE(
                TO_NUMBER(JSON_VALUE(valuejson, '$.divisible.decimal' RETURNING VARCHAR2)),
                0
            ) AS decimal_value
        FROM "<obpInstanceName>_<channelName>_state" state
        WHERE state."CHAINCODEID" = '<chaincodeName>'
          AND state."VALUEJSON".assetType = 'otoken'
    )
    SELECT
        s.key AS account_id,
        s.valueJSON.org_id AS org_id,
        h.valuejson.user_id AS user_id,
        h.valuejson.custom_account_id AS custom_account_id,
        s.valueJSON.token_id AS token_id,
        t.decimal_value
    FROM state_filtered s
    LEFT JOIN hist_ranked h
        ON h.account_id = s.key
       AND h.rn = 1
    LEFT JOIN token_details t
        ON s.valueJSON.token_id = t.key
    WHERE h.valuejson IS NOT NULL
)
UNION ALL
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID","TOKEN_ID","DECIMAL_VALUE"
FROM "ACCOUNTS_MOD"@<org1Name>_LINK
UNION ALL
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID","TOKEN_ID","DECIMAL_VALUE"
FROM "ACCOUNTS_MOD"@<org2Name>_LINK
UNION ALL
SELECT "ACCOUNT_ID","ORG_ID","USER_ID","CUSTOM_ACCOUNT_ID","TOKEN_ID","DECIMAL_VALUE"
FROM "ACCOUNTS_MOD"@<org3Name>_LINK;

Data Sources:

• <obpInstanceName>_<channelName>_hist: History table storing the chronological changes of account private data (oaccountpdc).
• <obpInstanceName>_<channelName>_state: State table holding current chaincode state in JSON format, used for oaccount and otoken details.
• Remote organization views: Additional data from ACCOUNTS_MOD views in other organizations is included using database links:
  • "ACCOUNTS_MOD"@<org1Name>_LINK
  • "ACCOUNTS_MOD"@<org2Name>_LINK
  • "ACCOUNTS_MOD"@<org3Name>_LINK

Key Logic:

• Rank Historical Records: Uses ROW_NUMBER() to pick the latest oaccountpdc record for each account ID from the history table.
• Filter Current Accounts: Retrieves only records with assetType = 'oaccount' from the state table.
• Join Token Metadata: Adds token precision (decimal_value) by joining against otoken entries in the state table.
• Combine Account Details: Joins filtered account records with their most recent history entry and token details. Ensures that only accounts with valid historical data (h.valuejson IS NOT NULL) are included.
• Multi-Org Union: Uses UNION ALL to merge account data from the local organization with multiple remote organizations through database links. This allows a single unified query to retrieve all accounts across the network.

Output Columns:

• ACCOUNT_ID: Unique identifier of the account.
• ORG_ID: Organization ID that owns the account.
• USER_ID: User ID linked to the account (from latest historical record).
• CUSTOM_ACCOUNT_ID: Optional custom user-defined account identifier.
• TOKEN_ID: Token identifier associated with the account.
• DECIMAL_VALUE: Token decimal precision (defaults to 0 if missing).

Dynamic Generation: The multi-organization part is not hardcoded. In the script, the extra unions are generated dynamically based on the Participant_orgs_list.yml file. This ensures that the view adapts automatically depending on how many organizations are configured in the network. The example query shows three orgs for clarity, but in practice the number of unions depends on the size of orgsList. For additional details, see the previous information on the Participant_orgs_list.yml file in this topic.

Confidential SQL Query, Participant Bank:

CREATE VIEW ACCOUNTS_MOD AS
    WITH hist_ranked AS (
        SELECT
            hist.valuejson,
            hist."VALUEJSON".account_id AS account_id,
            ROW_NUMBER() OVER (
                PARTITION BY hist."VALUEJSON".account_id
                ORDER BY hist.TXNTIMESTAMP DESC
            ) AS rn
        FROM "<obpInstanceName>_<channelName>_hist" hist
        WHERE hist."CHAINCODEID" = '<implicitPDCName>'
          AND hist."VALUEJSON".assetType = 'oaccountpdc'
    ),
    state_filtered AS (
        SELECT
            state.key,
            state.valueJSON
        FROM "$<obpInstanceName>_<channelName>_state" state
        WHERE state."CHAINCODEID" = '<chaincodeName>'
          AND state."VALUEJSON".assetType = 'oaccount'
    ),
    token_details AS (
        SELECT
            state.key,
            COALESCE(
                TO_NUMBER(JSON_VALUE(valuejson, '$.divisible.decimal' RETURNING VARCHAR2)),
                0
            ) AS decimal_value
        FROM "${stateTableName}" state
        WHERE state."CHAINCODEID" = '${chaincodeName}'
          AND state."VALUEJSON".assetType = 'otoken'
    )
    SELECT
        s.key AS account_id,
        s.valueJSON.org_id AS org_id,
        h.valuejson.user_id AS user_id,
        h.valuejson.custom_account_id AS custom_account_id,
        s.valueJSON.token_id AS token_id,
        t.decimal_value
    FROM state_filtered s
    LEFT JOIN hist_ranked h
        ON h.account_id = s.key
       AND h.rn = 1
    LEFT JOIN token_details t
        ON s.valueJSON.token_id = t.key
    WHERE h.valuejson IS NOT NULL;

This view is the same as the Confidential SQL Query, System Owner, but it retrieves data only for the local organization. The main differences are the following:

• No UNION ALL with database links is used.
• The output includes accounts only from the current organization's state and history tables.
• The view provides a single-organization account registry, unlike the multi-organization variant which consolidates accounts across multiple organizations.

TRANSACTION_MOD

This view provides a detailed record of all blockchain transactions, capturing metadata, participants, amounts, and resulting balances. It simplifies access to transaction history by extracting structured fields directly from the history table, eliminating the need to manually parse raw JSON.

Generic SQL Query, TypeScript:

`create view TRANSACTION_MOD as SELECT h.blockno, h.txnno, h.key, h.txntimestamp,
            h.valuejson.assetType, h.valuejson.transaction_id,
            h.valuejson.from_account_id, h.valuejson.to_account_id,
            h.valuejson.transaction_type, h.valuejson.amount,
            h.valuejson.category as transaction_earmark,
            h.valuejson.from_account_balance, h.valuejson.to_account_balance
     
            FROM "<obpInstanceName>_<channelName>_hist" h
     
            where
            h.key like '%otransaction%' AND h.chaincodeid = '<chaincodeName>'
     
            order by h.blockno DESC, h.txnno DESC`

Data Sources: <obpInstanceName>_<channelName>_hist, the history table holding all transaction events in JSON format. Includes both the transaction details and associated metadata (block, transaction number, time stamp).

Key Logic:

• Includes only rows where the following statements are true.
  • CHAINCODEID = <chaincodeName>
  • KEY contains otransaction (filters for transaction-related records)
• Orders results in the following manner.
  • blockno DESC (latest block first)
  • txnno DESC (latest transaction within block first)

Output Columns:

• BLOCKNO: Block number in which the transaction was recorded.
• TXNNO: Transaction number in the block.
• KEY: Unique identifier of the transaction entry.
• TXNTIMESTAMP: Time stamp when the transaction was completed.
• ASSETTYPE: Type of asset for the transaction (for example, otransaction).
• TRANSACTION_ID: Unique identifier of the transaction.
• FROM_ACCOUNT_ID: Account ID from which the asset/value originated.
• TO_ACCOUNT_ID: Account ID to which the asset/value was transferred.
• TRANSACTION_TYPE: Type of the transaction (for example, transfer, debit, credit).
• AMOUNT: Amount involved in the transaction.
• TRANSACTION_EARMARK: Category or earmark for the transaction.
• FROM_ACCOUNT_BALANCE: Balance of the source account after the transaction.
• TO_ACCOUNT_BALANCE: Balance of the destination account after the transaction.

Generic SQL Query, Go:

`create view TRANSACTION_MOD as SELECT h.blockno, h.txnno, h.key, h.txntimestamp,
            h.valuejson.AssetType, h.valuejson.TransactionId as transaction_id,
            h.valuejson.FromAccountId as from_account_id, h.valuejson.ToAccountId as to_account_id,
            h.valuejson.TransactionType as transaction_type, h.valuejson.Amount,
            h.valuejson.Category as transaction_earmark,
            h.valuejson.FromAccountBalance as from_account_balance, h.valuejson.ToAccountBalance as to_account_balance
     
            FROM "<obpInstanceName>_<channelName>_hist" h
     
            where
            h.key like '%otransaction%' AND h.chaincodeid = '<chaincodeName>'
     
            order by h.blockno DESC, h.txnno DESC`

This view is the same as the Generic SQL Query, TypeScript view. The only difference is in the JSON field naming convention, which uses Pascal case instead of lowercase.

• AssetType: previously assetType
• TransactionId: previously transaction_id
• FromAccountId: previously from_account_id
• ToAccountId: previously to_account_id
• TransactionType: previously transaction_type
• Amount: previously amount
• Category: previously category
• FromAccountBalance: previously from_account_balance
• ToAccountBalance: previously to_account_balance

Aliasing is applied (for example, TransactionId AS transaction_id) to keep output column names consistent across views, regardless of the JSON field naming convention.

Confidential SQL Query, System Owner:

`CREATE VIEW TRANSACTION_MOD AS
        SELECT
            COALESCE(CAST(sub."VALUEJSON".blockNo AS NUMBER), sub."BLOCKNO") AS BLOCKNO,
            COALESCE(CAST(sub."VALUEJSON".txnNo AS NUMBER), sub."TXNNO") AS TXNNO,
            sub.key,
            sub.txntimestamp,
            sub.valuejson.assetType,
            sub.valuejson.transaction_id,
            sub.valuejson.from_account_id,
            sub.valuejson.to_account_id,
            sub.valuejson.transaction_type,
            (TO_NUMBER(sub.quantity) / POWER(10, sub.decimal_value)) as amount,
            sub.valuejson.category as transaction_earmark,
            (TO_NUMBER(sub.from_account_balance) / POWER(10, sub.decimal_value)) as from_account_balance,
            (TO_NUMBER(sub.to_account_balance) / POWER(10, sub.decimal_value)) as to_account_balance
        from (
            SELECT
                hist.*,
                pdc.*,
                CASE
                    WHEN hist."VALUEJSON".from_account_id IS NOT NULL
                        THEN (SELECT DECIMAL_VALUE FROM "ACCOUNTS_MOD" WHERE ACCOUNT_ID = hist."VALUEJSON".from_account_id FETCH FIRST 1 ROW ONLY)
                    WHEN hist."VALUEJSON".to_account_id IS NOT NULL
                        THEN (SELECT DECIMAL_VALUE FROM "ACCOUNTS_MOD" WHERE ACCOUNT_ID = hist."VALUEJSON".to_account_id FETCH FIRST 1 ROW ONLY)
                    ELSE 0
                END AS decimal_value,
                ROW_NUMBER() OVER (
                    PARTITION BY hist."VALUEJSON".transaction_id
                    ORDER BY hist.TXNTIMESTAMP DESC
                ) AS rn
            FROM "<obpInstanceName>_<channelName>_hist" hist
            LEFT JOIN "<transactionPDCViewName>" pdc
                ON hist.key = pdc.TRANSACTION_ID
            WHERE hist."CHAINCODEID" = '<chaincodeName>'
            AND hist."VALUEJSON".assetType = 'otransaction'
            AND hist."KEY" NOT LIKE '_obp2pc_staged_%'
        ) sub
        where rn = 1`

Data Sources:

• <obpInstanceName>_<channelName>_hist: Historical chaincode table storing all transaction versions.
• <transactionPDCViewName>: View containing private data collection transaction details, joined to enrich transaction records.
• ACCOUNTS_MOD: Reference view used to fetch the correct decimal_value for account balances and transaction amounts.

Key Logic:

• Filters only rows where the following statements are true.
  • CHAINCODEID = <chaincodeName>
  • assetType = 'otransaction'
  • KEY does not start with _obp2pc_staged_
• Uses ROW_NUMBER() partitioned by transaction_id (ordered by txntimestamp DESC) to ensure that only the latest record per transaction is included.
• Dynamically fetches decimal_value from ACCOUNTS_MOD, based on whether the transaction involves a from_account_id or to_account_id.
• Amounts and balances (amount, from_account_balance, to_account_balance) are scaled by decimal precision using: value / POWER(10, decimal_value)
• Uses COALESCE on blockNo and txnNo to ensure ordering columns are always present, even if missing from JSON.

Output Columns:

• BLOCKNO: Block number for ordering (from JSON if available, else from metadata).
• TXNNO: Transaction number in the block (from JSON if available, else from metadata).
• KEY: Transaction key identifier.
• TXNTIMESTAMP: Time stamp of the transaction.
• ASSETTYPE: Asset type (otransaction).
• TRANSACTION_ID: Unique identifier of the transaction.
• FROM_ACCOUNT_ID: Account ID from which tokens were sent.
• TO_ACCOUNT_ID: Account ID to which tokens were sent.
• TRANSACTION_TYPE: Type of the transaction (for example, transfer, hold, release).
• AMOUNT: Transaction amount, scaled by token decimal precision.
• TRANSACTION_EARMARK: Category or earmark of the transaction.
• FROM_ACCOUNT_BALANCE: Balance of the sender's account after transaction, scaled by precision.
• TO_ACCOUNT_BALANCE: Balance of the receiver's account after transaction, scaled by precision.

Confidential SQL Query, Participant Bank:

`CREATE VIEW TRANSACTION_MOD AS
        SELECT
            COALESCE(CAST(sub."VALUEJSON".blockNo AS NUMBER), sub."BLOCKNO") AS BLOCKNO,
            COALESCE(CAST(sub."VALUEJSON".txnNo AS NUMBER), sub."TXNNO") AS TXNNO,
            sub.key,
            sub.txntimestamp,
            sub.valuejson.assetType,
            sub.valuejson.transaction_id,
            sub.valuejson.from_account_id,
            sub.valuejson.to_account_id,
            sub.valuejson.transaction_type,
            (TO_NUMBER(sub.quantity) / POWER(10, sub.decimal_value)) as amount,
            sub.valuejson.category as transaction_earmark,
            (TO_NUMBER(sub.from_account_balance) / POWER(10, sub.decimal_value)) as from_account_balance,
            (TO_NUMBER(sub.to_account_balance) / POWER(10, sub.decimal_value)) as to_account_balance
        from (
            SELECT
                hist.*,
                pdc.*,
                CASE
                    WHEN hist."VALUEJSON".from_account_id IS NOT NULL
                        THEN (SELECT DECIMAL_VALUE FROM "ACCOUNTS_MOD" WHERE ACCOUNT_ID = hist."VALUEJSON".from_account_id FETCH FIRST 1 ROW ONLY)
                    WHEN hist."VALUEJSON".to_account_id IS NOT NULL
                        THEN (SELECT DECIMAL_VALUE FROM "ACCOUNTS_MOD" WHERE ACCOUNT_ID = hist."VALUEJSON".to_account_id FETCH FIRST 1 ROW ONLY)
                    ELSE 0
                END AS decimal_value,
                ROW_NUMBER() OVER (
                    PARTITION BY hist."VALUEJSON".transaction_id
                    ORDER BY hist.TXNTIMESTAMP DESC
                ) AS rn
            FROM "<obpInstanceName>_<channelName>_hist" hist
            LEFT JOIN "<transactionPDCViewName>" pdc
                ON hist.key = pdc.TRANSACTION_ID
            WHERE hist."CHAINCODEID" = '<chaincodeName>'
            AND hist."VALUEJSON".assetType = 'otransaction'
            AND hist."KEY" NOT LIKE '_obp2pc_staged_%'
        ) sub
        where rn = 1`

Data Sources:

• <obpInstanceName>_<channelName>_hist: Historical chaincode table containing all transaction versions in JSON format.
• <transactionPDCViewName>: Private data collection view that holds transaction-level private data. It is joined with the history to enrich the transaction record.
• ACCOUNTS_MOD: View used to retrieve the correct token decimal precision (decimal_value) for accounts involved in transactions.

Key Logic:

• Filters only rows where the following statements are true.
  • CHAINCODEID = <chaincodeName>
  • assetType = 'otransaction'
  • Excludes staged transactions (KEY NOT LIKE '_obp2pc_staged_%')
• Decimal scaling: Fetches decimal_value dynamically from ACCOUNTS_MOD, based on from_account_id or to_account_id. Applies scaling to amount, from_account_balance, and to_account_balance using: value / POWER(10, decimal_value)
• Record selection: Uses ROW_NUMBER() partitioned by transaction_id (latest by TXNTIMESTAMP) to ensure that only the latest version of each transaction is returned.
• Data normalization: COALESCE is applied on blockNo and txnNo to ensure consistency even if the values are missing in the JSON payload.

Output Columns:

• BLOCKNO: Block number used for ordering (from JSON if available, else from metadata).
• TXNNO: Transaction number in the block (from JSON if available, else from metadata).
• KEY: Transaction key identifier.
• TXNTIMESTAMP: Time stamp of the transaction.
• ASSETTYPE: Asset type (otransaction).
• TRANSACTION_ID: Unique identifier of the transaction.
• FROM_ACCOUNT_ID: Account ID sending tokens.
• TO_ACCOUNT_ID: Account ID receiving tokens.
• TRANSACTION_TYPE: Type of the transaction (for example, transfer, hold, release).
• AMOUNT: Transaction amount, normalized using token decimal precision.
• TRANSACTION_EARMARK: Category or earmark for the transaction.
• FROM_ACCOUNT_BALANCE: Sender account's balance after the transaction (precision-adjusted).
• TO_ACCOUNT_BALANCE: Receiver account's balance after the transaction (precision-adjusted).

ACCOUNTS_TRANSACTION_MOD

This view extends transaction history by associating each transaction with the organization IDs of the participating accounts. It supports burn approvals, mint approvals, and regular transfers, ensuring that transactions include both account-level and organization-level context.

Generic SQL Query, TypeScript and Go:

`create view ACCOUNTS_TRANSACTION_MOD as SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            B.ORG_ID FROM_ORG_ID,
            B.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
            <transactionViewName> A, <accountsViewName> B
            WHERE A.FROM_ACCOUNT_ID=B.ACCOUNT_ID AND A.TRANSACTION_TYPE='APPROVE_BURN'
        UNION
        SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            C.ORG_ID FROM_ORG_ID,
            C.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
            <transactionViewName> A, <accountsViewName> C
            WHERE A.TO_ACCOUNT_ID=C.ACCOUNT_ID AND A.TRANSACTION_TYPE='APPROVE_MINT'
        UNION
        SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            B.ORG_ID FROM_ORG_ID,
            C.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
            <transactionViewName> A, <accountsViewName> B, <accountsViewName> C
            WHERE A.FROM_ACCOUNT_ID=B.ACCOUNT_ID AND A.TO_ACCOUNT_ID=C.ACCOUNT_ID
        ORDER BY BLOCKNO DESC`

Data Sources:

• <transactionViewName> (A): Provides core transaction details such as IDs, block numbers, accounts, amounts, and timestamps.
• <accountsViewName> (B, C): Maps accounts (ACCOUNT_ID) to organizations (ORG_ID) for both sender and receiver.

Key Logic: The view is built using a UNION of three cases:

1. APPROVE_BURN
   • Join on FROM_ACCOUNT_ID = B.ACCOUNT_ID.
   • Assign B.ORG_ID to both FROM_ORG_ID and TO_ORG_ID.
2. APPROVE_MINT
   • Join on TO_ACCOUNT_ID = C.ACCOUNT_ID.
   • Assign C.ORG_ID to both FROM_ORG_ID and TO_ORG_ID.
3. Regular Transfers
   • Join on FROM_ACCOUNT_ID = B.ACCOUNT_ID and TO_ACCOUNT_ID = C.ACCOUNT_ID.
   • Assign B.ORG_ID as FROM_ORG_ID and C.ORG_ID as TO_ORG_ID.

Finally, the results are ordered by BLOCKNO DESC to prioritize the latest transactions.

Output Columns:

• TRANSACTION_ID: Unique identifier of the transaction.
• BLOCKNO: Block number in which the transaction was recorded.
• FROM_ACCOUNT_ID: Account ID of the sender.
• TO_ACCOUNT_ID: Account ID of the receiver.
• EARMARK: Transaction earmark category (alias of TRANSACTION_EARMARK).
• FROM_ORG_ID: Organization ID of the sender's account (or same as TO for burn).
• TO_ORG_ID: Organization ID of the receiver's account (or same as FROM for mint).
• TRANSACTION_TYPE: Type of the transaction (APPROVE_BURN, APPROVE_MINT, or transfer).
• AMOUNT: Transaction amount.
• CRDATE: Transaction creation date (from TXNTIMESTAMP).

Confidential SQL Query, System Owner and Participant Bank:

`CREATE VIEW ACCOUNTS_TRANSACTION_MOD AS
        SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            B.ORG_ID FROM_ORG_ID,
            B.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
            "<transactionViewName>" A, "<accountsViewName>" B
            WHERE A.FROM_ACCOUNT_ID=B.ACCOUNT_ID AND A.TO_ACCOUNT_ID IS NULL AND A.TRANSACTION_TYPE != 'CREATE_ACCOUNT'
        UNION
        SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            C.ORG_ID FROM_ORG_ID,
            C.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
            "<transactionViewName>" A, "<accountsViewName>" C
            WHERE A.TO_ACCOUNT_ID=C.ACCOUNT_ID AND A.FROM_ACCOUNT_ID IS NULL
        UNION
        SELECT DISTINCT
            TRANSACTION_ID,
            A.BLOCKNO,
            FROM_ACCOUNT_ID,
            TO_ACCOUNT_ID,
            A.TRANSACTION_EARMARK EARMARK,
            B.ORG_ID FROM_ORG_ID,
            C.ORG_ID TO_ORG_ID,
            TRANSACTION_TYPE,
            AMOUNT,
            A.TXNTIMESTAMP CRDATE
        FROM
        "<transactionViewName>" A, "<accountsViewName>" B, "<accountsViewName>" C
            WHERE A.FROM_ACCOUNT_ID=B.ACCOUNT_ID AND A.TO_ACCOUNT_ID=C.ACCOUNT_ID
        ORDER BY BLOCKNO DESC

Data Sources:

• <transactionViewName> (A): Provides transaction-level data (IDs, block numbers, account references, amounts, timestamps).
• <accountsViewName> (B, C): Provides mapping between ACCOUNT_ID and ORG_ID.

Key Logic: The view uses UNION to merge results from three transaction patterns:

1. Sender-only Transactions (for example, outgoing or burn-like cases)
   • Condition: A.FROM_ACCOUNT_ID = B.ACCOUNT_ID and A.TO_ACCOUNT_ID IS NULL.
   • Excludes CREATE_ACCOUNT transaction type.
   • Both FROM_ORG_ID and TO_ORG_ID are derived from the sender's organization (B.ORG_ID).
2. Receiver-only Transactions (for example, incoming or mint-like cases)
   • Condition: A.TO_ACCOUNT_ID = C.ACCOUNT_ID and A.FROM_ACCOUNT_ID IS NULL.
   • Both FROM_ORG_ID and TO_ORG_ID are derived from the receiver's organization (C.ORG_ID).
3. Standard Transfers (transactions with both sender and receiver)
   • Condition: A.FROM_ACCOUNT_ID = B.ACCOUNT_ID and A.TO_ACCOUNT_ID = C.ACCOUNT_ID.
   • FROM_ORG_ID comes from sender's organization (B.ORG_ID), TO_ORG_ID from receiver's organization (C.ORG_ID).

Finally, the results are sorted in descending order of BLOCKNO to prioritize latest transactions.

Output Columns:

• TRANSACTION_ID: Unique identifier of the transaction.
• BLOCKNO: Block number where the transaction is recorded.
• FROM_ACCOUNT_ID: Account ID of the sender (may be NULL for mint).
• TO_ACCOUNT_ID: Account ID of the receiver (may be NULL for burn).
• EARMARK: Transaction earmark (alias of TRANSACTION_EARMARK).
• FROM_ORG_ID: Organization ID of the sender (or same as receiver if sender is NULL).
• TO_ORG_ID: Organization ID of the receiver (or same as sender if receiver is NULL).
• TRANSACTION_TYPE: Type of the transaction (for example, transfer, mint, burn, and so on).
• AMOUNT: Transaction amount.
• CRDATE: Transaction creation time stamp (TXNTIMESTAMP).