MSC_AP_TO_BOOK_HIST

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
LINE_ID ORDER_TYPE_CODE FROM_CURRENCY TO_CURRENCY ITEM_NAME ORGANIZATION_CODE CUS_LVL_MEMBER_NAME CUS_SITE_LVL_MEMBER_NAME BOOKED_DATE REQUESTED_DATE SCHEDULED_DATE VALUE_NUMBER_QUANTITY VALUE_NUMBER_AMOUNT

Name

LINE_ID

ORDER_TYPE_CODE

FROM_CURRENCY

TO_CURRENCY

ITEM_NAME

ORGANIZATION_CODE

CUS_LVL_MEMBER_NAME

CUS_SITE_LVL_MEMBER_NAME

BOOKED_DATE

REQUESTED_DATE

SCHEDULED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

Query

SQL_Statement
SELECT TOL.LINE_ID, null ORDER_TYPE_CODE, MP.CURRENCY_CODE FROM_CURRENCY, SRCMP.CURRENCY_CODE TO_CURRENCY, ITEMSV.ITEM_NUMBER ITEM_NAME, SRCORGS.SR_ORGANIZATION_CODE ORGANIZATION_CODE, CS.CUSTOMER_NUMBER CUS_LVL_MEMBER_NAME, CS.NAME CUS_SITE_LVL_MEMBER_NAME, CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) BOOKED_DATE, CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SCHEDULED_DATE, TOL.REQUESTED_QTY VALUE_NUMBER_QUANTITY, TOL.REQUESTED_QTY * TOL.UNIT_PRICE VALUE_NUMBER_AMOUNT from INV_TRANSFER_ORDER_LINES TOL , INV_TRANSFER_ORDER_HEADERS TOH, EGP_SYSTEM_ITEMS_VL ITEMSV, MSC_INSTANCE_ORGS INORGS,MSC_PARAMETERS MP, MSC_INSTANCE_ORGS SRCORGS,MSC_PARAMETERS SRCMP, MSC_DIM_CUST_SITE_V CS WHERE TOL.STATUS_LOOKUP in ('OPEN','CLOSED') AND TOL.HEADER_ID = TOH.HEADER_ID AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO') and TOL.SOURCE_ORGANIZATION_ID <> TOL.DESTINATION_ORGANIZATION_ID and TOL.SOURCE_ORGANIZATION_ID = SRCORGS.SR_ORGANIZATION_ID and SRCORGS.ORGANIZATION_ID = SRCMP.ORGANIZATION_ID and TOL.DESTINATION_ORGANIZATION_ID = INORGS.SR_ORGANIZATION_ID and INORGS.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MP.MODELED_CUSTOMER_ID = CS.PARENT_ID AND MP.MODELED_CUSTOMER_SITE_ID = CS.ID and ITEMSV.INVENTORY_ITEM_ID = TOL.INVENTORY_ITEM_ID and ITEMSV.organization_id = TOL.DESTINATION_ORGANIZATION_ID and MP.USE_CUST_FOR_XFER_FLAG = 1

SQL_Statement

SELECT

TOL.LINE_ID,

null ORDER_TYPE_CODE,

MP.CURRENCY_CODE FROM_CURRENCY,

SRCMP.CURRENCY_CODE TO_CURRENCY,

ITEMSV.ITEM_NUMBER ITEM_NAME,

SRCORGS.SR_ORGANIZATION_CODE ORGANIZATION_CODE,

CS.CUSTOMER_NUMBER CUS_LVL_MEMBER_NAME,

CS.NAME CUS_SITE_LVL_MEMBER_NAME,

CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) BOOKED_DATE,

CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) REQUESTED_DATE,

CAST(FROM_TZ(CAST(TOL.SCHEDULED_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SCHEDULED_DATE,

TOL.REQUESTED_QTY VALUE_NUMBER_QUANTITY,

TOL.REQUESTED_QTY * TOL.UNIT_PRICE VALUE_NUMBER_AMOUNT

from

INV_TRANSFER_ORDER_LINES TOL , INV_TRANSFER_ORDER_HEADERS TOH,

EGP_SYSTEM_ITEMS_VL ITEMSV,

MSC_INSTANCE_ORGS INORGS,MSC_PARAMETERS MP,

MSC_INSTANCE_ORGS SRCORGS,MSC_PARAMETERS SRCMP,

MSC_DIM_CUST_SITE_V CS

WHERE TOL.STATUS_LOOKUP in ('OPEN','CLOSED')

AND TOL.HEADER_ID = TOH.HEADER_ID

AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO')

and TOL.SOURCE_ORGANIZATION_ID <> TOL.DESTINATION_ORGANIZATION_ID

and TOL.SOURCE_ORGANIZATION_ID = SRCORGS.SR_ORGANIZATION_ID

and SRCORGS.ORGANIZATION_ID = SRCMP.ORGANIZATION_ID

and TOL.DESTINATION_ORGANIZATION_ID = INORGS.SR_ORGANIZATION_ID

and INORGS.ORGANIZATION_ID = MP.ORGANIZATION_ID

AND MP.MODELED_CUSTOMER_ID = CS.PARENT_ID

AND MP.MODELED_CUSTOMER_SITE_ID = CS.ID

and ITEMSV.INVENTORY_ITEM_ID = TOL.INVENTORY_ITEM_ID

and ITEMSV.organization_id = TOL.DESTINATION_ORGANIZATION_ID

and MP.USE_CUST_FOR_XFER_FLAG = 1