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 old_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