1 Reply Latest reply: Mar 1, 2011 6:30 AM by 337552 RSS

    How to find territory for a sales order?

    337552
      Hello,

      How do we find the territory for a sales order?
      Please help.

      Regards,
      Sam
        • 1. Re: How to find territory for a sales order?
          761288
          As you know, the territory keyflex is multi-segment so you'll need to adapt the following to show which segments you want. The SQL below prompts for OU org_id, order number and line number.

          Regards,
          Jom

          SELECT
          terr.segment1
          FROM
          hz_cust_site_uses_all ship_su
          , hz_party_sites ship_ps
          , hz_locations ship_loc
          , hz_cust_acct_sites_all ship_cas
          , oe_order_headers_all ooh
          , oe_order_lines_all ool
          , ra_territories terr
          WHERE 1=1
          AND ooh.org_id = &org_id
          AND ooh.order_number = &order_number
          AND ool.header_id = ooh.header_id
          AND ool.line_number = &line_number
          AND ool.ship_to_org_id = ship_su.site_use_id(+)
          AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
          AND ship_cas.party_site_id = ship_ps.party_site_id(+)
          AND ship_loc.location_id(+) = ship_ps.location_id
          AND ship_su.territory_id = terr.territory_id(+)