MSC_AP_TO_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 CUS_LVL_MEMBER_NAME CUS_SITE_LVL_MEMBER_NAME 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

CUS_LVL_MEMBER_NAME

CUS_SITE_LVL_MEMBER_NAME

SHIPPED_DATE

REQUESTED_DATE

SCHEDULED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

Query

SQL_Statement
SELECT trx.transaction_id , NULL 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, CS.CUSTOMER_NUMBER CUS_LVL_MEMBER_NAME, CS.NAME CUS_SITE_LVL_MEMBER_NAME, 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 , MSC_INSTANCE_ORGS DORG, MSC_DIM_CUST_SITE_V cs 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.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID AND trx.TRANSFER_ORGANIZATION_ID is not null AND trx.TRANSFER_ORGANIZATION_ID != trx.ORGANIZATION_ID and trx.TRANSFER_ORGANIZATION_ID = dorg.sr_organization_id AND mpara.MODELED_CUSTOMER_ID = CS.parent_id AND mpara.MODELED_CUSTOMER_SITE_ID = CS.ID AND MPARA.organization_id = dorg.ORGANIZATION_ID AND MPARA.USE_CUST_FOR_XFER_FLAG = 1 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 ,

NULL 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,

CS.CUSTOMER_NUMBER CUS_LVL_MEMBER_NAME,

CS.NAME CUS_SITE_LVL_MEMBER_NAME,

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 ,

MSC_INSTANCE_ORGS DORG,

MSC_DIM_CUST_SITE_V cs

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.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID

AND trx.TRANSFER_ORGANIZATION_ID is not null

AND trx.TRANSFER_ORGANIZATION_ID != trx.ORGANIZATION_ID

and trx.TRANSFER_ORGANIZATION_ID = dorg.sr_organization_id

AND mpara.MODELED_CUSTOMER_ID = CS.parent_id

AND mpara.MODELED_CUSTOMER_SITE_ID = CS.ID

AND MPARA.organization_id = dorg.ORGANIZATION_ID

AND MPARA.USE_CUST_FOR_XFER_FLAG = 1

AND trx.inventory_item_id = ITEMSV.INVENTORY_ITEM_ID

AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID

AND ITEMSV.MRP_PLANNING_CODE = 10