作者 DBLobster [sql] 2008-01-14 17:00 (点击下载)

SELECT DISTINCT
V_PLY_POLICY.PONO, V_PLY_POLICY.APPLNO,
TO_CHAR(V_PLY_POLICY.APPLRECDT, 'YYYY-MM-DD'),
TO_CHAR(V_PLY_POLICY.INFORCEDT, 'YYYY-MM-DD'),
TO_CHAR(V_PLY_RECEIPT.APPLRECDT, 'YYYY-MM-DD'),
V_PLY_POAGENT.AGCODE, V_AGTBASE_ANTITHES_INDINFO.FULLNAME,
V_PDF_PLANBASE.PLANTITLE, V_PLY_COVERAGE.INIMODEPR
FROM
V_PLY_POLICY, V_PLY_POAGENT,
V_AGTBASE_ANTITHES_INDINFO, V_RELATIVES_INDINFO, V_PLY_BENEFCIARY, V_PLY_RECEIPT,
V_PLY_COVERAGE, V_PDF_PLANBASE, V_AGT_AGTBASE
WHERE V_PLY_POLICY.PONO LIKE '015__01%'
  AND TO_CHAR(V_PLY_POLICY.APPLRECDT, 'YYYYMMDD') >= ?
  AND TO_CHAR(V_PLY_POLICY.APPLRECDT, 'YYYYMMDD') <= ?
--  AND TO_CHAR(V_PLY_POLICY.INFORCEDT, 'YYYYMMDD') >= ?
--  AND TO_CHAR(V_PLY_POLICY.INFORCEDT, 'YYYYMMDD') <= ?
  AND V_PLY_POLICY.POSTACD = 'P'
  AND V_PLY_POAGENT.RELATION = 'W'
  AND V_PLY_POAGENT.PONO =  V_PLY_POLICY.PONO
  AND V_PLY_POAGENT.AGCODE = V_AGTBASE_ANTITHES_INDINFO.ANTIID
  AND V_RELATIVES_INDINFO.PONO = V_PLY_POLICY.PONO
  AND V_PLY_BENEFCIARY.PONO = V_PLY_POLICY.PONO
  AND V_PLY_RECEIPT.PONO(+) = V_PLY_POLICY.PONO
  AND V_PLY_RECEIPT.DOCTP = '06'
  AND V_RELATIVES_INDINFO.CLIENTID != V_AGTBASE_ANTITHES_INDINFO.CLIENTID
  AND V_PLY_BENEFCIARY.CLIENTID != V_AGTBASE_ANTITHES_INDINFO.CLIENTID
  AND V_AGTBASE_ANTITHES_INDINFO.ANTIID = V_AGT_AGTBASE.AGCODE
  AND V_AGT_AGTBASE.AGTSTSID != '4'
  AND (V_RELATIVES_INDINFO.CLIENTID IN
(SELECT V_AGTBASE_ANTITHES_INDINFO.CLIENTID FROM V_AGTBASE_ANTITHES_INDINFO, V_AGT_AGTBASE
WHERE V_AGTBASE_ANTITHES_INDINFO.ANTIID LIKE 'I%'
  AND V_AGTBASE_ANTITHES_INDINFO.ANTIID = V_AGT_AGTBASE.AGCODE
  AND V_AGT_AGTBASE.AGTSTSID != '4')
  OR V_PLY_BENEFCIARY.CLIENTID IN
(SELECT V_AGTBASE_ANTITHES_INDINFO.CLIENTID FROM V_AGTBASE_ANTITHES_INDINFO, V_AGT_AGTBASE
WHERE V_AGTBASE_ANTITHES_INDINFO.ANTIID LIKE 'I%'
  AND V_AGTBASE_ANTITHES_INDINFO.ANTIID = V_AGT_AGTBASE.AGCODE)
  AND V_AGT_AGTBASE.AGTSTSID != '4')
  AND V_PLY_COVERAGE.PONO = V_PLY_POLICY.PONO
  AND V_PDF_PLANBASE.PLANCODE = V_PLY_COVERAGE.PLANCODE