Sales Person information for an Order.sql

-- 1. Obtain Sales Person information if the Sales Person ID is known
SELECT
	jrs.SALESREP_NUMBER
  , jrs.status
  , jrs.START_DATE_ACTIVE
  , jrs.end_date_active
  , hp.PARTY_Number
  , hp.party_name
  , hp.person_first_name
  , hp.person_last_name
FROM
	fusion.JTF_RS_SALESREPS JRS
  , fusion.HZ_PARTIES       HP
WHERE
	jrs.resource_id =
	&SALESPERSON_ID
	AND jrs.RESOURCE_ID = PARTY_ID
SELECT
	JRS.RESOURCE_SALESREP_ID
  , JRS.RESOURCE_ID
  , JRS.SALESREP_NUMBER
  , JRS.STATUS
  , asg.ASSIGNMENT_TYPE
  , HZP.PARTY_NAME SALESREP_NAME
  , HZP.PARTY_NUMBER
  , u.person_id
  , asg.BUSINESS_UNIT_ID
  , STS.SET_NAME
  , u.USERNAME
FROM
	fusion.JTF_RS_SALESREPS      JRS
  , fusion.HZ_PARTIES            HZP
  , fusion.per_users             u
  , fusion.per_all_assignments_f asg
  , fusion.FND_SETID_SETS_VL     sts
WHERE
	JRS.RESOURCE_ID         = HZP.PARTY_ID
	AND u.user_guid         = HZP.user_guid
	AND asg.person_id       = u.person_id
	AND jrs.set_id          = sts.set_ID
	AND asg.ASSIGNMENT_TYPE = 'E'
	AND JRS.STATUS          = 'A'
;

-- 2. Obtain Sales Person Information set at Order Header
SELECT
	dha.source_order_number
  , dha.salesperson_id
  , dsc.SALESPERSON_ID
  , dsc.PERCENT
  , dsc.SALES_CREDIT_TYPE_ID
  , jrs.SALESREP_NUMBER
  , jrs.status
  , hp.PARTY_Number
  , hp.party_name
  , hp.person_first_name
  , hp.person_last_name
FROM
	fusion.doo_headers_all   dha
  , fusion.DOO_SALES_CREDITS DSC
  , fusion.JTF_RS_SALESREPS  JRS
  , fusion.HZ_PARTIES        HP
WHERE
	dha.source_order_number IN ('&ORDER_NUMBER')
	AND DHA.header_id      = DSC.header_id
	AND dha.submitted_flag = 'Y'
	AND dsc.salesperson_id = jrs.RESOURCE_ID
	AND dha.creation_date BETWEEN jrs.START_DATE_ACTIVE AND jrs.end_date_active
	AND jrs.RESOURCE_ID = PARTY_ID
ORDER BY
	dha.creation_Date DESC
-- 3. Obtain Sales Person Information set at Order Line
SELECT
	dha.source_order_number
  , dha.salesperson_id
  , dfla.fulfill_line_id
  , dfla.FULFILL_LINE_NUMBER
  , dfla.source_line_number
  , dsc.SALESPERSON_ID
  , dsc.PERCENT
  , dsc.SALES_CREDIT_TYPE_ID
  , jrs.SALESREP_NUMBER
  , jrs.status
  , hp.PARTY_Number
  , hp.party_name
  , hp.person_first_name
  , hp.person_last_name
FROM
	fusion.doo_headers_all       dha
  , fusion.doo_fulfill_lines_all dfla
  , fusion.DOO_SALES_CREDITS     DSC
  , fusion.JTF_RS_SALESREPS      JRS
  , fusion.HZ_PARTIES            HP
WHERE
	dha.source_order_number IN ('&ORDER_NUMBER')
	AND dha.header_id        = dfla.header_id
	AND Dfla.fulfill_line_id = DSC.fulfill_line_id
	AND dha.submitted_flag   = 'Y'
	AND dsc.salesperson_id   = jrs.RESOURCE_ID
	AND dha.creation_date BETWEEN jrs.START_DATE_ACTIVE AND jrs.end_date_active
	AND jrs.RESOURCE_ID = PARTY_ID
ORDER BY
	dfla.fulfill_line_id DESC