SELECT b.ext_file_id "File Id",
f.seq_num,
'Record ' || to_char(f.seq_num) || ':' || c.name "Record Name",
g.seq_num "Data Element Seq",
d.ext_data_elmt_id "Ext Data Element",
d.name "Data Element Name",
g.strt_pos "Start Position",
g.dlmtr_val "Delimiter",
g.rqd_flag "Required",
nvl(d.just_cd, 'L') "Just",
h.meaning "Format Mask",
d.data_elmt_typ_cd "Data Element Type",
d.string_val "String Value",
decode(d.data_elmt_typ_cd, 'D', ' ' || d.dflt_val) "Default Value",
d.ttl_fnctn_cd "Total Function",
d.ttl_cond_oper_cd "Operator",
d.ttl_cond_val "Condition Value",
d.ttl_sum_ext_data_elmt_id "Sum Data Element",
d.ttl_cond_ext_data_elmt_id "Condition Data Element",
' ' || e.dcd_val "Decode Value",
BEN_BENXLAYT_XMLP_PKG.commentsformula(d.ttl_fnctn_cd, d.data_elmt_typ_cd, d.string_val, d.ttl_cond_ext_data_elmt_id, d.ext_data_elmt_id, d.ttl_sum_ext_data_elmt_id) Comments
FROM ben_ext_file b,
ben_ext_rcd c,
ben_ext_data_elmt d,
ben_ext_data_elmt_decd e,
ben_ext_rcd_in_file f,
ben_ext_data_elmt_in_rcd g,
hr_lookups h
WHERE b.ext_file_id = f.ext_file_id
AND f.ext_rcd_id = c.ext_rcd_id
AND f.hide_flag <> 'Y'
AND c.ext_rcd_id = g.ext_rcd_id
AND g.hide_flag <> 'Y'
AND g.ext_data_elmt_id = d.ext_data_elmt_id
AND d.ext_data_elmt_id = e.ext_data_elmt_id (+)
AND d.frmt_mask_cd = h.lookup_code (+)
AND h.lookup_type (+) = 'BEN_EXT_FRMT_MASK'
and b.ext_file_id=:Dfn_File_Id
--ORDER BY f.seq_num,3
ORDER BY f.seq_num, d.ext_data_elmt_id, g.seq_num,3 |
