Show Pricing Information - Segment, Strategy and Price List on a priced order. Show Price Lists that an Item is Included in.sql

-- 1. Show Pricing information derived when pricing an order.
SELECT  dha.source_order_number "Source Order Number"                                                             ,
        dha.status_code "Order Status"                                                                            ,
        dha.change_version_number "Order Version"                                                                 ,
        DECODE(dha.freeze_price_flag,'N','Fusion Priced Order','Fusion did not Price the Order') "Fusion Pricing" ,
        DHA.PRICING_SEGMENT_CODE                                                                                  ,
        dha.PRICING_SEGMENT_EXPLANATION                                                                           ,
        dha.PRICING_STRATEGY_EXPLANATION                                                                          ,
        qpsv.name "Pricing Strategy Name"                                                                         ,
        dla.display_line_number "Line Number"                                                                     ,
        egp.item_number "Item"                                                                                    ,
        qplt.name "Price List Name"
FROM    fusion.DOO_HEADERS_ALL dha           ,
        fusion.DOO_FULFILL_LINES_ALL dfla    ,
        fusion.DOO_LINES_ALL dla             ,
        fusion.QP_PRICING_STRATEGIES_VL qpsv ,
        fusion.QP_PRICE_LISTS_VL qplt        ,
        fusion.EGP_SYSTEM_ITEMS_B egp
WHERE   dha.header_id               = dfla.header_id
        AND dfla.line_id            = dla.line_id
        AND submitted_flag          = 'Y'
        AND dha.source_order_number = '&SOURCE_ORDER_NUMBER'
        AND dfla.inventory_item_id  = egp.inventory_item_id
        AND dfla.fulfill_org_id     = egp.organization_id
        AND dha.pricing_strategy_id = qpsv.pricing_strategy_id
        AND qplt.price_list_id      = dfla.applied_price_list_id
-- Price Lists that include the item on a Fulfillment Line - NOTE: this does not search ALL_ITEMS and Item Categories. The pricing information described may not match that chosen when the item was priced as the pricing setup may have changed since the order was priced.

SELECT  dha.source_order_number "Source Order Number"                                                             ,
        dha.status_code "Order Status"                                                                            ,
        dha.change_version_number "Order Version"                                                                 ,
        DECODE(dha.freeze_price_flag,'N','Fusion Priced Order','Fusion did not Price the Order') "Fusion Pricing" , 
        dla.display_line_number "Line Number"                                                                     ,
        egp.inventory_item_id                                                                                     ,
        egp.item_number "Item"                                                                                    ,
        qplt.name "Price List Name"                                                                               ,
        dfla.ORDERED_UOM                                                                                          ,
        qpli.PRICING_UOM_CODE                                                                                     ,
        qplc.BASE_PRICE                                                                                           ,
        qplc.USAGE_UOM_CODE                                                                                       ,
        qplc.PRICE_PERIODICITY_CODE                                                                               ,
        qplc.CAN_ADJUST_FLAG                                                                                      ,
        qplc.END_DATE                                                                                             ,
        qplc.TIERED_PRICING_HEADER_ID                                                                             ,
        qplc.ATTRIBUTE_PRICING_MATRIX_ID                                                                          ,
        qplc.CALCULATION_METHOD_CODE
FROM    fusion.DOO_HEADERS_ALL dha           ,
        fusion.DOO_FULFILL_LINES_ALL dfla    ,
        fusion.DOO_LINES_ALL dla             ,
        fusion.QP_PRICING_STRATEGIES_VL qpsv ,
        fusion.QP_PRICE_LISTS_VL qplt        ,
        fusion.QP_PRICE_LIST_ITEMS qpli      ,
        fusion.QP_PRICE_LIST_CHARGES qplc    ,
        fusion.EGP_SYSTEM_ITEMS_B egp
WHERE   dha.header_id                    = dfla.header_id
        AND dfla.line_id                 = dla.line_id
        AND submitted_flag               = 'Y'
        AND dha.source_order_number      = '&SOURCE_ORDER_NUMBER'
        AND dfla.inventory_item_id       = egp.inventory_item_id
        AND dfla.fulfill_org_id          = egp.organization_id
        AND dha.pricing_strategy_id      = qpsv.pricing_strategy_id
        AND qplt.price_list_id           = qpli.price_list_id
        AND qpli.item_id                 = dfla.inventory_item_id
        AND qplc.parent_entity_id        = qpli.PRICE_LIST_ITEM_ID
        AND qplc.PARENT_ENTITY_TYPE_CODE = 'PRICE_LIST_ITEM'
ORDER BY egp.INVENTORY_ITEM_ID ,
        qplt.Name