Linking 'Sales Orders' to 'Sales Agreements' (also known as BSA : Blanket Sales Orders) in Oracle EBS R12

/*SQL Query to Trace Blanket Sales Agreements from Sales Order Headers and Lines*/ 

In Oracle E-Business Suite (EBS) Release 12, Blanket Sales Agreements (BSA) are used to establish long-term agreements between a customer and a supplier. These agreements define specific terms, such as pricing, items, and quantities, which are then "released" via standard Sales Orders.

Key Tables: 

OE_BLANKET_HEADERS_ALL: Table to capture sales agreement header details..

OE_BLANKET_HEADERS_EXT: Table to capture sales agreement header details, specific to sales agreement functionality.

OE_BLANKET_HEADERS_HIST: Table to capture sales agreement header history. Primary for storing revisions.

Query: 

SELECT
-- Blanket Header Details
obh.order_number                                        AS bsa_number,
obh.header_id                                           AS bsa_header_id,
obh.agreement_id,
obhe.start_date_active                                  AS bsa_start_date,
obhe.end_date_active                                    AS bsa_end_date,
-- Blanket Line Details
obl.line_number                                         AS bsa_line_num,
obl.inventory_item_id,
(SELECT segment1
 FROM   mtl_system_items_b msi
 WHERE  msi.inventory_item_id = obl.inventory_item_id
        AND msi.organization_id = ool.ship_from_org_id) bsa_item,
obl.ordered_item                                        AS bsa_ordered_item,
-- Sales Order Header Details
ooh.order_number                                        AS sales_order_number,
ooh.ordered_date,
ooh.flow_status_code                                    AS order_status,
-- Sales Order Line Details
ool.line_number                                         AS so_line_num,
ool.ordered_item                                        AS so_ordered_item,
ool.order_quantity_uom,
ool.ordered_quantity
FROM   oe_order_headers_all ooh,
       oe_order_lines_all ool,
       oe_blanket_headers_all obh,
       oe_blanket_lines_all obl,
       oe_blanket_headers_ext obhe
WHERE  ooh.header_id = ool.header_id
       -- Link SO Header to BSA Header
       AND ooh.blanket_number = obh.order_number
       -- Link BSA Header to Extension table
       AND obh.order_number = obhe.order_number
       -- Link SO Line to BSA Line
       AND ool.blanket_number = obh.order_number
       AND ool.blanket_line_number = obl.line_number
       AND obh.header_id = obl.header_id
       -- Specific Order Filter
       AND ooh.order_number = :P_ORDER_NUMBER;