MSC_AP_TO_SHIP_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 SHIPPED_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

SHIPPED_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(WD.actual_ship_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SHIPPED_DATE, CAST(FROM_TZ(CAST(WD.actual_ship_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(WD.actual_ship_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SCHEDULED_DATE, WDD.SHIPPED_QUANTITY VALUE_NUMBER_QUANTITY, WDD.SHIPPED_QUANTITY * TOL.UNIT_PRICE VALUE_NUMBER_AMOUNT from INV_TRANSFER_ORDER_LINES TOL , INV_TRANSFER_ORDER_HEADERS TOH, WSH_delivery_details WDD, WSH_delivery_assignments WDA, WSH_NEW_DELIVERIES WD, 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 TOH.header_number= WDD.sales_order_number and TOL.line_number = WDD.sales_order_line_number and WDD.source_document_type_id in (8,10) and WDA.delivery_detail_id = WDD.delivery_detail_ID and WD.delivery_id = WDA.delivery_id and TOL.SHIPPED_QTY > 0 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 union all SELECT SUP.SUPPLY_SOURCE_ID 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(SUP.RECEIPT_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SHIPPED_DATE, CAST(FROM_TZ(CAST(SUP.RECEIPT_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(SUP.RECEIPT_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SCHEDULED_DATE, SUP.QUANTITY VALUE_NUMBER_QUANTITY, SUP.QUANTITY VALUE_NUMBER_AMOUNT from INV_SUPPLY SUP, 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 SUP.Transfer_Order_Line_Id is null AND SUP.SUPPLY_TYPE_CODE = 'SHIPMENT' and SUP.FROM_ORGANIZATION_ID <> SUP.TO_ORGANIZATION_ID and SUP.FROM_ORGANIZATION_ID = SRCORGS.SR_ORGANIZATION_ID and SRCORGS.ORGANIZATION_ID = SRCMP.ORGANIZATION_ID and SUP.TO_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 = SUP.ITEM_ID and ITEMSV.organization_id = SUP.TO_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(WD.actual_ship_date AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SHIPPED_DATE,

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

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

WDD.SHIPPED_QUANTITY VALUE_NUMBER_QUANTITY,

WDD.SHIPPED_QUANTITY * TOL.UNIT_PRICE VALUE_NUMBER_AMOUNT

from

INV_TRANSFER_ORDER_LINES TOL , INV_TRANSFER_ORDER_HEADERS TOH,

WSH_delivery_details WDD, WSH_delivery_assignments WDA, WSH_NEW_DELIVERIES WD,

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 TOH.header_number= WDD.sales_order_number

and TOL.line_number = WDD.sales_order_line_number

and WDD.source_document_type_id in (8,10)

and WDA.delivery_detail_id = WDD.delivery_detail_ID

and WD.delivery_id = WDA.delivery_id

and TOL.SHIPPED_QTY > 0

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

union all

SELECT

SUP.SUPPLY_SOURCE_ID 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(SUP.RECEIPT_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MP.timezone_code,'UTC') AS DATE) SHIPPED_DATE,

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

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

SUP.QUANTITY VALUE_NUMBER_QUANTITY,

SUP.QUANTITY VALUE_NUMBER_AMOUNT

from

INV_SUPPLY SUP, 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 SUP.Transfer_Order_Line_Id is null

AND SUP.SUPPLY_TYPE_CODE = 'SHIPMENT'

and SUP.FROM_ORGANIZATION_ID <> SUP.TO_ORGANIZATION_ID

and SUP.FROM_ORGANIZATION_ID = SRCORGS.SR_ORGANIZATION_ID

and SRCORGS.ORGANIZATION_ID = SRCMP.ORGANIZATION_ID

and SUP.TO_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 = SUP.ITEM_ID

and ITEMSV.organization_id = SUP.TO_ORGANIZATION_ID

and MP.USE_CUST_FOR_XFER_FLAG = 1