java读取mysql 写入xml文件_Java读取数据库中的xml格式内容，解析后修改属性节点内容并写回数据库...

【script[1] before】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID【script[1] after】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID【script[2] before】

{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD

【script[2] after】

{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD

【script[3] before】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID(SO_NBR

,ITEM_CODE

,SPEC_ID

,BILL_NO

,ACCT_ID

,GROUP_ID

,BILL_MONTH

,PHONE_NO

,USER_ID

,ADJUST_REASON

,SRC_BILL_MONTH

,ADJUST_FEE

,KPI_ADJUST_FEE

,KPI_ADJUST_RATE

,ADJUST_ASSET_FEE

,ADJUST_BILLFEE

,ADJUST_EXTFEE

,OP_ID

,ORG_ID

,SO_DATE

,BEGIN_DATE

,END_DATE

,ADJUST_TYPE

,REASON_ONE

,REASON_TWO

,REASON_THREE

,REASON_FOUR

,REASON_FIVE

,DEAL_REASON

,REMARKS

,EXC_ADJUST_FEE

,ADJUST_TAX_FEE

,ADJUST_TAX_RATE

,KPI_EXC_ADJUST_FEE

,KPI_ADJUST_TAX_FEE

,KPI_ADJUST_TAX_RATE

,EXC_ADJUST_ASSET_FEE

,ADJUST_ASSET_TAX_FEE

,ADJUST_ASSET_TAX_RATE

,EXC_ADJUST_BILLFEE

,ADJUST_BILL_TAX_FEE

,ADJUST_BILL_TAX_RATE

,EXC_ADJUST_EXTFEE

,ADJUST_EXT_TAX_FEE

,ADJUST_EXT_TAX_RATE)

SELECT

A.SO_NBR

,A.ITEM_CODE

,A.SPEC_ID

,A.BILL_NO

,A.ACCT_ID

,A.GROUP_ID

,A.BILL_MONTH

,A.PHONE_NO

,A.USER_ID

,A.ADJUST_REASON

,A.SRC_BILL_MONTH

,A.ADJUST_FEE

,A.KPI_ADJUST_FEE

,A.KPI_ADJUST_RATE

,A.ADJUST_ASSET_FEE

,A.ADJUST_BILLFEE

,A.ADJUST_EXTFEE

,A.OP_ID

,A.ORG_ID

,A.SO_DATE

,A.BEGIN_DATE

,A.END_DATE

,A.ADJUST_TYPE

,A.REASON_ONE

,A.REASON_TWO

,A.REASON_THREE

,A.REASON_FOUR

,A.REASON_FIVE

,A.DEAL_REASON

,A.REMARKS

,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE

,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE

,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE

,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE

,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE

,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE

,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE

,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE

,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE

,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE

,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE

,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE

,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE

,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE

,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE

FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_IDA

LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_IDT5

ON A.ITEM_CODE=T5.ITEM_CODE

WITH UR

yes insert into

【script[3] after】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID(SO_NBR

,ITEM_CODE

,SPEC_ID

,BILL_NO

,ACCT_ID

,GROUP_ID

,BILL_MONTH

,PHONE_NO

,USER_ID

,ADJUST_REASON

,SRC_BILL_MONTH

,ADJUST_FEE

,KPI_ADJUST_FEE

,KPI_ADJUST_RATE

,ADJUST_ASSET_FEE

,ADJUST_BILLFEE

,ADJUST_EXTFEE

,OP_ID

,ORG_ID

,SO_DATE

,BEGIN_DATE

,END_DATE

,ADJUST_TYPE

,REASON_ONE

,REASON_TWO

,REASON_THREE

,REASON_FOUR

,REASON_FIVE

,DEAL_REASON

,REMARKS

,EXC_ADJUST_FEE

,ADJUST_TAX_FEE

,ADJUST_TAX_RATE

,KPI_EXC_ADJUST_FEE

,KPI_ADJUST_TAX_FEE

,KPI_ADJUST_TAX_RATE

,EXC_ADJUST_ASSET_FEE

,ADJUST_ASSET_TAX_FEE

,ADJUST_ASSET_TAX_RATE

,EXC_ADJUST_BILLFEE

,ADJUST_BILL_TAX_FEE

,ADJUST_BILL_TAX_RATE

,EXC_ADJUST_EXTFEE

,ADJUST_EXT_TAX_FEE

,ADJUST_EXT_TAX_RATE)

SELECT

A.SO_NBR

,A.ITEM_CODE

,A.SPEC_ID

,A.BILL_NO

,A.ACCT_ID

,A.GROUP_ID

,A.BILL_MONTH

,A.PHONE_NO

,A.USER_ID

,A.ADJUST_REASON

,A.SRC_BILL_MONTH

,A.ADJUST_FEE

,A.KPI_ADJUST_FEE

,A.KPI_ADJUST_RATE

,A.ADJUST_ASSET_FEE

,A.ADJUST_BILLFEE

,A.ADJUST_EXTFEE

,A.OP_ID

,A.ORG_ID

,A.SO_DATE

,A.BEGIN_DATE

,A.END_DATE

,A.ADJUST_TYPE

,A.REASON_ONE

,A.REASON_TWO

,A.REASON_THREE

,A.REASON_FOUR

,A.REASON_FIVE

,A.DEAL_REASON

,A.REMARKS

,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE

,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE

,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE

,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE

,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE

,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE

,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE

,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE

,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE

,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE

,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE

,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE

,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE

,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE

,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE

FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_IDA

LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_IDT5

ON A.ITEM_CODE=T5.ITEM_CODE ;{sql_compress}

【script[4] before】

TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_IDDATA_DATE:&TASK_ID--SUCCESS--

【script[4] after】

TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_IDDATA_DATE:&TASK_ID--SUCCESS--

【script[5] before】

TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_IDDATA_DATE:&TASK_ID--BEGIN—

【script[5] after】

TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_IDDATA_DATE:&TASK_ID--BEGIN—

【script[6] before】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID【script[6] after】

{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID【script[7] before】

{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD

【script[7] after】

{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD

【script[8] before】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID【script[8] after】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID【script[9] before】

CREATE TABLE {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_IDLIKE

{TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx}

yes create table

【script[9] after】

create table {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <>1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress}

【script[10] before】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID(SO_NBR

,ITEM_CODE

,SPEC_ID

,BILL_NO

,ACCT_ID

,GROUP_ID

,BILL_MONTH

,PHONE_NO

,USER_ID

,ADJUST_REASON

,SRC_BILL_MONTH

,ADJUST_FEE

,KPI_ADJUST_FEE

,KPI_ADJUST_RATE

,ADJUST_ASSET_FEE

,ADJUST_BILLFEE

,ADJUST_EXTFEE

,OP_ID

,ORG_ID

,SO_DATE

,BEGIN_DATE

,END_DATE

,ADJUST_TYPE

,REASON_ONE

,REASON_TWO

,REASON_THREE

,REASON_FOUR

,REASON_FIVE

,DEAL_REASON

,REMARKS)

SELECT

T3.SO_NBR

,T3.ITEM_CODE

,T3.SPEC_ID

,T3.BILL_NO

,T3.ACCT_ID

,T1.GROUP_ID

,T3.BILL_MONTH

,T3.IDENTITY AS PHONE_NO

,T3.RESOURCE_ID AS USER_ID

,T3.ADJUST_REASON

,T3.SRC_BILL_MONTH

,VALUE(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE

,CASE

WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.0

WHEN T3.SRC_BILL_MONTH

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100>0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*0.5

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH<= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.5

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE)-7 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*2

END KPI_ADJUST_FEE

,CASE

WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100

WHEN T3.SRC_BILL_MONTH

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100>0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 50

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH<= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 150

WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE)-7 MONTHS)),6) THEN 200

END KPI_ADJUST_RATE

,VALUE(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE

,VALUE(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE

,VALUE(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE

,T3.OP_ID

,T3.ORG_ID

,T3.SO_DATE

,T3.BEGIN_DATE

,T3.END_DATE

,T3.ADJUST_TYPE

,T3.REASON_ONE

,T3.REASON_TWO

,T3.REASON_THREE

,T3.REASON_FOUR

,T3.REASON_FIVE

,T3.DEAL_REASON

,T3.REMARK AS REMARKS

FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1

INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_IDINNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_IDLEFT JOIN

(select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID

left join (

select item_code,item_name

from {DIM}.DIM_ACC_ITEM_CODE

WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE''

) t4

on t3.ITEM_CODE=t4.ITEM_CODE

WHERE T2.STATE=''U'';

yes insert into

【script[10] after】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID(SO_NBR

,ITEM_CODE

,SPEC_ID

,BILL_NO

,ACCT_ID

,GROUP_ID

,BILL_MONTH

,PHONE_NO

,USER_ID

,ADJUST_REASON

,SRC_BILL_MONTH

,ADJUST_FEE

,KPI_ADJUST_FEE

,KPI_ADJUST_RATE

,ADJUST_ASSET_FEE

,ADJUST_BILLFEE

,ADJUST_EXTFEE

,OP_ID

,ORG_ID

,SO_DATE

,BEGIN_DATE

,END_DATE

,ADJUST_TYPE

,REASON_ONE

,REASON_TWO

,REASON_THREE

,REASON_FOUR

,REASON_FIVE

,DEAL_REASON

,REMARKS)

SELECT

T3.SO_NBR

,T3.ITEM_CODE

,T3.SPEC_ID

,T3.BILL_NO

,T3.ACCT_ID

,T1.GROUP_ID

,T3.BILL_MONTH

,T3.IDENTITY AS PHONE_NO

,T3.RESOURCE_ID AS USER_ID

,T3.ADJUST_REASON

,T3.SRC_BILL_MONTH

,nvl(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE

,CASE

WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.0

WHEN T3.SRC_BILL_MONTH

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100>0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*0.5

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH<= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.5

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr(to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*2

END KPI_ADJUST_FEE

,CASE

WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100

WHEN T3.SRC_BILL_MONTH

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100>0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 50

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH<= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 150

WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr(to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 200

END KPI_ADJUST_RATE

,nvl(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE

,nvl(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE

,nvl(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE

,T3.OP_ID

,T3.ORG_ID

,T3.SO_DATE

,T3.BEGIN_DATE

,T3.END_DATE

,T3.ADJUST_TYPE

,T3.REASON_ONE

,T3.REASON_TWO

,T3.REASON_THREE

,T3.REASON_FOUR

,T3.REASON_FIVE

,T3.DEAL_REASON

,T3.REMARK AS REMARKS

FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1

INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_IDINNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_IDLEFT JOIN

(select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID

left join (

select item_code,item_name

from {DIM}.DIM_ACC_ITEM_CODE

WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE''

) t4

on t3.ITEM_CODE=t4.ITEM_CODE

WHERE T2.STATE=''U'';{sql_compress}

【script[11] before】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID【script[11] after】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID【script[12] before】

CREATE TABLE {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_IDLIKE

{TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx}

yes create table

【script[12] after】

create table {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <>1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress}

【script[13] before】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID(

ITEM_CODE

,ADJUST_TAX_RATE

,KPI_ADJUST_TAX_RATE

,ADJUST_ASSET_TAX_RATE

,ADJUST_BILL_TAX_RATE

,ADJUST_EXT_TAX_RATE

)

SELECT DISTINCT

a.ITEM_CODE

,T5.TAX_RATE AS ADJUST_TAX_RATE

,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE

,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE

,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE

,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE

FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_IDA

left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_IDT5

on a.ITEM_CODE=T5.ITEM_CODE

WITH UR

yes insert into

【script[13] after】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID;INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID(

ITEM_CODE

,ADJUST_TAX_RATE

,KPI_ADJUST_TAX_RATE

,ADJUST_ASSET_TAX_RATE

,ADJUST_BILL_TAX_RATE

,ADJUST_EXT_TAX_RATE

)

SELECT DISTINCT

a.ITEM_CODE

,T5.TAX_RATE AS ADJUST_TAX_RATE

,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE

,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE

,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE

,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE

FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_IDA

left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_IDT5

on a.ITEM_CODE=T5.ITEM_CODE ;{sql_compress}

【script[14] before】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID【script[14] after】

{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID【script[15] before】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID【script[15] after】

{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID