CREATE OR REPLACE PROCEDURE PRO_REWARD_REPORT AS
V_REWARD_PRICE NUMBER := NULL;
V_COUNT NUMBER := 0;
V_REWARD_LOCK_DATE DATE;
V_EXISTS NUMBER;
V_DEAD_LINE NUMBER(2);
BEGIN
/* 从系统中查找设定的锁定时间 */
SELECT TO_NUMBER(SP.P_VAL)
INTO V_DEAD_LINE
FROM T_SYSTEM_PARAM SP
WHERE SP.P_KEY = 'RewardDeadLine';
/* 查找最近的锁定日期 */
IF (TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) >= V_DEAD_LINE) THEN
SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') || '01', 'YYYYMMDD')
INTO V_REWARD_LOCK_DATE
FROM DUAL;
ELSE
SELECT TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') || '01',
'YYYYMMDD')
INTO V_REWARD_LOCK_DATE
FROM DUAL;
END IF;
/* 查找符合条件的串码数据 */
FOR ITEM IN (SELECT T.ID,
T.SERIAL_NO,
T.REWARD_TYPE,
T.Creator_Id,
T.Creator_Name,
T.CREATED_DATE,
T.MODI_DATE,
T.MODIFIER_ID,
T.MODIFIER_NAME,
T.CLERK_ID,
clerk.empl_code clerk_code,
clerk.name CLERK_NAME,
clerk.wo_account CLERK_WO_ACCOUNT /*店员信息*/,
clerk.ss_id SS_ID,
clerk.mobile_phone CLERK_PHONE,
clerk.modi_date CLERK_MODI_DATE,
T.RETAILER_ID,
NVL(T.CONTRACT_PLAN, '2') CONTRACT_PLAN,
T.IS_SPECIAL_SN,
T.BRAND_OLD_ID,
T.BRAND_OLD_NAME,
T.PROD_MODEL_OLD_ID,
T.PROD_MODEL_OLD_NAME,
T.SALES_OFFI_ID,
T.SALES_OFFI_NAME,
T.SALES_GROUP_ID,
T.SALES_GROUP_NAME,
TR.ID TR_ID,
TR.RETAILER_CODE TR_RETAILER_CODE,
TR.NAME TR_NAME,
TR.MODI_DATE TR_MODI_DATE,
T.RETAILER_CHANNELS /*暂时取销量上报时的渠道类型*/
FROM T_TSO_LINE T
join t_other_empl clerk
on t.clerk_id = clerk.id
join t_retailer tr
on tr.id = t.retailer_id
WHERE T.CREATED_DATE >= V_REWARD_LOCK_DATE
and t.person_status = '0'
AND T.CREATED_DATE < SYSDATE
AND T.CLERK_ID IS NOT NULL /* 店员不能为空值 */
AND T.SALES_OFFI_ID IS NOT NULL /* 省办不能为空值 */
AND T.RETAILER_ID IS NOT NULL /* 门店不能为空值 */
AND T.PROD_MODEL_OLD_ID IS NOT NULL /* 判定机型应该存在 */
and t.retailer_channels in ('1', '2', '3') /* 营销渠道的门店类型*/
and t.retailer_type in ('1', '2') /* 营销渠道的门店类型*/
AND T.AWARD_FLG = '0' /* 正常店奖 */
AND T.SERIALSTATUS = '0' /* 机构状态正常 */
AND T.IS_AWARD_FLG = '0' /* 销量类型正常 */
------新添标识非中小平台串码 -----
AND T.XN_STATUS IS NULL /* */
/* 判定是否是店奖机型 */
AND EXISTS
(SELECT 1
FROM T_RETA_REWARD RR
WHERE RR.STATUS != 3
AND RR.START_DATE < t.created_date
and rr.end_date >= t.created_date
AND RR.PROD_MODL_ID = T.PROD_MODEL_OLD_ID)) LOOP
V_COUNT := V_COUNT + 1;
IF item.modifier_id is null then
item.modifier_id := item.creator_id;
item.modi_date := item.created_date;
item.modifier_name := item.creator_name;
END IF;
/* 查找该串码是否在详情表中存在 */
SELECT COUNT(1)
INTO V_EXISTS
F
V_REWARD_PRICE NUMBER := NULL;
V_COUNT NUMBER := 0;
V_REWARD_LOCK_DATE DATE;
V_EXISTS NUMBER;
V_DEAD_LINE NUMBER(2);
BEGIN
/* 从系统中查找设定的锁定时间 */
SELECT TO_NUMBER(SP.P_VAL)
INTO V_DEAD_LINE
FROM T_SYSTEM_PARAM SP
WHERE SP.P_KEY = 'RewardDeadLine';
/* 查找最近的锁定日期 */
IF (TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) >= V_DEAD_LINE) THEN
SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') || '01', 'YYYYMMDD')
INTO V_REWARD_LOCK_DATE
FROM DUAL;
ELSE
SELECT TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') || '01',
'YYYYMMDD')
INTO V_REWARD_LOCK_DATE
FROM DUAL;
END IF;
/* 查找符合条件的串码数据 */
FOR ITEM IN (SELECT T.ID,
T.SERIAL_NO,
T.REWARD_TYPE,
T.Creator_Id,
T.Creator_Name,
T.CREATED_DATE,
T.MODI_DATE,
T.MODIFIER_ID,
T.MODIFIER_NAME,
T.CLERK_ID,
clerk.empl_code clerk_code,
clerk.name CLERK_NAME,
clerk.wo_account CLERK_WO_ACCOUNT /*店员信息*/,
clerk.ss_id SS_ID,
clerk.mobile_phone CLERK_PHONE,
clerk.modi_date CLERK_MODI_DATE,
T.RETAILER_ID,
NVL(T.CONTRACT_PLAN, '2') CONTRACT_PLAN,
T.IS_SPECIAL_SN,
T.BRAND_OLD_ID,
T.BRAND_OLD_NAME,
T.PROD_MODEL_OLD_ID,
T.PROD_MODEL_OLD_NAME,
T.SALES_OFFI_ID,
T.SALES_OFFI_NAME,
T.SALES_GROUP_ID,
T.SALES_GROUP_NAME,
TR.ID TR_ID,
TR.RETAILER_CODE TR_RETAILER_CODE,
TR.NAME TR_NAME,
TR.MODI_DATE TR_MODI_DATE,
T.RETAILER_CHANNELS /*暂时取销量上报时的渠道类型*/
FROM T_TSO_LINE T
join t_other_empl clerk
on t.clerk_id = clerk.id
join t_retailer tr
on tr.id = t.retailer_id
WHERE T.CREATED_DATE >= V_REWARD_LOCK_DATE
and t.person_status = '0'
AND T.CREATED_DATE < SYSDATE
AND T.CLERK_ID IS NOT NULL /* 店员不能为空值 */
AND T.SALES_OFFI_ID IS NOT NULL /* 省办不能为空值 */
AND T.RETAILER_ID IS NOT NULL /* 门店不能为空值 */
AND T.PROD_MODEL_OLD_ID IS NOT NULL /* 判定机型应该存在 */
and t.retailer_channels in ('1', '2', '3') /* 营销渠道的门店类型*/
and t.retailer_type in ('1', '2') /* 营销渠道的门店类型*/
AND T.AWARD_FLG = '0' /* 正常店奖 */
AND T.SERIALSTATUS = '0' /* 机构状态正常 */
AND T.IS_AWARD_FLG = '0' /* 销量类型正常 */
------新添标识非中小平台串码 -----
AND T.XN_STATUS IS NULL /* */
/* 判定是否是店奖机型 */
AND EXISTS
(SELECT 1
FROM T_RETA_REWARD RR
WHERE RR.STATUS != 3
AND RR.START_DATE < t.created_date
and rr.end_date >= t.created_date
AND RR.PROD_MODL_ID = T.PROD_MODEL_OLD_ID)) LOOP
V_COUNT := V_COUNT + 1;
IF item.modifier_id is null then
item.modifier_id := item.creator_id;
item.modi_date := item.created_date;
item.modifier_name := item.creator_name;
END IF;
/* 查找该串码是否在详情表中存在 */
SELECT COUNT(1)
INTO V_EXISTS
F