create or replace view QA_DATA_FEED_FIELD_VW as 
select 
  (setA || setC || setL || setM || setD || setO || setR) as SET_CODE,
  sdsdf.DATA_FILE_NAME,
  sdff.FIELD_NAME,
  to_char(sdff.FIELD_POSITION) as FIELD_POSITION,
  sdsdf.DATA_FILE_CODE,
  srl.REF_SCHEMA_NAME,
  srl.REF_TABLE_NAME,
  srl.REF_COLUMN_NAME,
  sdsdf.STG_TABLE_NAME,
  sdff.STG_TABLE_COLUMN_NAME,
  sfdt.DATA_TYPE_CODE,
  to_char(sdff.FIELD_DATA_LENGTH) as FIELD_DATA_LENGTH,
  to_char(sdff.FIELD_DATA_SCALE) as FIELD_DATA_SCALE,
  To_Char(Sdff.Nullable_Flag) As Nullable_Flag,
--  
  (case when sdfkc.FIELD_ORDER is not null 
      then 'D' || to_char(sdfkc.DATA_ERR_CHECK_PID) || ' ' || to_char(sdfkc.FIELD_ORDER) else ' ' end) ||
      (case when sdec_DUPL.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as DUPL,
--
  (case when sdec_INVDATE1.ERR_PID is not null then '!YMD1' else ' ' end) ||
      (case when sdec_INVDATE1.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as INVDATE1,
  (case when sdec_INVDATE2.ERR_PID is not null then '!YMD2' else ' ' end) ||
      (case when sdec_INVDATE2.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as INVDATE2,
  (case when sdec_INVLEN.ERR_PID is not null then 'Len' else ' ' end) ||
      (case when sdec_INVLEN.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as INVLEN,
  (case when sdec_INVNBR.ERR_PID is not null then '!123' else ' ' end) ||
      (case when sdec_INVNBR.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as INVNBR,
  (case when sdec_NV.ERR_PID is not null then 'NV' else ' ' end) ||
      (case when sdec_NV.CRITICAL_ERR_FLAG = 1 then '*' else '' end) as NV,
  (case when sdec_REF.ERR_PID is not null then 'Ref' else ' ' end) ||
      (CASE WHEN SDEC_REF.CRITICAL_ERR_FLAG = 1 THEN '*' ELSE '' END) AS "REF",
  (CASE WHEN SDEC_REF_RJCT.ERR_PID IS NOT NULL THEN 'Ref_Rjct' ELSE ' ' END) ||
      (Case When SDEC_REF_RJCT.Critical_Err_Flag = 1 Then '*' Else '' End) As "REF_RJCT",
  (case when sdec_PASTPED.ERR_PID is not null then 'PED' else ' ' end) ||
      (Case When sdec_PASTPED.Critical_Err_Flag = 1 Then '*' Else '' End) As PASTPED
--  
from (
  select 
    sdf.DATA_FEED_PID,
    sdf.DATA_FILE_CODE,
    sdf.DATA_FILE_NAME,
    sdf.STG_TABLE_NAME,
    sdf.DATA_FEED_ORDER,
    max(decode(DATA_SET_CODE, 'A', DATA_SET_CODE)) as setA, 
    max(decode(DATA_SET_CODE, 'C', DATA_SET_CODE)) as setC,
    max(decode(DATA_SET_CODE, 'L', DATA_SET_CODE)) as setL,
    max(decode(DATA_SET_CODE, 'M', DATA_SET_CODE)) as setM,
    max(decode(DATA_SET_CODE, 'D', DATA_SET_CODE)) as setD,
    max(decode(DATA_SET_CODE, 'O', DATA_SET_CODE)) as setO,
    max(decode(DATA_SET_CODE, 'R', DATA_SET_CODE)) as setR
  from STAGE.DATA_SET sds
  join STAGE.MODEL_FEED_SET smfs
    on smfs.DATA_SET_PID = sds.DATA_SET_PID
  join STAGE.DATA_FEED sdf
    on sdf.DATA_FEED_PID = smfs.DATA_FEED_PID
  group by sdf.DATA_FEED_PID, sdf.DATA_FILE_CODE, sdf.DATA_FILE_NAME, sdf.STG_TABLE_NAME, sdf.DATA_FEED_ORDER
  ) Sdsdf
--
join STAGE.DATA_FEED_FIELD sdff
  on  sdff.DATA_FEED_PID = sdsdf.DATA_FEED_PID
join STAGE.FIELD_DATA_TYPE sfdt
  on sfdt.FIELD_DATA_TYPE_PID = sdff.FIELD_DATA_TYPE_PID
--
left outer join STAGE.REF_LIST srl
  On Srl.Data_Feed_Field_Pid = Sdff.Data_Feed_Field_Pid
--  
left outer join STAGE.DATA_ERR_CHECK sdec_NV
  on sdec_NV.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  and sdec_NV.ERR_PID in (select ERR_PID from STAGE.ERR where ERR_CODE='NV')
left outer join STAGE.DATA_ERR_CHECK sdec_INVNBR
  on sdec_INVNBR.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  and sdec_INVNBR.ERR_PID in (select ERR_PID from STAGE.ERR where ERR_CODE='INVNBR')
left outer join STAGE.DATA_ERR_CHECK sdec_INVDATE1
  on sdec_INVDATE1.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  and sdec_INVDATE1.ERR_PID in (select ERR_PID from STAGE.ERR where ERR_CODE='INVDATE1')
left outer join STAGE.DATA_ERR_CHECK sdec_INVDATE2
  on sdec_INVDATE2.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  and sdec_INVDATE2.ERR_PID in (select ERR_PID from STAGE.ERR where ERR_CODE='INVDATE2')
left outer join STAGE.DATA_ERR_CHECK sdec_INVLEN
  on sdec_INVLEN.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  and sdec_INVLEN.ERR_PID in (select ERR_PID from STAGE.ERR where ERR_CODE='INVLEN')
left outer join STAGE.DATA_ERR_CHECK sdec_REF
  on sdec_REF.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  And Sdec_Ref.Err_Pid In (Select Err_Pid From Stage.Err Where Err_Code='REF')
left outer join STAGE.DATA_ERR_CHECK SDEC_REF_RJCT
  ON SDEC_REF_RJCT.DATA_FEED_FIELD_PID = SDFF.DATA_FEED_FIELD_PID
  And SDEC_REF_RJCT.Err_Pid In (Select Err_Pid From Stage.Err Where Err_Code='REF_RJCT')
left outer join STAGE.DATA_ERR_CHECK sdec_PASTPED
  on sdec_PASTPED.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
  And sdec_PASTPED.Err_Pid In (Select Err_Pid From Stage.Err Where Err_Code='PASTPED')
--  
left outer join STAGE.DATA_FEED_KEY_COLUMN sdfkc
  on sdfkc.DATA_FEED_FIELD_PID = sdff.DATA_FEED_FIELD_PID
left outer join STAGE.DATA_ERR_CHECK sdec_DUPL
  on sdec_DUPL.DATA_ERR_CHECK_PID = sdfkc.DATA_ERR_CHECK_PID
  And Sdec_Dupl.Err_Pid In (Select Err_Pid From Stage.Err Where Err_Code='DUPL')
--
order by
  sdsdf.DATA_FEED_ORDER,
  sdff.FIELD_POSITION;
/
exit