编辑代码

-- FE01不动产抵押贷款信息表
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.FINANCING_CURRENCY as "不动产抵押贷款融资币种",a.FINANCING_BALANCE as "不动产抵押贷款融资金额",a.FINANCING_RATE as "不动产抵押贷款融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_bdcdydkxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- F01不动产金融产品投资明细
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "产品名称",a.MGR_ORG_NO as "管理人机构",a.PRD_TOTAL_SHARE as "产品总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_bdcjrcptzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- H02存款证实书持有人份额变化流水
select '农银人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.MGR_ORG_NO as "账号所属机构代码",a.BUSIN_TYPE as "业务类型",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.PLEDGE_CERTIFICATE_NO as "质押证实书编号",a.PLEDGE_OTHER_ORGNO as "质押交易对手组织机构代码",a.PLEDGE_TERM as "质押期限",a.PLEDGE_DATE_UNIT as "质押期限单位",a.PLEDGE_FINANCING_BALA as "质押融资金额",a.INPUT_MEMO as "备注",a.PLEDGE_FINANCING_RATIO as "质押融资比例" from zbd_ja_ckzsscyrfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- H01存款证实书基本信息
select '农银人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.DEPOSIT_CERTIFICATE_NO as "存款证实书编号",a.OPEN_ACCO_BANK_ORG as "开户总行组织机构代码",a.SUB_OPEN_ACCO_BANK_NAME as "开户行全称",a.DEPOSIT_BANK_TRUST as "存款银行信用评级",a.DEPOSIT_CERTIFICATE_FULL as "存款证实书全称",a.DEPOSIT_CERTIFICATE_SHORT as "存款证实书简称",a.DEPOSIT_TYPE1 as "存款类型",a.DEPOSIT_BALA as "存款本金",a.DEPOSIT_MONEY as "存款币种",a.DEPOSIT_FLOAT_TYPE as "利率类型",a.DEPOSIT_DATE as "存款日期",a.DEPOSIT_END_DATE as "存款到期日",a.INTEREST_BASE as "计息基准",a.INTEREST_FREQUENCY as "年付息频率",a.INTEREST_RATE as "利率水平",a.INTEREST_RATE_BASE as "基准利率",a.INTEREST_GAP as "利差（BP）",a.INTEREST_RATE_BASE_OTHER as "基准利率乘数",a.INPUT_MEMO as "备注" from zbd_ja_ckzssjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- Z01产品持有人份额信息
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.HOLD_ORG_NAME as "持有机构全称",a.HOLD_ORG_NO as "持有机构统一社会信用代码",a.HOLD_ORG_TYPE as "持有机构类型",a.FUND_TYPE as "资金类型",a.HOLD_PRD_NAME as "产品名称",a.MGT_PRD_TYPE as "金融资产管理产品类型",a.HOLDER_ACCOUNT as "持有人账号",a.HOLD_SCALE_END as "期末持有规模",a.HOLD_SHARE_END as "期末持有份额",a.CHANGE_SHARE as "本期份额变动",a.HOLD_PRD_LEVEL as "持有产品级别",a.INPUT_MEMO as "备注" from zbd_ja_cpcyrfexxb_t a where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024'


-- Z01产品基本信息
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024' )
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024' )
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024' )
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024' )
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024' )
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024' )
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024' )
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_REG_NO as "产品登记通知书文号",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.MGR_ORG_NAME as "产品管理人名称",a.MGR_ORG_NO as "产品管理人统一社会信用代码",a.TRUST_ORG_NAME as "产品托管人名称",a.TRUST_ORG_NO as "产品托管人统一社会信用代码",a.INVEST_REGION as "投资范围",a.IS_BATCH as "是否分期",a.IS_CLASS as "是否为分级产品",a.SETUP_DATE as "成立日期",a.PRD_PERIOD as "产品期限",a.ESTMT_SCALE as "预计规模",a.INIT_SCALE as "初始规模",a.LAST_SCALE as "最新规模",a.OC_TYPE as "开放/封闭类型",a.OPEN_PERIOD as "开放周期",a.IS_NET_WTH_PRD as "是否为净值型产品",a.MGT_RATE as "管理费率",a.FLT_MGT_FEE as "浮动管理费",a.CUST_RATE as "托管费率",a.DIV_TYPE as "分红方式",a.LAST_SHARE_NET_VAL as "最新份额净值",a.LAST_ACC_NET_VAL as "最新累计净值",a.LEVERAGE_RATE as "杠杆比率",a.NEW_MNG_ASS_REG as "资管新规",a.DIFF_DESC as "差异情况说明",a.INPUT_MEMO as "备注" from zbd_ja_cpjbxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024' )


