CREATE OR REPLACE PROCEDURE GLU_SP_RECALCULATE_RATE (P OUT VARCHAR2)AS
rateRow REV_SHARE_RATE%rowtype;
cursor rateRows is
SELECT
DISTRIBUTOR_ID,
RETAILER_ID,
RATE,
MODIFIED_DATE ,
GENRE
FROM REV_SHARE_RATE
WHERE
IS_RUN=0;
BEGIN
FOR rateRow in rateRows loop
--Qualcomm,Qualcomm(p)Qualcomm(s)
IF rateRow.Distributor_Id =1 OR rateRow.Distributor_Id =35 OR rateRow.Distributor_Id =61 THEN
UPDATE DAILYDAP SET USD_VALUE = (NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate)/(NET_REV/USD_VALUE),-- v_usd_fx,
GBP_VAlUE = (NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
NET_REV= NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate
WHERE DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
--ATT
ELSIF rateRow.DISTRIBUTOR_ID = 49 THEN
update (select /*+ BYPASS_UJVC */ d.DISTRIB_ID,d.RETAILER_ID,rate.genre,d.USD_VALUE,d.DATA_RAW,d.NET_REV,d.GBP_VAlUE,d.REPORT_DATE from dailydap d
inner join att_raw_daily att on d.ref_id = att.id
inner join rev_share_rate rate on d.distrib_id = rate.DISTRIBUTOR_ID and d.retailer_id = rate.Retailer_Id and att.product_category = rate.genre
)
SET
USD_VALUE = (DATA_RAW * rateRow.Rate)/ (NET_REV/USD_VALUE),--v_usd_fx,
GBP_VAlUE = (DATA_RAW * rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
NET_REV= DATA_RAW * rateRow.Rate
where
DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND
GENRE=rateRow.GENRE AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
--sprint,nextel...
ELSE
UPDATE DAILYDAP SET USD_VALUE = (DATA_RAW * rateRow.Rate)/ (NET_REV/USD_VALUE),--v_usd_fx,
GBP_VAlUE = (DATA_RAW * rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
NET_REV= DATA_RAW * rateRow.Rate
WHERE DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
END IF;
--UPDATE REV_SHARE_RATE SET IS_RUN = 1
UPDATE REV_SHARE_RATE SET IS_RUN=1 WHERE DISTRIBUTOR_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id;
END LOOP;
COMMIT;
P:='OK';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
P:= SQLERRM;
END;