1. Tables and Views for SCM
  2. DOO_ORDER_PRICING_DETAILS_V

DOO_ORDER_PRICING_DETAILS_V

Details

  • Schema: FUSION

  • Object owner: DOO

  • Object type: VIEW

Columns

Name

CHANGE_VERSION_NUMBER

HEADER_ID

LEGAL_ENTITY_ID

ORDERED_DATE

ORDER_NUMBER

ORDER_TYPE_CODE

ORG_ID

HDR_ORIG_SYS_DOCUMENT_REF

HDR_PRICED_ON

REVISION_SOURCE_ORDER_SYSTEM

SOLD_TO_PARTY_CONTACT_ID

SOLD_TO_PARTY_CONTACT_POINT_ID

SOLD_TO_PARTY_ID

SOURCE_DOCUMENT_TYPE_CODE

SOURCE_ORDER_ID

SOURCE_ORDER_NUMBER

SOURCE_ORDER_SYSTEM

SOURCE_ORG_ID

SOURCE_REVISION_NUMBER

HDR_STATUS_CODE

SUBMITTED_FLAG

TRANSACTIONAL_CURRENCY_CODE

LINE_ID

LINE_NUMBER

DISPLAY_LINE_NUMBER

APPLIED_PRICE_LIST_ID

BILL_TO_CONTACT_ID

BILL_TO_CONTACT_POINT_ID

BILL_TO_CUSTOMER_ID

BILL_TO_SITE_USE_ID

CARRIER_ID

CATEGORY_CODE

CUSTOMER_ITEM_ID

CUSTOMER_PO_LINE_NUMBER

CUSTOMER_PO_NUMBER

CUSTOMER_PO_SCHEDULE_NUMBER

EXTENDED_AMOUNT

FOB_POINT_CODE

FULFILLMENT_SPLIT_REF_ID

FULFILL_LINE_ID

FULFILL_LINE_NUMBER

FULFILL_ORG_ID

INVENTORY_ITEM_ID

INVENTORY_ORGANIZATION_ID

ITEM_SUB_TYPE_CODE

ITEM_TYPE_CODE

LINE_TYPE_CODE

ORDERED_QTY

ORDERED_UOM

ORIG_SYS_DOCUMENT_LINE_REF

ORIG_SYS_DOCUMENT_REF

PARENT_FULFILL_LINE_ID

PRICED_ON

REQUEST_ARRIVAL_DATE

REQUEST_SHIP_DATE

ROOT_PARENT_FULFILL_LINE_ID

SCHEDULE_ARRIVAL_DATE

SCHEDULE_SHIP_DATE

SHIP_CLASS_OF_SERVICE

SHIP_MODE_OF_TRANSPORT

SHIP_TO_CONTACT_POINT_ID

SHIP_TO_PARTY_CONTACT_ID

SHIP_TO_PARTY_ID

SHIP_TO_PARTY_SITE_ID

SOURCE_LINE_ID

SOURCE_LINE_NUMBER

SOURCE_SCHEDULE_ID

SOURCE_SCHEDULE_NUMBER

STATUS_CODE

SUPPLIER_ID

SUPPLIER_SITE_ID

TOTAL_CONTRACT_AMOUNT

TOTAL_CONTRACT_QUANTITY

UNIT_LIST_PRICE

UNIT_SELLING_PRICE

SERVICE_DURATION_PERIOD_CODE

SERVICE_DURATION

SALES_PRODUCT_TYPE_CODE

PO_HEADER_ID

PO_LINE_ID

PO_LINE_LOCATION_ID

AVG_UNIT_SELLING_PRICE

CAN_ADJUST_FLAG

CHARGE_APPLIES_TO

CHARGE_DEFINITION_CODE

CHARGE_SUBTYPE_CODE

CHARGE_TYPE_CODE

GSA_UNIT_PRICE

ORDER_CHARGE_ID

PARENT_ENTITY_CODE

PARENT_ENTITY_ID

PRICED_QUANTITY

PRICED_QUANTITY_UOM_CODE

PRICE_PERIODICITY_CODE

PRICE_TYPE_CODE

PRIMARY_FLAG

ROLLUP_FLAG

CHARGE_SEQUENCE_NUMBER

EXPLANATION

EXPLANATION_MESSAGE_NAME

HEADER_CURRENCY_EXT_AMOUNT

HEADER_CURRENCY_UNIT_PRICE

HEADER_CURR_DURATION_EXT_AMT

ORDER_CHARGE_COMPONENT_ID

PARENT_CHARGE_COMPONENT_ID

PERCENT_OF_COMPARISON_ELEMENT

PRICE_ELEMENT_CODE

PRICE_ELEMENT_USAGE_CODE

PRICING_SOURCE_ID

PRICING_SOURCE_TYPE_CODE

CHARGE_COMP_SEQUENCE_NUMBER

TAX_INCLUDED_FLAG

ORDER_TAX_DETAIL_ID

