truncate table bi_dw.dw_t_ar_receipt_mon_indicators;
set optimizer = off; 

INSERT INTO bi_dw.dw_t_ar_receipt_mon_indicators(
receipt_header_id,
ou_code,
org_id,
org_name,
customer_code,
customer_name,
receipt_number,
receipt_amount,
receipt_date,
apply_date,
split_date,
date_turn,
payment_status,
balance_amount,
balance
)
SELECT receipt_header_id,ou_code,org_id,org_name,customer_code,customer_name,receipt_number,receipt_amount,receipt_date,apply_date,split_date,date_turn,
CASE WHEN apply_date IS NULL AND COALESCE(balance_amount,0)=0  THEN '正常'
WHEN (date_turn>=0 AND date_turn<=5) AND (COALESCE(balance_amount,0)=0 OR COALESCE(balance)=0) THEN '正常'
WHEN (date_turn>5 AND date_turn<=10) AND (COALESCE(balance_amount,0)=0 OR COALESCE(balance)=0) THEN '较正常'
WHEN (date_turn>10 AND date_turn<=15) OR (COALESCE(balance_amount,0)!=0 AND COALESCE(balance)!=0)THEN '异常'
WHEN date_turn>15 THEN '超异常' END payment_status,balance_amount,balance
FROM (
SELECT receipt_header_id,ou_code,org_id,org_name,customer_code,customer_name,receipt_number,receipt_amount,receipt_date,apply_date,split_date,
round(cast(CEIL( EXTRACT( epoch FROM(COALESCE(apply_date_work,now()) - receipt_date_work )))/60/60/24 as numeric),2)-
(SELECT count(1)
FROM bi_dim.dim_day_workday_d wd WHERE wd.days_flag ='节假日' 
AND wd.day_code >=substr(receipt_date_work,1,10) AND wd.day_code<substr(COALESCE(apply_date_work,now()),1,10)) date_turn,
balance_amount,balance
FROM
(
SELECT 
header.receipt_header_id,
header.ou_code,
mof.org_id,
mof.org_name,
header.customer_code, 
( CASE WHEN header.customer_code = - 1 THEN '未识别客户' ELSE mci.party_name END ) AS customer_name,
header.receipt_number,
header.receipt_amount,
header.receipt_date,
ara.apply_date,
line.split_date,
CASE WHEN d1.days_flag='节假日' THEN  d1.work_day_date ELSE header.receipt_date END receipt_date_work,
CASE WHEN d2.days_flag='节假日' THEN  d2.work_day_date ELSE ara.apply_date END apply_date_work,
COALESCE(t.balance_amount,0) balance_amount, 
COALESCE(line.balance,0) balance
FROM bi_src_t.ar_receipt_header header
LEFT JOIN (SELECT receipt_header_id,MAX(split_date) split_date,MAX(balance) balance FROM bi_src_t.ar_receipt_line line GROUP BY receipt_header_id) line 
ON line.receipt_header_id = header.receipt_header_id
LEFT JOIN (
SELECT receipt_header_id,max(apply_date) apply_date FROM bi_src_t.ar_receipt_apply GROUP BY receipt_header_id
)ara ON header.receipt_header_id = ara.receipt_header_id
LEFT JOIN bi_dim.dim_day_workday_d d1 ON CAST(TO_CHAR(header.receipt_date , 'YYYYMMDD') AS INTEGER) = d1.day_wid
LEFT JOIN bi_dim.dim_day_workday_d d2 ON CAST(TO_CHAR(ara.apply_date , 'YYYYMMDD') AS INTEGER) = d2.day_wid
LEFT JOIN bi_src_t.mdm_customer_info mci ON mci.account_number = header.customer_code
LEFT JOIN (SELECT DISTINCT ( org_code ), org_name, org_id, id FROM bi_src_t.mdm_legal_entity_info WHERE enable_flag = 'Y') mof ON mof.org_code = header.ou_code
LEFT JOIN (SELECT DISTINCT account_number,org_id,balance_amount from  bi_dw.dw_customer_balance_f 
where receipt_type_name='预收账款' and detail_type_name='期末余额' 
and month_wid= '${W_MONTH_WID}' and sales_channel_name='直销' )t on t.account_number=header.customer_code and mof.org_id=t.org_id
WHERE substr(header.receipt_number,1,1)!='D' 
AND EXISTS(
SELECT 1 FROM bi_src_t.mdm_customer_info d WHERE d.sales_channel_code='DIRECT' and header.customer_code = d.account_number
)
)d
)t;

reset optimizer;

