PROCEDURE

CREATE OR REPLACE PROCEDURE PRO_RIDDING_CUSTOMER IS
CURSOR CUR_AUDIT_CUSTOMER_INFO IS --通过审核的任务列表
SELECT PLANID,
AREACODE,
CREATEDBY,
CSMONTH,
CREATEDDATE,
NAMESIGN,
STATE,
FLAG,
CREATED_DATE
FROM (SELECT DISTINCT TRF.PLAN_ID PLANID,
(SELECT TPI.AREA_CODE
FROM T_PLAN_INSTITUTE TPI
WHERE TPI.PLAN_ID = TRF.PLAN_ID) AREACODE,
(SELECT TPI.CS_MONTH
FROM T_PLAN_INSTITUTE TPI
WHERE TPI.PLAN_ID = TRF.PLAN_ID) CSMONTH,
TRF.CREATED_BY CREATEDBY,
TO_CHAR(TRF.CREATED_DATE, 'yyyy-mm-dd') CREATEDDATE,
TRF.CREATED_DATE,
TPI.NAME_SIGN NAMESIGN,
TPI.STATE STATE,
TRF.FLAG FLAG
FROM T_CUSTOMER_RIDDING_FORMULA TRF, T_PLAN_INSTITUTE TPI
WHERE TRF.PLAN_ID = TPI.PLAN_ID
AND TRF.DEPART_AUDIT_FLAG = '1'
AND TPI.STATE = '0'
AND TRF.FLAG = '0'
AND TPI.AUDIT_STATE = '1'
AND TPI.VALID_FLAG = 'Y'
AND TRF.VALID_FLAG = 'Y');

CURSOR CUR_FIELD_ID(PLANID VARCHAR2) IS
SELECT TM.FIELD_ID FIELDID
FROM T_CUSTOMER_RIDDING_FORMULA TM
WHERE TM.PLAN_ID = PLANID;

V_CUR_AUDIT_CUSTOMER_INFO CUR_AUDIT_CUSTOMER_INFO%ROWTYPE;
V_CUR_FIELDID             CUR_FIELD_ID%ROWTYPE;
--FORMULA                   T_CUSTOMER_RIDDING_FORMULA%ROWTYPE;    

TYPE CUR_TYPE IS REF CURSOR;
CUR_CUSTOMER   CUR_TYPE;
V_PARTY_ID     VARCHAR2(8);
V_OLD_PCNUMBER VARCHAR2(50);
V_CREATED_BY   VARCHAR2(30);
V_PC_NUM       VARCHAR2(500);
V_AREA_CODE    VARCHAR2(500);
V_ORDER_NUM    VARCHAR2(10);
V_CS_MONTH     VARCHAR2(50);
V_ROLE_ID      NUMBER(8);
SQLSTR         VARCHAR2(4000) := '';
SQL_HEAD       VARCHAR2(1000) := '';
SQL_HEAD_WHERE VARCHAR2(1000) := '';
SQL_TAIL       VARCHAR2(4000) := '';
V_PLAN_ID      VARCHAR2(20);
--V_FIELDID                 VARCHAR2(20);
TIME_FLAG VARCHAR2(5);
--PHONE_FLAG  VARCHAR2(5);
POLICY_FLAG VARCHAR2(5);
V_ERR       VARCHAR2(1000) := '';
V_POI       VARCHAR2(2) := '01';
BEGIN
--上午九点到下午六点期间该程序不能执行
SELECT TO_CHAR(SYSDATE, 'hh24') INTO TIME_FLAG FROM DUAL;
--  IF TO_NUMBER(TIME_FLAG) < 09 OR TO_NUMBER(TIME_FLAG) > 18 THEN

