BIP – Extract Supervisor HDL details

SELECT a.datarow
  FROM (
SELECT 'METADATA|AssignmentSupervisor|AssignmentSupervisorId|AssignmentNumber|ManagerAssignmentNumber|ManagerPersonNumber|ManagerType|AssignmentId|EffectiveEndDate|EffectiveStartDate|ActionCode|ManagerId|PersonId|PrimaryFlag|ReasonCode|ManagerAssignmentId|SourceSystemId|SourceSystemOwner' DATAROW, 1 SEQUENCE
   FROM DUAL
UNION ALL
SELECT   DISTINCT 'MERGE'
         || CHR (124)
		 ||'AssignmentSupervisor'
		 || CHR (124)
		 ||NULL
		 || CHR (124)
		 ||paaf.assignment_number
		 || CHR (124)
         ||paaf_m.assignment_number
		 || CHR (124)
		 ||papf.person_number 
		 || CHR (124)
         ||pasup.manager_type
         || CHR (124)
		 ||NULL		 
         || CHR (124)		 
		 ||TO_CHAR (pasup.effective_end_date,'DD/MON/YYYY')
		 || CHR (124)
         ||TO_CHAR (pasup.effective_start_date,'DD/MON/YYYY')
         || CHR (124)         
	     ||pab.action_code
		 || CHR (124)         
	     ||NULL
		 || CHR (124)         
	     ||NULL
		 || CHR (124)         
	     ||pasup.primary_flag
		 || CHR (124)         
	     ||parb.action_reason_code
		 || CHR (124)         
	     ||NULL
		 || CHR (124)        
         ||hikm.source_system_id
		 || CHR (124)         		 
         ||hikm.source_system_owner DATAROW, 2 SEQUENCE
    FROM per_all_people_f papf,
         per_assignment_supervisors_f pasup,
         per_all_assignments_m paaf,
         per_all_assignments_m paaf_m,
		 hrc_integration_key_map hikm,
		 per_action_occurrences pao,
		 per_action_reasons_b parb,
		 per_actions_b pab
   WHERE pasup.assignment_id = paaf.assignment_id
     AND pasup.manager_assignment_id = paaf_m.assignment_id
     AND papf.person_id = paaf_m.person_id
     AND paaf.assignment_type = 'E'
     --AND paaf.assignment_status_type = 'ACTIVE'
     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND pasup.effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
     AND pasup.effective_start_date BETWEEN paaf_m.effective_start_date AND paaf_m.effective_end_date
     AND pasup.assignment_supervisor_id = hikm.surrogate_id
     AND OBJECT_NAME  = 'AssignmentSupervisor'
     AND pao.action_occurrence_id = pasup.action_occurrence_id
     AND pao.action_id = pab.action_id
     AND pao.action_reason_id = parb.action_reason_id	 
) a 
ORDER BY SEQUENCE