-- Z01产品投资资产持仓明细表
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024' )
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024' )
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024' )
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024' )
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024' )
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024' )
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024' )
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "持有资产类型",a.ASSET_NAME as "持有资产名称",a.ASSET_CODE as "持有资产证券代码",a.ASSET_ACCOUNT as "持有资产会计分录",a.END_BALANCE as "期末账面余额",a.INVEST_COST as "投资成本",a.INVEST_INCOME as "投资收益",a.PROFIT_LOSSES_CHG as "公允价值变动损益",a.SHAREHOLD_RATIO as "持股比例",a.BOND_RATING as "债券评级",a.ASSET_ISSU_NAME as "持有资产发行主体全称",a.CREDIT_ISSU_CODE as "持有资产发行主体统一社会信用代",a.LISTED_COM_NAME as "持有资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.ISSU_LISTED_REL as "资产发行主体与上市公司关系",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.SSPR_N_O1 as "上市公司第一大股东股票质押率",a.SSPR_N_O2 as "上市公司第二大股东股票质押率",a.SSPR_N_O3 as "上市公司第三大股东股票质押率",a.SSPR_N_O4 as "上市公司第四大股东股票质押率",a.SSPR_N_O5 as "上市公司第五大股东股票质押率",a.COM_GICS as "上市公司所属行业",a.COM_REGION as "上市公司所在区域",a.COM_CLASSIFY as "上市公司属性",a.ISSU_GICS as "资产发行主体所属行业",a.ISSU_REGION as "资产发行主体所在区域",a.ISSU_CLASSIFY as "资产发行主体公司属性",a.SSPR_RATE as "上市公司股东股票质押率",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcccmxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024' )



-- Z01产品投资资产交易明细表
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024' )
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024' )
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024' )
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024' )
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024' )
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024' )
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024' )
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ASSET_TYPE as "资产类型",a.ASSET_NAME as "资产名称",a.ASSET_CODE as "资产证券代码",a.TRADE_DATE as "交易日期",a.TRADE_DIRECTION as "交易方向",a.TRADE_MANNER as "交易方式",a.TRADE_VOLUME as "交易数量",a.TRADE_PRICE as "交易价格",a.TRADE_AMOUNT as "交易金额",a.TRADE_COUNTERPARTY as "交易对手方名称",a.ASSET_ISSU_NAME as "资产发行主体全称",a.CREDIT_ISSU_CODE as "资产发行主体统一社会信用代码",a.LISTED_COM_NAME as "资产所涉上市公司名称",a.CREDIT_LISTED_CODE as "上市公司统一社会信用代码",a.STOCK_PLEDGE_RATE as "上市公司股票质押率",a.CONNECTED_TRD as "是否关联关系",a.TRADERELATE_DESC as "关联关系描述",a.INPUT_MEMO as "备注" from zbd_ja_cptzzcjymxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024' )


-- Z01产品资产负债表
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024' )
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024' )
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024' )
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024' )
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024' )
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024' )
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024' )
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACC_BASE_TYPE as "基础类别",a.PRD_ACCOUNT as "科目",a.PRD_ACCOUNT_DTL as "明细名称",a.PRD_END_BALANCE as "期末余额",a.INPUT_MEMO as "备注" from zbd_ja_cpzcfzb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024' )


