Results for “vat_code”

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

Overview

IGI_AP_TRAN_TEMP is a transient upgrade table owned by the IGI schema (IGI – Public Sector Financials International), a vertical product that extends Oracle Payables and Purchasing with public-sector tax and fund accounting capabilities. Per the ETRM metadata for 12.2.2, the table is described as an "Upgrade Table to store information related to Gross Base Vat feature." It is therefore not a transactional work table in day-to-day Payables processing, but a staging artifact used by the Gross Base VAT upgrade or conversion process, which computes and carries forward VAT recovery information for Payables distributions before that data is written into the permanent Payables and VAT tables. The table contains 14 documented columns and is classified as VALID. Because its purpose is migration and enrichment rather than durable transaction capture, its content is generally short-lived: it is populated by the conversion program, consumed by downstream update logic, and can be truncated once the upgrade or feature enablement completes.

Under the heuristic Data Vault classification mined from the FK structure, IGI_AP_TRAN_TEMP is identified as standalone. This is a modeling suggestion only: the table carries no child references of its own, and its foreign keys point outward to invocation and upgrade staging parents rather than forming a hub-link-satellite constellation internally. Practically, it behaves more like a satellite of the upgrade/invocation context than a hub, with INV_ID and DIST_ID acting as the join paths back to that context.

Key Information Stored

Although 14 columns exist, the following carry the substantive business meaning:

• INV_ID – Foreign key to JTF_XML_INVOCATIONS_B, identifying the XML invocation or program context under which the conversion row was generated.
• DIST_ID – Foreign key to OKL_UPG_TRNS_ACC_DSTRS_T, linking the row to the staged distribution record being upgraded (this is a full primary-key-based FK on OKL_UPG_TRNS_ACC_DSTRS_T).
• DIST_LINE_NUM – Distinguishes individual distribution lines belonging to the same distribution document, allowing a one-to-many relationship within DIST_ID.
• LINE_TYPE – Classifies the line (for example, tax versus non-tax or taxable versus exempt classification) so the upgrade logic can select the correct VAT treatment.
• VAT_CODE – The VAT/tax code assigned to the line, used as the driver for recoverability calculations.
• AMOUNT – The monetary amount on which VAT recovery is computed, typically the gross base for the Gross Base VAT feature.
• TAX_RECOVERY_RATE – The percentage recovery rate applied to the base amount.
• TAX_RECOVERABLE_FLAG – Indicates whether the tax is recoverable.
• TAX_RECOVERY_OVERRIDE_FLAG and TAX_CODE_OVERRIDE_FLAG – Flag columns recording whether the calculated recovery rate or the VAT code was manually overridden, ensuring override decisions survive the upgrade.
• JE_UK_VAT_LINK_TO_LINE_TO_LNK, JE_UK_VAT_LINK_TO_LINE_NUM, and JE_UK_VAT_RECLAIM_TYPE – United Kingdom VAT linkage attributes tying the line to its reclaim line and defining the reclaim type applied in the journal entry.
• ORG_ID – The operating unit (multi-org) identifier, establishing the business partition under which the row applies.

The metadata does not document a surrogate primary key or unique index; the closest thing to a business key is the combination of DIST_ID, DIST_LINE_NUM and ORG_ID, which the modeler should validate against the live data dictionary before assuming uniqueness. No heuristic hub candidate is documented, reinforcing the standalone classification.

Common Use Cases and Queries

Typical uses center on pre- and post-upgrade validation and reconciliation of VAT recovery. A conversion analyst might verify that every staged distribution has a matching temporary row before running the permanent update:

SELECT d.dist_id, d.dist_line_num
FROM   okl_upg_trns_acc_dstrs_t d
WHERE  NOT EXISTS (SELECT 1
                   FROM   igi.igi_ap_tran_temp t
                   WHERE  t.dist_id = d.dist_id
                   AND    t.dist_line_num = d.dist_line_num);

Recovery-rate reporting by VAT code and operating unit is another common pattern:

SELECT org_id, vat_code, tax_recoverable_flag,
       SUM(amount), AVG(tax_recovery_rate)
FROM   igi.igi_ap_tran_temp
GROUP  BY org_id, vat_code, tax_recoverable_flag;

A third scenario compares the calculated rate against overridden lines, using TAX_RECOVERY_OVERRIDE_FLAG and TAX_CODE_OVERRIDE_FLAG to confirm that manual decisions were preserved. Finally, JE_UK_VAT_* columns support reconciliation of UK VAT reclaim lines back to their source distributions.

Related Objects

• JTF_XML_INVOCATIONS_B – referenced via INV_ID; identifies the invocation context that produced the conversion rows.
• OKL_UPG_TRNS_ACC_DSTRS_T – referenced via DIST_ID; the upgrade staging parent for distribution accounting.
• AP_INVOICE_DISTRIBUTIONS_ALL – the permanent Payables distribution table the upgrade ultimately feeds, joined by distribution identity.
• AP_TAX_CODES_ALL / AP_TAX_CODES_B – supply VAT_CODE definitions used in the recovery logic.
• IGI_AP_TRAN_UPG – the companion upgrade program/table that reads this staging data.
• AP_INVOICES_ALL – invoice header context for the underlying Payables transaction.
• IGI_TAX_RECOVERY_RATES – source of TAX_RECOVERY_RATE defaults and overrides.
• GL_JE_LINES – destination of the UK VAT reclaim linkage in journal entries.

Because the object is an internal upgrade artifact, Oracle Support documentation is sparse; treat the FK relationships above as the authoritative integration surface should you need to extend or troubleshoot the Gross Base VAT conversion.