CREATE OR REPLACE PROCEDURE DRUG_SALE_SUM
(
    p_d_id            IN  NUMBER
)
DELETE FROM DRUG_TMP WHERE D_ID = p_d_id;
 
 
INSERT INTO DRUG_TMP (D_ID, TEAM_ID, SOLD, LOC, REGION, REV, MARGIN, UNIT)
    SELECT D_ID, IN_PATIENT_ID, 1 SOLD, LOC, REGION,
        SUM(REV * RATE) AS SUM_REVENUE,
        SUM(MARGIN * RATE) AS SUM_MARGIN,
        SUM(UNIT) AS SUM_UNIT
    FROM DRUG_MASTER
    WHERE (DRUG_CANCL IS NULL OR DRUG_CANCL <>'N')
    AND D_ID = p_d_id
    GROUP BY D_ID, IN_PATIENT_ID, LOC, REGION;
 
    INSERT INTO DRUG_TMP (D_ID, TEAM_ID, SOLD, LOC, REGION, REV, MARGIN, UNIT)
    SELECT D_ID, OUTSIDE_TEAM_ID, 2 SOLD, LOC, REGION,
        SUM(REV * RATE) AS SUM_REVENUE,
        SUM(MARGIN * RATE) AS SUM_MARGIN,
        SUM(UNIT) AS SUM_UNIT
    FROM DRUG_MASTER
    WHERE (DRUG_CANCL IS NULL OR DRUG_CANCL <>'N')
    AND D_ID = p_d_id
    GROUP BY D_ID, OUTSIDE_TEAM_ID, LOC, REGION;
    COMMIT;
 
    UPDATE drug_report DP
    SET
             (DP.TODAY_REV,
             DP.TODAY_MARGIN,
             DP.TODAY_UNIT
             ) =
                (SELECT   t.rev, t.margin, t.unit
                   FROM   DRUG_TMP t
                  WHERE       DP.D_ID = t.D_ID
                          AND DP.team_id = t.team_id
                          AND DP.SOLD = t.SOLD
                          AND DP.SEGMENT = t.REGION
                          AND DP.LOC = t.LOC
                          AND t.D_ID = p_d_id)
    WHERE   DP.snapshot_id = p_new_snapshotid
            AND DP.D_ID = p_d_id
            AND (DP.D_ID, DP.team_id, DP.SOLD, DP.SEGMENT, DP.LOC) IN
                      (SELECT   D_ID,
                                team_id,
                                SOLD,
                                REGION,
                                LOC
                         FROM   DRUG_TMP);