同步撤单文件到和包的脚本

##############################撤单的对帐文件############################

export_file_name_path="/home/dspt/huibuy_order/JXXYDBGJ000R${export_yyyymm_dates}001.DAT"

export_file_name="JXXYDBGJ000R${export_yyyymm_dates}001.DAT"

sqlplus -S ucr_shop/dspt2015@huibuy<

set trimspool on;

set term off;

set feedback off;

set head off;

set linesize 1000;

set pagesize 0;

spool tmp_record_hb.txt

select count(1) from HE_BAO_LOAN HBL

INNER JOIN ECPS_ORDER EO ON EO.ORDER_ID = HBL.ORDERID AND EO.ORDER_STATUS = '2'

INNER JOIN ECPS_ORDER_RETURN EOR ON EOR.ORDER_ID = EO.ORDER_ID

INNER JOIN ECPS_ORDER EO1 ON EOR.ORDER_RETURN_ID = EO1.ORDER_ID AND EO1.ORDER_STATUS = '2'

       where to_char(eo1.finish_time,'yyyymmdd') =to_char(sysdate-1,'yyyyMMdd')

  group by to_char(eo1.finish_time,'yyyyMMdd');

spool off;

exit;

!

sql="SELECT to_char(eo1.finish_time,'yyyyMMdd')||'|'||count(mplOrdNo)||'|'||sum(loanAmt) FROM HE_BAO_LOAN HBL INNER JOIN ECPS_ORDER EO ON EO.ORDER_ID = HBL.ORDERID AND EO.ORDER_STATUS = '2' INNER JOIN ECPS_ORDER_RETURN EOR ON EOR.ORDER_ID = EO.ORDER_ID INNER JOIN ECPS_ORDER EO1 ON EOR.ORDER_RETURN_ID = EO1.ORDER_ID AND EO1.ORDER_STATUS = '2' where to_char(eo1.finish_time,'yyyymmdd') =to_char(sysdate-1,'yyyyMMdd') group by to_char(eo1.finish_time,'yyyyMMdd')"

record_numcount_tmp1=cat tmp_record_hb.txt

if [ "$record_numcount_tmp1" = "" ]

then

    sql="select to_char(sysdate-1,'yyyyMMdd')||'|'||0||'|'||0 from dual"

fi

echo $sql

sqlplus -S ucr_shop/dspt2015@huibuy<

set trimspool on

set term off

set feedback off

set head off

set linesize 1000

set pagesize 0

spool ${export_file_name_path}

${sql}

union all

SELECT distinct epcId||'|'||mblNo||'|'||packId||'|'||mplOrdNo||'|'||argMonth||'|'||loanAmt||'|'||pmgNo||'|'||bossOrdNo||'|'||modelId||'|'||

modelNm||'|'||oprId||'|'||channelid||'|'||channelname||'|'||epcId||'|'||citId||'|'||revoketime||'|'||chr(13)

        FROM HE_BAO_LOAN HBL

INNER JOIN ECPS_ORDER EO ON EO.ORDER_ID = HBL.ORDERID AND EO.ORDER_STATUS = '2'

INNER JOIN ECPS_ORDER_RETURN EOR ON EOR.ORDER_ID = EO.ORDER_ID

INNER JOIN ECPS_ORDER EO1 ON EOR.ORDER_RETURN_ID = EO1.ORDER_ID AND EO1.ORDER_STATUS = '2'

       where to_char(eo1.finish_time,'yyyymmdd') =to_char(sysdate-1,'yyyyMMdd')

union all

select '@END@' from dual;

exit;

!

echo "start ftp 211.138.236.196"

sftp -oport=8422 jxcpch@211.138.236.196<

cd /recv

lcd /home/dspt/huibuy_order/

put "${export_file_name}"

bye

!