MSC_AP_OPT_BOOK_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 PRD_LVL_MEMBER_ID ITEM_NAME CTO_ITEM_UNIQUE_VALUE CTO_PARENT_UNIQUE_VALUE DEPTH ORG_LVL_MEMBER_ID ORGANIZATION_CODE CUSTOMER_ID CUS_LVL_MEMBER_NAME SHIP_TO_SITE_ID CUS_SITE_LVL_MEMBER_NAME ORDER_TYPE_FLAG BOOKED_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

PRD_LVL_MEMBER_ID

ITEM_NAME

CTO_ITEM_UNIQUE_VALUE

CTO_PARENT_UNIQUE_VALUE

DEPTH

ORG_LVL_MEMBER_ID

ORGANIZATION_CODE

CUSTOMER_ID

CUS_LVL_MEMBER_NAME

SHIP_TO_SITE_ID

CUS_SITE_LVL_MEMBER_NAME

ORDER_TYPE_FLAG

BOOKED_DATE

REQUESTED_DATE

PROMISED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

DF_ROW_ID

DH_ROW_ID

Query

SQL_Statement
SELECT 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, DF.INVENTORY_ITEM_ID PRD_LVL_MEMBER_ID, 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, nvl( DF.FULFILL_ORG_ID ,org.sr_organization_id ) ORG_LVL_MEMBER_ID, ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE, ship_to_party_id customer_id, custHZP.party_number CUS_LVL_MEMBER_NAME, ship_to_party_site_id ship_to_site_id, custsitesHPS.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, / Changed Due to SCP Time ZOne feature/ CAST(FROM_TZ(CAST(DH.ORDERED_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) BOOKED_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, / End of casting/ DF.ORDERED_QTY VALUE_NUMBER_QUANTITY, DF.ORDERED_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, MSC_INSTANCE_ORGS ORG, MSC_PARAMETERS MPARA, HZ_PARTIES custHZP, HZ_PARTY_SITES custsitesHPS, msc_apps_instances apps WHERE DH.HEADER_ID = DF.HEADER_ID AND DH.SUBMITTED_FLAG='Y' and DF.CATEGORY_CODE = 'ORDER' 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 MPARA.organization_code=ORG.sr_Organization_code and MPARA.organization_id=org.organization_id AND ORG.SR_INSTANCE_ID = apps.INSTANCE_ID AND apps.INSTANCE_ID =DF.FULFILL_INSTANCE_ID AND custHZP.party_id = DF.ship_to_party_id AND custsitesHPS.PARTY_SITE_ID=DF.ship_to_party_site_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

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,

DF.INVENTORY_ITEM_ID PRD_LVL_MEMBER_ID,

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,

nvl( DF.FULFILL_ORG_ID ,org.sr_organization_id ) ORG_LVL_MEMBER_ID,

ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE,

ship_to_party_id customer_id,

custHZP.party_number CUS_LVL_MEMBER_NAME,

ship_to_party_site_id ship_to_site_id,

custsitesHPS.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,

/* Changed Due to SCP Time ZOne feature*/

CAST(FROM_TZ(CAST(DH.ORDERED_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) BOOKED_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,

/* End of casting*/

DF.ORDERED_QTY VALUE_NUMBER_QUANTITY,

DF.ORDERED_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,

MSC_INSTANCE_ORGS ORG,

MSC_PARAMETERS MPARA,

HZ_PARTIES custHZP,

HZ_PARTY_SITES custsitesHPS,

msc_apps_instances apps

WHERE DH.HEADER_ID = DF.HEADER_ID

AND DH.SUBMITTED_FLAG='Y'

and DF.CATEGORY_CODE = 'ORDER'

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 MPARA.organization_code=ORG.sr_Organization_code

and MPARA.organization_id=org.organization_id

AND ORG.SR_INSTANCE_ID = apps.INSTANCE_ID

AND apps.INSTANCE_ID =DF.FULFILL_INSTANCE_ID

AND custHZP.party_id = DF.ship_to_party_id

AND custsitesHPS.PARTY_SITE_ID=DF.ship_to_party_site_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)

)