MSC_AP_ORGINV_HIST_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
TRANSACTION_ID ORDER_TYPE_CODE FROM_CURRENCY TO_CURRENCY ITEM_NAME ORGANIZATION_CODE SHIPPED_DATE REQUESTED_DATE SCHEDULED_DATE VALUE_NUMBER_QUANTITY VALUE_NUMBER_AMOUNT

Query

SQL_Statement

SELECT trx.transaction_id , 'SUB_INV' ORDER_TYPE_CODE, trx.CURRENCY_CODE FROM_CURRENCY, MPARA.CURRENCY_CODE TO_CURRENCY, ITEMSV.ITEM_NUMBER ITEM_NAME, ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE, CAST(FROM_TZ(CAST(trx.transaction_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE NVL(MPARA.timezone_code,'UTC') AS DATE) SHIPPED_DATE, CAST(FROM_TZ(CAST(trx.transaction_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE NVL(MPARA.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(trx.transaction_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE NVL(MPARA.timezone_code,'UTC') AS DATE) SCHEDULED_DATE, abs(trx.transaction_quantity)*decode (ITEMSV.primary_uom_code ,trx.transaction_uom, 1,inv_convert.inv_um_convert(trx.INVENTORY_ITEM_ID,0,null,trx.transaction_uom,ITEMSV.primary_uom_code,null,null ) ) value_number_quantity, (abs(trx.transaction_quantity) * trx.actual_cost) value_number_amount FROM INV_MATERIAL_TXNS trx, EGP_SYSTEM_ITEMS_VL ITEMSV, MSC_INSTANCE_ORGS ORG, MSC_PARAMETERS MPARA WHERE trx.transaction_quantity < 0 AND trx.TRANSACTION_ACTION_ID IN (1) AND trx.TRANSACTION_TYPE_ID NOT IN (33,36,37,45,93,1001,20006) AND trx.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID AND MPARA.organization_id = ORG.ORGANIZATION_ID AND trx.inventory_item_id = ITEMSV.INVENTORY_ITEM_ID AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID AND ITEMSV.MRP_PLANNING_CODE = 10