-- Z01产品终止信息表
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%农银人寿%' and DI_YEARS<='2024' )
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%英大泰和人寿%' and DI_YEARS<='2024' )
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中汇人寿%' and DI_YEARS<='2024' )
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%瑞众人寿%' and DI_YEARS<='2024' )
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中意人寿%' and DI_YEARS<='2024' )
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中英人寿%' and DI_YEARS<='2024' )
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%华泰人寿%' and DI_YEARS<='2024' )
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品代码",a.ACTURE_TERM_DATE as "实际终止日期",a.EXIT_TYPE as "退出方式",a.EXIT_OTHER as "其他退出方式描述",a.INPUT_MEMO as "备注" from zbd_ja_cpzzxxb_t a where concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) in( select concat(PRD_NAME_FULL,DI_YEARS,SEND_MONTHS) from zbd_ja_cpcyrfexxb_t where HOLD_ORG_NAME like '%中银三星人寿%' and DI_YEARS<='2024' )


-- FE01共同投资方信息
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COMMON_INVESTOR_NAME as "共同投资方名称",a.INVEST_CURRENCY as "共同投资方投资币种",a.INVEST_BALANCE as "共同投资方出资金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模板行号" from zbd_ja_gttzfxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- D01理财产品信息
select '农银人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INCOME_TYPE as "收益类型",a.RISK_RATING_IN as "风险评级",a.CAP_INVEST_FIELD as "资金投向",a.INPUT_MEMO as "备注" from zbd_ja_lccpxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- FE01内保外贷信息
select '鼎诚人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%鼎诚人寿%' and DI_YEARS<='2024'
union all select '民生人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%民生人寿%' and DI_YEARS<='2024'
union all select '现代财产',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%现代财产%' and DI_YEARS<='2024'
union all select '合众财产',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%合众财产%' and DI_YEARS<='2024'
union all select '长城人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%长城人寿%' and DI_YEARS<='2024'
union all select '瑞泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞泰人寿%' and DI_YEARS<='2024'
union all select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '幸福人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%幸福人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'
union all select '中银保险',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银保险%' and DI_YEARS<='2024'
union all select '英大泰和财产',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和财产%' and DI_YEARS<='2024'
union all select '中国铁路财产',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中国铁路财产%' and DI_YEARS<='2024'
union all select '中意财产',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.ISSUING_BANK_NAME as "开出保函银行名称/直接提供担保",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_BALA as "融资金额",a.FINANCING_RATE as "融资利率",a.FINANCING_TERM as "融资期限（月）",a.VALUE_DATE as "起息日",a.END_DATE as "到期日",a.FINANCING_BANK as "融资银行",a.IS_COUNTER_GUARANTEE as "是否提供反担保",a.COUNTER_GUARANTEE_SUB as "提供反担保的主体",a.IS_IN_AND_OUT as "是否是内存外贷",a.DOM_BANK_PLEDGE_BALA as "境内银行存款质押金额",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_nbwdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意财产%' and DI_YEARS<='2024';


-- F01投资股权基金明细
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRD_NAME_FULL as "被投资基金名称",a.INVEST_ORG_NO as "投资机构代码",a.COPARTNER_NAME as "合伙人名称",a.PRD_TOTAL_SHARE as "基金总规模",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "份额占比",a.PRE_BUY_BALA as "认缴金额",a.FACE_BUY_BALA as "实缴金额",a.INVEST_REMAIN_BALA as "投资余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzgqjjmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- F01投资性不动产投资明细
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.FIRST_INVEST_DATE as "初始投资时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_tzxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- FE01项目基本信息表
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.PRJ_TYPE as "项目类别",a.INVEST_TYPE as "投资方式",a.COUNTERPARTY as "交易对手",a.PRJ_STATE as "项目状态",a.PRJ_STATE_DES as "项目状态说明",a.BOOK_BALANCE_CURRENCY as "账面余额币种",a.BOOK_BALANCE as "账面余额",a.BOOK_BALANCE_BALA_RMB as "账面余额折人民币",a.EXPECT_INCOME_RATE as "预期收益率",a.INSURER_INVEST_CURRENCY as "保险公司投资币种",a.INSURER_INVEST_BALA as "保险公司出资金额",a.FINANCING_CURRENCY as "融资币种",a.FINANCING_AMOUNT as "融资金额",a.INVEST_AMOUNT_CURRENCY as "投资金额合计币种",a.TOTAL_INVEST_AMOUNT as "投资金额合计",a.INVEST_AMOUNT_RMB as "投资金额折人民币",a.FSPV_NAME as "第一层SPV名称",a.FSPV_ADRESS as "第一层SPV设立地点",a.FSPV_REG_CURRENCY as "第一层SPV注册资本币种",a.FSPV_REG_BALA as "第一层SPV注册资本",a.IS_COMMON_INVESTOR as "是否有共同投资方",a.IS_IN_AND_OUT_BUSINESS as "是否办理内保外贷业务",a.IS_REAL_ESTATE_MORTGAGE as "是否办理不动产抵押贷款",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmjbxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- FE01项目所在地信息
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.PRJ_NAME as "项目名称",a.COM_COUNTRY as "项目所在国家或地区",a.COM_REGION as "项目所在城市",a.IS_EXIST_LSPV as "是否存在最后一层SPV",a.LSPV_NAME as "最后一层SPV名称",a.LSPV_ADRESS as "最后一层SPV设立地点",a.LSPV_REG_CURRENCY as "最后一层SPV注册资本币种",a.LSPV_REG_BALA as "最后一层SPV注册资本",a.INPUT_MEMO as "备注",a.IS_MODIFY as "是否重新报送数据",a.INPUT_SERIAL_NO as "重新报送数据流水号",a.RE_EXCEL_SN as "重新报送数据在报送excel模" from zbd_ja_xmszdxxb_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- E02信托份额变化流水
select '农银人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.ACCO_ORG_NO as "账号所属机构代码",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.INVEST_END_DATE as "投资到期日",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_xtfebhls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- E01信托计划信息
select '农银人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "注册发行面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.PRD_TYPE1 as "信托功能",a.PRD_TYPE2 as "收益方式",a.PRD_MANAGE_TYPE as "产品管理方式",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.HAS_CREDIT_ENHANCE as "是否增信",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.INVEST_FINANCE_DESC as "融资主体",a.CAP_INVEST_FIELD as "资金投向",a.CAP_INVEST_FIELD1 as "标的类型",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.HAS_FINANCE_ADVISOR as "融资顾问",a.LAW_OFFICE_ORG_NO as "律师事务所",a.CURRENT_BATCH as "分期情况",a.FINANCE_ADVISOR_RATE as "顾问费率",a.DEDUCT_RATE as "业绩报酬固定费率",a.INPUT_MEMO as "备注",a.FIRST_EXPINT_DATE as "首个结息日",a.ENTRUST_MANAGER_FEE as "业绩报酬浮动费率",a.TRUSTEE_FEE as "托管费率",a.RATING_ORG_DATE_OUT as "外部评级时间",a.CREDIT_ENHANCE_OUT as "外部增信",a.CREDIT_ENHANCE_DESC_OUT as "外部增信描述",a.REAL_ESTATE_TYPE as "房地产类型" from zbd_ja_xtjhxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- F01养老不动产投资明细
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.LAND_TYPE as "土地性质",a.AREA_FLOOR as "用地面积",a.AREA_BUILDING as "建筑面积",a.FIRST_INVEST_DATE as "初始投资时间",a.START_WORK_DATE as "开工时间",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.BED_COUNT_DESIGN as "设计床位数",a.INVEST_BALA_PLAN as "预计投资金额",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_ylbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- D01业外产品基本信息
select '农银人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '幸福人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%幸福人寿%'
union all select '中汇人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.PRD_CODE_OUT as "系统外部产品代码",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.PRD_NAME_SHORT as "产品简称",a.PRD_TYPE as "产品类型",a.PRD_GROUP_SINGLE as "产品单一集合类型",a.PRD_REG_DATE as "产品注册日",a.PRD_REG_NO as "产品注册备案号",a.PRD_REG_BALA as "注册发行规模",a.BATCH_ISSUE_BALA as "本期发行规模",a.BATCH_COLLECT_BALA as "本期募集规模",a.PRD_TOTAL_SHARE as "产品总份额",a.PRD_REG_PRICE as "份额面值",a.CAP_MONEY_TYPE as "本金币种",a.INCOME_MONEY_TYPE as "收益币种",a.PRD_SETUP_DATE as "产品设立日",a.COLLECT_LEN as "募集期限",a.PRD_IINT_BEGIN_DATE as "产品起息日",a.PRD_IINT_END_DATE as "产品到期日",a.MGR_ORG_NO as "产品管理人",a.TRUSTEE_ORG_NO as "产品托管人",a.EXIT_TYPE as "退出方式",a.PRD_IS_CLASS as "是否结构化",a.PRD_INCOME_FEATURE as "结构化级别",a.TRADE_STRUCT_DESC as "交易结构描述",a.INVEST_INCOME_RATE as "投资收益率",a.EXPECT_INCOME_RATE as "预期收益率",a.EXPECT_INCOME_RATE_MAX as "预期收益率上限",a.EXPECT_INCOME_RATE_MIN as "预期收益率下限",a.INPUT_MEMO as "备注" from zbd_ja_ywcpjbxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- D02业外持有产品份额变动流水
select '农银人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.CUST_ACCO as "持有人账号",a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.AGENCY_CODE as "销售机构",a.BUSIN_TYPE as "业务类型",a.TRD_SHARE as "变动份额",a.TRD_BALA as "变动金额",a.TRD_DATE as "交易日期",a.RATING_COMPANY_IN as "内部主体信用评级",a.RATING_PRJ_IN as "内部项目信用评级",a.INVEST_END_DATE as "投资到期日",a.CONNECTED_TRD as "是否关联交易",a.INPUT_MEMO as "备注" from zbd_ja_ywcycpfebdls_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- D01资产支持证券信息
select '农银人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '中汇人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.INFRA_TYPE as "基础资产类型",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_TYPE as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zczczqxx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- G01组合类资管产品持有人名册
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.CUST_ACCO_NAME as "持有人账号名称",a.CUST_ACCO as "持有人账号",a.PRD_CODE as "产品临时编码",a.CUST_TYPE as "持有人账户类型",a.CUST_ORG_CODE_TYPE as "持有人账户所属人代码类型",a.CUST_ORG_NO as "持有人账户所属人代码",a.CUST_ORG_NAME as "持有人账户所属人全称",a.CUST_ORG_TYPE as "持有人账户所属人机构类型",a.PRD_TYPE as "产品类型",a.IS_LINKORG as "是否关联方",a.END_HOLD_SCALE as "期末持有规模",a.INPUT_MEMO as "备注"



-- G01组合类资管产品概况
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.IS_CLASS as "分级/非分级类型",a.TOTAL_NETVALUE as "累计净值",a.ISSUE_PRICE as "面值",a.MGR_ORG_NO as "产品管理人",a.INVEST_OBJECT_DESC as "投资标的概况",a.COMB_PRD_TYPE as "组合类资管产品类型",a.APPROVE_DATE as "核准时间",a.SETUP_DATE as "成立时间",a.INIT_SCALE as "初始规模",a.NEWEST_SCALE as "最新规模",a.OPEN_CLOSE_TYPE as "开放/封闭类型",a.OPEN_CYCLE as "开放周期",a.NEWEST_SHARE_PRICE as "最新份额净值",a.CUR_PERIOD_YEAR_RATE as "当期年化收益率",a.INPUT_MEMO as "备注"


-- G01组合类资管产品投资资产明细
select '农银人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '中意人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '中英人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.SEND_MONTHS as "报送月份",a.PRD_NAME_FULL as "产品全称",a.PRD_CODE as "产品临时编码",a.INVEST_SERIAL_NO as "投资明细流水号",a.INVEST_ASSETS_TYPE as "投资资产类型",a.END_EXISTENCE_SCALE as "期末存续规模",a.ASSETS_CAP_RATIO as "该类资产占产品净资产比例",a.DEPOSIT_BANK_ORG as "存款行机构代码",a.DEPOSIT_TYPE as "存款类型",a.DEPOSIT_TERM as "存款期限",a.FINANCE_TOOLS_FULL_NAME as "投资资产全称",a.FINANCE_TOOLS_MGR_ORG as "金融工具管理人机构代码",a.INPUT_MEMO as "备注"

