BIP – Query to find Primary flags for a Worker

There are multiple primary flags for a worker in assignment table. Namely – Primary Assignment Flag, Primary Work Terms flag and Primary flag.

Below query can be used to check these flags:

--> Primary Flags Query
SELECT papf.person_number
      ,ppnf.first_name
      ,ppnf.last_name
      ,to_char(ppos.date_start,'RRRR/MM/DD') start_date
	  ,paam.assignment_number
      ,to_char(paam.effective_start_date,'RRRR/MM/DD') asg_eff_start_date
      ,to_char(paam.effective_end_date,'RRRR/MM/DD') asg_eff_end_date
	  ,paam.action_code
	  ,pastt.user_status
	  ,paam.assignment_status_type
	  ,paam.primary_flag
	  ,paam.primary_assignment_flag
	  ,paam.primary_work_relation_flag
	  ,ppos.primary_flag "WR Table Primary Flag"
  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERSON_NAMES_F ppnf
      ,PER_ALL_ASSIGNMENTS_M paam
	  ,PER_PERIODS_OF_SERVICE ppos
      ,PER_ASSIGNMENT_STATUS_TYPES_TL pastt
WHERE papf.person_id = ppnf.person_id
  AND ppnf.name_type = 'GLOBAL'
  AND papf.person_id = paam.person_id
  AND paam.period_of_service_id = ppos.period_of_service_id
  AND paam.assignment_status_type_id = pastt.assignment_status_type_id
  AND paam.effective_sequence = 1
  AND paam.assignment_type NOT LIKE '%T'
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date 
  AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date 
  AND pastt.language = 'US'
  AND papf.person_number IN ('500035','500036')
ORDER BY 1, 6