IF CUR_AUDIT_CUSTOMER_INFO%ISOPEN THEN
CLOSE CUR_AUDIT_CUSTOMER_INFO;
END IF;
V_POI := '02';
OPEN CUR_AUDIT_CUSTOMER_INFO;
LOOP
V_POI := '03';
FETCH CUR_AUDIT_CUSTOMER_INFO
INTO V_CUR_AUDIT_CUSTOMER_INFO;
EXIT WHEN CUR_AUDIT_CUSTOMER_INFO%NOTFOUND;
BEGIN
SELECT TRIM(TO_CHAR(RN, '00'))
INTO V_ORDER_NUM
FROM (SELECT ROWNUM RN, PLAN_ID
FROM (SELECT TPI.PLAN_ID
FROM T_PLAN_INSTITUTE TPI
WHERE TPI.CREATED_DATE BETWEEN
TRUNC(V_CUR_AUDIT_CUSTOMER_INFO.CREATED_DATE) AND
TRUNC(V_CUR_AUDIT_CUSTOMER_INFO.CREATED_DATE) + 1
ORDER BY TPI.CREATED_DATE) A) B
WHERE B.PLAN_ID = V_CUR_AUDIT_CUSTOMER_INFO.PLANID;

--PHONE_FLAG     := 'false';
POLICY_FLAG    := 'false';
SQL_HEAD       := '';
SQL_HEAD_WHERE := '';
V_POI          := '04';
V_AREA_CODE    := V_CUR_AUDIT_CUSTOMER_INFO.AREACODE;
--V_ORDER_NUM    := V_CUR_AUDIT_CUSTOMER_INFO.ORDERNUM;
V_CS_MONTH   := V_CUR_AUDIT_CUSTOMER_INFO.CSMONTH;
V_PLAN_ID    := V_CUR_AUDIT_CUSTOMER_INFO.PLANID;
V_PC_NUM     := V_CUR_AUDIT_CUSTOMER_INFO.CREATEDBY || '-' ||
V_CUR_AUDIT_CUSTOMER_INFO.CREATEDDATE || '-' ||
V_ORDER_NUM;
V_CREATED_BY := V_CUR_AUDIT_CUSTOMER_INFO.CREATEDBY;
OPEN CUR_FIELD_ID(V_PLAN_ID);
LOOP
V_POI := '05';
FETCH CUR_FIELD_ID
INTO V_CUR_FIELDID;
EXIT WHEN CUR_FIELD_ID%NOTFOUND;

V_POI := '06';
IF V_CUR_FIELDID.FIELDID = 'ACCIDENT_TIMES' THEN
POLICY_FLAG := 'true';
END IF;

END LOOP;
CLOSE CUR_FIELD_ID;
V_POI := '07';

SQL_HEAD       := 'SELECT DISTINCT A.PARTY_ID PARTYID,A.PCNUMBER FROM T_CAR_INFO C,T_CUSTOMER A';
SQL_HEAD_WHERE := ' WHERE C.VALID_FLAG=''Y'' AND A.PARTY_ID = C.PARTY_ID  AND A.VALID_FLAG=''Y''' ||
' and to_number(A.AREA_CODE) in (' || V_AREA_CODE || ')';

V_POI := '08';

IF V_CS_MONTH IS NOT NULL THEN
V_POI          := '09';
SQL_HEAD_WHERE := SQL_HEAD_WHERE ||
' and to_number(to_char(C.INSUREDUE_DATE,''MM'')) in (' ||
V_CS_MONTH || ')';
END IF;

IF POLICY_FLAG = 'true' THEN
V_POI          := '11';
SQL_HEAD       := SQL_HEAD || ',T_POLICY_INFORMATION  TPI';
SQL_HEAD_WHERE := SQL_HEAD_WHERE ||
' AND C.CAR_ID = TPI.CAR_ID  AND SYSDATE BETWEEN TPI.INSURANCE_BEGIN_TIME AND TPI.INSURANCE_END_TIME AND TPI.POLICY_TYPE=1 ';
END IF;

V_POI := '12';
PRO_RIDDING_CALLLIST(V_PLAN_ID, '1', SQL_TAIL);
IF SQL_TAIL IS NOT NULL THEN
V_POI := '13';
--dbms_output.put_line(SQL_TAIL||'===');
SQLSTR := SQL_HEAD || SQL_HEAD_WHERE || ' ' || SQL_TAIL;

V_POI := '14';
INSERT INTO T_CALLLIST_STRSQL
(SQL_ID, CREATED_DATE, SOURCE_PRO, STRSQL, POSITION)
VALUES
(SEQ_CALLLIST_STRSQL_ID.NEXTVAL,
SYSDATE,
'PRO_RIDDING_CUSTOMER',
SQLSTR,
V_POI);

