Results for “igs_fi_inv_int_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_FI_INV_INT_ALL is an interface (staging) table in the Oracle E-Business Suite Student System (IGS) product family, owned by the IGS schema. It stores invoice data generated by the invoice process, acting as the persistence and hand-off point between Oracle Student Financials fee calculation and downstream receivables, payment, and credit processing. In Oracle EBS 12.1.1 and 12.2.2, the object is documented as VALID with 77 columns and a single unique index, IGS_FI_INV_INT_ALL_U1, on INVOICE_ID. Its primary key constraint is IGS_FI_INV_INT_PK.

Because the table carries a large attribute set, an invoice identifier that is both a surrogate primary key and a business-key candidate, and multiple foreign-key relationships fanning out to fee, billing, party, and payment tables, the mined Data Vault classification is hub-leaning. In practice, this suggests treating INVOICE_ID as a durable hub key, with the descriptive and payment-oriented columns modeled as satellites and the numerous outbound relationships modeled as links to billing, subaccount, and payer hubs. This classification is a modeling suggestion derived from the constraint structure, not a shipped Data Vault design.

Key Information Stored

The 77 documented columns can be grouped into identification, financial, payment, fee-reference, and audit categories. The most significant are:

• INVOICE_ID — the surrogate primary key (IGS_FI_INV_INT_PK) and the column covered by the unique index IGS_FI_INV_INT_ALL_U1, making it the principal business-key candidate for the row.
• PERSON_ID — the student or payer, joined to HZ_PARTIES.
• INVOICE_NUMBER, INVOICE_CREATION_DATE, INVOICE_AMOUNT, INVOICE_AMOUNT_DUE, INVOICE_DESC — the core invoice identity and monetary values.
• BILL_ID, BILL_NUMBER, BILL_DATE, BILL_PAYMENT_DUE_DATE — the billing context, with BILL_ID referencing IGS_FI_BILL_ALL.
• SUBACCOUNT_ID — the student financials subaccount, referencing IGS_FI_SUBACCTS_ALL.
• FEE_TYPE, FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, COURSE_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE — the fee and enrollment attribution of the charge.
• CURRENCY_CD and EXCHANGE_RATE — currency conversion context for the invoice amounts.
• RECEIPT_METHOD_ID and RECEIPT_METHOD_NAME — payment instrument, referencing AR_RECEIPT_METHODS; companion payment columns include PAYMENT_METHOD, PAYMENT_STATUS, and CUSTOMER_BANK_ACCOUNT_ID.
• WAIVER_FLAG, WAIVER_REASON, WAIVER_NAME, OPTIONAL_FEE_FLAG — waiver and optional-fee indicators.
• STATUS, TERM_ID, ACCOUNT_ID, TRANSACTION_TYPE, ORG_ID — processing state, academic term, receivable account, and operating unit.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY — concurrent-program traceability and standard audit columns.

Common Use Cases and Queries

Typical usage centers on reconciliation of invoice generation, reporting on fees due by student, and diagnosing failures in downstream payment or credit processing. A representative query retrieves invoices for a student with their billing and subaccount context:

• SELECT i.INVOICE_ID, i.INVOICE_NUMBER, i.INVOICE_AMOUNT_DUE, i.STATUS, b.BILL_NUMBER, s.SUBACCOUNT_ID FROM IGS_FI_INV_INT_ALL i LEFT JOIN IGS_FI_BILL_ALL b ON b.BILL_ID = i.BILL_ID LEFT JOIN IGS_FI_SUBACCTS_ALL s ON s.SUBACCOUNT_ID = i.SUBACCOUNT_ID WHERE i.PERSON_ID = :person_id AND i.TERM_ID = :term_id
• Payment-status exceptions: filter on PAYMENT_STATUS, PAYMENT_SERVER_TXN_STATUS, or the BEP_ERROR_CODE / PAYMENT_SERVER_ERROR_CODE columns to isolate failed credit card transactions.
• Waiver reconciliation: select rows where WAIVER_FLAG = 'Y' and join to IGS_FI_INV_WAV_DET on INVOICE_ID.
• Concurrent-program audit: query by REQUEST_ID or PROGRAM_APPLICATION_ID and PROGRAM_ID to trace which invoice process populated a given batch.

Related Objects

The table is a referenced parent for many IGS financial objects and a child of several core Student System entities:

• HZ_PARTIES — joined via PERSON_ID for payer identity.
• IGS_FI_BILL_ALL — joined via BILL_ID for billing header detail.
• IGS_FI_SUBACCTS_ALL — joined via SUBACCOUNT_ID for receivable subaccount context.
• AR_RECEIPT_METHODS — joined via RECEIPT_METHOD_ID for payment method lookup.
• IGS_FI_APPLICATIONS — references IGS_FI_INV_INT_ALL.INVOICE_ID; applications applied against the invoice.
• IGS_FI_CREDITS_ALL — references SOURCE_INVOICE_ID; credits originating from the invoice.
• IGS_FI_REFUNDS and IGS_FI_OTC_CHARGES — both reference INVOICE_ID for refund and over-the-counter charge linkage.
• IGS_FI_INV_WAV_DET, IGS_FI_SPECIAL_FEES, IGS_FI_PP_STD_ATTRS, IGS_FI_BILL_TRNSCTNS, and IGS_AS_ORDER_HDR — additional INVOICE_ID (and INVOICE_CREDITACT_ID) dependents supporting waivers, special fees, payment-plan attributes, transactions, and order headers.