MSC_AP_SUBINV_HIST

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

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 \|\| ':' \|\| trx.subinventory_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, INV_SECONDARY_INVENTORIES subinv, EGP_SYSTEM_ITEMS_VL ITEMSV, MSC_INSTANCE_ORGS ORG, MSC_PARAMETERS MPARA WHERE trx.transaction_quantity < 0 AND trx.TRANSACTION_ACTION_ID IN (1,2,3,21,23,28) AND trx.TRANSACTION_TYPE_ID NOT IN (5,6,9,18,36,37,46,47,85,93,1001,20006 ) AND trx.ORGANIZATION_ID = subinv.ORGANIZATION_ID AND trx.subinventory_code = subinv.secondary_inventory_name AND ( trx.transfer_organization_id = trx.ORGANIZATION_ID or trx.transfer_organization_id is null ) 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

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 || ':'

|| trx.subinventory_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,

INV_SECONDARY_INVENTORIES subinv,

EGP_SYSTEM_ITEMS_VL ITEMSV,

MSC_INSTANCE_ORGS ORG,

MSC_PARAMETERS MPARA

WHERE trx.transaction_quantity < 0

AND trx.TRANSACTION_ACTION_ID IN (1,2,3,21,23,28)

AND trx.TRANSACTION_TYPE_ID NOT IN (5,6,9,18,36,37,46,47,85,93,1001,20006 )

AND trx.ORGANIZATION_ID = subinv.ORGANIZATION_ID

AND trx.subinventory_code = subinv.secondary_inventory_name

AND ( trx.transfer_organization_id = trx.ORGANIZATION_ID or trx.transfer_organization_id is null )

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