CREATE OR REPLACE PROCEDURE C_VIP_UPGRADE_AC(p_id IN NUMBER,
p_code OUT NUMBER,
p_message OUT VARCHAR2) AS
/*
Alley 20100205 init
vip升级功能
*/
TYPE myarray IS TABLE OF NUMBER(10);
v_viptype_filter myarray;
v_vip_filter myarray;
v_filter clob;
v_fil CLOB;
v_sql VARCHAR(32676);
v1_sql VARCHAR(32676);
v VARCHAR(32676);
v1 VARCHAR(32676);
v_seq NUMBER(10);
v_fa_vipacc fa_vipacc%ROWTYPE;BEGIN
SELECT t.c_viptype_filter INTO v_filter FROM c_vip_upgrade t WHERE t.id = p_id;
IF v_filter IS NULL THEN
raise_application_error(-20201, '请选择VIP类型!');
END IF;
v_sql := GET_FITLER_SQL(v_filter);
v_sql := 'select t.id from c_viptype t where t.id ' || v_sql;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_viptype_filter; SELECT t.c_vip_filter INTO v_fil FROM c_vip_upgrade t WHERE t.id = p_id;
IF v_fil IS NULL THEN
raise_application_error(-20201, '请选择VIP卡号!');
END IF;
v1_sql := GET_FITLER_SQL(v_fil);
v1_sql := 'select t.id from c_vip t where t.id ' || v1_sql;
EXECUTE IMMEDIATE v1_sql BULK COLLECT
INTO v_vip_filter; --获取所选卡类型的VIP客户信息,插入明细
while v1 IN ('v_vip_filter.FIRST .. v_vip_filter.LAST') and
v in ('v_viptype_filter.FIRST .. v_viptype_filter.LAST')
LOOP
v_seq := get_sequences('c_vip_upgradeitem');
INSERT INTO c_vip_upgradeitem
(ID, ad_client_id, ad_org_id, ownerid, modifierid, modifieddate, creationdate,
Isactive, c_vip_upgrade_id, c_vip_id, c_viptype_id, amount, integral,
tot_amt_actual, MINUSINTEGRAL, c_viptype_destid,OPENCARDDATE)
SELECT get_sequences('c_vip_upgradeitem'), a.AD_CLIENT_ID, a.AD_ORG_ID,
a.OWNERID, a.MODIFIERID, SYSDATE, SYSDATE, 'Y', p_id, v_vip_filter(v1) ,
v_viptype_filter(v), b.amount, b.integral, b.tot_amt_actual,
c.checkoffintg, c.c_viptypeup_id, a.OPENCARDDATE
FROM c_vip a, fa_vipacc b, c_viptype c
WHERE a.C_VIPTYPE_ID = v_viptype_filter(v)
and a.id = v_vip_filter(v1)
AND a.ID = b.c_vip_id
AND a.C_VIPTYPE_ID = c.id
AND c.canupgrade = 'Y'
AND b.integral >= c.needintg;
END LOOP;
p_code := 1;
END;存储
p_code OUT NUMBER,
p_message OUT VARCHAR2) AS
/*
Alley 20100205 init
vip升级功能
*/
TYPE myarray IS TABLE OF NUMBER(10);
v_viptype_filter myarray;
v_vip_filter myarray;
v_filter clob;
v_fil CLOB;
v_sql VARCHAR(32676);
v1_sql VARCHAR(32676);
v VARCHAR(32676);
v1 VARCHAR(32676);
v_seq NUMBER(10);
v_fa_vipacc fa_vipacc%ROWTYPE;BEGIN
SELECT t.c_viptype_filter INTO v_filter FROM c_vip_upgrade t WHERE t.id = p_id;
IF v_filter IS NULL THEN
raise_application_error(-20201, '请选择VIP类型!');
END IF;
v_sql := GET_FITLER_SQL(v_filter);
v_sql := 'select t.id from c_viptype t where t.id ' || v_sql;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_viptype_filter; SELECT t.c_vip_filter INTO v_fil FROM c_vip_upgrade t WHERE t.id = p_id;
IF v_fil IS NULL THEN
raise_application_error(-20201, '请选择VIP卡号!');
END IF;
v1_sql := GET_FITLER_SQL(v_fil);
v1_sql := 'select t.id from c_vip t where t.id ' || v1_sql;
EXECUTE IMMEDIATE v1_sql BULK COLLECT
INTO v_vip_filter; --获取所选卡类型的VIP客户信息,插入明细
while v1 IN ('v_vip_filter.FIRST .. v_vip_filter.LAST') and
v in ('v_viptype_filter.FIRST .. v_viptype_filter.LAST')
LOOP
v_seq := get_sequences('c_vip_upgradeitem');
INSERT INTO c_vip_upgradeitem
(ID, ad_client_id, ad_org_id, ownerid, modifierid, modifieddate, creationdate,
Isactive, c_vip_upgrade_id, c_vip_id, c_viptype_id, amount, integral,
tot_amt_actual, MINUSINTEGRAL, c_viptype_destid,OPENCARDDATE)
SELECT get_sequences('c_vip_upgradeitem'), a.AD_CLIENT_ID, a.AD_ORG_ID,
a.OWNERID, a.MODIFIERID, SYSDATE, SYSDATE, 'Y', p_id, v_vip_filter(v1) ,
v_viptype_filter(v), b.amount, b.integral, b.tot_amt_actual,
c.checkoffintg, c.c_viptypeup_id, a.OPENCARDDATE
FROM c_vip a, fa_vipacc b, c_viptype c
WHERE a.C_VIPTYPE_ID = v_viptype_filter(v)
and a.id = v_vip_filter(v1)
AND a.ID = b.c_vip_id
AND a.C_VIPTYPE_ID = c.id
AND c.canupgrade = 'Y'
AND b.integral >= c.needintg;
END LOOP;
p_code := 1;
END;存储
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货