Sales Order Comprehensive Line Export in Oracle Fusion

 Sales Order Comprehensive Line Export

SELECT DISTINCT bu.bu_name                                   AS
                "Business Unit Name",
                Trunc(ha.ordered_date)                       AS
                "Sales Ordered Date",
                party.party_name                             AS "Customer Name",
                bill_party.party_name                        AS
                "Bill-to Customer Name",
                bill_acc.account_number                      AS
                "Bill-to Account Number",
                ship_party.party_name                        AS
                "Ship-to Customer Name",
                hl.address1
                || ', '
                || hl.city                                   AS
                "Ship-to Address",
                ha.order_type_code                           AS "Order Type",
                msi.item_number                              AS "Item Number",
                msi.item_number                              AS "Item Name",
                msi.description                              AS
                "Item Description",
                dla.status_code                              AS "Status",
                dla.ordered_qty                              AS "Quantity",
                dla.ordered_uom                              AS "UOM",
                dla.unit_selling_price                       AS "Item Price",
                ship_site.party_site_number                  AS "Ship To Site",
                ( dla.ordered_qty * dla.unit_selling_price ) AS "Amount",
                -- Billing Frequency (usually stored at fulfillment level for recurring)
                (SELECT periodicity_code
                 FROM   doo_billing_plans
                 WHERE  fulfill_line_id = dfa.fulfill_line_id
                        AND ROWNUM = 1)                      AS
                "Billing Frequency",
                -- Number of Billing Periods
                (SELECT billing_num_of_periods
                 FROM   doo_billing_plans
                 WHERE  fulfill_line_id = dfa.fulfill_line_id
                        AND ROWNUM = 1)                      AS
                "Number of Billing Periods",
                dla.line_type_code                           AS "Line Type",
                dla.canceled_flag                            AS
                "Cancel Backorders",
                dla.open_flag                                AS
                "Enforce Single Shipment",
                dla.line_number
FROM   doo_headers_all ha,
       doo_lines_all dla,
       doo_fulfill_lines_all dfa,
       fun_all_business_units_v bu,
       hz_parties party,
       (SELECT header_id,
               Max(cust_acct_id) AS cust_acct_id
        FROM   doo_order_addresses
        WHERE  address_use_type = 'BILL_TO'
        GROUP  BY header_id) bill_addr_fix,
       (SELECT header_id,
               Max(party_id)      AS party_id,
               Max(party_site_id) AS party_site_id
        FROM   doo_order_addresses
        WHERE  address_use_type = 'SHIP_TO'
        GROUP  BY header_id) ship_addr_fix,
       hz_cust_accounts bill_acc,
       hz_parties bill_party,
       hz_parties ship_party,
       hz_party_sites ship_site,
       hz_locations hl,
       egp_system_items_vl msi
WHERE
  -- Primary Joins
  ha.header_id = dla.header_id
  AND dla.line_id = dfa.line_id
  AND ha.org_id = bu.bu_id
  AND ha.sold_to_party_id = party.party_id
  AND dla.inventory_item_id = msi.inventory_item_id
  AND dla.inventory_organization_id = msi.organization_id
  -- Bill-to Joins
  AND ha.header_id = bill_addr_fix.header_id (+)
  AND bill_addr_fix.cust_acct_id = bill_acc.cust_account_id (+)
  AND bill_acc.party_id = bill_party.party_id (+)
  --Ship-to Joins
  AND ha.header_id = ship_addr_fix.header_id (+)
  AND ship_addr_fix.party_id = ship_party.party_id (+)
  AND ship_addr_fix.party_site_id = ship_site.party_site_id (+)
  AND ship_site.location_id = hl.location_id (+)
  -- Parameters
  AND ha.order_number = '98514'
--AND ha.order_number = :p_order_number
--AND bu.bu_name = :p_bu_name
ORDER  BY dla.line_number ASC