V_POI := '15';
OPEN CUR_CUSTOMER FOR SQLSTR;
LOOP
V_POI          := '16';
V_PARTY_ID     := NULL;
V_OLD_PCNUMBER := NULL;
FETCH CUR_CUSTOMER
INTO V_PARTY_ID, V_OLD_PCNUMBER;
EXIT WHEN V_PARTY_ID IS NULL;
UPDATE T_CUSTOMER
SET PCNUMBER = V_PC_NUM, LAST_UPDATED_TIME = SYSDATE
WHERE PARTY_ID = V_PARTY_ID;
V_POI := '17';
INSERT INTO T_LIST_PLANSET_BAK
(BAK_ID,
PARTY_ID,
OLD_PC_NUMBER,
NEW_PC_NUMBER,
UPDATED_BY,
CREATED_BY)
VALUES
(SEQ_T_LIST_DISPATCH_BAK.NEXTVAL,
V_PARTY_ID,
V_OLD_PCNUMBER,
V_PC_NUM,
'admin',
'admin');

V_POI := '21';
--修改任务表批次号  
SELECT R.ROLE_ID
INTO V_ROLE_ID
FROM T_ROLE R, T_USER_ROLE UR, T_USER U
WHERE U.UM_CODE = V_CREATED_BY
AND R.ROLE_ID = UR.ROLE_ID
AND UR.USER_ID = U.USER_ID;

IF V_ROLE_ID = 3 THEN

UPDATE T_CUSTOMER_TASK
SET PCNUMBER     = V_PC_NUM,
UPDATED_DATE = SYSDATE,
UPDATED_BY   = 'admin'
WHERE PARTY_ID = V_PARTY_ID
AND ROLL_GROUP = '1'
OR ROLL_GROUP = '';
END IF;

IF V_ROLE_ID != 3 THEN
UPDATE T_CUSTOMER_TASK
SET PCNUMBER     = V_PC_NUM,
UPDATED_DATE = SYSDATE,
UPDATED_BY   = 'admin'
WHERE PARTY_ID = V_PARTY_ID;
END IF;
END LOOP;

CLOSE CUR_CUSTOMER;
V_POI := '18';
COMMIT;
END IF;
V_POI := '19';
UPDATE T_CUSTOMER_RIDDING_FORMULA
SET FLAG = '1'
WHERE PLAN_ID = V_PLAN_ID;
UPDATE T_PLAN_INSTITUTE
SET STATE = '1', PC_NUMBER = V_PC_NUM
WHERE PLAN_ID = V_PLAN_ID;

V_POI := '20';
INSERT INTO T_CUSTOMER_IMPORT_INFO
(ID,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
IMPORT_SIGN,
NAME_SIGN,
AREA_CODE)
VALUES
(SEQ_IMPORT_INFO_ID.NEXTVAL,
V_CUR_AUDIT_CUSTOMER_INFO.CREATEDBY,
SYSDATE,
V_CUR_AUDIT_CUSTOMER_INFO.CREATEDBY,
SYSDATE,
V_PC_NUM,
V_CUR_AUDIT_CUSTOMER_INFO.NAMESIGN,
V_CUR_AUDIT_CUSTOMER_INFO.AREACODE);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
V_ERR := SUBSTR(SQLERRM, 0, 250);
ROLLBACK;
INSERT INTO JOB_LOG
VALUES
('888',
':::::',
'PRO_RIDDING_CUSTOMER',
SYSDATE,
'0',
V_CUR_AUDIT_CUSTOMER_INFO.PLANID || ':V_POI=' || V_POI || ':' ||
V_ERR,
SYSDATE,
'E');
COMMIT;
END;
END LOOP;
CLOSE CUR_AUDIT_CUSTOMER_INFO;

--END IF;

EXCEPTION
WHEN OTHERS THEN
V_ERR := SUBSTR(SQLERRM, 0, 250);
ROLLBACK;
INSERT INTO JOB_LOG
VALUES
('888',
':::::',
'PRO_RIDDING_CUSTOMER',
SYSDATE,
'0',
V_CUR_AUDIT_CUSTOMER_INFO.PLANID || '——V_POI=' || V_POI || '——' ||
V_ERR,
SYSDATE,
'E');
COMMIT;
END PRO_RIDDING_CUSTOMER;