Results for “igs_as_order_status”

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

Overview

IGS_AS_ORDER_PAYMENT_INFO_V is an Oracle E-Business Suite Advanced Student Systems view owned by the APPS schema. It consolidates order header, invoice, credit instrument, and application data for student document orders — transcripts, certifications, and similar academic outputs requested through the student self-service and administrative order flows. The view presents a single denormalized row per order/invoice combination, joining payment application and credit card details to the originating order header.

Because the view fabricates translated lookup meanings for order status, request type, and credit instrument, it is frequently the target of ad hoc reporting, custom concurrent programs, and outbound integrations that must present human-readable status and payment values without re-implementing lookup resolution logic. The search term "igs_as_doc_reqtype" corresponds directly to the LKUP2 join in the view definition, where HDR.REQUEST_TYPE is resolved against IGS_LOOKUPS_VIEW with LOOKUP_TYPE = 'IGS_AS_DOC_REQTYPE'. This lookup determines the document/request type classification displayed for each academic order.

Underlying Base Objects

The view definition references several IGS (Advanced Student Systems) transactional tables and the shared lookup view. The documented base objects are listed below.

• IGS_AS_ORDER_HDR — the master order header alias HDR, supplying order number, status, person, address, contact, fee, request type, submit method, and invoice linkage.
• IGS_FI_INV_INT_ALL — the invoice/charge interface alias CH, supplying bill date, invoice amount, amount due, and bill number.
• IGS_FI_CREDITS_ALL — the credits alias CR, supplying credit instrument and credit card approval code.
• IGS_FI_APPLICATIONS — aliased twice (AP as an aggregate subquery and AP2 as a direct join) to derive applied amounts, application dates, and the credit-to-invoice association.
• IGS_LOOKUPS_VIEW — aliased LKUP1, LKUP2, and LKUP3 to resolve meanings for IGS_AS_ORDER_STATUS, IGS_AS_DOC_REQTYPE, and IGS_FI_CREDIT_INSTRUMENT respectively.

The join is driven from the order header outward: HDR.PERSON_ID matches CH.PERSON_ID and CR.PARTY_ID, HDR.INVOICE_ID matches CH.INVOICE_ID and the aggregated application invoice, and AP2 links invoice to credit. Lookup joins are outer (+) so orders without a matching lookup code still appear.

Key Columns

The projection exposes order identity and lifecycle columns, party contact details, financial totals, and coded/translated fields.

• ORDER_NUMBER, ORDER_STATUS, MEANING (LKUP1) — order identifier and the decoded order status description.
• REQUEST_TYPE, MEANING (LKUP2) — the requested document/request type and its IGS_AS_DOC_REQTYPE meaning.
• SUBMIT_METHOD, DATE_COMPLETED — how the request was submitted and when it completed.
• PERSON_ID, ADDR_LINE_1..4, CITY, STATE, PROVINCE, COUNTY, COUNTRY, POSTAL_CODE, EMAIL_ADDRESS, PHONE_*, FAX_* — recipient contact and mailing data.
• DELIVERY_FEE, ORDER_FEE — order-level charges.
• INVOICE_ID, BILL_NUMBER, BILL_DATE, INVOICE_AMOUNT, INVOICE_AMOUNT_DUE — invoice reference and balance data.
• CREDIT_INSTRUMENT, MEANING (LKUP3), CREDIT_CARD_APPROVAL_CODE, AMOUNT_APPLIED, APPLY_DATE — payment instrument, authorization, and applied amounts.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include reporting orders by document request type, reconciling applied payments against invoice balances, and exporting contact/payment details for fulfillment. The query below filters by the request type lookup that motivated the original search.

SELECT order_number, meaning AS request_type, order_status, person_id, invoice_amount, amount_applied, bill_number FROM apps.igs_as_order_payment_info_v WHERE request_type = 'TRANSCRIPT' ORDER BY order_number;

A second pattern aggregates outstanding balances per person:

SELECT person_id, SUM(invoice_amount_due) amount_due, COUNT(*) orders FROM apps.igs_as_order_payment_info_v GROUP BY person_id HAVING SUM(invoice_amount_due) > 0;

A third joins back to IGS_LOOKUPS_VIEW to enumerate available REQUEST_TYPE codes when the displayed meaning must be validated against the lookup definition. Note that the aggregate application subquery collapses multiple applications to a single row per invoice using MAX/SUM, so consumers should treat APPLY_DATE and AMOUNT_APPLIED as consolidated values rather than per-application detail.