MSC_AP_OPT_SHIP_HIST

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
FULFILL_LINE_ID SR_INSTANCE_ID SUPPLIER_SITE_SOURCE_SYSTEM ORDER_TYPE_CODE DEMAND_CLASS_CODE ORIGINAL_ITEM_NAME ITEM_NAME CTO_ITEM_UNIQUE_VALUE CTO_PARENT_UNIQUE_VALUE DEPTH ORGANIZATION_CODE CUS_LVL_MEMBER_NAME CUS_SITE_LVL_MEMBER_NAME ORDER_TYPE_FLAG SHIPPED_DATE REQUESTED_DATE PROMISED_DATE VALUE_NUMBER_QUANTITY VALUE_NUMBER_AMOUNT DF_ROW_ID DH_ROW_ID

Name

FULFILL_LINE_ID

SR_INSTANCE_ID

SUPPLIER_SITE_SOURCE_SYSTEM

ORDER_TYPE_CODE

DEMAND_CLASS_CODE

ORIGINAL_ITEM_NAME

ITEM_NAME

CTO_ITEM_UNIQUE_VALUE

CTO_PARENT_UNIQUE_VALUE

DEPTH

ORGANIZATION_CODE

CUS_LVL_MEMBER_NAME

CUS_SITE_LVL_MEMBER_NAME

ORDER_TYPE_FLAG

SHIPPED_DATE

REQUESTED_DATE

PROMISED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

DF_ROW_ID

DH_ROW_ID

Query

