1. Tables and Views for Financials
  2. FV_CROSS_SERVICE_RECEIPTS_ALL

FV_CROSS_SERVICE_RECEIPTS_ALL

This table contains staged Treasury IAI Collections File inbound records for cross-servicing collections, reversals, adjustments, accounts receivable receipts, debit memos, and receipt application processing.

Details

  • Schema: FUSION

  • Object owner: FV

  • Object type: TABLE

  • Tablespace: FUSION_TS_TX_DATA

Primary Key

Name Columns

FV_CROSS_SERVICE_RECEIPTS_PK

CROSS_SERVICE_RECEIPT_ID

Columns

Name Datatype Length Precision Not-null Comments
CROSS_SERVICE_RECEIPT_ID NUMBER 18 Yes Unique identifier of the inbound cross-servicing receipt record.
ORG_ID NUMBER 18 Yes Indicates the identifier of the business unit associated to the row.
FILE_NAME VARCHAR2 240 Yes Name of the Treasury IAI collections source file.
FILE_DATE DATE Yes The date the IAI collections file was generated.
RECORD_TYPE_CODE VARCHAR2 30 Yes Code that identifies the IAI collections record, for example, I (file header), 0, 1, or 2 (detail type), C (batch control), or Z (trailer).
PROCESS_STATUS_CODE VARCHAR2 30 Yes Code that identifies the process status for validation, receipt or debit memo creation, application, adjustment, or reversal processing. This is based on the lookup type ORA_FV_FED_PROCESS_STATUS. Valid values are U (Unprocessed), P(Processed), E (Error), and I (Invalid).
FAST_CODE VARCHAR2 30 Yes The Treasury Department FAST code. For Treasury Disbursing Office, this is the first 2 digits of the Agency Location Code.
AGENCY_LOCATION_CODE VARCHAR2 30 Yes Eight-digit Agency Location Code identifying the agency or area. The first 2 digits identify the agency or department and the next 2 identify the bureau.
STATION_CODE VARCHAR2 30 Yes The program code used on IAI records that consists of 4 or 5 characters.
DEBTOR_TAX_IDENTIFIER_NUMBER VARCHAR2 25 Tax Identification Number (TIN) for the debtor, when available. For cross-servicing, this is optional and must not contain all zeros or all nines.
COLLECTION_AMOUNT NUMBER Signed collection, adjustment, or reversal amount based on the record type. This is stored without currency symbols or decimal characters in the source file.
FEE_AMOUNT NUMBER Total fee amount applied to the collection; full reversals include the fee amount, while most partial reversals are zero-filled.
DEBTOR_LAST_NAME_OR_BUSINESS VARCHAR2 360 Debtor last name for an individual or business name for a business debtor, as submitted by the agency.
DEBTOR_FIRST_NAME VARCHAR2 150 Individual debtor first name as submitted by the agency. This is required for individual debtor records.
AGENCY_DEBT_REF_NUMBER VARCHAR2 30 A number that identifies the creditor agency's debt reference. This is a combination of the first 11 digits of the transaction number and 18 digits of the customer transaction ID separated by an underscore (_). If the customer transaction ID is less than 18 digits, the remaining characters from the transaction number are used.
AGENCY_DEBTOR_REF_NUMBER VARCHAR2 30 The debtor identifier assigned by the creditor agency to reference the debtor.
PAYMENT_DATE DATE Deposit date of the payment or effective date of the reversal or transaction.
APPLIED_PRINCIPAL_AMOUNT NUMBER Signed transaction amount applied to the principal balance component.
APPLIED_INTEREST_AMOUNT NUMBER Signed transaction amount applied to the interest balance component.
APPLIED_PENALTY_AMOUNT NUMBER Signed transaction amount applied to the penalty balance component.
APPLIED_ADMIN_COSTS NUMBER Signed transaction amount applied to the administrative cost balance component.
APPLIED_OVERAGE_AMOUNT NUMBER Signed overage amount reported on the collections transaction.
APPLIED_DMS_FEE_AMOUNT NUMBER Signed transaction amount applied to the Debt Management Services (DMS) fee assessed for the transaction when charged to the debtor. This value is zero when fees are charged to the creditor agency.
APPLIED_PCA_FEE_AMOUNT NUMBER Signed transaction amount applied to the Private Collection Agency (PCA) fee assessed for the transaction when charged to the debtor. This value is zero when fees are charged to the creditor agency.
APPLIED_TOP_FEE_AMOUNT NUMBER Signed transaction amount applied to the Treasury Offset Program (TOP) fee assessed for the transaction when charged to the debtor. This value is zero when fees are charged to the creditor agency.
APPLIED_DOJ_FEE_AMOUNT NUMBER Signed transaction amount applied to the Department of Justice (DOJ) fee assessed for the transaction when charged to the debtor. This value is zero when fees are charged to the creditor agency.
CHARGED_DMS_FEE_AMOUNT NUMBER Debt Management Services (DMS) fee charged on the transaction.
CHARGED_PCA_FEE_AMOUNT NUMBER Private Collection Agency (PCA) fee charged on the transaction.
CHARGED_TOP_FEE_AMOUNT NUMBER Treasury Offset Program (TOP) fee charged on the transaction.
CHARGED_DOJ_FEE_AMOUNT NUMBER Department of Justice (DOJ) fee charged on the transaction.
IPAC_NET_TRANSFER_AMOUNT NUMBER Net amount transferred to the agency; Transaction Amount minus Fee Amount equals Net Transfer Amount.
IPAC_TRANSACTION_DATE DATE IPAC transaction date provided in the inbound file.
IPAC_REFERENCE_NUMBER VARCHAR2 30 IPAC reference number reported on the collections file for Treasury payment reconciliation.
PAYMENT_ADJUSTMENT_REF_NUMBER VARCHAR2 30 Originating transaction ID for the payment or adjustment. This is populated with Agency, DMS/PCA, or TOP reference, as applicable.
REVERSAL_TRX_REF_NUMBER VARCHAR2 30 Originating transaction ID for the reversal.
PRINCIPAL_TAS VARCHAR2 35 Treasury Account Symbol associated with the principal component, when provided.
INTEREST_TAS VARCHAR2 35 Treasury Account Symbol associated with the interest component, when provided.
PENALTY_TAS VARCHAR2 35 Treasury Account Symbol associated with the penalty component, when provided.
ADMIN_COST_TAS VARCHAR2 35 Treasury Account Symbol associated with the administrative cost component, when provided.
COLLECTION_TOOL_CODE VARCHAR2 30 Indicates the collection source or tool, such as DOJ, PCA, DMS, TOP, or overage-related activity. Valid values are: J – DOJ Fee > O; J – DOJ and DMS fee <> 0; P – PCA fee>0; P – PCA and DMS fee <> 0; D – Overage amount <> 0; D – DMS fee <> and DOJ fee, PCA fee, TOP fee =0; T- TOP fee<> 0; T – TOP fee and DMS fee<> 0
RECORD_COUNT NUMBER 10 The cumulative record count for all records in Station.
COLLECTION_COUNT NUMBER 10 The cumulative record count for all collection records [Type Indicator = 1 or 2] for the Station.
REVERSAL_COUNT NUMBER 10 The cumulative record count for all Reversal records [Type Indicator = 0] current for the Station.
REVERSAL_AMOUNT NUMBER The cumulative total of all Reversal amounts [Type Indicator = 0] for the Station.
NET_COLLECTIONS_AMOUNT NUMBER Signed net collections or net transfer total for the station or file.
EXCESS_OFFSET_AMOUNT NUMBER Amount reflecting the net collections when the collection amount exceeds the reversal amount.
EXCESS_REVERSAL_AMOUNT NUMBER Amount reflecting the net reversals when the reversal amount exceeds the collection amount.
TOTAL_FEE_AMOUNT NUMBER Absolute value of the summed fee amounts across individual records for the station or file.
DEBT_MANAGEMENT_CYCLE VARCHAR2 6 Debt Management Services cycle based on the yearly Treasury Offset Program (TOP) cycle chart.
COLLECTION_CYCLE_END_DATE DATE Ending date of the collection cycle provided in the inbound file.
DEBT_MANAGEMENT_PROGRAM_YEAR VARCHAR2 4 Four-digit DMS program year reported in the control or trailer records.
CUSTOMER_TRX_ID NUMBER 18 Customer Transaction ID associated with AGENCY_DEBTOR_REF_NUMBER.
DEBIT_MEMO_CUSTOMER_TRX_ID NUMBER 18 Customer transaction identifier of the debit memo created or used for fee or component processing.
CASH_RECEIPT_ID NUMBER 18 Fusion Receivables cash receipt identifier created or matched for the collections transaction.
RECEIVABLE_APPLICATION_ID NUMBER 18 Receipt application identifier created when the receipt is applied to an invoice, debit memo, or related target.
ADJUSTMENT_ID NUMBER 18 Identifier of the debit memo adjustment.
PROCESSED_DATE DATE Date the staged inbound record was processed successfully.
OBJECT_VERSION_NUMBER NUMBER 9 Yes Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried.
ATTRIBUTE_CATEGORY VARCHAR2 30 Descriptive Flexfield: structure definition of the user descriptive flexfield.
ATTRIBUTE1 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE2 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE3 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE4 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE5 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE6 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE7 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE8 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE9 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE10 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE11 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE12 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE13 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE14 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE15 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER1 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER2 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER3 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER4 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER5 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE1 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE2 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE3 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE4 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE5 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
CREATION_DATE TIMESTAMP Yes Who column: indicates the date and time of the creation of the row.
LAST_UPDATED_BY VARCHAR2 64 Yes Who column: indicates the user who last updated the row.
LAST_UPDATE_DATE TIMESTAMP Yes Who column: indicates the date and time of the last update of the row.
LAST_UPDATE_LOGIN VARCHAR2 32 Who column: indicates the session login associated to the user who last updated the row.
CREATED_BY VARCHAR2 64 Yes Who column: indicates the user who created the row.

Indexes

Index Uniqueness Tablespace Columns
FV_CROSS_SERVICE_RECEIPTS_N1 Non Unique FUSION_TS_TX_DATA FILE_NAME, FILE_DATE, AGENCY_DEBT_REF_NUMBER
FV_CROSS_SERVICE_RECEIPTS_N2 Non Unique FUSION_TS_TX_DATA FILE_NAME, FILE_DATE, AGENCY_DEBTOR_REF_NUMBER
FV_CROSS_SERVICE_RECEIPTS_N3 Non Unique FUSION_TS_TX_DATA PROCESS_STATUS_CODE
FV_CROSS_SERVICE_RECEIPTS_U1 Unique FUSION_TS_TX_DATA CROSS_SERVICE_RECEIPT_ID