#----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_001) #--【抑止請求先一覧作成】 #--記事を取得する。 #----------------------------------------------------------- --KEY:CH_SELECT_001 SELECT CH0551.TAIO_DTM, CH0551.PRC_TAIO_KIROK_SBT_DTL_CD, CH0551.KIJI FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.SEIKY_KEI_NO = ? AND CH0551.PRC_TAIO_KIROK_SBT_DTL_CD IN ( ?, ?, ?, ?, ?, ?, ?, ?) AND CH0551.TAIO_DTM = (SELECT MAX(CH0551_GENE.TAIO_DTM) AS CH0551_MAX FROM CH_T_PRC_TAIOKRK CH0551_GENE WHERE CH0551_GENE.SEIKY_KEI_NO = CH0551.SEIKY_KEI_NO AND CH0551_GENE.PRC_TAIO_KIROK_SBT_DTL_CD = CH0551.PRC_TAIO_KIROK_SBT_DTL_CD AND CH0551_GENE.MK_FLG = '0' ) #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_002) #--【料金調整リスト作成】 #--電子ファイル管理番号を取得する。 #----------------------------------------------------------- --KEY:CH_SELECT_002 SELECT CH0551.EFILE_KANRI_NO FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.PRC_TAIO_KIROK_SBT_CD = ? AND SUBSTR(CH0551.TAIO_DTM,01,08) = ? AND CH0551.MK_FLG = ? ORDER BY CH0551.PRC_TAIO_USER_ID, CH0551.TAIO_DTM, CH0551.SEIKY_KEI_NO #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_003) #--【入金登録データ抽出処理】 #----------------------------------------------------------- --KEY:CH_SELECT_003 SELECT CH0551.PRC_TAIO_USER_ID ,CH0551.TAIO_DTM ,CH0551.SYR_KBN ,CH0551.SYR_KBN_NM ,CH0551.SEIKY_KEI_NO ,KK0491.SHS_KANA ,CH0551.KIROK_TG_YM ,CH0551.RECEIPT_YMD ,CH0551.NYUKIN_TRN_YMD ,CH0551.OUT_ORG_SHUNO_YMD ,CH0551.NYUKIN_ROUTE_CD ,CH0551.SHUNOKIKAN_CD ,CASE WHEN CH0551.NYUKIN_AMNT IS NOT NULL THEN CH0551.NYUKIN_AMNT ELSE 0 END AS NYUKIN_AMNT ,CASE WHEN CH0201.JUTO_SUM IS NOT NULL THEN CH0201.JUTO_SUM ELSE 0 END AS JUTO_SUM ,CASE WHEN CH0191.KNK_AMNT IS NOT NULL THEN CH0191.KNK_AMNT ELSE 0 END AS DPSIT ,CH0551.BANK_CD ,NVL(KK0511.BANK_NM, KK0511_GENE2.BANK_NM) AS BANK_NM ,CH0551.BANK_SHITEN_CD ,NVL(KK0511.BANK_SHITEN_NM, KK0511_GENE2.BANK_SHITEN_NM) AS BANK_SHITEN_NM ,CH0551.CRECARD_COMP_CD ,KK0531.CRECARD_COMP_NM ,KK0531.CREDIT_KOKAN_CD ,CH0551.CVSTORE_CD ,CH0181.CVSTORE_COMP_NM ,CH0551.ORG_CD ,ZM0061.ORG_NM ,CH0551.SKNKSITKCM_CD ,CH0601.SKNKSITKCM_NM FROM ( (SELECT CH0551_SUB.PRC_TAIO_USER_ID ,CH0551_SUB.SEIKY_KEI_NO ,CH0551_SUB.TAIO_DTM ,CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD ,CH0551_SUB.KIROK_TG_YM ,CH0111_SUB.SEIKY_NO ,CH0551_SUB.NYUKIN_NO ,DECODE(CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD,?,?,?,?,'') AS SYR_KBN ,DECODE(CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD,?,?,?,?,'') AS SYR_KBN_NM ,CH0111_SUB.NYUKIN_ROUTE_CD ,CH0111_SUB.SHUNOKIKAN_CD ,CH0111_SUB.OUT_ORG_SHUNO_YMD ,CH0111_SUB.NYUKIN_TRN_YMD ,CH0111_SUB.RECEIPT_YMD ,CH0111_SUB.NYUKIN_AMNT ,CH0111_SUB.CRECARD_COMP_CD ,CH0111_SUB.SKNKSITKCM_CD ,CASE WHEN CH0111_SUB.HRAKMISAKI_BANK_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '10' OR CH0111_SUB.NYUKIN_ROUTE_CD = '15' THEN CH0111_SUB.HRAKMISAKI_BANK_CD WHEN CH0111_SUB.MADOGUCHI_BANK_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '20' OR CH0111_SUB.NYUKIN_ROUTE_CD = '40' THEN CH0111_SUB.MADOGUCHI_BANK_CD WHEN CH0111_SUB.KHRI_BANK_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '30' OR CH0111_SUB.NYUKIN_ROUTE_CD = '35' THEN CH0111_SUB.KHRI_BANK_CD ELSE NULL END AS BANK_CD ,CASE WHEN CH0111_SUB.HRAKMISAKI_BANK_SHITEN_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '10' OR CH0111_SUB.NYUKIN_ROUTE_CD = '15' THEN CH0111_SUB.HRAKMISAKI_BANK_SHITEN_CD WHEN CH0111_SUB.MADOGUCHI_BANK_SHITEN_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '20' OR CH0111_SUB.NYUKIN_ROUTE_CD = '40' THEN CH0111_SUB.MADOGUCHI_BANK_SHITEN_CD WHEN CH0111_SUB.KHRI_BANK_SHITEN_CD IS NOT NULL AND CH0111_SUB.NYUKIN_ROUTE_CD = '30' OR CH0111_SUB.NYUKIN_ROUTE_CD = '35' THEN CH0111_SUB.KHRI_BANK_SHITEN_CD ELSE NULL END AS BANK_SHITEN_CD ,CH0111_SUB.CVSTORE_CD ,CASE WHEN CH0111_SUB.UKETORI_ORG_CD IS NOT NULL THEN CH0111_SUB.UKETORI_ORG_CD WHEN CH0111_SUB.SYAYOBUN_ORG_CD IS NOT NULL THEN CH0111_SUB.SYAYOBUN_ORG_CD WHEN CH0111_SUB.YUKEN_UKETORI_ORG_CD IS NOT NULL THEN CH0111_SUB.YUKEN_UKETORI_ORG_CD ELSE NULL END AS ORG_CD FROM CH_T_PRC_TAIOKRK CH0551_SUB LEFT OUTER JOIN CH_T_NYUKIN CH0111_SUB ON CH0111_SUB.NYUKIN_NO = CH0551_SUB.NYUKIN_NO WHERE (CH0551_SUB.PRC_TAIO_KIROK_SBT_CD = ? OR CH0551_SUB.PRC_TAIO_KIROK_SBT_CD = ?) AND (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? OR CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ?) AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = ? AND CH0551_SUB.MK_FLG = '0' ) CH0551 LEFT OUTER JOIN CH_T_SEIKY CH0051 ON CH0051.SEIKY_NO = CH0551.SEIKY_NO AND CH0051.MK_FLG = '0' LEFT OUTER JOIN (SELECT CH0201.NYUKIN_NO ,SUM(CH0201.JUTO_AMNT) AS JUTO_SUM FROM CH_T_JUTO CH0201 ,CH_T_PRC_TAIOKRK CH0551_SUB WHERE CH0551_SUB.NYUKIN_NO = CH0201.NYUKIN_NO AND ( (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = CH0201.JUTO_YMD AND CH0201.JUTO_SBT_CD = ?) OR (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = CH0201.CL_YMD) ) GROUP BY CH0201.NYUKIN_NO ) CH0201 ON CH0551.NYUKIN_NO = CH0201.NYUKIN_NO LEFT OUTER JOIN (SELECT CH0191_NEW.NYUKIN_NO ,CH0191_NEW.IDOMOTO_KNK_NO ,CH0191_NEW.KNK_AMNT FROM CH_T_KNK CH0191_NEW INNER JOIN (SELECT MIN(CH0191_SUB.KNK_NO) AS MIN_NO ,CH0191_SUB.NYUKIN_NO FROM CH_T_KNK CH0191_SUB WHERE CH0191_SUB.MK_FLG = '0' GROUP BY CH0191_SUB.NYUKIN_NO ) CH0191_SUB ON CH0191_NEW.KNK_NO = CH0191_SUB.MIN_NO AND CH0191_NEW.NYUKIN_NO = CH0191_SUB.NYUKIN_NO AND CH0191_NEW.MK_FLG = '0' ) CH0191 ON CH0191.NYUKIN_NO = CH0551.NYUKIN_NO AND CH0191.IDOMOTO_KNK_NO IS NULL LEFT OUTER JOIN (SELECT KK0511_NEW.BANK_CD ,KK0511_NEW.BANK_SHITEN_CD ,KK0511_NEW.BANK_TSTAYMD ,KK0511_NEW.BANK_TENDYMD ,KK0511_NEW.BANK_NM ,KK0511_NEW.BANK_SHITEN_NM FROM KK_M_BANK KK0511_NEW INNER JOIN (SELECT KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ,MAX(KK0511_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_M_BANK KK0511_GENE WHERE ? BETWEEN KK0511_GENE.BANK_TSTAYMD AND KK0511_GENE.BANK_TENDYMD AND KK0511_GENE.MK_FLG = '0' GROUP BY KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ) KK0511_GENE ON KK0511_NEW.BANK_CD = KK0511_GENE.BANK_CD AND KK0511_NEW.BANK_SHITEN_CD = KK0511_GENE.BANK_SHITEN_CD AND KK0511_NEW.GENE_ADD_DTM = KK0511_GENE.MAX_GENE_DTM ) KK0511 ON KK0511.BANK_CD = CH0551.BANK_CD AND KK0511.BANK_SHITEN_CD = CH0551.BANK_SHITEN_CD LEFT OUTER JOIN (SELECT KK0511_NEW.BANK_CD ,KK0511_NEW.BANK_SHITEN_CD ,KK0511_NEW.BANK_TSTAYMD ,KK0511_NEW.BANK_TENDYMD ,KK0511_NEW.BANK_NM ,KK0511_NEW.BANK_SHITEN_NM FROM KK_M_BANK KK0511_NEW INNER JOIN (SELECT KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ,MAX(KK0511_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_M_BANK KK0511_GENE WHERE ? BETWEEN KK0511_GENE.BANK_TSTAYMD AND KK0511_GENE.HAISHI_YUYO_PRD_ENDYMD AND KK0511_GENE.MK_FLG = '0' GROUP BY KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ) KK0511_GENE ON KK0511_NEW.BANK_CD = KK0511_GENE.BANK_CD AND KK0511_NEW.BANK_SHITEN_CD = KK0511_GENE.BANK_SHITEN_CD AND KK0511_NEW.GENE_ADD_DTM = KK0511_GENE.MAX_GENE_DTM ) KK0511_GENE2 ON KK0511_GENE2.BANK_CD = CH0551.BANK_CD AND KK0511_GENE2.BANK_SHITEN_CD = CH0551.BANK_SHITEN_CD LEFT OUTER JOIN (SELECT KK0531_NEW.CRECARD_COMP_CD ,KK0531_NEW.CRECARD_COMP_TSTAYMD ,KK0531_NEW.CRECARD_COMP_TENDYMD ,KK0531_NEW.CRECARD_COMP_NM ,KK0531_NEW.CREDIT_KOKAN_CD FROM KK_M_CRECARD_COMP KK0531_NEW INNER JOIN (SELECT KK0531_GENE.CRECARD_COMP_CD ,MAX(KK0531_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_M_CRECARD_COMP KK0531_GENE WHERE KK0531_GENE.MK_FLG = '0' GROUP BY KK0531_GENE.CRECARD_COMP_CD ) KK0531_GENE ON KK0531_NEW.CRECARD_COMP_CD = KK0531_GENE.CRECARD_COMP_CD AND KK0531_NEW.GENE_ADD_DTM = KK0531_GENE.MAX_GENE_DTM AND KK0531_NEW.MK_FLG = '0' ) KK0531 ON KK0531.CRECARD_COMP_CD = CH0551.CRECARD_COMP_CD AND ? BETWEEN KK0531.CRECARD_COMP_TSTAYMD AND KK0531.CRECARD_COMP_TENDYMD LEFT OUTER JOIN (SELECT CH0181_NEW.CVSTORE_CD ,CH0181_NEW.CVSTORE_TSTAYMD ,CH0181_NEW.CVSTORE_TENDYMD ,CH0181_NEW.CVSTORE_COMP_NM FROM CH_M_CVSTORE CH0181_NEW INNER JOIN (SELECT CH0181_GENE.CVSTORE_CD ,MAX(CH0181_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CH_M_CVSTORE CH0181_GENE WHERE CH0181_GENE.MK_FLG = '0' GROUP BY CH0181_GENE.CVSTORE_CD ) CH0181_GENE ON CH0181_NEW.CVSTORE_CD = CH0181_GENE.CVSTORE_CD AND CH0181_NEW.GENE_ADD_DTM = CH0181_GENE.MAX_GENE_DTM AND CH0181_NEW.MK_FLG = '0' ) CH0181 ON CH0181.CVSTORE_CD = CH0551.CVSTORE_CD AND ? BETWEEN CH0181.CVSTORE_TSTAYMD AND CH0181.CVSTORE_TENDYMD LEFT OUTER JOIN ZM_M_ORGANIZATION ZM0061 ON ZM0061.ORG_CD = CH0551.ORG_CD LEFT OUTER JOIN (SELECT CH0601_NEW.SKNKSITKCM_CD ,CH0601_NEW.SKNKSITKCM_TSTAYMD ,CH0601_NEW.SKNKSITKCM_TENDYMD ,CH0601_NEW.SKNKSITKCM_NM FROM CH_M_SKNKSITKCM CH0601_NEW INNER JOIN (SELECT CH0601_GENE.SKNKSITKCM_CD, MAX(CH0601_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CH_M_SKNKSITKCM CH0601_GENE WHERE CH0601_GENE.MK_FLG = '0' GROUP BY CH0601_GENE.SKNKSITKCM_CD ) CH0601_GENE ON CH0601_NEW.GENE_ADD_DTM = CH0601_GENE.MAX_GENE_DTM AND CH0601_NEW.SKNKSITKCM_CD = CH0601_GENE.SKNKSITKCM_CD AND CH0601_NEW.MK_FLG = '0' ) CH0601 ON CH0601.SKNKSITKCM_CD = CH0551.SKNKSITKCM_CD AND ? BETWEEN CH0601.SKNKSITKCM_TSTAYMD AND CH0601.SKNKSITKCM_TENDYMD LEFT OUTER JOIN (SELECT KK0491.SEIKY_KEI_NO ,KK0491.SHS_KANA FROM KK_T_SEIKY_KEI KK0491 WHERE KK0491.GENE_ADD_DTM = (SELECT MAX(KK0491_GENE.GENE_ADD_DTM) AS KK0491_MAX FROM KK_T_SEIKY_KEI KK0491_GENE WHERE KK0491_GENE.SEIKY_KEI_NO = KK0491.SEIKY_KEI_NO AND KK0491_GENE.RSV_APLY_YMD <= ? AND KK0491_GENE.RSV_APLY_CD = '2' AND KK0491_GENE.MK_FLG = '0' ) )KK0491 ON KK0491.SEIKY_KEI_NO = CH0551.SEIKY_KEI_NO ) ORDER BY CH0551.SYR_KBN ASC ,CH0551.PRC_TAIO_USER_ID ASC ,CH0551.TAIO_DTM ASC #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_004) #--【料金再調整リスト作成】 #--記事に設定する値を取得する。 #----------------------------------------------------------- --KEY:CH_SELECT_004 SELECT CH0551.KIJI FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.SEIKY_KEI_NO = ? AND CH0551.PRC_TAIO_KIROK_SBT_CD = ? AND CH0551.PRC_TAIO_KIROK_SBT_DTL_CD IN (?,?) AND CH0551.TAIO_DTM LIKE ? AND CH0551.MK_FLG = '0' ORDER BY CH0551.TAIO_DTM DESC #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_005) #--【随時入金登録データ抽出処理】 #----------------------------------------------------------- --KEY:CH_SELECT_005 SELECT CH0551.PRC_TAIO_USER_ID ,CH0551.TAIO_DTM ,CH0551.SYR_KBN ,CH0551.SYR_KBN_NM ,CH0551.SEIKY_KEI_NO ,KK0491.SHS_KANA ,CH0551.KIROK_TG_YM ,CH0551.RECEIPT_YMD ,CH0551.NYUKIN_TRN_YMD ,CH0551.OUT_ORG_SHUNO_YMD ,CH0551.NYUKIN_ROUTE_CD ,CH0551.SHUNOKIKAN_CD ,CASE WHEN CH0551.NYUKIN_AMNT IS NOT NULL THEN CH0551.NYUKIN_AMNT ELSE 0 END AS NYUKIN_AMNT ,CASE WHEN CH0201.JUTO_SUM IS NOT NULL THEN CH0201.JUTO_SUM ELSE 0 END AS JUTO_SUM ,CASE WHEN CH0191.KNK_AMNT IS NOT NULL THEN CH0191.KNK_AMNT ELSE 0 END AS DPSIT ,CH0551.BANK_CD ,KK0511.BANK_NM ,CH0551.BANK_SHITEN_CD ,KK0511.BANK_SHITEN_NM ,CH0551.CRECARD_COMP_CD ,KK0531.CRECARD_COMP_NM ,KK0531.CREDIT_KOKAN_CD ,CH0551.CVSTORE_CD ,CH0181.CVSTORE_COMP_NM ,CH0551.ORG_CD ,ZM0061.ORG_NM ,CH0551.SKNKSITKCM_CD ,CH0601.SKNKSITKCM_NM ,KK0491.NAIHAN_COMP_SKBT_CD ,SUBSTR(CH0101.PRC_KMK_CD,1,2) AS SEIKY_MT_COMP ,CH0101.PRC_KMK_CD ,CH0391.PRC_KMK_NM FROM (SELECT CH0551_SUB.PRC_TAIO_USER_ID ,CH0551_SUB.SEIKY_KEI_NO ,CH0551_SUB.TAIO_DTM ,CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD ,CH0551_SUB.KIROK_TG_YM ,CH0111_SUB.SEIKY_NO ,CH0551_SUB.NYUKIN_NO ,? AS SYR_KBN ,? AS SYR_KBN_NM ,CH0111_SUB.NYUKIN_ROUTE_CD ,CH0111_SUB.SHUNOKIKAN_CD ,CH0111_SUB.OUT_ORG_SHUNO_YMD ,CH0111_SUB.NYUKIN_TRN_YMD ,CH0111_SUB.RECEIPT_YMD ,CH0111_SUB.NYUKIN_AMNT ,CH0111_SUB.CRECARD_COMP_CD ,CH0111_SUB.SKNKSITKCM_CD ,CASE WHEN CH0111_SUB.HRAKMISAKI_BANK_CD IS NOT NULL THEN CH0111_SUB.HRAKMISAKI_BANK_CD WHEN CH0111_SUB.MADOGUCHI_BANK_CD IS NOT NULL THEN CH0111_SUB.MADOGUCHI_BANK_CD WHEN CH0111_SUB.KHRI_BANK_CD IS NOT NULL THEN CH0111_SUB.KHRI_BANK_CD ELSE NULL END AS BANK_CD ,CASE WHEN CH0111_SUB.HRAKMISAKI_BANK_CD IS NOT NULL THEN CH0111_SUB.HRAKMISAKI_BANK_SHITEN_CD WHEN CH0111_SUB.MADOGUCHI_BANK_CD IS NOT NULL THEN CH0111_SUB.MADOGUCHI_BANK_SHITEN_CD WHEN CH0111_SUB.KHRI_BANK_CD IS NOT NULL THEN CH0111_SUB.KHRI_BANK_SHITEN_CD ELSE NULL END AS BANK_SHITEN_CD ,CH0111_SUB.CVSTORE_CD ,CASE WHEN CH0111_SUB.UKETORI_ORG_CD IS NOT NULL THEN CH0111_SUB.UKETORI_ORG_CD WHEN CH0111_SUB.SYAYOBUN_ORG_CD IS NOT NULL THEN CH0111_SUB.SYAYOBUN_ORG_CD WHEN CH0111_SUB.YUKEN_UKETORI_ORG_CD IS NOT NULL THEN CH0111_SUB.YUKEN_UKETORI_ORG_CD ELSE NULL END AS ORG_CD FROM CH_T_PRC_TAIOKRK CH0551_SUB LEFT OUTER JOIN CH_T_NYUKIN CH0111_SUB ON CH0111_SUB.NYUKIN_NO = CH0551_SUB.NYUKIN_NO WHERE CH0551_SUB.PRC_TAIO_KIROK_SBT_CD = ? AND (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? OR CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ?) AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = ? AND CH0551_SUB.MK_FLG = '0' ) CH0551 LEFT OUTER JOIN CH_T_SEIKY CH0051 ON CH0051.SEIKY_NO = CH0551.SEIKY_NO AND CH0051.MK_FLG = '0' LEFT OUTER JOIN (SELECT CH0201.NYUKIN_NO ,SUM(CH0201.JUTO_AMNT) AS JUTO_SUM FROM CH_T_JUTO CH0201 ,CH_T_PRC_TAIOKRK CH0551_SUB WHERE CH0551_SUB.NYUKIN_NO = CH0201.NYUKIN_NO AND ( (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = CH0201.JUTO_YMD AND CH0201.JUTO_SBT_CD = ?) OR (CH0551_SUB.PRC_TAIO_KIROK_SBT_DTL_CD = ? AND SUBSTR(CH0551_SUB.TAIO_DTM,1,8) = CH0201.CL_YMD) ) GROUP BY CH0201.NYUKIN_NO ) CH0201 ON CH0551.NYUKIN_NO = CH0201.NYUKIN_NO LEFT OUTER JOIN (SELECT CH0191_NEW.NYUKIN_NO ,CH0191_NEW.IDOMOTO_KNK_NO ,CH0191_NEW.KNK_AMNT FROM CH_T_KNK CH0191_NEW INNER JOIN (SELECT MIN(CH0191_SUB.KNK_NO) AS MIN_NO ,CH0191_SUB.NYUKIN_NO FROM CH_T_KNK CH0191_SUB WHERE CH0191_SUB.MK_FLG = '0' GROUP BY CH0191_SUB.NYUKIN_NO ) CH0191_SUB ON CH0191_NEW.KNK_NO = CH0191_SUB.MIN_NO AND CH0191_NEW.NYUKIN_NO = CH0191_SUB.NYUKIN_NO AND CH0191_NEW.MK_FLG = '0' ) CH0191 ON CH0191.NYUKIN_NO = CH0551.NYUKIN_NO AND CH0191.IDOMOTO_KNK_NO IS NULL LEFT OUTER JOIN (SELECT KK0511_NEW.BANK_CD ,KK0511_NEW.BANK_SHITEN_CD ,KK0511_NEW.BANK_TSTAYMD ,KK0511_NEW.BANK_TENDYMD ,KK0511_NEW.BANK_NM ,KK0511_NEW.BANK_SHITEN_NM FROM KK_M_BANK KK0511_NEW INNER JOIN (SELECT KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ,MAX(KK0511_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_M_BANK KK0511_GENE WHERE ? BETWEEN KK0511_GENE.BANK_TSTAYMD AND KK0511_GENE.BANK_TENDYMD AND KK0511_GENE.MK_FLG = '0' GROUP BY KK0511_GENE.BANK_CD ,KK0511_GENE.BANK_SHITEN_CD ) KK0511_GENE ON KK0511_NEW.BANK_CD = KK0511_GENE.BANK_CD AND KK0511_NEW.BANK_SHITEN_CD = KK0511_GENE.BANK_SHITEN_CD AND KK0511_NEW.GENE_ADD_DTM = KK0511_GENE.MAX_GENE_DTM ) KK0511 ON KK0511.BANK_CD = CH0551.BANK_CD AND KK0511.BANK_SHITEN_CD = CH0551.BANK_SHITEN_CD LEFT OUTER JOIN (SELECT KK0531_NEW.CRECARD_COMP_CD ,KK0531_NEW.CRECARD_COMP_TSTAYMD ,KK0531_NEW.CRECARD_COMP_TENDYMD ,KK0531_NEW.CRECARD_COMP_NM ,KK0531_NEW.CREDIT_KOKAN_CD FROM KK_M_CRECARD_COMP KK0531_NEW INNER JOIN (SELECT KK0531_GENE.CRECARD_COMP_CD ,MAX(KK0531_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_M_CRECARD_COMP KK0531_GENE WHERE KK0531_GENE.MK_FLG = '0' GROUP BY KK0531_GENE.CRECARD_COMP_CD ) KK0531_GENE ON KK0531_NEW.CRECARD_COMP_CD = KK0531_GENE.CRECARD_COMP_CD AND KK0531_NEW.GENE_ADD_DTM = KK0531_GENE.MAX_GENE_DTM AND KK0531_NEW.MK_FLG = '0' ) KK0531 ON KK0531.CRECARD_COMP_CD = CH0551.CRECARD_COMP_CD AND ? BETWEEN KK0531.CRECARD_COMP_TSTAYMD AND KK0531.CRECARD_COMP_TENDYMD LEFT OUTER JOIN (SELECT CH0181_NEW.CVSTORE_CD ,CH0181_NEW.CVSTORE_TSTAYMD ,CH0181_NEW.CVSTORE_TENDYMD ,CH0181_NEW.CVSTORE_COMP_NM FROM CH_M_CVSTORE CH0181_NEW INNER JOIN (SELECT CH0181_GENE.CVSTORE_CD ,MAX(CH0181_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CH_M_CVSTORE CH0181_GENE WHERE CH0181_GENE.MK_FLG = '0' GROUP BY CH0181_GENE.CVSTORE_CD ) CH0181_GENE ON CH0181_NEW.CVSTORE_CD = CH0181_GENE.CVSTORE_CD AND CH0181_NEW.GENE_ADD_DTM = CH0181_GENE.MAX_GENE_DTM AND CH0181_NEW.MK_FLG = '0' ) CH0181 ON CH0181.CVSTORE_CD = CH0551.CVSTORE_CD AND ? BETWEEN CH0181.CVSTORE_TSTAYMD AND CH0181.CVSTORE_TENDYMD LEFT OUTER JOIN ZM_M_ORGANIZATION ZM0061 ON ZM0061.ORG_CD = CH0551.ORG_CD LEFT OUTER JOIN (SELECT CH0601_NEW.SKNKSITKCM_CD ,CH0601_NEW.SKNKSITKCM_TSTAYMD ,CH0601_NEW.SKNKSITKCM_TENDYMD ,CH0601_NEW.SKNKSITKCM_NM FROM CH_M_SKNKSITKCM CH0601_NEW INNER JOIN (SELECT CH0601_GENE.SKNKSITKCM_CD, MAX(CH0601_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CH_M_SKNKSITKCM CH0601_GENE WHERE CH0601_GENE.MK_FLG = '0' GROUP BY CH0601_GENE.SKNKSITKCM_CD ) CH0601_GENE ON CH0601_NEW.GENE_ADD_DTM = CH0601_GENE.MAX_GENE_DTM AND CH0601_NEW.SKNKSITKCM_CD = CH0601_GENE.SKNKSITKCM_CD AND CH0601_NEW.MK_FLG = '0' ) CH0601 ON CH0601.SKNKSITKCM_CD = CH0551.SKNKSITKCM_CD AND ? BETWEEN CH0601.SKNKSITKCM_TSTAYMD AND CH0601.SKNKSITKCM_TENDYMD LEFT OUTER JOIN CH_T_SEIKY_UCWK CH0101 ON CH0101.SEIKY_NO = CH0551.SEIKY_NO AND CH0101.SEIKY_KEI_NO = CH0551.SEIKY_KEI_NO AND CH0101.MK_FLG = '0' AND CH0101.SEIKY_YM = SUBSTR(CH0551.TAIO_DTM,1,6) LEFT OUTER JOIN (SELECT CH0391_NEW.PRC_GRP_CD ,CH0391_NEW.PCRS_CD ,CH0391_NEW.PRC_SVC_CD ,CH0391_NEW.PRC_KMK_CD ,CH0391_NEW.PRC_KMK_OPUT_NM_TSTAYMD ,CH0391_NEW.PRC_KMK_OPUT_NM_TENDYMD ,CH0391_NEW.PRC_KMK_NM ,CH0391_NEW.DSP_JUN FROM CH_M_PRC_KMK_OPUT_NM CH0391_NEW INNER JOIN (SELECT CH0391_GENE.PRC_KMK_NM_SBT_CD ,CH0391_GENE.PRC_GRP_CD ,CH0391_GENE.PCRS_CD ,CH0391_GENE.PRC_SVC_CD ,CH0391_GENE.PRC_KMK_CD ,MAX(CH0391_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CH_M_PRC_KMK_OPUT_NM CH0391_GENE WHERE CH0391_GENE.PRC_KMK_NM_SBT_CD = ? AND ? BETWEEN CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD AND CH0391_GENE.PRC_KMK_OPUT_NM_TENDYMD AND CH0391_GENE.MK_FLG = '0' GROUP BY CH0391_GENE.PRC_KMK_NM_SBT_CD ,CH0391_GENE.PRC_GRP_CD ,CH0391_GENE.PCRS_CD ,CH0391_GENE.PRC_SVC_CD ,CH0391_GENE.PRC_KMK_CD ) CH0391_GENE ON CH0391_NEW.PRC_KMK_NM_SBT_CD = CH0391_GENE.PRC_KMK_NM_SBT_CD AND CH0391_NEW.PRC_GRP_CD = CH0391_GENE.PRC_GRP_CD AND CH0391_NEW.PCRS_CD = CH0391_GENE.PCRS_CD AND CH0391_NEW.PRC_SVC_CD = CH0391_GENE.PRC_SVC_CD AND CH0391_NEW.PRC_KMK_CD = CH0391_GENE.PRC_KMK_CD AND CH0391_NEW.GENE_ADD_DTM = CH0391_GENE.MAX_GENE_DTM ) CH0391 ON CH0391.PRC_GRP_CD = CH0101.PRC_GRP_CD AND CH0391.PCRS_CD = CH0101.PCRS_CD AND CH0391.PRC_SVC_CD = CH0101.PRC_SVC_CD AND CH0391.PRC_KMK_CD = CH0101.PRC_KMK_CD INNER JOIN (SELECT CH0401.PRC_GRP_CD ,CH0401.PCRS_CD ,CH0401.PRC_SVC_CD ,CH0401.PRC_KMK_CD FROM CH_M_PRC_KMK_CS_CHGE CH0401 INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321 ON CH0401.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE WHERE CH0401.SYS_CD = ? AND CH0401.CHSHT_CHG_CD = ? AND CH0401.PRC_KMK_CS_CHGE_TSTAYMD <= ? AND CH0401.PRC_KMK_CS_CHGE_TENDYMD >= ? AND CH0401.MK_FLG = '0' AND ZM0321.WORK_PARAM_ID = ? AND ZM0321.WORK_PARAM_TSTAYMD <= ? AND ZM0321.WORK_PARAM_TENDYMD >= ? AND ZM0321.MK_FLG = '0' )CH0401 ON CH0401.PRC_GRP_CD = CH0101.PRC_GRP_CD AND CH0401.PCRS_CD = CH0101.PCRS_CD AND CH0401.PRC_SVC_CD = CH0101.PRC_SVC_CD AND CH0401.PRC_KMK_CD = CH0101.PRC_KMK_CD LEFT OUTER JOIN (SELECT KK0491.SEIKY_KEI_NO ,KK0491.SYSID ,KK0491.SHS_KANA ,CK0011.NAIHAN_COMP_SKBT_CD FROM KK_T_SEIKY_KEI KK0491 INNER JOIN (SELECT KK0491_GENE.SEIKY_KEI_NO , MAX(KK0491_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_T_SEIKY_KEI KK0491_GENE WHERE KK0491_GENE.RSV_APLY_CD = ? AND KK0491_GENE.RSV_APLY_YMD <= ? AND KK0491_GENE.MK_FLG = '0' GROUP BY KK0491_GENE.SEIKY_KEI_NO ) KK0491_GENE ON KK0491.SEIKY_KEI_NO = KK0491_GENE.SEIKY_KEI_NO AND KK0491.GENE_ADD_DTM = KK0491_GENE.MAX_GENE_DTM INNER JOIN (SELECT CK0011.SYSID ,CK0011.NAIHAN_COMP_SKBT_CD FROM CK_T_CUST CK0011 INNER JOIN (SELECT CK0011_GENE.SYSID , MAX(CK0011_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM CK_T_CUST CK0011_GENE WHERE CK0011_GENE.RSV_APLY_CD = ? AND CK0011_GENE.RSV_APLY_YMD <= ? AND CK0011_GENE.MK_FLG = '0' GROUP BY CK0011_GENE.SYSID ) CK0011_GENE ON CK0011.SYSID = CK0011_GENE.SYSID AND CK0011.GENE_ADD_DTM = CK0011_GENE.MAX_GENE_DTM ) CK0011 ON KK0491.SYSID = CK0011.SYSID ) KK0491 ON CH0551.SEIKY_KEI_NO = KK0491.SEIKY_KEI_NO ORDER BY CH0551.SYR_KBN ASC ,CH0551.PRC_TAIO_USER_ID ASC ,CH0551.TAIO_DTM ASC ,CH0391.DSP_JUN ASC #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_006) #--【督促ステータス変更リスト作成】 #----------------------------------------------------------- --KEY:CH_SELECT_006 SELECT CH0551.PRC_TAIO_USER_ID ,CH0551.TAIO_DTM ,CH0551.PRC_TAIO_KIROK_SBT_CD ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ,CH0551.SEIKY_KEI_NO ,CH0551.KIJI ,CH0551.TITLE ,CH0551.TOKUSOKU_NO ,CH0551.TOKUSOKU_IDO_NO FROM CH_T_PRC_TAIOKRK CH0551 WHERE SUBSTR(CH0551.TAIO_DTM, 1, 8) = ? AND CH0551.PRC_TAIO_KIROK_SBT_CD IN(?, ?) AND MK_FLG = '0' ORDER BY CH0551.PRC_TAIO_USER_ID ,CH0551.TAIO_DTM #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_007) #--【CHPRC03701_金庫移動リスト作成】 #----------------------------------------------------------- --KEY:CH_SELECT_007 SELECT CH0551.SEIKY_KEI_NO ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ,CH0551.CHG_BF_PRC_KMK_CD ,CH0551.CHG_AF_PRC_KMK_CD ,CH0551.KNK_NO ,CH0551.UPD_DTM ,CH0551.UPD_OPEACNT FROM CH_T_PRC_TAIOKRK CH0551 WHERE SUBSTR(CH0551.TAIO_DTM, 1, 8) = ? AND CH0551.PRC_TAIO_KIROK_SBT_CD IN(?, ?, ?) AND MK_FLG = '0' ORDER BY CH0551.KNK_NO ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_008) #--【CHPRC03501_入金登録リスト作成】入金登録、入金取消、随時入金登録データ抽出 #----------------------------------------------------------- --KEY:CH_SELECT_008 SELECT CH0551.PRC_TAIO_USER_ID ,CH0551.TAIO_DTM ,DECODE(CH0551.PRC_TAIO_KIROK_SBT_CD, ?, ?, ?, ?, ?, ?, NULL) AS SYR_KBN ,DECODE(CH0551.PRC_TAIO_KIROK_SBT_CD, ?, ?, ?, ?, ?, ?, NULL) AS SYR_KBN_NM ,CH0551.EFILE_KANRI_NO FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.PRC_TAIO_KIROK_SBT_CD IN (?, ?, ?) AND SUBSTR(CH0551.TAIO_DTM,01,08) = ? AND CH0551.MK_FLG = '0' ORDER BY SYR_KBN ASC ,CH0551.PRC_TAIO_USER_ID ASC ,CH0551.TAIO_DTM ASC #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_009) #--【CHPRC10901_督促架電・口座振替残高不足者リストデータ抽出】 #----------------------------------------------------------- --KEY:CH_SELECT_009 SELECT COUNT(1) CNT FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.SEIKY_KEI_NO = ? AND CH0551.TAIO_DTM >= ? AND CH0551.TAIO_DTM <= ? AND CH0551.CHG_AF_CD_SBT_CD = 'CD00184' AND CH0551.CHG_AF_CD_DIV = ? AND CH0551.MK_FLG = '0' #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_010) #--【CHPRC11002_譲渡未収料金載せ替え登録】 #----------------------------------------------------------- --KEY:CH_SELECT_010 SELECT CH0551.PRC_TAIO_KIROK_NO FROM CH_T_PRC_TAIOKRK CH0551 WHERE CH0551.SEIKY_KEI_NO = ? AND SUBSTR(CH0551.TAIO_DTM,01,06) = ? AND CH0551.PRC_TAIO_KIROK_SBT_CD = ? AND (CH0551.PRC_TAIO_KIROK_SBT_DTL_CD = ? OR CH0551.PRC_TAIO_KIROK_SBT_DTL_CD = ?) AND CH0551.KIJI LIKE ? AND CH0551.MK_FLG = '0' #- #----------------------------------------------------------- #--料金対応記録(CH_T_PRC_TAIOKRK-CH_SELECT_011) #--【CHPRC03801_請求書再発行一覧作成】 #----------------------------------------------------------- --KEY:CH_SELECT_011 SELECT Calendar.HAKKO_YMD ,NVL(CH0101_1.Count_1,0) - NVL(CH0101_2.Count_2,0) AS RE_HAKKO_SKS_CNT ,NVL(CH0101_3.Count_3,0) AS SOKUJI_KSSAI_CNT FROM (SELECT TO_CHAR(TO_DATE(? + Calendar_1.LVL - 1,'YYYYMMDD'),'yyyymmdd') HAKKO_YMD FROM (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= ?) Calendar_1) Calendar LEFT JOIN ( select SUBSTR(CH0101_1.TAIO_DTM,1,8) as TAIO_YMD_1 ,COUNT(*) as Count_1 from CH_T_PRC_TAIOKRK CH0101_1 where SUBSTR(CH0101_1.TAIO_DTM,1,8) BETWEEN ? AND ? and ( ( CH0101_1.PRC_TAIO_KIROK_SBT_CD = '104' and CH0101_1.PRC_TAIO_KIROK_SBT_DTL_CD IN ('0215','0216','0217','0218','0219','0220','0221','0222','0223','0224','0225','0226') ) OR ( CH0101_1.PRC_TAIO_KIROK_SBT_CD = '116' and CH0101_1.PRC_TAIO_KIROK_SBT_DTL_CD IN ('1501') ) OR ( CH0101_1.PRC_TAIO_KIROK_SBT_CD = '105' and CH0101_1.PRC_TAIO_KIROK_SBT_DTL_CD IN ('0213','0214','0227','0228') ) ) and CH0101_1.MK_FLG = '0' GROUP BY SUBSTR(CH0101_1.TAIO_DTM,1,8) ) CH0101_1 ON Calendar.HAKKO_YMD = CH0101_1.TAIO_YMD_1 LEFT JOIN ( select SUBSTR(CH0101_2.TAIO_DTM,1,8) as TAIO_YMD_2 ,COUNT(*) as Count_2 from CH_T_PRC_TAIOKRK CH0101_2 where SUBSTR(CH0101_2.TAIO_DTM,1,8) BETWEEN ? AND ? and CH0101_2.PRC_TAIO_KIROK_SBT_CD IN ('104','105','116') and CH0101_2.PRC_TAIO_KIROK_SBT_DTL_CD IS NULL and CH0101_2.MK_FLG = '0' GROUP BY SUBSTR(CH0101_2.TAIO_DTM,1,8) ) CH0101_2 ON Calendar.HAKKO_YMD = CH0101_2.TAIO_YMD_2 LEFT JOIN ( select SUBSTR(CH0101_3.TAIO_DTM,1,8) as TAIO_YMD_3 ,Count(*) as Count_3 from CH_T_PRC_TAIOKRK CH0101_3 where SUBSTR(CH0101_3.TAIO_DTM,1,8) BETWEEN ? AND ? and CH0101_3.PRC_TAIO_KIROK_SBT_CD = '104' and CH0101_3.PRC_TAIO_KIROK_SBT_DTL_CD IN ('0205','0206') and CH0101_3.MK_FLG = '0' GROUP BY SUBSTR(CH0101_3.TAIO_DTM,1,8) ) CH0101_3 ON Calendar.HAKKO_YMD = CH0101_3.TAIO_YMD_3 ORDER BY 1 #-