SQL_Statement
SELECT distinct DF.FULFILL_LINE_ID, apps.INSTANCE_ID SR_INSTANCE_ID, apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM , DH.ORDER_TYPE_CODE ORDER_TYPE_CODE, DF.DEMAND_CLASS_CODE DEMAND_CLASS_CODE, ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME, ITEMSV.ITEM_NUMBER ITEM_NAME, regexp_replace(DF.CONFIGURATOR_PATH, '%[0-9]','') CTO_ITEM_UNIQUE_VALUE, DF.COMPONENT_ID_PATH CTO_PARENT_UNIQUE_VALUE, (length(DF.CONFIGURATOR_PATH) - length(replace(DF.CONFIGURATOR_PATH,'.','')) + 1) DEPTH, ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE, HZ.PARTY_NUMBER CUS_LVL_MEMBER_NAME, HPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME, (CASE WHEN ((DH.SOURCE_DOCUMENT_TYPE_CODE = 'TO') OR (DH.SOURCE_DOCUMENT_TYPE_CODE = 'ISO')) THEN 'Internal' ELSE 'External' END) ORDER_TYPE_FLAG, CAST(FROM_TZ(CAST( DF.ACTUAL_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SHIPPED_DATE, CAST(FROM_TZ(CAST(DF.REQUEST_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(DF.PROMISE_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) PROMISED_DATE, DF.SHIPPED_QTY VALUE_NUMBER_QUANTITY, DF.SHIPPED_QTY DF.UNIT_SELLING_PRICE VALUE_NUMBER_AMOUNT, DF.rowid DF_ROW_ID, DH.rowid DH_ROW_ID FROM DOO_FULFILL_LINES_ALL DF, DOO_HEADERS_ALL DH, EGP_SYSTEM_ITEMS_VL ITEMSV, EGP_SYSTEM_ITEMS_VL ORGITEMSV, MSC_INSTANCE_ORGS ORG, MSC_PARAMETERS MPARA, HZ_PARTIES HZ, HZ_PARTY_SITES HPS, msc_apps_instances apps WHERE 1=1 /* AND DF.FULFILL_LINE_ID IN (SELECT DSID.FULFILL_LINE_ID FROM DOO_STEP_INSTANCE_DETAILS DSID , DOO_PROCESS_STEP_INSTANCES DSI , DOO_TASK_INSTANCES DTI , DOO_TASK_DEFINITIONS_VL DTD , DOO_TASK_TYPES_VL DTT WHERE DSID.STEP_INSTANCE_ID = DSI.STEP_INSTANCE_ID AND DTI.TASK_ID = DTD.TASK_ID AND DTD.TASK_TYPE_ID = DTT.TASK_TYPE_ID AND DTT.TASK_TYPE = 'Shipment' ) */ AND DH.HEADER_ID = DF.HEADER_ID AND DH. SUBMITTED_FLAG='Y' AND DF.SHIPPED_QTY > 0 AND DF.CONFIGURATOR_PATH is not null AND DF.ITEM_TYPE_CODE IN ('2','4') AND DF.ITEM_SUB_TYPE_CODE IN ('ATO-OPTION-CLASS','ATO-OPTION','OPTION-CLASS','OPTION') AND ITEMSV.INVENTORY_ITEM_ID = DF.INVENTORY_ITEM_ID AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID AND ORGITEMSV.INVENTORY_ITEM_ID(+) = DF.ORIGINAL_INVENTORY_ITEM_ID AND ORGITEMSV.ORGANIZATION_ID(+) = ORG.SR_ORGANIZATION_ID AND HPS.PARTY_SITE_ID = DF.SHIP_TO_PARTY_SITE_ID AND HZ.PARTY_ID = HPS.PARTY_ID and MPARA.organization_code=ORG.Organization_code and MPARA.organization_id=org.organization_id and apps.instance_id = DF.FULFILL_INSTANCE_ID AND ( ( ORG.sr_ORGANIZATION_ID = DF.FULFILL_ORG_ID AND ORG.FULFILLMENT_INSTANCE_FLAG = 1 ) OR ( DF.FULFILL_ORG_ID is null and (MPARA.modeled_supplier_id =df.supplier_id AND MPARA.MODELED_SUPPLIER_SITE_ID = DF.SUPPLIER_SITE_ID and ORG.drop_ship_val_org_flag is null) or ( ORG.drop_ship_val_org_flag= 1 and DF.SUPPLIER_SITE_ID is null) ) )

SQL_Statement

SELECT distinct

DF.FULFILL_LINE_ID,

apps.INSTANCE_ID SR_INSTANCE_ID,

apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM ,

DH.ORDER_TYPE_CODE ORDER_TYPE_CODE,

DF.DEMAND_CLASS_CODE DEMAND_CLASS_CODE,

ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME,

ITEMSV.ITEM_NUMBER ITEM_NAME,

regexp_replace(DF.CONFIGURATOR_PATH, '%[0-9]*','') CTO_ITEM_UNIQUE_VALUE,

DF.COMPONENT_ID_PATH CTO_PARENT_UNIQUE_VALUE,

(length(DF.CONFIGURATOR_PATH) - length(replace(DF.CONFIGURATOR_PATH,'.','')) + 1) DEPTH,

ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE,

HZ.PARTY_NUMBER CUS_LVL_MEMBER_NAME,

HPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME,

(CASE

WHEN ((DH.SOURCE_DOCUMENT_TYPE_CODE = 'TO') OR (DH.SOURCE_DOCUMENT_TYPE_CODE = 'ISO')) THEN

'Internal'

ELSE 'External'

END) ORDER_TYPE_FLAG,

CAST(FROM_TZ(CAST( DF.ACTUAL_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SHIPPED_DATE,

CAST(FROM_TZ(CAST(DF.REQUEST_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) REQUESTED_DATE,

CAST(FROM_TZ(CAST(DF.PROMISE_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) PROMISED_DATE,

DF.SHIPPED_QTY VALUE_NUMBER_QUANTITY,

DF.SHIPPED_QTY * DF.UNIT_SELLING_PRICE VALUE_NUMBER_AMOUNT,

DF.rowid DF_ROW_ID,

DH.rowid DH_ROW_ID

FROM DOO_FULFILL_LINES_ALL DF,

DOO_HEADERS_ALL DH,

EGP_SYSTEM_ITEMS_VL ITEMSV,

EGP_SYSTEM_ITEMS_VL ORGITEMSV,

MSC_INSTANCE_ORGS ORG,

MSC_PARAMETERS MPARA,

HZ_PARTIES HZ,

HZ_PARTY_SITES HPS,

msc_apps_instances apps

WHERE 1=1

/* AND DF.FULFILL_LINE_ID IN

(SELECT DSID.FULFILL_LINE_ID

FROM DOO_STEP_INSTANCE_DETAILS DSID ,

DOO_PROCESS_STEP_INSTANCES DSI ,

DOO_TASK_INSTANCES DTI ,

DOO_TASK_DEFINITIONS_VL DTD ,

DOO_TASK_TYPES_VL DTT

WHERE

DSID.STEP_INSTANCE_ID = DSI.STEP_INSTANCE_ID

AND DTI.TASK_ID = DTD.TASK_ID

AND DTD.TASK_TYPE_ID = DTT.TASK_TYPE_ID

AND DTT.TASK_TYPE = 'Shipment'

)

*/

AND DH.HEADER_ID = DF.HEADER_ID

AND DH. SUBMITTED_FLAG='Y'

AND DF.SHIPPED_QTY > 0

AND DF.CONFIGURATOR_PATH is not null

AND DF.ITEM_TYPE_CODE IN ('2','4')

AND DF.ITEM_SUB_TYPE_CODE IN ('ATO-OPTION-CLASS','ATO-OPTION','OPTION-CLASS','OPTION')

AND ITEMSV.INVENTORY_ITEM_ID = DF.INVENTORY_ITEM_ID

AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID

AND ORGITEMSV.INVENTORY_ITEM_ID(+) = DF.ORIGINAL_INVENTORY_ITEM_ID

AND ORGITEMSV.ORGANIZATION_ID(+) = ORG.SR_ORGANIZATION_ID

AND HPS.PARTY_SITE_ID = DF.SHIP_TO_PARTY_SITE_ID

AND HZ.PARTY_ID = HPS.PARTY_ID

and MPARA.organization_code=ORG.Organization_code

and MPARA.organization_id=org.organization_id

and apps.instance_id = DF.FULFILL_INSTANCE_ID

AND (

( ORG.sr_ORGANIZATION_ID = DF.FULFILL_ORG_ID

AND ORG.FULFILLMENT_INSTANCE_FLAG = 1

)

OR ( DF.FULFILL_ORG_ID is null and

(MPARA.modeled_supplier_id =df.supplier_id

AND MPARA.MODELED_SUPPLIER_SITE_ID = DF.SUPPLIER_SITE_ID

and ORG.drop_ship_val_org_flag is null)

or

( ORG.drop_ship_val_org_flag= 1

and DF.SUPPLIER_SITE_ID is null)

)