HDR_CURR_TAX_UNIT_AMT

HDR_CURR_TAXABLE_UNIT_AMT

INCLUSIVE_TAX_FLAG

TAX_RATE

TAX_RATE_ID

TAX_EXEMPT_CERTIFICATE_NUMBER

TAX_EXEMPT_REASON_CODE

SECONDARY_UOM

SECONDARY_ORDERED_QTY

SECONDARY_SHIPPED_QTY

SECONDARY_RMA_DELIVERED_QTY

PRICE_USING_SEC_UOM_FLAG

SECONDARY_FULFILLED_QTY

Query

SQL_Statement

SELECT

DHA.CHANGE_VERSION_NUMBER,

DHA.HEADER_ID,

DHA.LEGAL_ENTITY_ID,

DHA.ORDERED_DATE,

DHA.ORDER_NUMBER,

DHA.ORDER_TYPE_CODE,

DHA.ORG_ID,

DHA.ORIG_SYS_DOCUMENT_REF HDR_ORIG_SYS_DOCUMENT_REF,

DHA.PRICED_ON HDR_PRICED_ON,

DHA.REVISION_SOURCE_ORDER_SYSTEM,

DHA.SOLD_TO_PARTY_CONTACT_ID,

DHA.SOLD_TO_PARTY_CONTACT_POINT_ID,

DHA.SOLD_TO_PARTY_ID,

DHA.SOURCE_DOCUMENT_TYPE_CODE,

DHA.SOURCE_ORDER_ID,

DHA.SOURCE_ORDER_NUMBER,

DHA.SOURCE_ORDER_SYSTEM,

DHA.SOURCE_ORG_ID,

DHA.SOURCE_REVISION_NUMBER,

DHA.STATUS_CODE HDR_STATUS_CODE,

DHA.SUBMITTED_FLAG,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('CURRENCY_CODE', 'CURRENCIES', DHA.TRANSACTIONAL_CURRENCY_CODE ) TRANSACTIONAL_CURRENCY_CODE,

DLA.LINE_ID,

DLA.LINE_NUMBER,

DLA.DISPLAY_LINE_NUMBER,

DFLA.APPLIED_PRICE_LIST_ID,

DFLA.BILL_TO_CONTACT_ID,

DFLA.BILL_TO_CONTACT_POINT_ID,

DFLA.BILL_TO_CUSTOMER_ID,

DFLA.BILL_TO_SITE_USE_ID,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('CARRIER_ID', 'CARRIERS', DFLA.CARRIER_ID ) CARRIER_ID,

DFLA.CATEGORY_CODE,

DFLA.CUSTOMER_ITEM_ID,

DFLA.CUSTOMER_PO_LINE_NUMBER,

DFLA.CUSTOMER_PO_NUMBER,

DFLA.CUSTOMER_PO_SCHEDULE_NUMBER,

DFLA.EXTENDED_AMOUNT,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'FOB', DFLA.FOB_POINT_CODE ) FOB_POINT_CODE,

DFLA.FULFILLMENT_SPLIT_REF_ID,

DFLA.FULFILL_LINE_ID,

DFLA.FULFILL_LINE_NUMBER,

DFLA.FULFILL_ORG_ID,

DFLA.INVENTORY_ITEM_ID,

DFLA.INVENTORY_ORGANIZATION_ID,

DFLA.ITEM_SUB_TYPE_CODE,

DFLA.ITEM_TYPE_CODE,

DFLA.LINE_TYPE_CODE,

DFLA.ORDERED_QTY,

DFLA.ORDERED_UOM,

DFLA.ORIG_SYS_DOCUMENT_LINE_REF,

DFLA.ORIG_SYS_DOCUMENT_REF,

DFLA.PARENT_FULFILL_LINE_ID,

DFLA.PRICED_ON,

DFLA.REQUEST_ARRIVAL_DATE,

DFLA.REQUEST_SHIP_DATE,

DFLA.ROOT_PARENT_FULFILL_LINE_ID,

DFLA.SCHEDULE_ARRIVAL_DATE,

DFLA.SCHEDULE_SHIP_DATE,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'WSH_SERVICE_LEVELS', DFLA.SHIP_CLASS_OF_SERVICE ) SHIP_CLASS_OF_SERVICE,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'WSH_MODE_OF_TRANSPORT', DFLA.SHIP_MODE_OF_TRANSPORT ) SHIP_MODE_OF_TRANSPORT,

DFLA.SHIP_TO_CONTACT_POINT_ID,

DFLA.SHIP_TO_PARTY_CONTACT_ID,

DFLA.SHIP_TO_PARTY_ID,

DFLA.SHIP_TO_PARTY_SITE_ID,

DFLA.SOURCE_LINE_ID,

DFLA.SOURCE_LINE_NUMBER,

DFLA.SOURCE_SCHEDULE_ID,

DFLA.SOURCE_SCHEDULE_NUMBER,

