PROCEDURE ads_amp_pd_ma_merchant_sale AS v_createsql varchar2(32767); BEGIN PKG_COMMON.drop_table('ads_amp_pd_ma_merchant_sale'); v_createsql := ' create table ads_amp_pd_ma_merchant_sale as (select PKG_ADS_AMP_PD.PARTNER_ID as PARTNER_ID, 0 as PROJECT_ID, 0 as CONT_ID, zjwgsals.bis_shop_name as BRAND_NAME, zjwgsals.sales_money as SALE_AMOUNT, 0 as BUDGET_AMOUNT, decode(zjwgsals.rent_square, 0, 0, round(zjwgsals.sales_money / zjwgsals.rent_square, 2)) as PERFORMANCE_VALUE, decode(zjwgsals.sales_money, 0, 0, round(zjwgsals.must_zj / zjwgsals.sales_money, 2)) as RENT_SALE_RATE, decode(zjwgsals.sort_name_root, ''超市'', ''100'', ''影院'', ''200'', ''百货'', ''300'', ''服装'', ''400'', ''儿童'', ''500'', ''餐饮'', ''600'', ''配套'', ''700'', ''800'') as COMMERCIAL_TYPE, zjwgsals.sort_name_root as COMMERCIAL_TYPE_NAME, zjwgsals.YEAR as YEAR, zjwgsals.MONTH as MONTH, zjwgsals.qz_year_month as DUTY_MONTH, ''0'' as IS_DEL, sysdate as CREATE_DATE, PKG_ADS_AMP_PD.USER_ID as CREATER_ID, zjwgsals.bis_project_id as OUT_PROJECT_ID, zjwgsals.bis_cont_id as OUT_CONTRACT_ID, round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100 / 30.42, 2) as RENT_PER_DAY, round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100, 2) as RENT_PER_MONTH from ads_amp_pd_ma_contract cont inner join (select a.*, to_number(substr(qz_year_month, 1, 4)) as YEAR, to_number(substr(qz_year_month, 6, 2)) as MONTH from dws_pd_cont_zjwgsals_1m a where dt = PKG_COMMON.YESTERDAY_YYYYMMDD and a.status_cd not in (''3'', ''5'') and a.sals_data_flg = ''1'' ) zjwgsals on cont.OUT_PROJECT_ID = zjwgsals.bis_project_id and cont.OUT_CONTRACT_ID = zjwgsals.bis_cont_id left outer join (select bis_cont_id, year, royalty_ratio, guaranteed_money from ods_pd_bis_must_rent where dt = PKG_COMMON.YESTERDAY_YYYYMMDD ) mustrent on zjwgsals.bis_cont_id = mustrent.bis_cont_id and (to_number(zjwgsals.YEAR) - to_number(to_char(cont.START_DATE,
|