oracle存储过程日常开发-零售追踪报表

零售追踪报表需求：
1.加两字段-月销量指标+实际销售数量完成率（实际销售数量/月销量指标）
2.销售数量和金额去掉属性2 L P Z 物料、配饰、赠品
3.顾客数改成客单数

• 写功能注释和说明

--1.author:lzj
    --  date:20210712
    --  description:客户要求零售追踪报表新增两字段，分别是月销量指标+实际销售数量完成率
    --  月销量指标从点仓营业指标中获取月销量指标
    --  实际销售数量完成率根据报表已有的实际销售数/ 月销量指标

• 加字段：
  // 月销量指标
  // 实际销售数量完成率

alter table RP_RETAILFLW add TOT_MONSALE_TARGET NUMBER(10);
alter table RP_RETAILFLW add mon_qty_rate NUMBER(12,4);

• 修改存储过程

--start - 20210712 - lzj 更新月零售指标 + 实际销售数量完成率（实际销售数量/月销量指标）
    -- update时要考虑被除数为0的情况 ，如果不能确定被除数为0，那么必须对被除数进行处理。
    MERGE INTO rp_retailflw rpr
    USING (SELECT c_store_id, tot_monsale_target
           FROM c_storemark
           WHERE yearmonth BETWEEN substr(v_datebegin, 0, 6) AND
                 substr(v_dateend, 0, 6)) fl
    ON (rpr.c_store_id = fl.c_store_id AND rpr.ad_pi_id = p_pi_id)
    WHEN MATCHED THEN
        UPDATE
        SET rpr.tot_monsale_target = fl.tot_monsale_target,
            rpr.mon_qty_rate = (CASE
    WHEN nvl(fl.tot_monsale_target, 0) = 0 THEN 0 ELSE rpr.qty / fl.tot_monsale_target END);
    --end - 20210712 - lzj 更新月零售指标

• 销售数量和金额去掉属性2 L P Z 物料、配饰、赠品

// 销售业务对应的零售单是数量较多的单据，查询是如果直接去掉，用not in，会使索引失效，可以反向用 in 包含， 提高查询效率
INSERT INTO rp_retailflw
        (id, ad_client_id, ad_org_id, ownerid, modifierid, creationdate,
         modifieddate, isactive, c_store_id, tot_amt_mark, qty, tot_amt_actual,
         compareqty, tot_amt_compare, cus_count, compcus_count, all_amt_actual,
         all_amt_compare, all_qty, all_comqty, all_cus_count, all_comcus_count,
         ad_pi_id)
        SELECT 1, 37, 27, 893, 893, SYSDATE, SYSDATE, 'Y', a.c_store_id,
               0 AS tot_amt_mark, SUM(nvl(b.qty, 0)) AS qty,
               SUM(nvl(b.tot_amt_actual, 0)) AS tot_amt_actual, 0 AS compareqty,
               0 AS tot_amt_compare, COUNT(DISTINCT a.docno) AS cus_count,
               0 AS compcus_count, 0 AS all_amt_actual, 0 AS all_amt_compare,
               0 AS all_qty, 0 AS all_comqty, 0 AS all_cus_count,
               0 AS all_comcus_count, p_pi_id
        FROM m_retail a, m_retailitem b, TABLE(r_store) rs
             -- add by lzj 20210712
            , m_product mp, m_dim md
        -- end by lzj 20210712
        WHERE a.c_store_id = rs.id
             -- add by lzj 20210712
        AND b.m_product_id = mp.id
        AND mp.m_dim2_id = md.id
        AND md.dimflag = 'DIM2'
        AND md.attribcode IN ('S', 'W', 'K', 'Q', 'T') // 销售数量和金额去掉属性2 L P Z 物料、配饰、赠品
             -- end by lzj 20210712
        AND a.id = b.m_retail_id
        AND a.status = 2
        AND a.billdate BETWEEN v_datebegin AND v_dateend
        GROUP BY a.c_store_id;