DFLA.STATUS_CODE,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('SUPPLIER_ID', 'SUPPLIERS', DFLA.SUPPLIER_ID ) SUPPLIER_ID,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('SUPPLIER_SITE_ID', 'SUPPLIER_SITES', DFLA.SUPPLIER_SITE_ID ) SUPPLIER_SITE_ID,

DFLA.TOTAL_CONTRACT_AMOUNT,

DFLA.TOTAL_CONTRACT_QUANTITY,

DFLA.UNIT_LIST_PRICE,

DFLA.UNIT_SELLING_PRICE,

DFLA.SERVICE_DURATION_PERIOD_CODE,

DFLA.SERVICE_DURATION,

DFLA.SALES_PRODUCT_TYPE_CODE,

DDR.DOC_ID PO_HEADER_ID,

DDR.DOC_LINE_ID PO_LINE_ID,

DDR.DOC_SUBLINE_ID PO_LINE_LOCATION_ID,

DOC.AVG_UNIT_SELLING_PRICE,

DOC.CAN_ADJUST_FLAG,

DOC.CHARGE_APPLIES_TO,

DOC.CHARGE_DEFINITION_CODE,

DOC.CHARGE_SUBTYPE_CODE,

DOC.CHARGE_TYPE_CODE,

DOC.GSA_UNIT_PRICE,

DOC.ORDER_CHARGE_ID,

DOC.PARENT_ENTITY_CODE,

DOC.PARENT_ENTITY_ID,

DOC.PRICED_QUANTITY,

DOC.PRICED_QUANTITY_UOM_CODE,

DOC.PRICE_PERIODICITY_CODE,

DOC.PRICE_TYPE_CODE,

DOC.PRIMARY_FLAG,

DOC.ROLLUP_FLAG,

DOC.SEQUENCE_NUMBER CHARGE_SEQUENCE_NUMBER,

DOCC.EXPLANATION,

DOCC.EXPLANATION_MESSAGE_NAME,

DOCC.HEADER_CURRENCY_EXT_AMOUNT,

DOCC.HEADER_CURRENCY_UNIT_PRICE,

DOCC.HEADER_CURR_DURATION_EXT_AMT,

DOCC.ORDER_CHARGE_COMPONENT_ID,

DOCC.PARENT_CHARGE_COMPONENT_ID,

DOCC.PERCENT_OF_COMPARISON_ELEMENT,

DOCC.PRICE_ELEMENT_CODE,

DOCC.PRICE_ELEMENT_USAGE_CODE,

DOCC.PRICING_SOURCE_ID,

DOCC.PRICING_SOURCE_TYPE_CODE,

DOCC.SEQUENCE_NUMBER CHARGE_COMP_SEQUENCE_NUMBER,

DOCC.TAX_INCLUDED_FLAG,

DOTD.ORDER_TAX_DETAIL_ID,

DOTD.HDR_CURR_TAX_UNIT_AMT,

DOTD.HDR_CURR_TAXABLE_UNIT_AMT,

DOTD.TAX_INCLUDED_FLAG INCLUSIVE_TAX_FLAG,

DOTD.TAX_RATE,

DOTD.TAX_RATE_ID,

DOTD.TAX_EXEMPT_CERTIFICATE_NUMBER,

DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'ZX_EXEMPTION_REASON_CODE', DOTD.TAX_EXEMPT_REASON_CODE ) TAX_EXEMPT_REASON_CODE,

DFLA.SECONDARY_UOM,

DFLA.SECONDARY_ORDERED_QTY,

DFLA.SECONDARY_SHIPPED_QTY,

DFLA.SECONDARY_RMA_DELIVERED_QTY,

DFLA.PRICE_USING_SEC_UOM_FLAG,

DFLA.SECONDARY_FULFILLED_QTY

FROM

DOO_HEADERS_ALL DHA,

DOO_LINES_ALL DLA,

DOO_FULFILL_LINES_ALL DFLA,

DOO_DOCUMENT_REFERENCES DDR,

DOO_ORDER_CHARGES DOC,

DOO_ORDER_CHARGE_COMPONENTS DOCC,

DOO_ORDER_TAX_DETAILS DOTD

WHERE DHA.HEADER_ID = DLA.HEADER_ID(+)

AND DLA.LINE_ID = DFLA.LINE_ID (+)

AND DFLA.FULFILL_LINE_ID = DOC.PARENT_ENTITY_ID(+) AND DOC.PARENT_ENTITY_CODE(+) = 'LINE'

AND DFLA.FULFILL_LINE_ID = DDR.FULFILL_LINE_ID(+) AND DDR.DOC_REF_TYPE(+) = 'DROPSHIP_PO_REFERENCE'

AND DOC.ORDER_CHARGE_ID = DOCC.ORDER_CHARGE_ID (+)

AND DOCC.ORDER_CHARGE_COMPONENT_ID = DOTD.ORDER_CHARGE_COMPONENT_ID(+)