CREATE OR REPLACE PROCEDURE sp_cust_main_data_yx(InStrDate IN VARCHAR2,
OS_ERR_MSG OUT VARCHAR2) AS
BEGIN
--1、清空臨時表數據
execute immediate 'truncate table DATA_SALE_DAY_ITEM';
execute immediate 'truncate table DATA_SALE_DAY_TP';
execute immediate 'truncate table DATA_SALE_DAY';
execute immediate 'truncate table DATA_SALE_MONTH_TP';
execute immediate 'truncate table DATA_SALE_MONTH';
--2、跑出結果到臨時表
--交易明細數據
INSERT INTO DATA_SALE_DAY_ITEM
SELECT TO_DATE(A.CRMPOSTDAT, 'yyyymmdd') p_date,
TO_DATE(DECODE(A."/BIC/ZTCRMC01",
'00000000',
NULL,
' ',
NULL,
A."/BIC/ZTCRMC01"),
'yyyymmdd') order_date,
A."CRM_ITMTYP" order_type,
A."/BIC/ZKUNNR_L1" order_channel_l1,
D.TXTSH channel_l1_name, --
A."/BIC/ZKUNNR_L2" order_channel_l2,
E.TXTSH channel_l2_name, --
A.CRM_OBJ_ID crm_order_id,
A."/BIC/ZTCRMC04" order_no,
A.CRM_ENDCST cust_no,
A."/BIC/ZTCMC016" cust_grade,
LTRIM(A."/BIC/ZMATERIAL", '0') ITEM_CODE,
C.TXTMD item_name,
LTRIM(B."/BIC/ZMATDL", '0') CATE_B, --大類
LTRIM(B."/BIC/ZMATZL", '0') CATE_M, --中類
LTRIM(B."/BIC/ZMATXL", '0') CATE_S, --小類
LTRIM(B.MATL_GROUP, '0') CATE_D, --細類
A."/BIC/ZEAMC001" unit_no,
TO_DATE(decode(A."/BIC/ZEAMC011" || A."/BIC/ZEAMC013",
'00000000000000',
null,
' 00000',
null,
A."/BIC/ZEAMC011" || A."/BIC/ZEAMC013"),
'yyyymmddhh24miss') unit_begin_time,
TO_DATE(decode(A."/BIC/ZEAMC012" || A."/BIC/ZEAMC014",
'00000000000000',
null,
' 00000',
null,
A."/BIC/ZEAMC012" || A."/BIC/ZEAMC014"),
'yyyymmddhh24miss') unit_end_time,
LTRIM(A."/BIC/ZTCMC020", '0') addr_no,
H."/BIC/ZTCMC017" province, --
H1.TXTSH province_name, --
DECODE(A."/BIC/ZCRMD015", 'X', 1, 0) is_related_sale,
DECODE(A."/BIC/ZCRMD016", 'X', 1, 0) is_ivr,
A."/BIC/ZCRMD018" order_source,
A."/BIC/ZEAMC027" md_code,
SUBSTR(A."/BIC/ZPST_TIM", 1, 2) sale_period,
--總訂購
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 0) *
A."/BIC/ZAMK0010" tot_order_qty,
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZAMK0011" tot_order_amt,
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."CRM_SRVKB" tot_order_cost,
--淨訂購
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 'ZCR1', -1, 0) *
A."/BIC/ZAMK0010" net_order_qty,
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 'ZCR1', -1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZAMK0011" net_order_amt,
DECODE(A.CRM_PRCTYP, 'ZA01', 1, 'ZA02', 1, 'ZCR1', -1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."CRM_SRVKB" net_order_cost,
--有效訂購
DECODE(A.CRM_PRCTYP,
'ZA01',
1,
'ZA02',
1,
'ZCR1',
-1,
'ZB01',
-1,
0) * A."/BIC/ZAMK0010" effect_order_qty,
DECODE(A.CRM_PRCTYP,
'ZA01',
1,
'ZA02',
1,
'ZCR1',
-1,
'ZB01',
-1,
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZAMK0011" effect_order_amt,
DECODE(A.CRM_PRCTYP,
'ZA01',
1,
'ZA02',
1,
'ZCR1',
-1,
'ZB01',
-1,
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZCRMK009" effect_price_amt,
DECODE(A.CRM_PRCTYP,
'ZA01',
1,
'ZA02',
1,
'ZCR1',
-1,
'ZB01',
-1,
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."CRM_SRVKB" effect_order_cost,
DECODE(A.CRM_PRCTYP,
'ZA01',
1,
'ZA02',
1,
'ZCR1',
-1,
'ZB01',
-1,
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZCRMK006" effect_discount_amt,
--取消
DECODE(A.CRM_PRCTYP, 'ZCR1', 1, 0) * A."/BIC/ZAMK0010" order_cancel_qty,
DECODE(A.CRM_PRCTYP, 'ZCR1', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZAMK0011" order_cancel_amt,
--拒收
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 1, 0),
0) * A."/BIC/ZAMK0010" order_reject_qty,
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 1, 0),
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZAMK0011" order_reject_amt,
--退貨
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 0, 1),
0) * A."/BIC/ZAMK0010" order_refund_qty,
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 0, 1),
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZAMK0011" order_refund_amt,
--拒收取消
0 order_reject_cancel_qty,
0 order_reject_cancel_amt,
--退貨取消
0 order_refund_cancel_qty,
0 order_refund_cancel_amt
FROM ITF_SAP.SAP_BIC_AZTCRD00100 A,
ITF_SAP.SAP_BIC_PZMATERIAL B,
ITF_SAP.SAP_BIC_TZMATERIAL C,
ITF_SAP."/BIC/OHZO_MSCODE" D,
ITF_SAP."/BIC/OHZO_MSGB" E,
ITF_SAP."/BIC/OHZO_PZHEN" H,
ITF_SAP."/BIC/OHZO_TSHENG" H1
WHERE A.CRMPOSTDAT BETWEEN '20060301' AND InStrDate
AND NVL(A."/BIC/ZCRMD001", ' ') <> 'TA10' --剔除預訂購
AND NVL(A."/BIC/ZCRMD048", ' ') <> 'TANN' --剔除贈品
AND A."/BIC/ZCRMD199" IS NULL --剔除預訂購
AND A."/BIC/ZMATERIAL" = B."/BIC/ZMATERIAL"(+)
AND A."/BIC/ZMATERIAL" = C."/BIC/ZMATERIAL"(+)
AND A."/BIC/ZKUNNR_L1" = D."/BIC/ZKUNNR_L1"(+)
AND D.LANGU(+) = '1'
AND A."/BIC/ZKUNNR_L2" = E."/BIC/ZKUNNR_L2"(+)
AND E.LANGU(+) = '1'
AND A."/BIC/ZTCMC020" = H."/BIC/ZTCMC020"(+)
AND H."/BIC/ZTCMC017" = H1."/BIC/ZTCMC017"(+)
UNION ALL
SELECT to_date(A."/BIC/ZTCMC021", 'yyyymmdd') 過賬日期,
TO_DATE(DECODE(A."/BIC/ZTCRMC01",
'00000000',
NULL,
' ',
NULL,
A."/BIC/ZTCRMC01"),
'yyyymmdd') CRM訂購日期_權責制,
A."CRM_ITMTYP" 項目交易類型,
A."/BIC/ZKUNNR_L1" order_channel_l1,
D.TXTSH channel_l1_name, --
A."/BIC/ZKUNNR_L2" order_channel_l2,
E.TXTSH channel_l2_name, --
A.CRM_OBJ_ID 交易編號,
A."/BIC/ZTCRMC04" "CRM訂單編號_權責制",
A.CRM_ENDCST 會員,
A."/BIC/ZTCMC016" 會員等級,
LTRIM(A."/BIC/ZMATERIAL", '0') ITEM_CODE,
C.TXTMD item_name,
LTRIM(B."/BIC/ZMATDL", '0') CATE_B, --大類
LTRIM(B."/BIC/ZMATZL", '0') CATE_M, --中類
LTRIM(B."/BIC/ZMATXL", '0') CATE_S, --小類
LTRIM(B.MATL_GROUP, '0') CATE_D, --細類
A."/BIC/ZEAMC001" unit_no,
TO_DATE(decode(A."/BIC/ZEAMC011" || A."/BIC/ZEAMC013",
'00000000000000',
null,
' 00000',
null,
A."/BIC/ZEAMC011" || A."/BIC/ZEAMC013"),
'yyyymmddhh24miss') unit_begin_time,
TO_DATE(decode(A."/BIC/ZEAMC012" || A."/BIC/ZEAMC014",
'00000000000000',
null,
' 00000',
null,
A."/BIC/ZEAMC012" || A."/BIC/ZEAMC014"),
'yyyymmddhh24miss') unit_end_time,
LTRIM(A."/BIC/ZTCMC020", '0') addr_no,
H."/BIC/ZTCMC017" province, --
H1.TXTSH province_name, --
DECODE(A."/BIC/ZCRMD015", 'X', 1, 0) is_related_sale,
DECODE(A."/BIC/ZCRMD016", 'X', 1, 0) is_ivr,
A."/BIC/ZCRMD018" order_source,
A."/BIC/ZEAMC027" md_code,
SUBSTR(A."/BIC/ZPST_TIM", 1, 2) sale_period,
--總訂購
0 總訂購件數,
0 總訂購金額,
0 總訂購成本,
--淨訂購
0 淨訂購件數,
0 淨訂購金額,
0 淨訂購成本,
--有效訂購
DECODE(A.CRM_PRCTYP, 'ZB01', 1, 0) * A."/BIC/ZAMK0010" 有效訂購件數,
DECODE(A.CRM_PRCTYP, 'ZB01', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZAMK0011" 有效訂購金額,
DECODE(A.CRM_PRCTYP, 'ZB01', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZCRMK009" 有效售價金額,
DECODE(A.CRM_PRCTYP, 'ZB01', 1, 0) *
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."CRM_SRVKB" 有效訂購成本,
DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) * A."/BIC/ZCRMK006" "有效折扣金額",
--取消
0 取消訂購件數,
0 取消訂購金額,
--拒收
0 拒收訂購件數,
0 拒收訂購金額,
--退貨
0 退貨訂購件數,
0 退貨訂購金額,
--拒收取消
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 1, 0),
0) * A."/BIC/ZAMK0010" 拒收取消訂購件數,
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 1, 0),
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZAMK0011" 拒收取消訂購金額,
--退貨取消
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 0, 1),
0) * A."/BIC/ZAMK0010" 退貨取消訂購件數,
DECODE(A.CRM_PRCTYP,
'ZB01',
DECODE(A."/BIC/ZTCMC008", '20', 0, 1),
0) * DECODE(A.CRM_ITMTYP, 'TANN', 0, 1) *
A."/BIC/ZAMK0011" 退貨取消訂購金額
FROM ITF_SAP.SAP_BIC_AZTCRD00100 A,
ITF_SAP.SAP_BIC_PZMATERIAL B,
ITF_SAP.SAP_BIC_TZMATERIAL C,
ITF_SAP."/BIC/OHZO_MSCODE" D,
ITF_SAP."/BIC/OHZO_MSGB" E,
ITF_SAP."/BIC/OHZO_PZHEN" H,
ITF_SAP."/BIC/OHZO_TSHENG" H1
WHERE A."/BIC/ZTCMC021" BETWEEN '20060301' AND InStrDate
AND NVL(A."/BIC/ZCRMD001", ' ') <> 'TA10' --剔除預訂購
AND NVL(A."/BIC/ZCRMD048", ' ') <> 'TANN' --剔除贈品
AND A."/BIC/ZCRMD199" IS NULL --剔除預訂購
AND A."/BIC/ZMATERIAL" = B."/BIC/ZMATERIAL"(+)
AND A."/BIC/ZMATERIAL" = C."/BIC/ZMATERIAL"(+)
AND A."/BIC/ZKUNNR_L1" = D."/BIC/ZKUNNR_L1"(+)
AND D.LANGU(+) = '1'
AND A."/BIC/ZKUNNR_L2" = E."/BIC/ZKUNNR_L2"(+)
AND E.LANGU(+) = '1'
AND A."/BIC/ZTCMC020" = H."/BIC/ZTCMC020"(+)
AND H."/BIC/ZTCMC017" = H1."/BIC/ZTCMC017"(+);
--按天統計臨時表
INSERT INTO DATA_SALE_DAY_TP
SELECT TO_CHAR(T.P_DATE, 'YYYYMMDD') P_DATE,
T.ORDER_CHANNEL_L1,
T.CHANNEL_L1_NAME,
T.ORDER_CHANNEL_L2,
T.CHANNEL_L2_NAME,
T.CUST_GRADE,
LTRIM(T.CATE_B, '0') CATE_B,
T.PROVINCE,
T.PROVINCE_NAME,
SUM(T.TOT_ORDER_QTY) TOT_ORDER_QTY,
SUM(T.TOT_ORDER_AMT) TOT_ORDER_AMT,
SUM(T.TOT_ORDER_COST) TOT_ORDER_COST,
SUM(T.NET_ORDER_QTY) NET_ORDER_QTY,
SUM(T.NET_ORDER_AMT) NET_ORDER_AMT,
SUM(T.NET_ORDER_COST) NET_ORDER_COST,
SUM(T.EFFECT_ORDER_QTY) EFFECT_ORDER_QTY,
SUM(T.EFFECT_ORDER_AMT) EFFECT_ORDER_AMT,
SUM(T.EFFECT_PRICE_AMT) EFFECT_PRICE_AMT,
SUM(T.EFFECT_ORDER_COST) EFFECT_ORDER_COST,
SUM(T.EFFECT_DISCOUNT_AMT) EFFECT_DISCOUNT_AMT,
SUM(T.ORDER_CANCEL_QTY) ORDER_CANCEL_QTY,
SUM(T.ORDER_CANCEL_AMT) ORDER_CANCEL_AMT,
SUM(T.ORDER_REJECT_QTY) ORDER_REJECT_QTY,
SUM(T.ORDER_REJECT_AMT) ORDER_REJECT_AMT,
SUM(T.ORDER_REFUND_QTY) ORDER_REFUND_QTY,
SUM(T.ORDER_REFUND_AMT) ORDER_REFUND_AMT,
SUM(T.ORDER_REJECT_CANCEL_QTY) ORDER_REJECT_CANCEL_QTY,
SUM(T.ORDER_REJECT_CANCEL_AMT) ORDER_REJECT_CANCEL_AMT,
SUM(T.ORDER_REFUND_CANCEL_QTY) ORDER_REFUND_CANCEL_QTY,
SUM(T.ORDER_REFUND_CANCEL_AMT) ORDER_REFUND_CANCEL_AMT
FROM DATA_SALE_DAY_ITEM T
GROUP BY TO_CHAR(T.P_DATE, 'YYYYMMDD'),
T.ORDER_CHANNEL_L1,
T.CHANNEL_L1_NAME,
T.ORDER_CHANNEL_L2,
T.CHANNEL_L2_NAME,
T.CUST_GRADE,
LTRIM(T.CATE_B, '0'),
T.PROVINCE,
T.PROVINCE_NAME;
--按天統計數據表
INSERT INTO DATA_SALE_DAY
SELECT A.*,
B.TOT_ORDER_QTY TOT_ORDER_QTY_L1,
B.TOT_ORDER_AMT TOT_ORDER_AMT_L1,
B.TOT_ORDER_COST TOT_ORDER_COST_L1,
B.NET_ORDER_QTY NET_ORDER_QTY_L1,
B.NET_ORDER_AMT NET_ORDER_AMT_L1,
B.NET_ORDER_COST NET_ORDER_COST_L1,
B.EFFECT_ORDER_QTY EFFECT_ORDER_QTY_L1,
B.EFFECT_ORDER_AMT EFFECT_ORDER_AMT_L1,
B.EFFECT_PRICE_AMT EFFECT_PRICE_AMT_L1,
B.EFFECT_ORDER_COST EFFECT_ORDER_COST_L1,
B.EFFECT_DISCOUNT_AMT EFFECT_DISCOUNT_AMT_L1,
B.ORDER_CANCEL_QTY ORDER_CANCEL_QTY_L1,
B.ORDER_CANCEL_AMT ORDER_CANCEL_AMT_L1,
B.ORDER_REJECT_QTY ORDER_REJECT_QTY_L1,
B.ORDER_REJECT_AMT ORDER_REJECT_AMT_L1,
B.ORDER_REFUND_QTY ORDER_REFUND_QTY_L1,
B.ORDER_REFUND_AMT ORDER_REFUND_AMT_L1,
B.ORDER_REJECT_CANCEL_QTY ORDER_REJECT_CANCEL_QTY_L1,
B.ORDER_REJECT_CANCEL_AMT ORDER_REJECT_CANCEL_AMT_L1,
B.ORDER_REFUND_CANCEL_QTY ORDER_REFUND_CANCEL_QTY_L1,
B.ORDER_REFUND_CANCEL_AMT ORDER_REFUND_CANCEL_AMT_L1
FROM DATA_SALE_DAY_TP A, DATA_SALE_DAY_TP B
WHERE ((SUBSTR(A.P_DATE, 1, 4) - 1) || SUBSTR(A.P_DATE, 5, 4)) =
B.P_DATE(+)
AND A.ORDER_CHANNEL_L1 = B.ORDER_CHANNEL_L1(+)
AND A.CHANNEL_L1_NAME = B.CHANNEL_L1_NAME(+)
AND A.ORDER_CHANNEL_L2 = B.ORDER_CHANNEL_L2(+)
AND A.CHANNEL_L2_NAME = B.CHANNEL_L2_NAME(+)
AND A.CUST_GRADE = B.CUST_GRADE(+)
AND A.CATE_B = B.CATE_B(+)
AND A.PROVINCE = B.PROVINCE(+)
AND A.PROVINCE_NAME = B.PROVINCE_NAME(+);
--按月統計臨時表
INSERT INTO DATA_SALE_MONTH_TP
SELECT SUBSTR(A.P_DATE, 1, 6) P_MONTH,
A.ORDER_CHANNEL_L1,
A.CHANNEL_L1_NAME,
A.ORDER_CHANNEL_L2,
A.CHANNEL_L2_NAME,
A.CUST_GRADE,
A.CATE_B,
A.PROVINCE,
A.PROVINCE_NAME,
SUM(TOT_ORDER_QTY) TOT_ORDER_QTY,
SUM(TOT_ORDER_AMT) TOT_ORDER_AMT,
SUM(TOT_ORDER_COST) TOT_ORDER_COST,
SUM(NET_ORDER_QTY) NET_ORDER_QTY,
SUM(NET_ORDER_AMT) NET_ORDER_AMT,
SUM(NET_ORDER_COST) NET_ORDER_COST,
SUM(EFFECT_ORDER_QTY) EFFECT_ORDER_QTY,
SUM(EFFECT_ORDER_AMT) EFFECT_ORDER_AMT,
SUM(EFFECT_PRICE_AMT) EFFECT_PRICE_AMT,
SUM(EFFECT_ORDER_COST) EFFECT_ORDER_COST,
SUM(EFFECT_DISCOUNT_AMT) EFFECT_DISCOUNT_AMT,
SUM(ORDER_CANCEL_QTY) ORDER_CANCEL_QTY,
SUM(ORDER_CANCEL_AMT) ORDER_CANCEL_AMT,
SUM(ORDER_REJECT_QTY) ORDER_REJECT_QTY,
SUM(ORDER_REJECT_AMT) ORDER_REJECT_AMT,
SUM(ORDER_REFUND_QTY) ORDER_REFUND_QTY,
SUM(ORDER_REFUND_AMT) ORDER_REFUND_AMT,
SUM(ORDER_REJECT_CANCEL_QTY) ORDER_REJECT_CANCEL_QTY,
SUM(ORDER_REJECT_CANCEL_AMT) ORDER_REJECT_CANCEL_AMT,
SUM(ORDER_REFUND_CANCEL_QTY) ORDER_REFUND_CANCEL_QTY,
SUM(ORDER_REFUND_CANCEL_AMT) ORDER_REFUND_CANCEL_AMT,
SUM(TOT_ORDER_QTY) / B.DATE_CNT TOT_ORDER_QTY_AVG,
SUM(TOT_ORDER_AMT) / B.DATE_CNT TOT_ORDER_AMT_AVG,
SUM(TOT_ORDER_COST) / B.DATE_CNT TOT_ORDER_COST_AVG,
SUM(NET_ORDER_QTY) / B.DATE_CNT NET_ORDER_QTY_AVG,
SUM(NET_ORDER_AMT) / B.DATE_CNT NET_ORDER_AMT_AVG,
SUM(NET_ORDER_COST) / B.DATE_CNT NET_ORDER_COST_AVG,
SUM(EFFECT_ORDER_QTY) / B.DATE_CNT EFFECT_ORDER_QTY_AVG,
SUM(EFFECT_ORDER_AMT) / B.DATE_CNT EFFECT_ORDER_AMT_AVG,
SUM(EFFECT_PRICE_AMT) / B.DATE_CNT EFFECT_PRICE_AMT_AVG,
SUM(EFFECT_ORDER_COST) / B.DATE_CNT EFFECT_ORDER_COST_AVG,
SUM(EFFECT_DISCOUNT_AMT) / B.DATE_CNT EFFECT_DISCOUNT_AMT_AVG,
SUM(ORDER_CANCEL_QTY) / B.DATE_CNT ORDER_CANCEL_QTY_AVG,
SUM(ORDER_CANCEL_AMT) / B.DATE_CNT ORDER_CANCEL_AMT_AVG,
SUM(ORDER_REJECT_QTY) / B.DATE_CNT ORDER_REJECT_QTY_AVG,
SUM(ORDER_REJECT_AMT) / B.DATE_CNT ORDER_REJECT_AMT_AVG,
SUM(ORDER_REFUND_QTY) / B.DATE_CNT ORDER_REFUND_QTY_AVG,
SUM(ORDER_REFUND_AMT) / B.DATE_CNT ORDER_REFUND_AMT_AVG,
SUM(ORDER_REJECT_CANCEL_QTY) / B.DATE_CNT ORDER_REJECT_CANCEL_QTY_AVG,
SUM(ORDER_REJECT_CANCEL_AMT) / B.DATE_CNT ORDER_REJECT_CANCEL_AMT_AVG,
SUM(ORDER_REFUND_CANCEL_QTY) / B.DATE_CNT ORDER_REFUND_CANCEL_QTY_AVG,
SUM(ORDER_REFUND_CANCEL_AMT) / B.DATE_CNT ORDER_REFUND_CANCEL_AMT_AVG
FROM DATA_SALE_DAY_TP A,
(SELECT SUBSTR(P_DATE, 1, 6) P_MONTH,
COUNT(DISTINCT SUBSTR(P_DATE, 7, 2)) DATE_CNT
FROM DATA_SALE_DAY_TP
GROUP BY SUBSTR(P_DATE, 1, 6)) B
WHERE SUBSTR(A.P_DATE, 1, 6) = B.P_MONTH(+)
GROUP BY SUBSTR(A.P_DATE, 1, 6),
A.ORDER_CHANNEL_L1,
A.CHANNEL_L1_NAME,
A.ORDER_CHANNEL_L2,
A.CHANNEL_L2_NAME,
A.CUST_GRADE,
A.CATE_B,
A.PROVINCE,
A.PROVINCE_NAME,
B.DATE_CNT;
--按月統計數據表
INSERT INTO DATA_SALE_MONTH
SELECT A.*,
B.TOT_ORDER_QTY TOT_ORDER_QTY_L1,
B.TOT_ORDER_AMT TOT_ORDER_AMT_L1,
B.TOT_ORDER_COST TOT_ORDER_COST_L1,
B.NET_ORDER_QTY NET_ORDER_QTY_L1,
B.NET_ORDER_AMT NET_ORDER_AMT_L1,
B.NET_ORDER_COST NET_ORDER_COST_L1,
B.EFFECT_ORDER_QTY EFFECT_ORDER_QTY_L1,
B.EFFECT_ORDER_AMT EFFECT_ORDER_AMT_L1,
B.EFFECT_PRICE_AMT EFFECT_PRICE_AMT_L1,
B.EFFECT_ORDER_COST EFFECT_ORDER_COST_L1,
B.EFFECT_DISCOUNT_AMT EFFECT_DISCOUNT_AMT_L1,
B.ORDER_CANCEL_QTY ORDER_CANCEL_QTY_L1,
B.ORDER_CANCEL_AMT ORDER_CANCEL_AMT_L1,
B.ORDER_REJECT_QTY ORDER_REJECT_QTY_L1,
B.ORDER_REJECT_AMT ORDER_REJECT_AMT_L1,
B.ORDER_REFUND_QTY ORDER_REFUND_QTY_L1,
B.ORDER_REFUND_AMT ORDER_REFUND_AMT_L1,
B.ORDER_REJECT_CANCEL_QTY ORDER_REJECT_CANCEL_QTY_L1,
B.ORDER_REJECT_CANCEL_AMT ORDER_REJECT_CANCEL_AMT_L1,
B.ORDER_REFUND_CANCEL_QTY ORDER_REFUND_CANCEL_QTY_L1,
B.ORDER_REFUND_CANCEL_AMT ORDER_REFUND_CANCEL_AMT_L1,
B.TOT_ORDER_QTY_AVG TOT_ORDER_QTY_AVG_L1,
B.TOT_ORDER_AMT_AVG TOT_ORDER_AMT_AVG_L1,
B.TOT_ORDER_COST_AVG TOT_ORDER_COST_AVG_L1,
B.NET_ORDER_QTY_AVG NET_ORDER_QTY_AVG_L1,
B.NET_ORDER_AMT_AVG NET_ORDER_AMT_AVG_L1,
B.NET_ORDER_COST_AVG NET_ORDER_COST_AVG_L1,
B.EFFECT_ORDER_QTY_AVG EFFECT_ORDER_QTY_AVG_L1,
B.EFFECT_ORDER_AMT_AVG EFFECT_ORDER_AMT_AVG_L1,
B.EFFECT_PRICE_AMT_AVG EFFECT_PRICE_AMT_AVG_L1,
B.EFFECT_ORDER_COST_AVG EFFECT_ORDER_COST_AVG_L1,
B.EFFECT_DISCOUNT_AMT_AVG EFFECT_DISCOUNT_AMT_AVG_L1,
B.ORDER_CANCEL_QTY_AVG ORDER_CANCEL_QTY_AVG_L1,
B.ORDER_CANCEL_AMT_AVG ORDER_CANCEL_AMT_AVG_L1,
B.ORDER_REJECT_QTY_AVG ORDER_REJECT_QTY_AVG_L1,
B.ORDER_REJECT_AMT_AVG ORDER_REJECT_AMT_AVG_L1,
B.ORDER_REFUND_QTY_AVG ORDER_REFUND_QTY_AVG_L1,
B.ORDER_REFUND_AMT_AVG ORDER_REFUND_AMT_AVG_L1,
B.ORDER_REJECT_CANCEL_QTY_AVG ORDER_REJECT_CANCEL_QTY_AVG_L1,
B.ORDER_REJECT_CANCEL_AMT_AVG ORDER_REJECT_CANCEL_AMT_AVG_L1,
B.ORDER_REFUND_CANCEL_QTY_AVG ORDER_REFUND_CANCEL_QTY_AVG_L1,
B.ORDER_REFUND_CANCEL_AMT_AVG ORDER_REFUND_CANCEL_AMT_AVG_L1,
C.TOT_ORDER_QTY TOT_ORDER_QTY_L2,
C.TOT_ORDER_AMT TOT_ORDER_AMT_L2,
C.TOT_ORDER_COST TOT_ORDER_COST_L2,
C.NET_ORDER_QTY NET_ORDER_QTY_L2,
C.NET_ORDER_AMT NET_ORDER_AMT_L2,
C.NET_ORDER_COST NET_ORDER_COST_L2,
C.EFFECT_ORDER_QTY EFFECT_ORDER_QTY_L2,
C.EFFECT_ORDER_AMT EFFECT_ORDER_AMT_L2,
C.EFFECT_PRICE_AMT EFFECT_PRICE_AMT_L2,
C.EFFECT_ORDER_COST EFFECT_ORDER_COST_L2,
C.EFFECT_DISCOUNT_AMT EFFECT_DISCOUNT_AMT_L2,
C.ORDER_CANCEL_QTY ORDER_CANCEL_QTY_L2,
C.ORDER_CANCEL_AMT ORDER_CANCEL_AMT_L2,
C.ORDER_REJECT_QTY ORDER_REJECT_QTY_L2,
C.ORDER_REJECT_AMT ORDER_REJECT_AMT_L2,
C.ORDER_REFUND_QTY ORDER_REFUND_QTY_L2,
C.ORDER_REFUND_AMT ORDER_REFUND_AMT_L2,
C.ORDER_REJECT_CANCEL_QTY ORDER_REJECT_CANCEL_QTY_L2,
C.ORDER_REJECT_CANCEL_AMT ORDER_REJECT_CANCEL_AMT_L2,
C.ORDER_REFUND_CANCEL_QTY ORDER_REFUND_CANCEL_QTY_L2,
C.ORDER_REFUND_CANCEL_AMT ORDER_REFUND_CANCEL_AMT_L2,
C.TOT_ORDER_QTY_AVG TOT_ORDER_QTY_AVG_L2,
C.TOT_ORDER_AMT_AVG TOT_ORDER_AMT_AVG_L2,
C.TOT_ORDER_COST_AVG TOT_ORDER_COST_AVG_L2,
C.NET_ORDER_QTY_AVG NET_ORDER_QTY_AVG_L2,
C.NET_ORDER_AMT_AVG NET_ORDER_AMT_AVG_L2,
C.NET_ORDER_COST_AVG NET_ORDER_COST_AVG_L2,
C.EFFECT_ORDER_QTY_AVG EFFECT_ORDER_QTY_AVG_L2,
C.EFFECT_ORDER_AMT_AVG EFFECT_ORDER_AMT_AVG_L2,
C.EFFECT_PRICE_AMT_AVG EFFECT_PRICE_AMT_AVG_L2,
C.EFFECT_ORDER_COST_AVG EFFECT_ORDER_COST_AVG_L2,
C.EFFECT_DISCOUNT_AMT_AVG EFFECT_DISCOUNT_AMT_AVG_L2,
C.ORDER_CANCEL_QTY_AVG ORDER_CANCEL_QTY_AVG_L2,
C.ORDER_CANCEL_AMT_AVG ORDER_CANCEL_AMT_AVG_L2,
C.ORDER_REJECT_QTY_AVG ORDER_REJECT_QTY_AVG_L2,
C.ORDER_REJECT_AMT_AVG ORDER_REJECT_AMT_AVG_L2,
C.ORDER_REFUND_QTY_AVG ORDER_REFUND_QTY_AVG_L2,
C.ORDER_REFUND_AMT_AVG ORDER_REFUND_AMT_AVG_L2,
C.ORDER_REJECT_CANCEL_QTY_AVG ORDER_REJECT_CANCEL_QTY_AVG_L2,
C.ORDER_REJECT_CANCEL_AMT_AVG ORDER_REJECT_CANCEL_AMT_AVG_L2,
C.ORDER_REFUND_CANCEL_QTY_AVG ORDER_REFUND_CANCEL_QTY_AVG_L2,
C.ORDER_REFUND_CANCEL_AMT_AVG ORDER_REFUND_CANCEL_AMT_AVG_L2
FROM DATA_SALE_MONTH_TP A, DATA_SALE_MONTH_TP B, DATA_SALE_MONTH_TP C
WHERE ((SUBSTR(A.P_MONTH, 1, 4) - 1) || SUBSTR(A.P_MONTH, 5, 2)) =
B.P_MONTH(+)
AND A.ORDER_CHANNEL_L1 = B.ORDER_CHANNEL_L1(+)
AND A.CHANNEL_L1_NAME = B.CHANNEL_L1_NAME(+)
AND A.ORDER_CHANNEL_L2 = B.ORDER_CHANNEL_L2(+)
AND A.CHANNEL_L2_NAME = B.CHANNEL_L2_NAME(+)
AND A.CUST_GRADE = B.CUST_GRADE(+)
AND A.CATE_B = B.CATE_B(+)
AND A.PROVINCE = B.PROVINCE(+)
AND A.PROVINCE_NAME = B.PROVINCE_NAME(+)
AND TO_CHAR(ADD_MONTHS(TO_DATE(A.P_MONTH, 'YYYYMM'), -1), 'YYYYMM') =
C.P_MONTH(+)
AND A.ORDER_CHANNEL_L1 = C.ORDER_CHANNEL_L1(+)
AND A.CHANNEL_L1_NAME = C.CHANNEL_L1_NAME(+)
AND A.ORDER_CHANNEL_L2 = C.ORDER_CHANNEL_L2(+)
AND A.CHANNEL_L2_NAME = C.CHANNEL_L2_NAME(+)
AND A.CUST_GRADE = C.CUST_GRADE(+)
AND A.CATE_B = C.CATE_B(+)
AND A.PROVINCE = C.PROVINCE(+)
AND A.PROVINCE_NAME = C.PROVINCE_NAME(+);
COMMIT;
exception
when others then
os_err_msg := to_char('調用失敗,請檢查! ' || sqlerrm);
rollback;
return;
END sp_cust_main_data_yx;
