Query to find Salesperson associated with a Sales Order in Oracle Apps R12

SELECT oha.order_number,
       jrre.resource_name SALES_PERSON,
       jrre.source_email,
       jrs.name
FROM   oe_order_headers_all OHA,
       jtf_rs_salesreps jrs,
       jtf_rs_resource_extns_vl JRRE
WHERE  1 = 1
       AND oha.salesrep_id = jrs.salesrep_id
       AND JRS.resource_id = JRRE.resource_id
       AND oha.order_number = :order_number; 

(or)

SELECT xx.resource_name sales_person
FROM   jtf_rs_salesreps jrs,
       jtf_rs_resource_extns_vl xx
WHERE  jrs.resource_id = xx.resource_id
       AND jrs.org_id = rct.org_id
       AND jrs.salesrep_id IN (SELECT ool.salesrep_id
                               FROM   oe_order_lines_all ool
                               WHERE  ool.line_id = ra_customer_trx_lines_all.interface_line_attribute6); 

(or)

SELECT Max(Nvl(jrs.name, jrd.resource_name)) 
FROM   oe_order_headers_all ooh, 
       apps.jtf_rs_salesreps jrs, 
       apps.jtf_rs_defresources_v jrd, 
       apps.ra_customer_trx_all rct 
WHERE  ooh.salesrep_id = jrs.salesrep_id 
       AND jrs.org_id = ooh.org_id 
       AND jrs.resource_id = jrd.resource_id 
       AND rct.interface_header_context = 'ORDER ENTRY' 
       AND rct.org_id = ooh.org_id 
       AND To_char(ooh.order_number) = rct.interface_header_attribute1 
       AND rct.customer_trx_id = :customer_trx_id 
       AND rct.org_id = :org_id;

The field JTF_RS_SALESREPS.NAME is an obsolete column in 11i release. Since salesrep is now tied to a resource, resource name should be used instead of Salesrep Name. The Resource name is maintained in JTF_RS_RESOURCE_EXTNS_VL.RESOURCE_NAME.

You can use the following SQL statement to determine the salesrep name:

SELECT rs.salesrep_number,
       --rs.NAME salesrep_name,
       res.resource_name salesrep_name,
       hou.name          org_name,
       rs.salesrep_id,
       rs.org_id,
       rs.start_date_active,
       rs.end_date_active
FROM   apps.jtf_rs_salesreps rs,
       apps.jtf_rs_resource_extns_vl RES,
       hr_organization_units hou
WHERE  hou.organization_id = rs.org_id
       AND rs.resource_id = res.resource_id;