-- F01直接投资股权明细
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.COM_COUNTRY as "被投资企业国家地区",a.COMPANY_ORG_NO as "被投资企业代码",a.COMPANY_BIZ_FILE_NO as "被投资企业有效商业文件登记号",a.ORG_NAME as "被投资企业名称",a.COM_REGION as "被投资企业地区",a.FIRST_INVEST_DATE as "初始投资时间",a.RATIO_IN_COMPANY as "持股占比",a.IS_HOLDINGS as "是否控股",a.CAP_MONEY_TYPE as "投资币种",a.FIRST_INVEST_BALA as "投资原值",a.BOOK_BALANCE as "账面余额",a.EXCHANGE_RATE_MID as "汇率中间价",a.INCOME_RATE_BATCH as "当期收益率",a.INCOME_RATE_TOTAL as "累计收益率",a.EXPECT_INCOME_RATE as "预期年收益率",a.INPUT_MEMO as "备注" from zbd_ja_zjtzgqmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'


-- D01证券资管产品
elect '农银人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%'
union all select '英大泰和人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%'
union all select '幸福人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%幸福人寿%'
union all select '中汇人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%'
union all select '瑞众人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%'
union all select '中意人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%'
union all select '中英人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%'
union all select '华泰人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%'
union all select '中银三星人寿',a.MGR_ORG_NO as "产品管理人",a.PRD_CODE_IN as "产品代码",a.PRD_NAME_FULL as "产品全称",a.RATING_PRD_OUT as "外部信用评级",a.RATING_ORG_NO_OUT as "外部评级机构",a.OWNER_NAME as "原始权益人",a.ASSET_SERVICE_NAME as "资产服务人",a.HAS_CREDIT_ENHANCE as "增信措施",a.CREDIT_ENHANCE_DESC as "增信措施描述",a.HAS_GUA as "是否担保",a.GUA_TYPE1 as "担保类型",a.GUA_ORG_NO as "担保主体",a.PLEDGE_DESC as "质押物描述",a.MORTGAGE_DESC as "抵押物描述",a.COM_REGION as "项目区域",a.CAP_INVEST_FIELD as "资金投向",a.CAP_USE_DESC as "资金运用",a.DURATIONS_OPTION as "存续期选择权",a.PRD_RETURN_TYPE as "还本付息方式",a.INCOME_ALLOT_FREQUENCY as "收益分配方式",a.IINT_FLOAT_FIX as "利率类型",a.IINT_CHG_FREQUENCY as "利率浮动形式",a.ALLOW_TRANSFER as "可否转让",a.LAW_OFFICE_ORG_NO as "律师事务所",a.INPUT_MEMO as "备注" from zbd_ja_zqzgcp_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%'


-- F01自用性不动产投资明细表
select '农银人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%农银人寿%' and DI_YEARS<='2024'
union all select '英大泰和人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%英大泰和人寿%' and DI_YEARS<='2024'
union all select '幸福人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%幸福人寿%' and DI_YEARS<='2024'
union all select '中汇人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中汇人寿%' and DI_YEARS<='2024'
union all select '瑞众人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%瑞众人寿%' and DI_YEARS<='2024'
union all select '中意人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中意人寿%' and DI_YEARS<='2024'
union all select '中英人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中英人寿%' and DI_YEARS<='2024'
union all select '华泰人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%华泰人寿%' and DI_YEARS<='2024'
union all select '中银三星人寿',a.DI_YEARS as "报送年份",a.DI_QUARTER_TYPE as "报送季度",a.CUST_ACCO as "持有人账号",a.PRJ_NAME as "项目名称",a.COM_REGION as "所属地区",a.INVEST_TYPE as "投资方式",a.PRJ_SCHEDULE_TYPE as "项目状态",a.FIRST_INVEST_BALA as "投资原值",a.INVEST_REMAIN_BALA as "账面余额",a.INPUT_MEMO as "备注",a.DEPOSIT_MONEY as "币种",a.COM_COUNTRY as "国家地区" from zbd_ja_zyxbdctzmx_t a left join jes_inst i on a.dis_bank_id=i.inst_id where i.inst_name like '%中银三星人寿%' and DI_YEARS<='2024'