1. Tables and Views for SCM
  2. INV_CONS_ADVICE_TXNS_V

INV_CONS_ADVICE_TXNS_V

Details

  • Schema: FUSION

  • Object owner: INV

  • Object type: VIEW

Columns

Name

CONSUMPTION_HEADER_ID

CONSUMPTION_NUMBER

CONSUMPTION_ADVICE_GEN_DATE

LEGAL_ENTITY_ID

BUSINESS_UNIT_ID

VENDOR_ID

VENDOR_SITE_ID

AGREEMENT_HEADER_ID

AGREEMENT_LINE_ID

PO_SCHEDULE_ID

PO_HEADER_ID

PO_LINE_ID

CONSUMPTION_LINE_ID

CONSUMPTION_LINE_NUMBER

RECEIPT_NUM

SHIPMENT_HEADER_ID

RECEIPT_LINE_NUM

SHIPMENT_LINE_ID

INVENTORY_ORGANIZATION_ID

INVENTORY_ITEM_ID

TRANSACTION_DATE

TRANSACTION_TYPE_ID

CONSUMED_QUANTITY

CONSUMED_QUANTITY_UOM_CODE

SECONDARY_CONSUMED_QUANTITY

SECONDARY_QUANTITY_UOM_CODE

BILLED_QUANTITY

BILLED_AMOUNT

CURRENCY_CODE

CURRENCY_CONVERSION_RATE

CURRENCY_CONVERSION_DATE

CURRENCY_CONVERSION_TYPE

CONSUMPTION_UNIT_PRICE

CONSUMPTION_TAX_EXC_UNIT_PRICE

EXTENDED_PRICE

RCV_TRANSACTION_ID

REMAINING_QUANTITY

DEFAULT_TAXATION_COUNTRY

FIRST_PTY_REG_ID

THIRD_PTY_REG_ID

FINAL_DISCHARGE_LOCATION_ID

DOCUMENT_SUB_TYPE

TRX_BUSINESS_CATEGORY

USER_DEFINED_FISC_CLASS

TAX_INVOICE_NUMBER

TAX_INVOICE_DATE

PROD_FC_CATEG_ID

PRODUCT_CATEGORY

INTENDED_USE_CLASSIF_ID

PRODUCT_TYPE

ASSESSABLE_VALUE

TAX_CLASSIFICATION_CODE

SHIP_FROM_LOCATION_ID

OWNED_LINE_ID

Query

SQL_Statement

SELECT

ICAH.consumption_header_id CONSUMPTION_HEADER_ID,

ICAH.consumption_number CONSUMPTION_NUMBER,

ICAH.consumption_date CONSUMPTION_ADVICE_GEN_DATE,

ICAH.legal_entity_id LEGAL_ENTITY_ID,

ICAH.business_unit_id BUSINESS_UNIT_ID,

PSSAM.vendor_id VENDOR_ID,

Nvl(PSSAM.default_pay_site_id,ICAH.owning_entity_id) VENDOR_SITE_ID,

ICAH.agreement_header_id AGREEMENT_HEADER_ID,

ICAL.agreement_line_id AGREEMENT_LINE_ID,

ICAL.po_schedule_id PO_SCHEDULE_ID,

ICAL.po_header_id PO_HEADER_ID,

ICAL.po_line_id PO_LINE_ID,

ICAL.consumption_line_id CONSUMPTION_LINE_ID,

ICAL.consumption_line_number CONSUMPTION_LINE_NUMBER,

RSH.receipt_num RECEIPT_NUM,

ICAL.transaction_source_id SHIPMENT_HEADER_ID,

RSL.line_num RECEIPT_LINE_NUM,

ICAL.source_line_id SHIPMENT_LINE_ID,

ICAL.inventory_organization_id INVENTORY_ORGANIZATION_ID,

ICAL.inventory_item_id INVENTORY_ITEM_ID,

ICAL.transaction_date TRANSACTION_DATE,

ICAL.transaction_type_id TRANSACTION_TYPE_ID,

ICAL.consumed_quantity CONSUMED_QUANTITY,

ICAL.consumed_quantity_uom_code CONSUMED_QUANTITY_UOM_CODE,

ICAL.secondary_consumed_quantity SECONDARY_CONSUMED_QUANTITY,

ICAL.secondary_quantity_uom_code SECONDARY_QUANTITY_UOM_CODE,

ICAL.billed_quantity BILLED_QUANTITY,

ICAL.billed_amount BILLED_AMOUNT,

ICAL.currency_code CURRENCY_CODE,

ICAL.currency_conversion_rate CURRENCY_CONVERSION_RATE,

ICAL.currency_conversion_date CURRENCY_CONVERSION_DATE,

ICAL.currency_conversion_type CURRENCY_CONVERSION_TYPE,

ICAL.consumption_unit_price CONSUMPTION_UNIT_PRICE,

ICAL.CONSUMPTION_TAX_EXC_UNIT_PRICE CONSUMPTION_TAX_EXC_UNIT_PRICE,

ICAL.extended_price EXTENDED_PRICE,

ICAL.RECEIVE_TRANSACTION_ID RCV_TRANSACTION_ID, /* RECEIVE */

ICAL.consumed_quantity - NVL(ICAL.billed_quantity, 0) REMAINING_QUANTITY,

ICAL.DEFAULT_TAXATION_COUNTRY,

ICAL.FIRST_PTY_REG_ID,

ICAL.THIRD_PTY_REG_ID,

ICAL.FINAL_DISCHARGE_LOCATION_ID,

ICAL.DOCUMENT_SUB_TYPE,

ICAL.TRX_BUSINESS_CATEGORY,

ICAL.USER_DEFINED_FISC_CLASS,

ICAL.TAX_INVOICE_NUMBER,

ICAL.TAX_INVOICE_DATE,

ICAL.PROD_FC_CATEG_ID,

ICAL.PRODUCT_CATEGORY,

ICAL.INTENDED_USE_CLASSIF_ID,

ICAL.PRODUCT_TYPE,

ICAL.ASSESSABLE_VALUE,

ICAL.TAX_CLASSIFICATION_CODE,

ICAL.SHIP_FROM_LOCATION_ID,

ICAL.OWNED_LINE_ID

FROM

INV_CONS_ADVICE_HEADERS ICAH,

INV_CONS_ADVICE_LINES ICAL,

POZ_SUPPLIER_SITES_ALL_M PSSAM,

RCV_SHIPMENT_HEADERS RSH,

RCV_SHIPMENT_LINES RSL

WHERE

ICAH.consumption_header_id = ICAL.consumption_header_id

AND PSSAM.vendor_site_id = ICAH.owning_entity_id

AND ICAL.transaction_source_id = RSH.shipment_header_id

AND ICAL.source_line_id = RSL.shipment_line_id