CREATE OR REPLACE PACKAGE BODY P_Sale_Before AS
/******************************************************************************
NAME: P_Sale_Middle
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2007-11-6 1. Created this package body.
******************************************************************************/
--客户采集
--查询采集客户-基本信息
PROCEDURE Get_Corp_BaseInfo(Ret_Cur OUT cur,
S_ORG_ID VARCHAR2, --采集机构ID
S_CORP_NAME VARCHAR2, --客户名称
I_ORIGIN NUMBER, --来源渠道
S_START_COLLECT_DATE VARCHAR2, --采集开始日期
S_END_COLLECT_DATE VARCHAR2 --采集结束日期
)
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strSql := 'select COLLECT_ORG_ID, DECODE(corp.COLLECT_ORG_ID,''$$'',''总部'',org.ORG_LONG_NAME) COLLECT_ORG_NAME, CORP_ID, CORP_NAME, INDUSTRY_NAME, INDUSTRY_ID,'
||' CORP_TYPE1, class1.SUBCLASS_NAME CORP_TYPE1_NAME, CORP_TYPE2, class2.SUBCLASS_NAME CORP_TYPE2_NAME, LEGALIS_HOMO, CONTACT_PERSON, CONTACT_PHONE, FAX,'
||' REGISTERED_DATE, REGISTERED_ADDRESS, REGISTERED_CAPITAL, '
||' EMAIL, URL, POSTALCODE, UPSTREAM_CORP, DOWNSTREAM_CORP, '
||' COLLECT_SALESMAN_ID, salesman.SALESMAN_NAME COLLECT_SALESMAN_NAME, COLLECT_DATE, ORIGIN_CHANNEL, class3.SUBCLASS_NAME ,corp.INSERT_TIME, corp.DATA_FILE_NAME';
strSql := strSql
||' from SM_CORP_INFO corp, SYS_ORGANIZE org, SYS_CLASS_DETAIL class1 , SYS_CLASS_DETAIL class2 , SYS_CLASS_DETAIL class3 , DAT_SALESMAN salesman '
||' where corp.COLLECT_ORG_ID = org.ORG_ID(+) '
||' and (corp.CORP_TYPE1 = class1.SUBCLASS_ID and class1.CLASS_ID = 4)'
||' and (corp.CORP_TYPE2 = class2.SUBCLASS_ID and class2.CLASS_ID = 5)'
||' and (corp.ORIGIN_CHANNEL = class3.SUBCLASS_ID and class3.CLASS_ID = 3)'
||' and corp.COLLECT_SALESMAN_ID = salesman.SALESMAN_ID(+) ';
--||' order by COLLECT_ORG_NAME desc';
IF S_ORG_ID IS NOT NULL THEN
IF S_ORG_ID <> '$$' THEN
strSql := strSql
||' and corp.COLLECT_ORG_ID like ''' || S_ORG_ID || '%'' ';
END IF;
END IF;
IF S_CORP_NAME IS NOT NULL THEN
strSql := strSql
||' and corp.CORP_NAME like ''%' || S_CORP_NAME || '%'' ';
END IF;
IF I_ORIGIN <> -1 THEN
strSql := strSql
||' and corp.ORIGIN_CHANNEL = ' || I_ORIGIN || ' ';
END IF;
IF S_START_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
||' and corp.COLLECT_DATE >= ''' || S_START_COLLECT_DATE|| ''' ';
END IF;
IF S_END_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
||' and corp.COLLECT_DATE <= ''' || S_END_COLLECT_DATE|| '''';
END IF;
strSql := strSql
|| ' order by COLLECT_ORG_NAME ,corp.INSERT_TIME desc';
OPEN Ret_Cur FOR strSql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Corp_BaseInfo;
--查询筛漏历史信息
PROCEDURE Get_Filter_History(Ret_Cur OUT cur,
S_CORP_ID IN VARCHAR2--客户ID号
)
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strSql := 'select CORP_ID, MISING_SALESMAN_ID, salesman.SALESMAN_NAME MISING_SALESMAN_NAME, '
||'mising.MISING_SAL_ORG_ID, DECODE(mising.MISING_SAL_ORG_ID,''$$'',''总部'',org.ORG_LONG_NAME) MISING_SAL_ORG_NAME, '
||'MISING_DATA_DATE, MISING_LIND, lind.SUBCLASS_NAME MISING_LIND_NAME, '-- 时间 筛漏环节
||'MISING_REASION, MISING_OTHER_INFO'--筛漏理由，其他信息
||' from SM_MISING_INFO mising,SYS_ORGANIZE org, SYS_CLASS_DETAIL lind, DAT_SALESMAN salesman ';
strSql := strSql
||'where mising.MISING_SAL_ORG_ID = org.ORG_ID(+) '
||'and mising.MISING_SALESMAN_ID = salesman.SALESMAN_ID(+) '
||'and (mising.MISING_LIND = lind.SUBCLASS_ID and lind.CLASS_ID = 8)'
||'and CORP_ID = ''' || S_CORP_ID || '''';
strSql := strSql
||' order by MISING_DATA_DATE desc';
OPEN Ret_Cur FOR strSql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Filter_History;
--查询采集客户-基本信息（客户综合查询时用）
PROCEDURE Get_CorpBaseIfno_Filter(Ret_Cur OUT cur,
S_ORG_ID VARCHAR2, --采集机构ID
S_CORP_NAME VARCHAR2, --客户名称
I_ORIGIN NUMBER, --是否筛漏 （-1,全部 １：是 ２：否 ）
S_START_COLLECT_DATE VARCHAR2, --筛漏 开始日期
S_END_COLLECT_DATE VARCHAR2 --筛漏 结束日期
)
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strSql := 'select COLLECT_ORG_ID, DECODE(corp.COLLECT_ORG_ID,''$$'',''总部'',org.ORG_LONG_NAME) COLLECT_ORG_NAME, CORP_ID, CORP_NAME, SCREENING_STATE, decode(SCREENING_STATE,0,''未处理'',1,''是'',2,''否'') SCREENING_STATE_NAME, INDUSTRY_NAME,INDUSTRY_ID,'
||' CORP_TYPE1, class1.SUBCLASS_NAME CORP_TYPE1_NAME, CORP_TYPE2, class2.SUBCLASS_NAME CORP_TYPE2_NAME, LEGALIS_HOMO, CONTACT_PERSON, CONTACT_PHONE, FAX,'
||' REGISTERED_DATE, REGISTERED_ADDRESS, REGISTERED_CAPITAL, '
||' EMAIL, URL, POSTALCODE, UPSTREAM_CORP, DOWNSTREAM_CORP,'
||' COLLECT_SALESMAN_ID, salesman.SALESMAN_NAME COLLECT_SALESMAN_NAME, COLLECT_DATE, ORIGIN_CHANNEL, class3.SUBCLASS_NAME,corp.FENBU_ORG_ID, corp.TUANDUI_ORG_ID';
strSql := strSql
||' from SM_CORP_INFO corp, SYS_ORGANIZE org, SYS_CLASS_DETAIL class1 , SYS_CLASS_DETAIL class2 , SYS_CLASS_DETAIL class3 , DAT_SALESMAN salesman '
||' where corp.COLLECT_ORG_ID = org.ORG_ID(+)'
||' and (corp.CORP_TYPE1 = class1.SUBCLASS_ID and class1.CLASS_ID = 4)'
||' and (corp.CORP_TYPE2 = class2.SUBCLASS_ID and class2.CLASS_ID = 5)'
||' and (corp.ORIGIN_CHANNEL = class3.SUBCLASS_ID and class3.CLASS_ID = 3)'
||' and corp.COLLECT_SALESMAN_ID = salesman.SALESMAN_ID(+) ';
IF S_ORG_ID IS NOT NULL THEN
IF S_ORG_ID <> '$$' THEN
strSql := strSql
||' and (corp.COLLECT_ORG_ID like ''' || S_ORG_ID || '%'' '
||' or corp.FENBU_ORG_ID like ''' || S_ORG_ID || '%'' '
||' or corp.TUANDUI_ORG_ID like ''' || S_ORG_ID || '%'' '
||' )';
END IF;
END IF;
IF I_ORIGIN <> -1 THEN
strSql := strSql
||' and corp.SCREENING_STATE = ' || I_ORIGIN || ' ';
END IF;
IF S_CORP_NAME IS NOT NULL THEN
strSql := strSql
||' and corp.CORP_NAME like ''%' || S_CORP_NAME || '%'' ';
END IF;
IF S_START_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
||' and (SUBSTR(corp.SCREENING_DATA_TIME,1,10) >= ''' || S_START_COLLECT_DATE|| ''' ';
END IF;
IF S_END_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
||' and SUBSTR(corp.SCREENING_DATA_TIME,1,10) <= ''' || S_END_COLLECT_DATE|| ''' ';
END IF;
IF I_ORIGIN = -1 AND S_END_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
|| ' or corp.SCREENING_DATA_TIME is null)';
ELSIF S_END_COLLECT_DATE IS NOT NULL THEN
strSql := strSql
|| ')';
END IF;
strSql := strSql
||' order by COLLECT_ORG_NAME,SCREENING_STATE desc ';
OPEN Ret_Cur FOR strSql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_CorpBaseIfno_Filter;
--查询客户-基本信息，金融需求信息，客户推荐信息
PROCEDURE Get_Corp_All_Info(Ret_Cur1 OUT cur, --基本信息
Ret_Cur2 OUT cur, --金融需求信息
Ret_Cur3 OUT cur, --客户推荐信息
S_CORP_ID VARCHAR2 --客户虚拟ID号
)
IS
s_err VARCHAR2(100);
BEGIN
--基本信息
OPEN Ret_Cur1 FOR
SELECT CORP_NAME, LEGALIS_HOMO, INDUSTRY_NAME, INDUSTRY_ID,
REGISTERED_DATE, REGISTERED_ADDRESS, REGISTERED_CAPITAL,
CONTACT_PERSON, CONTACT_PHONE, FAX, EMAIL, URL, POSTALCODE,
UPSTREAM_CORP, DOWNSTREAM_CORP,
SCREENING_STATE, COLLECT_SALESMAN_ID, salesman.SALESMAN_NAME COLLECT_SALESMAN_NAME, COLLECT_DATE, COLLECT_DATE_TIME,
ORIGIN_CHANNEL, class3.SUBCLASS_NAME ORIGIN_CHANNEL_NAME,
COLLECT_ORG_ID, DECODE(corp.COLLECT_ORG_ID,'$$','总部',org.ORG_LONG_NAME) COLLECT_ORG_NAME, OTHER_INFO, DATA_FILE_NAME,
FENBU_ORG_ID, TUANDUI_ORG_ID,
CORP_TYPE1, class1.SUBCLASS_NAME CORP_TYPE1_NAME,
CORP_TYPE2, class2.SUBCLASS_NAME CORP_TYPE2_NAME, ORIGIN_EVALUATION
FROM SM_CORP_INFO corp, SYS_ORGANIZE org, SYS_CLASS_DETAIL CLASS1 , SYS_CLASS_DETAIL CLASS2 , SYS_CLASS_DETAIL CLASS3 , DAT_SALESMAN salesman
WHERE CORP_ID = S_CORP_ID AND corp.COLLECT_ORG_ID = org.ORG_ID(+)
AND (corp.CORP_TYPE1 = CLASS1.SUBCLASS_ID AND CLASS1.CLASS_ID = 4)
AND (corp.CORP_TYPE2 = CLASS2.SUBCLASS_ID AND CLASS2.CLASS_ID = 5)
AND (corp.ORIGIN_CHANNEL = CLASS3.SUBCLASS_ID AND CLASS3.CLASS_ID = 3)
AND corp.COLLECT_SALESMAN_ID = salesman.SALESMAN_ID ;
--金融信息表
OPEN Ret_Cur2 FOR
SELECT MAIN_PRODUCTS, REAL_CONTROL_PERSON, STAFF_COUNT, GENERAL_ASSETS,
LAST_YEAR_SALE_INCOME, OFFICE_ADDRESS, LAST_YEAR_PROFIT, INDUSTRY_POSITION, BUSINESS_COPE,
ACTION_SUBJECT, ACTION_DATE_TIME, ACTION_SITE,
COOPERATIVE_WISH, HOPE_VISIT_DATE_TIME, FINANCIAL_NEED, COOPERATIVE_BANKS,
USED_PRODUCTS, PROVIDE_GUARANTEE, ACCEPT_RATE
FROM SM_CORP_INFO
WHERE CORP_ID = S_CORP_ID;
--客户推荐表
OPEN Ret_Cur3 FOR
SELECT MAIN_PRODUCTS, REAL_CONTROL_PERSON, STAFF_COUNT, GENERAL_ASSETS,
LAST_YEAR_SALE_INCOME, OFFICE_ADDRESS, LAST_YEAR_PROFIT, INDUSTRY_POSITION, BUSINESS_COPE,
CONTACT_PERSON_OTHER_INFO, CONTACT_PERSON_DUTY, CONTACT_MOBILE, RECOMMEND_PERSON, RELATION_REC_AND_CORP, REC_PHONE, OTHER_ITEM
FROM SM_CORP_INFO
WHERE CORP_ID = S_CORP_ID;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Corp_All_Info;
--删除采集客户
PROCEDURE Del_Corp(N_RTN OUT NUMBER, --返回值 0 成功删除 -1 删除失败
S_CORP_ID VARCHAR2) --虚拟客户号
IS
s_err VARCHAR2(100);
BEGIN
DELETE FROM SM_CORP_INFO WHERE CORP_ID = S_CORP_ID;
N_RTN := 0;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END Del_Corp;
--删除采集客户（当导入数据的时候先将已有文件的客户删除）
PROCEDURE Del_FILENAME_Corp(N_RTN OUT NUMBER, --返回值 0 成功删除 -1 删除失败
S_FILE_NAME VARCHAR2) --文件名
IS
s_err VARCHAR2(100);
BEGIN
IF S_FILE_NAME IS NOT NULL THEN
DELETE FROM SM_CORP_INFO WHERE DATA_FILE_NAME = S_FILE_NAME;
N_RTN := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END Del_FILENAME_Corp;
--判断是否存在相同的客户
PROCEDURE Get_Corp_Count_ByCorpName(N_RTN OUT NUMBER, --返回值 0 不存在相同的客户 1 存在相同的客户 -1 查询出错
S_CORP_NAME VARCHAR2, --客户名称
S_CORP_ID VARCHAR2, -- 客户ID
S_FILE_NAME VARCHAR2) --文件名
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strsql := 'SELECT COUNT(*) FROM SM_CORP_INFO WHERE CORP_NAME = ''' || S_CORP_NAME || '''';
IF S_CORP_ID IS NOT NULL THEN
strsql := strsql || ' AND CORP_ID != ''' || S_CORP_ID || ''' ';
END IF;
IF S_FILE_NAME IS NOT NULL THEN
strsql := strsql || ' AND DATA_FILE_NAME != ''' || S_FILE_NAME || ''' ';
END IF;
EXECUTE IMMEDIATE strsql INTO N_RTN;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR (SQLERRM, 1, 100);
N_RTN := -1;
END Get_Corp_Count_ByCorpName;
--判断是否进行过操作，
PROCEDURE Get_IS_CONTRO(N_RTN OUT NUMBER, --返回值 0 未处理 其它为，已处理
S_CORP_NAME VARCHAR2) --客户名称
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strsql := 'SELECT SCREENING_STATE FROM SM_CORP_INFO WHERE CORP_NAME = ''' || S_CORP_NAME || '''';
EXECUTE IMMEDIATE strsql INTO N_RTN;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR (SQLERRM, 1, 100);
N_RTN := -1;
END Get_IS_CONTRO;
--判断文件名是否存在
PROCEDURE Get_FileName_Count(N_RTN OUT NUMBER, --返回值 0 不存在 1 存在?-1 查询出错
S_FILE_NAME VARCHAR2) --文件名称
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strsql := 'SELECT COUNT(*) FROM SM_CORP_INFO WHERE DATA_FILE_NAME = ''' || S_FILE_NAME || '''';
EXECUTE IMMEDIATE strsql INTO N_RTN;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR (SQLERRM, 1, 100);
N_RTN := -1;
END Get_FileName_Count;
--查询客户筛选
PROCEDURE GET_SHAIXUAN_CORP(RET_CUR OUT cur, --返回结果集
I_SCREENING_STATE NUMBER, --筛选状态
S_CORP_NAME VARCHAR2, --客户名称
S_SCREENING_STA_DATA_TIME VARCHAR2, --筛选开始时间
S_SCREENING_END_DATA_TIME VARCHAR2, --筛选结束时间
S_ORG_ID VARCHAR2 --机构ID
)
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
s_org_id_para VARCHAR2(20);
BEGIN
strSql := 'select COLLECT_ORG_ID, DECODE(corp.COLLECT_ORG_ID,''$$'',''总部'',org.ORG_LONG_NAME) COLLECT_ORG_NAME, '
||' CORP_ID, CORP_NAME, INDUSTRY_NAME, INDUSTRY_ID '
||', CORP_TYPE1, class1.SUBCLASS_NAME CORP_TYPE1_NAME, CORP_TYPE2, class2.SUBCLASS_NAME CORP_TYPE2_NAME '
||', SCREENING_DATA_TIME, SCREENING_STATE, decode(SCREENING_STATE,0,''未处理'',1,''是'',2,''否'') SCREENING_STATE_NAME ';
strSql := strSql
||' FROM SM_CORP_INFO corp, SYS_ORGANIZE org, SYS_CLASS_DETAIL class1 , SYS_CLASS_DETAIL class2 '
||' where corp.COLLECT_ORG_ID = org.ORG_ID(+)'
||' and (corp.CORP_TYPE1 = class1.SUBCLASS_ID and class1.CLASS_ID = 4)'
||' and (corp.CORP_TYPE2 = class2.SUBCLASS_ID and class2.CLASS_ID = 5)';
IF S_ORG_ID <> '$$' THEN
strSql := strSql
||' and corp.COLLECT_ORG_ID like ''' || S_ORG_ID || '%''';
END IF;
IF I_SCREENING_STATE <> -1 THEN
strSql := strSql
||' and corp.SCREENING_STATE = ' || I_SCREENING_STATE;
END IF;
IF S_CORP_NAME IS NOT NULL THEN
strSql := strSql
||' and corp.CORP_NAME like ''%' || S_CORP_NAME || '%'' ';
END IF;
IF S_SCREENING_STA_DATA_TIME IS NOT NULL THEN
strSql := strSql
||' and (SUBSTR(corp.SCREENING_DATA_TIME,1,10) >= ''' || S_SCREENING_STA_DATA_TIME|| ''' ';
END IF;
IF S_SCREENING_END_DATA_TIME IS NOT NULL THEN
strSql := strSql
||' and SUBSTR(corp.SCREENING_DATA_TIME,1,10) <= ''' || S_SCREENING_END_DATA_TIME|| ''' ';
END IF;
IF I_SCREENING_STATE = -1 AND S_SCREENING_STA_DATA_TIME IS NOT NULL THEN
strSql := strSql
|| ' or corp.SCREENING_DATA_TIME is null)';
ELSIF S_SCREENING_STA_DATA_TIME IS NOT NULL THEN
strSql := strSql
|| ')';
END IF;
strSql := strSql
|| ' order by SCREENING_STATE,COLLECT_ORG_NAME,SCREENING_DATA_TIME desc';
OPEN RET_CUR FOR strSql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END GET_SHAIXUAN_CORP;
--客户筛选
PROCEDURE SHAIXUAN_CORP(N_RTN OUT NUMBER, --返回值 0 操作成功 -1 操作失败
S_CORP_ID VARCHAR2, --虚拟客户号
I_SCREENING_STATE NUMBER, --筛漏状态 1 是 2 否
S_MISING_SALESMAN_ID VARCHAR2, --筛选人员ID
S_MISING_SAL_ORG_ID VARCHAR2, --筛选人员所属机构ID
I_MISING_LIND NUMBER, --筛漏环节
S_MISING_REASION VARCHAR2, --筛选理由
S_MISING_OTHER_INFO VARCHAR2, --其他信息（筛漏）
S_INSERT_ID VARCHAR2, --修改人员ID
S_INSERT_NAME VARCHAR2 --修改人员姓名
)
IS
s_err VARCHAR2(100);
BEGIN
N_Rtn := 0;
UPDATE SM_CORP_INFO SET SCREENING_STATE = I_SCREENING_STATE,
SCREENING_DATA_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'),
INSERT_ID = S_INSERT_ID,
INSERT_NAME = S_INSERT_NAME,
INSERT_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
WHERE CORP_ID = S_CORP_ID;
IF I_SCREENING_STATE = 1 THEN --进入筛漏池
P_Sale_Middle.AddMisingInfo(S_CORP_ID, S_MISING_SALESMAN_ID, S_MISING_SAL_ORG_ID, I_MISING_LIND, S_MISING_REASION, S_MISING_OTHER_INFO, N_RTN);
END IF;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END SHAIXUAN_CORP;
--客户分配
PROCEDURE ASSIGN_CORP(N_RTN OUT NUMBER, --返回值 0 操作成功 -1 操作失败
S_CORP_ID VARCHAR2, --虚拟客户号
I_TYPE NUMBER, --分配人员类型 0 总部 1 分部
S_ASSIGN_SALESMAN_ID VARCHAR2, --分配人员ID
S_ASSIGN_SAL_ORG_ID VARCHAR2, --分配人员所属机构
S_ASSIGN_ORG_ID VARCHAR2, --分配到哪个机构
S_ASSIGN_OTHER_INFO VARCHAR2, --其他信息（筛漏）
S_INSERT_ID VARCHAR2, --修改人员ID
S_INSERT_NAME VARCHAR2 --修改人员姓名
)
IS
s_err VARCHAR2(100);
BEGIN
N_RTN := 0;
IF S_ASSIGN_ORG_ID IS NOT NULL THEN
IF I_TYPE = 0 THEN
UPDATE SM_CORP_INFO SET FENBU_ORG_ID = S_ASSIGN_ORG_ID,
INSERT_ID = S_INSERT_ID,
TUANDUI_ORG_ID = '',
INSERT_NAME = S_INSERT_NAME,
INSERT_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
WHERE CORP_ID = S_CORP_ID;
ELSE
UPDATE SM_CORP_INFO SET TUANDUI_ORG_ID = S_ASSIGN_ORG_ID,
INSERT_ID = S_INSERT_ID,
INSERT_NAME = S_INSERT_NAME,
INSERT_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
WHERE CORP_ID = S_CORP_ID;
END IF;
ELSE
IF I_TYPE = 0 THEN --总部选择分部，置分部ID
UPDATE SM_CORP_INFO SET FENBU_ORG_ID = '',
TUANDUI_ORG_ID = '',
INSERT_ID = S_INSERT_ID,
INSERT_NAME = S_INSERT_NAME,
INSERT_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
WHERE CORP_ID = S_CORP_ID;
ELSE
UPDATE SM_CORP_INFO SET --FENBU_ORG_ID = S_ASSIGN_ORG_ID,
TUANDUI_ORG_ID = '',
INSERT_ID = S_INSERT_ID,
INSERT_NAME = S_INSERT_NAME,
INSERT_TIME = TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
WHERE CORP_ID = S_CORP_ID;
END IF;
END IF;
--插入客户分配历史表
P_Sale_Before.ASSIGN_CORP_HISTORY_INFO(N_RTN, S_CORP_ID, S_ASSIGN_SALESMAN_ID, S_ASSIGN_SAL_ORG_ID,
S_ASSIGN_ORG_ID, S_ASSIGN_OTHER_INFO, I_TYPE);
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END ASSIGN_CORP;
--客户分配历史表
PROCEDURE ASSIGN_CORP_HISTORY_INFO(N_RTN OUT NUMBER,
S_CORP_ID VARCHAR2, --虚拟客户号
S_ASSIGN_SALESMAN_ID VARCHAR2, --分配人员ID
S_ASSIGN_SAL_ORG_ID VARCHAR2, --分配人员所属机构
S_ASSIGN_ORG_ID VARCHAR2, --分配到哪个机构
S_ASSIGN_OTHER_INFO VARCHAR2, --其他信息（筛漏）
I_TYPE NUMBER --分配人员类型 0 总部 1 分部
)
IS
s_err VARCHAR2(100);
BEGIN
INSERT INTO SM_CORP_ASSIGN (CORP_ID, ASSIGN_SALESMAN_ID, ASSIGN_SAL_ORG_ID,
ASSIGN_DATA_DATE, ASSIGN_ORG_ID, ASSIGN_OTHER_INFO)
VALUES ( S_CORP_ID, S_ASSIGN_SALESMAN_ID, S_ASSIGN_SAL_ORG_ID,
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'), S_ASSIGN_ORG_ID, S_ASSIGN_OTHER_INFO);
N_RTN := 0;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END ASSIGN_CORP_HISTORY_INFO;
--返回最近 虚拟客户号
FUNCTION Get_Max_CORPID(S_CORP VARCHAR2)
RETURN VARCHAR2
IS
LAST_CORP_ID VARCHAR(20);
sAssignMaxDate VARCHAR2(19);
BEGIN
LAST_CORP_ID := '';
BEGIN
SELECT CORP_ID INTO LAST_CORP_ID FROM SM_CORP_INFO
--(SELECT CORP_ID FROM SM_CORP_INFO ORDER BY ROWNUM DESC)
--WHERE ROWNUM <= 1;
where rowid = (select max(rowid) from SM_CORP_INFO );
EXCEPTION
WHEN NO_DATA_FOUND THEN
LAST_CORP_ID := '';
END;
RETURN LAST_CORP_ID;
END Get_Max_CORPID;
--根据客户ID取最近的分配日期
FUNCTION Get_Max_Assign_Date(S_CORP_ID IN VARCHAR2, --虚拟客户号
S_ORG_ID IN VARCHAR2) --机构ID
RETURN VARCHAR2
IS
sAssignMaxDate VARCHAR2(19);
sOrg VARCHAR2(20);
sCount NUMBER;
s_err VARCHAR2(100);
BEGIN
sAssignMaxDate := '';
sOrg := '';
sCount := 0;
BEGIN
IF S_ORG_ID <> '$$' THEN
--********
--SELECT COUNT(*) INTO sCount FROM SM_CORP_ASSIGN
-- WHERE CORP_ID = S_CORP_ID;
--IF sCount <> 0 THEN
--*********
BEGIN
SELECT ASSIGN_ORG_ID INTO sOrg FROM
(SELECT ASSIGN_ORG_ID
FROM SM_CORP_ASSIGN
WHERE CORP_ID = S_CORP_ID AND ASSIGN_ORG_ID is not null
ORDER BY ASSIGN_DATA_DATE DESC) t
WHERE ROWNUM < 2;
END;
--**********
--ELSE
--SELECT INSERT_TIME INTO sAssignMaxDate FROM SM_CORP_INFO WHERE CORP_ID = S_CORP_ID;
--END IF;
--**********
IF sOrg IS NOT NULL THEN
SELECT MAX(ASSIGN_DATA_DATE) INTO sAssignMaxDate FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID AND ASSIGN_ORG_ID is not null;
ELSE
--**********
--IF sAssignMaxDate IS NULL THEN
sAssignMaxDate := '';
--END IF;
--**********
END IF;
ELSE
SELECT COUNT(*) INTO sCount FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID;
BEGIN
SELECT ASSIGN_ORG_ID INTO sOrg FROM
(SELECT ASSIGN_ORG_ID
FROM SM_CORP_ASSIGN
WHERE CORP_ID = S_CORP_ID AND ASSIGN_SAL_ORG_ID = '$$'
ORDER BY ASSIGN_DATA_DATE DESC) t
WHERE ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sOrg := '';
END;
IF sOrg IS NOT NULL THEN
SELECT MAX(ASSIGN_DATA_DATE) INTO sAssignMaxDate FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID AND ASSIGN_SAL_ORG_ID = '$$';
ELSE
sAssignMaxDate := '';
END IF;
IF sCount = 1 THEN
SELECT MAX(ASSIGN_DATA_DATE) INTO sAssignMaxDate FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--SELECT INSERT_TIME INTO sAssignMaxDate FROM SM_CORP_INFO WHERE CORP_ID = S_CORP_ID;
sAssignMaxDate := '';
END;
RETURN sAssignMaxDate;
END Get_Max_Assign_Date;
--根据客户ID取最近的分配到团队日期
FUNCTION Get_Max_Assign_TUANDUI_TIME(S_CORP_ID VARCHAR2) --虚拟客户号
RETURN VARCHAR2
IS
sAssignMaxDate VARCHAR2(19);
sOrg VARCHAR2(20);
s_err VARCHAR2(100);
BEGIN
sAssignMaxDate := '';
sOrg := '';
BEGIN
BEGIN
SELECT ASSIGN_ORG_ID INTO sOrg FROM
(SELECT ASSIGN_ORG_ID
FROM SM_CORP_ASSIGN
WHERE CORP_ID = S_CORP_ID AND ASSIGN_TUANDUI_TIME IS NOT NULL
ORDER BY ASSIGN_TUANDUI_TIME DESC) t
WHERE ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sOrg := '';
END;
IF sOrg IS NOT NULL THEN
SELECT MAX(ASSIGN_TUANDUI_TIME) INTO sAssignMaxDate FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID;
ELSE
sAssignMaxDate := '';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sAssignMaxDate := '';
END;
RETURN sAssignMaxDate;
END Get_Max_Assign_TUANDUI_TIME;
--根据客户ID取最近的分配其他信息
FUNCTION Get_Last_Assign_Other_Info(S_CORP_ID IN VARCHAR2, --虚拟客户号
S_ORG_ID IN VARCHAR2) --机构ID
RETURN VARCHAR2
IS
sAssign_Other_Info VARCHAR2(500);
sDate VARCHAR2(100);
BEGIN
sAssign_Other_Info := '';
BEGIN
sDate := P_Sale_Before.Get_Max_Assign_Date(S_CORP_ID,S_ORG_ID);
IF sDate IS NOT NULL THEN
SELECT ASSIGN_OTHER_INFO INTO sAssign_Other_Info FROM SM_CORP_ASSIGN
WHERE ASSIGN_DATA_DATE = sDate
AND CORP_ID = S_CORP_ID;
ELSE
sAssign_Other_Info := '';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sAssign_Other_Info := '';
END;
RETURN sAssign_Other_Info;
END Get_Last_Assign_Other_Info;
--查询客户分配
PROCEDURE Get_Assign_Corp(RET_CUR OUT cur,
S_ORG_ID VARCHAR2, --机构ID
S_CORP_NAME VARCHAR2, --客户名称
ID_STATUS NUMBER, --状态（0 未分配 1 已分配）-1 全部
S_ASSIGN_STA_DATE VARCHAR2, --分配开始时间
S_ASSIGN_END_DATE VARCHAR2, --分配截至时间
N_ZH NUMBER, --1总行 2非
S_SEL_ORG_ID VARCHAR2, --查询人所属分部ID
S_TUANDUI_ID VARCHAR2 --查询人所属团队ＩＤ
)
IS
--strSql VARCHAR2(5000);
strSql1 VARCHAR2(1000);
strSql2 VARCHAR2(1000);
strSql3 VARCHAR2(1000);
s_err VARCHAR2(100);
BEGIN
strSql1 := 'select COLLECT_ORG_ID, decode(COLLECT_ORG_ID, ''$$'', ''总部'', org.ORG_LONG_NAME) COLLECT_ORG_NAME, '
||' CORP_ID, CORP_NAME, INDUSTRY_NAME, INDUSTRY_ID ,'
||' CORP_TYPE1, class1.SUBCLASS_NAME CORP_TYPE1_NAME, CORP_TYPE2, class2.SUBCLASS_NAME CORP_TYPE2_NAME, '
||' ASSIGN_DATA_DATE ,'
||' FENBU_ORG_ID, org2.ORG_LONG_NAME FENBU_ORG_NAME, TUANDUI_ORG_ID, org3.ORG_LONG_NAME TUANDUI_ORG_NAME, '
||' ASSIGN_OTHER_INFO ';
strSql1 := strSql1
||' from SYS_ORGANIZE org,SYS_ORGANIZE org2,SYS_ORGANIZE org3, SYS_CLASS_DETAIL class1 , SYS_CLASS_DETAIL class2, ';
strSql2 := ' select FENBU_ORG_ID, corp1.CORP_ID, CORP_NAME, INDUSTRY_NAME, INDUSTRY_ID, CORP_TYPE1, CORP_TYPE2, assign1.ASSIGN_DATA_DATE, TUANDUI_ORG_ID, COLLECT_ORG_ID, assign1.ASSIGN_OTHER_INFO, SCREENING_STATE '
||' from '
||' ( '
||' select FENBU_ORG_ID, CORP_ID, CORP_NAME, INDUSTRY_NAME, INDUSTRY_ID, CORP_TYPE1, CORP_TYPE2, TUANDUI_ORG_ID, COLLECT_ORG_ID, SCREENING_STATE '
||' from SM_CORP_INFO '
||' ) corp1,'
||' ( '
||' select CORP_ID, P_Sale_Before.Get_Max_Assign_Date(CORP_ID, ''' || S_SEL_ORG_ID || ''') ASSIGN_DATA_DATE, '
-- ||' P_Sale_Before.Get_Max_Assign_TUANDUI_TIME(CORP_ID) ASSIGN_TUANDUI_TIME, '
||' P_Sale_Before.Get_Last_Assign_Other_Info(CORP_ID, ''' || S_SEL_ORG_ID || ''') ASSIGN_OTHER_INFO '
||' FROM SM_CORP_ASSIGN group by corp_id'
||' ) assign1 '
||' where corp1.corp_id = assign1.corp_id(+)';
IF N_ZH = 2 THEN
IF ID_STATUS = 0 THEN --分部--未分配
strSql2 := strSql2
|| ' and TUANDUI_ORG_ID is null ';
END IF;
IF ID_STATUS = 1 THEN --分部 --已分配
strSql2 := strSql2
|| ' and TUANDUI_ORG_ID is not null ';
END IF;
ELSE
IF ID_STATUS = 0 THEN --总部 --未分配
strSql2 := strSql2
|| ' and FENBU_ORG_ID is null';
END IF;
IF ID_STATUS = 1 THEN --总部 --已分配
strSql2 := strSql2
|| ' and FENBU_ORG_ID is not null';
END IF;
END IF;
IF S_ASSIGN_STA_DATE IS NOT NULL THEN
strSql2 := strSql2
||' and ';
IF ID_STATUS = -1 THEN
strSql2 := strSql2
||' ( ';
END IF;
strSql2 := strSql2
||'SUBSTR(assign1.ASSIGN_DATA_DATE,1,10) >= ''' || S_ASSIGN_STA_DATE || ''' ';
END IF;
IF S_ASSIGN_END_DATE IS NOT NULL THEN
strSql2 := strSql2
||' and SUBSTR(assign1.ASSIGN_DATA_DATE,1,10) <= ''' || S_ASSIGN_END_DATE || ''' ';
IF ID_STATUS = -1 THEN
strSql2 := strSql2
||' or assign1.ASSIGN_DATA_DATE is null )';
END IF;
END IF;
strSql3 := ' where corp.COLLECT_ORG_ID = org.ORG_ID(+) AND corp.fenbu_org_id = org2.org_id(+) AND corp.tuandui_org_id = org3.org_id(+)'
||' and corp.SCREENING_STATE = 2 '
||' and (corp.CORP_TYPE1 = class1.SUBCLASS_ID and class1.CLASS_ID = 4)'
||' and (corp.CORP_TYPE2 = class2.SUBCLASS_ID and class2.CLASS_ID = 5)';
IF S_ORG_ID IS NOT NULL THEN --采集机构ID
strSql3 := strSql3
||' and COLLECT_ORG_ID like ''' || S_ORG_ID || '%'' ';
END IF;
IF S_TUANDUI_ID IS NOT NULL THEN --所属团队
strSql3 := strSql3
||' and TUANDUI_ORG_ID like ''' || S_TUANDUI_ID || '%'' ';
END IF;
IF N_ZH = 2 THEN
strSql3 := strSql3
||' and FENBU_ORG_ID like ''' || S_SEL_ORG_ID || '%'' ';
END IF;
IF S_CORP_NAME IS NOT NULL THEN --客户名称
strSql3 := strSql3
||' and CORP_NAME like ''' || S_CORP_NAME || '%'' ';
END IF;
strSql3 := strSql3
||' order by corp.COLLECT_ORG_ID,corp.FENBU_ORG_ID,CORP_ID desc ';
OPEN RET_CUR FOR
strSql1 || ' ( ' || strSql2 || ' ) corp ' || strSql3;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Assign_Corp;
--查询客户分配的历史记录
PROCEDURE Get_History_Assign_Corp(RET_CUR OUT cur,
S_CORP_ID VARCHAR2, --虚拟客户号
S_ORG_ID VARCHAR2) --机构ＩＤ
IS
strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
strSql := 'select ASSIGN_SALESMAN_ID, salesman.SALESMAN_NAME ASSIGN_SALESMAN_NAME ,'
||' ASSIGN_SAL_ORG_ID, decode(ASSIGN_SAL_ORG_ID, ''$$'', ''总部'', org1.ORG_LONG_NAME) ASSIGN_SAL_ORG_NAME, '
||' ASSIGN_DATA_DATE, ASSIGN_TUANDUI_TIME, '
||' ASSIGN_ORG_ID, org2.ORG_LONG_NAME ASSIGN_ORG_NAME, '
||' ASSIGN_OTHER_INFO ';
strSql := strSql
||' from SM_CORP_ASSIGN assign1, SYS_ORGANIZE org1, SYS_ORGANIZE org2, DAT_SALESMAN salesman '
||' where assign1.ASSIGN_SAL_ORG_ID = org1.ORG_ID(+) '
||' and assign1.ASSIGN_ORG_ID = org2.ORG_ID(+) '
||' and assign1.ASSIGN_SALESMAN_ID = salesman.SALESMAN_ID(+) '
||' and ASSIGN_ORG_ID IS NOT NULL '
||' and ASSIGN_SALESMAN_ID IS NOT NULL';--去掉分部 团队采集时进来的信息
IF S_ORG_ID <> '$$' THEN
strSql := strSql
||' and ASSIGN_ORG_ID like ''' || S_ORG_ID || '%'' '
||' and assign1.CORP_ID = ''' || S_CORP_ID || ''' order by ASSIGN_DATA_DATE,ASSIGN_ORG_NAME desc';
ELSE
strSql := strSql
||' and assign1.CORP_ID = ''' || S_CORP_ID || ''' order by ASSIGN_DATA_DATE,ASSIGN_ORG_NAME desc';
END IF;
OPEN RET_CUR FOR strSql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_History_Assign_Corp;
--根据客户ID取最近的筛选日期
FUNCTION Get_Max_Filter_Date(S_CORP_ID VARCHAR2) --虚拟客户号
RETURN VARCHAR2
IS
sAssignMaxDate VARCHAR2(19);
s_err VARCHAR2(100);
BEGIN
sAssignMaxDate := '';
BEGIN
SELECT MAX(ASSIGN_DATA_DATE) INTO sAssignMaxDate FROM SM_CORP_ASSIGN WHERE CORP_ID = S_CORP_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sAssignMaxDate := '';
END;
RETURN sAssignMaxDate;
END Get_Max_Filter_Date;
--更新客户分配历史表信息
PROCEDURE UPDATA_ASSIGN_HISTOR(N_RTN OUT NUMBER,
S_CORP_ID VARCHAR2, --虚拟客户号
S_TIME VARCHAR2 --要修改的是哪一时间的数据
)
IS
s_err VARCHAR2(100);
BEGIN
UPDATE SM_CORP_ASSIGN SET ASSIGN_DATA_DATE = '',ASSIGN_ORG_ID = ''
WHERE CORP_ID = S_CORP_ID AND ASSIGN_DATA_DATE = S_TIME;
N_RTN := 0;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END UPDATA_ASSIGN_HISTOR;
--恢复筛漏
PROCEDURE UPDATA_FILTER_CORP(N_RTN OUT NUMBER,
S_CORP_ID IN VARCHAR2 --客户虚拟ＩＤ号
)
IS
--strSql VARCHAR2(2000);
s_err VARCHAR2(100);
BEGIN
IF S_CORP_ID IS NOT NULL THEN
UPDATE SM_CORP_INFO SET SCREENING_STATE = 0, ORIGIN_CHANNEL = 8,SCREENING_DATA_TIME = ''
WHERE CORP_ID = S_CORP_ID ;
END IF;
N_RTN := 0;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
N_RTN := -1;
END UPDATA_FILTER_CORP;
--取得不同统计类型下的拜访统计数
FUNCTION Get_EachOther_Count(S_COUNT_TYPE IN NUMBER, --统计类型（0：机构１：总经理２：客户经理３：客户）
S_TYPE_ID IN VARCHAR2, --机构ID 或 总经理ＩＤ或 客户经理ＩＤ 或 客户ＩＤ
S_USER_ID IN VARCHAR2) --登录人的ＩＤ
RETURN NUMBER
IS
result NUMBER;
BEGIN
BEGIN
IF S_COUNT_TYPE = 0 THEN
SELECT COUNT(*) INTO result FROM SM_VISIT
WHERE ORG_ID like S_TYPE_ID || '%';--= S_TYPE_ID;
END IF;
IF S_COUNT_TYPE = 1 THEN
SELECT count(*) INTO result FROM SM_VISIT
WHERE P_APP_INFO.GET_PERSON_POS(S_USER_ID,S_TYPE_ID) = 79
AND ORG_ID = S_TYPE_ID
AND VISIT_MAN_ID = S_USER_ID;
END IF;
IF S_COUNT_TYPE = 2 THEN
SELECT count(*) INTO result FROM SM_VISIT
WHERE P_APP_INFO.GET_PERSON_POS(S_USER_ID,S_TYPE_ID) = 80
AND ORG_ID = S_TYPE_ID
AND VISIT_MAN_ID = S_USER_ID;
END IF;
IF S_COUNT_TYPE = 3 THEN
SELECT COUNT(*) INTO result FROM SM_VISIT
WHERE CORP_ID = S_TYPE_ID;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result := 0;
END;
RETURN result;
END Get_EachOther_Count;
--取得不同统计类型下的 销售成功率
FUNCTION Get_Sell_Rate (S_COUNT_TYPE IN NUMBER, --统计类型（0：机构１：总经理２：客户经理３：客户）
S_TYPE_ID IN VARCHAR2, --机构ID 或 总经理ＩＤ或 客户经理ＩＤ 或 客户ＩＤ
S_USER_ID IN VARCHAR2) --登录人的ＩＤ
RETURN NUMBER
IS
result NUMBER;
BEGIN
BEGIN
result := 0;
END;
RETURN result;
END Get_Sell_Rate;
--取得不同统计类型下的 授信成功率
FUNCTION Get_Medal_Rate (S_COUNT_TYPE IN NUMBER, --统计类型（0：机构１：总经理２：客户经理３：客户）
S_TYPE_ID IN VARCHAR2, --机构ID 或 总经理ＩＤ或 客户经理ＩＤ 或 客户ＩＤ
S_USER_ID IN VARCHAR2) --登录人的ＩＤ
RETURN NUMBER
IS
result NUMBER;
BEGIN
BEGIN
result := 0;
END;
RETURN result;
END Get_Medal_Rate;
--查询拜访统计
PROCEDURE Get_Visit_Count (RET_CUR OUT cur, --返回结果集
S_VISITE_TYPE IN NUMBER, -- 0:机构１：总经理２：客户经理３：客户
S_ORG_ID IN VARCHAR2, -- 机构ID --
S_GM_NAME IN VARCHAR2, -- 总经理--
S_CorpManaer_NAME IN VARCHAR2, -- 客户经理--,
S_Corp_NAME IN VARCHAR2, -- 客户名称--,
S_START_DATE IN VARCHAR2, -- 开始时间--,
S_END_DATE IN VARCHAR2 -- 结束时间--,
)
IS
s_err VARCHAR2(100);
s_sql VARCHAR2(1000);
BEGIN
IF S_VISITE_TYPE = 0 THEN --按 机构
s_sql:=' select distinct visit.ORG_ID, org.ORG_LONG_NAME NAME,P_Sale_Before.Get_EachOther_Count(0,visit.ORG_ID,'''') count'
||' ,P_Sale_Before.Get_Sell_Rate('''','''','''') sell '
||',P_Sale_Before.Get_Medal_Rate('''','''','''') medal '
||' FROM SM_VISIT visit,SYS_ORGANIZE org,DAT_SALESMAN salesman,SM_CORP_INFO corp'
||' where visit.ORG_ID = org.ORG_ID(+) '
||' AND visit.VISIT_MAN_ID = salesman.SALESMAN_ID(+)'
||' AND visit.CORP_ID = corp.CORP_ID'
||' AND P_Sale_Before.Get_EachOther_Count(0,visit.ORG_ID,'''') > 0 ';
END IF;
IF S_VISITE_TYPE = 1 THEN --按 总经理
s_sql:=' select distinct salesman.SALESMAN_NAME NAME,'
||' P_Sale_Before.Get_EachOther_Count(1,visit.ORG_ID,visit.VISIT_MAN_ID) COUNT '
||',P_Sale_Before.Get_Sell_Rate('''','''','''') sell '
||',P_Sale_Before.Get_Medal_Rate('''','''','''') medal'
||' FROM SM_VISIT visit,SYS_ORGANIZE org,DAT_SALESMAN salesman,SM_CORP_INFO corp'
||' where visit.VISIT_MAN_ID = salesman.SALESMAN_ID(+)'
||' AND visit.CORP_ID = corp.CORP_ID'
||' AND visit.ORG_ID = org.ORG_ID(+)'
||' AND P_Sale_Before.Get_EachOther_Count(1,visit.ORG_ID,visit.VISIT_MAN_ID) > 0';
IF S_GM_NAME IS NOT NULL THEN --总经理
s_sql := s_sql || ' AND salesman.SALESMAN_NAME like ''%'|| S_GM_NAME || '%'' ';
END IF;
END IF;
IF S_VISITE_TYPE = 2 THEN --按 客户经理
s_sql:=' select distinct salesman.SALESMAN_NAME NAME,'
||' P_Sale_Before.Get_EachOther_Count(2,visit.ORG_ID,visit.VISIT_MAN_ID) COUNT'
||',P_Sale_Before.Get_Sell_Rate('''','''','''') sell'
||',P_Sale_Before.Get_Medal_Rate('''','''','''') medal'
||' FROM SM_VISIT visit,SYS_ORGANIZE org,DAT_SALESMAN salesman,SM_CORP_INFO corp'
||' where visit.VISIT_MAN_ID = salesman.SALESMAN_ID(+)'
||' AND visit.CORP_ID = corp.CORP_ID'
||' AND visit.ORG_ID = org.ORG_ID(+)'
||' AND P_Sale_Before.Get_EachOther_Count(2,visit.ORG_ID,visit.VISIT_MAN_ID) > 0';
IF S_CorpManaer_NAME IS NOT NULL THEN --客户经理
s_sql := s_sql || ' AND salesman.SALESMAN_NAME like ''%'|| S_CorpManaer_NAME || '%'' ';
END IF;
END IF;
IF S_VISITE_TYPE = 3 THEN --按 客户
s_sql:='select distinct corp.CORP_NAME NAME , P_Sale_Before.Get_EachOther_Count(3,visit.CORP_ID,'''') count'
||' FROM SM_VISIT visit,SYS_ORGANIZE org,DAT_SALESMAN salesman,SM_CORP_INFO corp'
||' where visit.CORP_ID = corp.CORP_ID'
||' AND visit.ORG_ID = org.ORG_ID(+)'
||' AND visit.VISIT_MAN_ID = salesman.SALESMAN_ID(+)'
||' AND P_Sale_Before.Get_EachOther_Count(3,visit.CORP_ID,'''') > 0';
IF S_Corp_NAME IS NOT NULL THEN --客户
s_sql := s_sql || ' AND corp.CORP_NAME like ''%'|| S_Corp_NAME || '%'' ';
END IF;
END IF;
IF S_ORG_ID IS NOT NULL THEN--机构ＩＤ
s_sql := s_sql || 'AND visit.ORG_ID like ''' || S_ORG_ID || '%'' ';
END IF;
--IF S_GM_NAME IS NOT NULL THEN --总经理
-- s_sql := s_sql || ' AND visit.MEET_MAN_NAME like ''%'|| S_GM_NAME || '%'' ';
--END IF;
--IF S_CorpManaer_NAME IS NOT NULL THEN --客户经理
-- s_sql := s_sql || ' AND visit.MEET_MAN_NAME like ''%'|| S_CorpManaer_NAME || '%'' ';
--END IF;
--IF S_Corp_NAME IS NOT NULL THEN --客户
-- s_sql := s_sql || ' AND corp.CORP_NAME like ''%'|| S_Corp_NAME || '%'' ';
-- END IF;
IF S_START_DATE IS NOT NULL THEN
s_sql := s_sql
||' and visit.INSERT_DATE >= ''' || S_START_DATE || ''' ';
END IF;
IF S_END_DATE IS NOT NULL THEN
s_sql := s_sql
||' and visit.INSERT_DATE <= ''' || S_END_DATE || '''';
END IF;
OPEN RET_CUR FOR s_sql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Visit_Count;
-- 查询拜访详细信息
PROCEDURE Get_Visit_Particular (ret_cur OUT cur, --返回结果集
S_VISIT_NAME IN VARCHAR2, --查询备件
S_VISIT_TYPE IN NUMBER --查询类型(0,机构１：总经理２：客户)
)
IS
s_err VARCHAR2(100);
s_sql VARCHAR2(1000);
BEGIN
s_sql := 'select visit.CORP_ID,corp.CORP_NAME,'
||' corp.SCREENING_STATE,DECODE(corp.SCREENING_STATE,0,''未处理'',1,''是'',2,''否'',''已被删除'') SCREENING,'
||' visit.VISIT_STATE,DECODE(visit.VISIT_STATE,0,''预约拜访'',1,''非预约拜访'',2,''交叉销售'') VISIT_STATE_NAME,'
||' visit.MEET_MAN_NAME,visit.MEET_BUSINESS,visit.MEET_PHONE,'
||' visit.MEET_NOTE,visit.ACCOMPANY_STATE,DECODE(visit.ACCOMPANY_STATE,1,''是'',2,''否'') ACCOMPANY_STATE_NAME,'
||' visit.MEET_LOCUS,'
||' visit.ORG_ID,DECODE(corp.COLLECT_ORG_ID,''$$'',''总部'',org.ORG_LONG_NAME) VISIT_ORG_NAME,'
||' visit.VISIT_MAN_ID,salesman.SALESMAN_NAME VISIT_MAN_NAME,'
||' visit.VISIT_DATE'
||' from SM_VISIT visit,SM_CORP_INFO corp,DAT_SALESMAN salesman,SYS_ORGANIZE org'
||' where visit.ORG_ID = org.ORG_ID(+)'
||' AND visit.CORP_ID = corp.CORP_ID(+)'
||' AND visit.VISIT_MAN_ID = salesman.SALESMAN_ID(+)';
IF S_VISIT_TYPE = 1 THEN
s_sql := s_sql || ' AND salesman.SALESMAN_NAME like ''' || S_VISIT_NAME ||'%''';
END IF;
IF S_VISIT_TYPE = 0 THEN
s_sql := s_sql || ' AND visit.ORG_ID like ''' || S_VISIT_NAME ||'%''';
END IF;
IF S_VISIT_TYPE = 2 THEN
s_sql := s_sql || ' AND corp.CORP_NAME like ''%' || S_VISIT_NAME ||'%''';
END IF;
s_sql := s_sql || ' ORDER BY visit.VISIT_DATE DESC';
OPEN RET_CUR FOR s_sql;
EXCEPTION
WHEN OTHERS THEN
s_err := SUBSTR(SQLERRM, 1, 100);
NULL;
END Get_Visit_Particular;
END P_Sale_Before;
/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
