DBpatch5.0.137.SQL
--是否购买过特殊
delete from t_discount_obj where Field_Code =‘isHadBuySpecProd‘;
insert into t_discount_obj ( id,Field_Code,description,macro_code,del_flag) values(s_id.nextval, ‘isHadBuySpecProd‘,‘是否曾买过特定产品‘, ‘isHadBuySpecProd‘,0);
delete from t_ar_cacl_obj where Field_Code = ‘isHadBuySpecProd‘;
insert into t_ar_cacl_obj ( id,Field_Code,description,macro_code,del_flag) values (s_id.nextval , ‘isHadBuySpecProd‘,‘是否曾买过特定产品‘, ‘isHadBuySpecProd‘,0);
--检查是否购买过特定产品(长春需求) ，此为空壳函数，业务逻辑以本地为主
declare
ct integer;
begin
select COUNT(*) into ct from user_objects where object_name =‘FUN_DISC_COND_ISHADBUYSPECPROD‘;
IF ct= 0 THEN
EXECUTE IMMEDIATE --单引号内的语句为创建函数语句
‘ --创建函数语句开始
create or replace function fun_disc_cond_isHadBuySpecProd(v_id in number)
return number as
begin
return 0 ;
exception
when others then
return - 1;
end fun_disc_cond_isHadBuySpecProd;
--创建函数语句结束
‘ ;
END IF;
END;
/
CREATE OR REPLACE VIEW V_FS_OBJ AS
SELECT DISTINCT c.house_cnt, a.city_id, a.buyer_type_id, a.cust_id,
TO_CHAR (a.create_date, ‘yyyyMMdd‘) cust_reg_date,
a.cust_type_id, a.cust_no, d.buyer_id, d.cur_id,
d.cur_rate, d.sale_price, d.sale_natural_price,
d.pay_term_id, g.is_mortagage, f.ID, f.flat_type_id,
DECODE (fun_get_unitaccinfo (f.ID, 9007),
NULL, 0,
fun_get_unitaccinfo (f.ID, 9007)
) terminal_count,
f.prod_category_id, f.list_price, f.price_one,
f.tatol_area, f.inner_area, f.measure_total_area,
f.measure_inner_area,
f.PRE_TOTAL_AREA PRE_TOTAL_AREA,f.PRE_INNER_AREA PRE_INNER_AREA,
DECODE (k.amount, NULL, 0, k.amount) draw_amount,
k.ar_type_id, d.mortagage_years, d.mortagage_percent,
f.sale_type_id, f.project_id, f.phase_id, f.garden_id,
f.tower_id, f.floor_id, i.bank_id,
(SELECT COUNT (*)
FROM t_customer_person
WHERE cust_id = a.cust_id AND status = 0) person_count,
j.account_id, TO_CHAR (h.sign_date, ‘yyyymmdd‘) sign_date,
DECODE (h.del_flag, 0, 1, 0) is_sign,
TO_CHAR ((SELECT ar_date + g.sign_date_delay
FROM T_AR
WHERE ar_type_id = 3101
AND del_flag = 0
AND account_id = j.account_id),
‘yyyymmdd‘
) plan_sign_date,
TO_CHAR (SYSDATE, ‘yyyyMMdd‘) current_day,
TO_CHAR (d.first_select_payment_date,
‘yyyyMMdd‘
) buyer_add_day,
f.lot_no,
--NVL((f.fact_handover_date-h.handover_date),0) handover_day,
NVL((f.fact_handover_date-greatest(h.handover_date,nvl(fun_get_maxReceivedDate(j.account_id),h.handover_date))),0) handover_day,
fun_get_unitinfo(c.buyer_id,8) received_amt,
f.measure_area1,
f.measure_area2,
f.AREA2,
a.FAMILY_PERSONS holdUnitCnt,
tft.category_id flat_type_category_id,
decode(a.cust_level_ex,40,1,0) isOldCustomer,
a.cust_level_ex isRecommend,
fun_disc_cond_isHadBuySpecProd(a.cust_id) isHadBuySpecProd -- 调用函数
FROM T_CUSTOMER a,
T_BUYER c,
T_SALE d,
T_SALE_ITEM e,
v_unit f,
T_PAYMENT_TERM g,
T_ACCOUNT j,
T_AR k,
T_MARKET_CONTRACT h,
T_MORTGAGE_CONTRACT i,
t_flat_type tft
WHERE a.cust_id = c.cust_id
AND c.buyer_id = d.buyer_id
AND j.buyer_id = c.buyer_id