程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE no1 存儲過程插入更新表數據,oracleno1

ORACLE no1 存儲過程插入更新表數據,oracleno1

編輯:Oracle教程

ORACLE no1 存儲過程插入更新表數據,oracleno1


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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved