Ticket #1: cyp_update_product_11.sh

#!/bin/sh

NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
ORACLE_HOME=/oracle/product/10.2.0
ORACLE_SID=v880
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
PATH=/usr/ccs/bin:/usr/bin:/usr/local/bin:/usr/sbin:/usr/ucb:/etc:$ORACLE_HOME/bin:.
export NLS_LANG
export ORACLE_HOME
export ORACLE_SID
export ORA_NLS33
export PATH

cd /data/oracle/crontab/app

sqlplus "/ as sysdba " <<EOF
alter session set current_schema=cyp_nw_app; 

update pdl_product_base  set EMALL_OWNED=0 where create_date>sysdate-700;


commit; 

update pdl_product_base p1 set EMALL_OWNED=1 where 
exists (select 1 from (select t1.product_id
from ent_product_item t1, ent_user t2, ent_company t6 
where t1.user_id = t2.id and  t1.user_id = t6.id 
and t1.sell_status = 1 and t1.start_date <= sysdate
and sysdate <= t1.end_date and t1.status in (0,1)
and t2.status = 1 and t6.status = 1 ) p2  where p1.id=p2.product_id);

commit;  

update pdl_product a set a.EYP_PRICE_COUNT=0;

update pdl_product a set a.EYP_PRICE_COUNT=(select b.company_count from (
SELECT product_id, count(*) as company_count 
FROM V_ENT_PRODUCT
group by product_id) b where b.product_id= a.id)
where exists (select 1 from v_ent_product c where a.id=c.product_id);


commit ; 

update ent_product_item set price_status=1 where company_product_id in
(select t1.company_product_id from ent_product_item t1,v_pdl_product t2 
where t1.product_id=t2.id
and t2.price<>0 and (t1.RETAIL_PRICE>t2.price*1.4 or t1. RETAIL_PRICE<t2.price*0.6) and t1.price_status=0);

commit;

truncate table product_app.eml_price;

insert into eml_price(product_id,min_price,max_price)  
(SELECT product_id, min(retail_price),max(retail_price)
FROM V_ENT_PRODUCT where price_status=0
group by product_id);

commit;


EOF