1. Tables and Views for SCM
  2. CMR_XLA_TRADE_TXNS_V

CMR_XLA_TRADE_TXNS_V

Details

  • Schema: FUSION

  • Object owner: CMR

  • Object type: VIEW

Columns

Name

ACCOUNTING_EVENT_ID

TRADE_EVENT_ID

SOURCE_DOCUMENT_NUMBER

TRANSACTION_TYPE

TRANSACTION_DATE

QUANTITY

UOM_CODE

INVENTORY_ITEM_ID

ITEM_NUMBER

INVENTORY_ORGANIZATION_ID

TRADE_ORG_CODE

BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

SFO_AGREEMENT_NUMBER

SOURCE_DOCUMENT_TYPE

PRIOR_TRADE_ORGANIZATION_ID

FROM_INV_ORG_CODE

FROM_PROFIT_CENTER_BU_ID

FROM_PROFIT_CENTER_BU_NAME

Query

SQL_Statement

select CRE.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID ,

CRE.TRADE_EVENT_ID TRADE_EVENT_ID,

cast(null as varchar2(30)) SOURCE_DOCUMENT_NUMBER ,

CRET.TRANSACTION_TYPE TRANSACTION_TYPE ,

CRE.TRANSACTION_DATE TRANSACTION_DATE ,

CRE.SOURCE_DOC_QTY QUANTITY ,

IUOM.UNIT_OF_MEASURE UOM_CODE ,

CRE.INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,

ESI.ITEM_NUMBER ITEM_NUMBER ,

CRE.INVENTORY_ORG_ID INVENTORY_ORGANIZATION_ID ,

IOP.ORGANIZATION_CODE TRADE_ORG_CODE,

CRE.BUSINESS_UNIT_ID BUSINESS_UNIT_ID ,

HAOUS.name as BUSINESS_UNIT_NAME,

CTE.AGREEMENT_NUMBER SFO_AGREEMENT_NUMBER,

FND.MEANING SOURCE_DOCUMENT_TYPE,

CTE.PRIOR_INVENTORY_ORG_ID PRIOR_TRADE_ORGANIZATION_ID,

FROMINVORG.ORGANIZATION_CODE FROM_INV_ORG_CODE,

CTE.PRIOR_BU_ID FROM_PROFIT_CENTER_BU_ID,

FROMBU.name as FROM_PROFIT_CENTER_BU_NAME

from CST_TRADE_EVENTS CTE ,

EGP_SYSTEM_ITEMS_VL ESI ,

INV_ORG_PARAMETERS IOP ,

HR_ALL_ORGANIZATION_UNITS HAOUS ,

CMR_RCV_EVENTS CRE ,

CMR_RCV_EVENT_TYPES CRET,

INV_UNITS_OF_MEASURE_VL IUOM,

FND_LOOKUPS FND,

INV_ORG_PARAMETERS FROMINVORG,

HR_ALL_ORGANIZATION_UNITS FROMBU

where CRE.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID(+)

and CRE.INVENTORY_ORG_ID = ESI.INVENTORY_ORGANIZATION_ID(+)

and IOP.ORGANIZATION_ID = CRE.INVENTORY_ORG_ID

and HAOUS.ORGANIZATION_ID = CRE.BUSINESS_UNIT_ID

and CRE.TRADE_EVENT_ID = CTE.TRADE_EVENT_ID

and CRE.EVENT_CLASS_CODE in ('TRADE_RECEIPT_ACCOUNTING','TRADE_ACCRUALS')

and CRE.EVENT_TYPE_CODE = CRET.EVENT_TYPE_CODE

and IUOM.UOM_CODE = CRE.SOURCE_DOC_UOM_CODE

and CRE.EVENT_SOURCE = FND.LOOKUP_CODE

and FND.LOOKUP_TYPE ='ORA_CMR_DOC_FLOW_TYPES'

and CTE.PRIOR_INVENTORY_ORG_ID = FROMINVORG.ORGANIZATION_ID

and FROMBU.ORGANIZATION_ID = FROMINVORG.BUSINESS_UNIT_ID