CREATE OR REPLACE FUNCTION FUN_POLICY_OP0073_PA (V_CUST_NAME IN VARCHAR2, —客户姓名 V_CUST_CERTI_TYPE IN VARCHAR2, —证件类型 V_CUST_CERTI_CODE IN VARCHAR2, —证件号码 V_BANK_ACCOUNT IN VARCHAR2, —银行账号 V_BANK_CODE IN VARCHAR2 —银行代码 ) RETURN TYPE_POLICY_OP0073_RETURN_PA —返回一个类型，此类型格式和TYPE_POLICY_TABULATION一致 PIPELINED IS V_POLICY TYPE_POLICY_OP0073_PA; —类型，用来接收所有指标

BEGIN

FOR I IN (SELECT TPA.POLICY_ID FROM WIFT_PA.T_PA_CUSTOMER TPA WHERE TPA.NAME = V_CUST_NAME AND TPA.CERTI_CODE = V_CUST_CERTI_CODE) —通过姓名、证件找到名下所有保单 LOOP —由于管道函数一次只能接收一行值，故用循环 SELECT TYPE_POLICY_OP0073_PA(POLICYNO, PRODUCTNAME, POLICYSTATE, FEEAMOUNT, BANKNAME, BANKNO, BANKCODE, MOBILE, SORTDATE, SORTNO, CUST_CERTI_TYPE, CUST_CERTI_CODE, CUST_NAME) INTO V_POLICY —将指标赋值于类型 FROM (

SELECT TP.POLICY_NO AS POLICYNO, MAX(TPR.PRODUCT_NAME) AS PRODUCTNAME, TP.POLICY_STATUS AS POLICYSTATE, SUM(DECODE(TO_CHAR(C.STD_PREM_BF, ‘FM9999999999999999.00’), ‘.00’, ‘0.00’, TO_CHAR(C.STD_PREM_BF, ‘FM9999999999999999.00’))) OVER(PARTITION BY C.POLICY_ID) AS FEEAMOUNT, DB.BANK_NAME AS BANKNAME, TMP.ACCOUNT_CODE AS BANKNO, TMP.BANK_CODE AS BANKCODE, NVL(TMP.RESERVE_MOBILE, TPC.MOBILE) AS MOBILE, TP.ISSUE_DATE SORTDATE, CASE TP.POLICY_STATUS WHEN ‘00’THEN ‘1’ ELSE ‘2’ END SORTNO, MC.CODE CUST_CERTI_TYPE, TPC.CERTI_CODE CUST_CERTI_CODE, TPC.NAME CUST_NAME FROM WIFT_PA.T_PA_POLICY TP INNER JOIN WIFT_PA.T_PA_POLICY_PRODUCT C ON TP.POLICY_ID = C.POLICY_ID INNER JOIN WIFT_PA.T_PA_CUSTOMER TPC ON TP.POLICY_ID = TPC.POLICY_ID AND TP.HOLDER_CUST_ID = TPC.CUSTOMER_ID INNER JOIN WIFT_IIWS.T_PRODUCT TPR ON C.PRODUCT_ID = TPR.PRODUCT_ID INNER JOIN —LXX UPDATE 20230919:由客户账户信息表左关联付款人账户表(优先付款人账户表)，调整为客户账户信息表或者首先付款人账户表中存在（若均存在，优先客户账户信息表） (SELECT TT.POLICY_CODE, —保单号码 TT.ACCOUNT_CODE, —银行账号 TT.BANK_CODE, —银行代码 TT.RESERVE_MOBILE, —预留手机号 TT.INSERT_TIME, —插入时间 TT.FLAG, —来源标记：01-承保 02-保全 ROW_NUMBER() OVER(PARTITION BY TT.POLICY_CODE ORDER BY TT.FLAG ASC, TT.INSERT_TIME DESC) RN FROM (SELECT TPA.POLICY_CODE, —保单号码 TPA.ACCOUNT_CODE ACCOUNT_CODE, —银行账号 TPA.BANK_CODE BANK_CODE, —银行代码 TPA.RESERVE_MOBILE, —预留手机号 TPA.INSERT_TIME, —插入时间 ‘01’ AS FLAG —来源标记：01-承保 02-保全 FROM WIFT_PA.T_PA_ACCOUNT TPA —客户账户信息表 WHERE 1 = 1 AND TPA.ACCOUNT_CODE = V_BANK_ACCOUNT AND TPA.BANK_CODE =V_BANK_CODE UNION ALL SELECT TP.POLICY_NO AS POLICY_CODE, —保单号码 CA.BANK_ACCOUNT AS ACCOUNT_CODE, —银行账号 CA.BANK_CODE, —银行代码 CA.RESERVE_MOBILE, —银行预留手机号 CA.CREATE_TIME AS INSERT_TIME, —创建时间 ‘02’ AS FLAG —来源标记：01-承保 02-保全 FROM WIFT_CS.T_CS_PAYER_ACCOUNT CA —付款人账户表 INNER JOIN WIFT_PA.T_PA_POLICY TP —保单主表 ON TP.POLICY_ID = CA.POLICY_ID WHERE 1 = 1 AND CA.BANK_ACCOUNT = V_BANK_ACCOUNT —参数，银行账号 AND CA.BANK_CODE = V_BANK_CODE —参数，银行代码 ) TT ) TMP ON TP.POLICY_NO = TMP.POLICY_CODE AND TMP.RN= 1 LEFT OUTER JOIN JDTM.M_CERTY_TYPE MC ON TPC.CERTI_TYPE = MC.SOUR_CODE AND MC.SOURCE_ID = 11 LEFT JOIN WIFT_IIWS.D_BANK DB ON TMP.BANK_CODE = DB.BANK_CODE WHERE TPC.NAME = V_CUST_NAME —参数，姓名 AND MC.CODE = V_CUST_CERTI_TYPE —参数，证件类型 AND TPC.CERTI_CODE = V_CUST_CERTI_CODE —参数，证件号码 AND TPR.MAIN_RIDER = ‘M’ AND TP.POLICY_ID= I.POLICY_ID —游标，保单号，循环查询，较快 GROUP BY TP.POLICY_NO, TP.POLICY_STATUS, DB.BANK_NAME, TMP.BANK_CODE, TPC.CERTI_CODE, NVL(TMP.RESERVE_MOBILE, TPC.MOBILE), C.STD_PREM_BF, C.POLICY_ID, TP.ISSUE_DATE, TMP.ACCOUNT_CODE, CASE TP.POLICY_STATUS WHEN ‘00’THEN ‘1’ ELSE ‘2’ END, MC.CODE, TPC.CERTI_CODE, TPC.NAME); PIPE ROW(V_POLICY); END LOOP; RETURN; END;

—调用方法 SELECT policyNo,—保单号 productName,—险种 policyState, —保单状态 feeAmount, —金额 bankName, —银行 bankNo, —银行账号 bankCode, —银行代码 mobile, —手机号 sortDate, —承保时间 sortNo FROM TABLE(FUN_POLICY_OP0073_PA(参数1，，，参数5))