PER Employment Equity

SELECT
meaning     "f4_p_naic",
decode(segment4,'FR','Fulltime Regular'
	         ,'PR', 'Part-time Regular'
	         ,'PT' , 'Temporary') "f4_p_emp_cat",
context || decode(segment4,'FR','A',
	            'PR','B',
	            'PT','C') "f4_p_type",
segment2 "f4_p_name",
decode(context,'FORM4','HIRED'
                         ,'FORM5','PROMOTED'
                         ,'FORM6','TERMINATED') "f4_p_fname",
segment3 "f4_p_eeog",
nvl(to_number(segment5),0) "f4_p_total",
nvl(to_number(segment6),0) "f4_p_totfe",
nvl(to_number(segment7),0) "f4_p_totma",
nvl(to_number(segment8),0)  "f4_p_totab",
nvl(to_number(segment9),0) "f4_p_feab",
nvl(to_number(segment10 ),0) "f4_p_maab",
nvl(to_number(segment11),0)  "f4_p_totvi",
nvl(to_number(segment12 ),0)  "f4_p_fevi",
nvl(to_number(segment13),0)  "f4_p_mavi",
nvl(to_number(segment14),0) "f4_p_totdi",
nvl(to_number(segment15),0)  "f4_p_fedi",
nvl(to_number(segment16),0)  "f4_p_madi", 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totalformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_total, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totmaformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_totma, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totfeformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_totfe, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totabformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_totab, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_maabformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_maab, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_feabformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_feab, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totdiformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_totdi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_madiformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_madi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_fediformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_fedi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_totviformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_totvi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_maviformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_mavi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_feviformula(context || decode ( segment4 , 'FR' , 'A' , 'PR' , 'B' , 'PT' , 'C' ), segment2) CF_f5_p_fevi, 
	PER_PERCAEER_XMLP_PKG.cf_f5_p_promptformula() CF_f5_p_prompt
from
per_ca_ee_report_lines pcer1,
hr_lookups
where
request_id=:p_conc_request_id and
context in ('FORM4','FORM5','FORM6')  and
segment1 ='PROVINCE' and
segment20='Y' and
lookup_type = 'NAIC' and
lookup_code = segment21 and
exists
( 
   select pcer2.segment4
   from    per_ca_ee_report_lines pcer2
   where  pcer2.request_id = pcer1.request_id 
   and      pcer2.context = 'FORM14'
   and      pcer2.segment1 = pcer1.segment1
   and      pcer2.segment2 = pcer1.segment2
   and      pcer2.segment3 <> '0' 
   and      pcer2.segment4 = 'Y'
)
order by 1,4,3