Results for “jai_st_forms_dtl_grpiss_v”

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

Overview

The JAI_ST_FORMS_DTL_GRPISS_V view is a reporting object owned by the APPS schema within the JA – Asia/Pacific Localizations product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It consolidates detail lines from the Sales Tax (ST) Forms infrastructure used by Asia/Pacific statutory reporting, specifically the group issue (GRP/ISS) aggregation used to reconcile tax detail against invoices and receiving transactions. The view joins header, detail and tax definition tables into a single denormalized result set suitable for feeding formatted statutory forms and reconciliation reports. Its most distinguishing feature is the derived RCV_TRANSACTION_ID column: for vendor party types the value is suppressed (NULL), and for other party types the underlying receiving transaction identifier is propagated. This conditional logic separates vendor-driven tax lines from internal issue/receipt flows in the statutory output.

Underlying Base Objects

The view is defined over four documented base objects, all exposed through APPS synonyms:

• JAI_CMN_STFORM_HDRS_ALL (aliased A) – the ST form header, supplying ST_HDR_ID, PARTY_TYPE_FLAG and FORM_TYPE. The PARTY_TYPE_FLAG value ('V' for vendor, 'O' for other) drives the conditional RCV_TRANSACTION_ID logic.
• JAI_CMN_ST_FORM_DTLS (aliased B) – the form detail lines, contributing HEADER_ID, RCV_TRANSACTION_ID, INVOICE_ID, ISSUE_RECEIPT_FLAG, PO_NUM, DOC_TYPE, TAX_TARGET_AMOUNT and MATCHED_AMOUNT.
• JAI_CMN_TAXES_ALL (aliased C) – the tax definition, joined on TAX_ID and STFORM_TYPE to validate that the detail tax belongs to the form type.
• AP_INVOICES_ALL (aliased D) – referenced in a correlated EXISTS subquery for vendor rows, restricting results to invoices that are neither cancelled nor carrying a cancellation amount.

The join path is A.ST_HDR_ID = B.ST_HDR_ID and B.TAX_ID = C.TAX_ID with A.FORM_TYPE = C.STFORM_TYPE. Only issue lines (ISSUE_RECEIPT_FLAG = 'I') are retained.

Key Columns

• ST_HDR_ID – Primary key of the ST form header; groups all detail lines for a form instance.
• HEADER_ID – Identifier of the associated transaction/document header.
• RCV_TRANSACTION_ID – Receiving transaction identifier. For vendor party types the view returns NULL via DECODE; for other party types it exposes the stored receiving transaction reference. This is the column most frequently searched by users reconciling receiving activity to tax forms.
• FORM_TYPE – Statutory form classification, matched to the tax definition.
• INVOICE_ID – AP invoice identifier for vendor-side lines.
• ISSUE_RECEIPT_FLAG – Direction indicator; the view filters to 'I' (issue).
• PO_NUM – Purchase order number associated with the line.
• DOC_TYPE – Document type classification.
• INV_TAX_TARGET_AMT – Sum of TAX_TARGET_AMOUNT (NVL to zero).
• INV_MATCHED_AMT – Sum of MATCHED_AMOUNT (NVL to zero).

Aggregation is by ST_HDR_ID, HEADER_ID, the DECODE-derived RCV_TRANSACTION_ID, FORM_TYPE, INVOICE_ID, ISSUE_RECEIPT_FLAG, PO_NUM, DOC_TYPE and TAX_ID.

Common Use Cases and Queries

Typical usage includes statutory form generation, tax-to-receiving reconciliation, and drill-down from a receiving transaction to its ST form context. A representative query retrieving lines for a specific receiving transaction is:

• SELECT st_hdr_id, header_id, rcv_transaction_id, form_type, invoice_id, po_num, inv_tax_target_amt FROM apps.jai_st_forms_dtl_grpiss_v WHERE rcv_transaction_id = :p_rcv_transaction_id;
• Aggregation by form for reporting: SELECT form_type, SUM(inv_tax_target_amt), SUM(inv_matched_amt) FROM apps.jai_st_forms_dtl_grpiss_v GROUP BY form_type;
• Reconciliation of issue lines to purchase orders: SELECT po_num, header_id, rcv_transaction_id, inv_matched_amt FROM apps.jai_st_forms_dtl_grpiss_v WHERE po_num IS NOT NULL;

Because the view applies the issue filter and the vendor invoice validity test internally, callers need not re-apply these predicates. Note that vendor rows always return NULL for RCV_TRANSACTION_ID by design, so queries searching exclusively on that column return only non-vendor (party type 'O') records.