QA_DATA_FEED_FIELD_VW

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