| 1 |
- window.repositoryObject = {"parameters_custom_fields":[],"object_id":"f367","name":"FN_GET_APPLY_CPN1_PRICE","subtype":"FUNCTION","is_user_defined":false,"description":null,"summary":[{"field":"Documentation","value":{"_type":"link","name":"style24@172.30.90.100","id":"d10"}},{"field":"Schema","value":""},{"field":"Name","value":"FN_GET_APPLY_CPN1_PRICE"},{"field":"Type","value":"Function"}],"script":"BEGIN\r\n DECLARE O_RET_VALUE INT;\r\n DECLARE O_CURR_PRICE INT;\r\n DECLARE O_DC_VAL INT;\r\n\r\n SET O_RET_VALUE = 0;\r\n SET O_DC_VAL = 0;\r\n\r\n SELECT CURR_PRICE\r\n INTO O_CURR_PRICE -- 상품금액\r\n FROM TB_GOODS\r\n WHERE GOODS_CD = I_GOODS_CD\r\n ;\r\n\r\n WITH GOODS_DATA AS (\r\n SELECT G.GOODS_CD\r\n , G.CURR_PRICE\r\n , G.BRAND_CD\r\n , G.SUPPLY_COMP_CD\r\n FROM TB_GOODS G\r\n WHERE 1=1\r\n AND G.GOODS_CD = I_GOODS_CD\r\n )\r\n SELECT CASE WHEN Y.FRONT_GB IN ('P') THEN Y.DC_PVAL\r\n WHEN Y.FRONT_GB IN ('M') THEN Y.DC_MVAL\r\n WHEN Y.FRONT_GB IN ('A') THEN Y.DC_AVAL\r\n END\r\n INTO O_DC_VAL\r\n FROM (\r\n SELECT CP.CPN_ID\r\n , I_FRONT_GB AS FRONT_GB\r\n , CASE WHEN CP.DC_WAY IN ('G240_11')\r\n THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_PVAL) / 100\r\n THEN (Z.CURR_PRICE * CP.DC_PVAL) / 100\r\n ELSE CP.MAX_DC_AMT\r\n END\r\n ELSE CP.DC_PVAL\r\n END DC_PVAL\r\n , CASE WHEN CP.DC_WAY IN ('G240_11')\r\n THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_MVAL) / 100\r\n THEN (Z.CURR_PRICE * CP.DC_MVAL) / 100\r\n ELSE CP.MAX_DC_AMT\r\n END\r\n ELSE CP.DC_MVAL\r\n END DC_MVAL\r\n , CASE WHEN CP.DC_WAY IN ('G240_11')\r\n THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_AVAL) / 100\r\n THEN (Z.CURR_PRICE * CP.DC_AVAL) / 100\r\n ELSE CP.MAX_DC_AMT\r\n END\r\n ELSE CP.DC_AVAL\r\n END DC_AVAL\r\n FROM TB_COUPON CP\r\n INNER JOIN (\r\n SELECT X.*\r\n FROM (\r\n SELECT CR.CPN_ID\r\n , GD.GOODS_CD\r\n , GD.CURR_PRICE\r\n FROM TB_COUPON_REFVAL CR\r\n INNER JOIN GOODS_DATA GD\r\n ON GD.GOODS_CD = CR.REF_VAL\r\n WHERE 1=1\r\n AND CR.CPN_TARGET = 'G260_10' -- 상품\r\n AND CR.DEL_YN = 'N'\r\n UNION\r\n SELECT CR.CPN_ID\r\n , GD.GOODS_CD\r\n , GD.CURR_PRICE\r\n FROM (\r\n SELECT C4.LEAF_CATE_NO\r\n , CASE WHEN X = 1 THEN 'CATE1_NO'\r\n WHEN X = 2 THEN 'CATE2_NO'\r\n WHEN X = 3 THEN 'CATE3_NO'\r\n WHEN X = 4 THEN 'CATE4_NO'\r\n ELSE 'CATE5_NO'\r\n END CATE_LVL\r\n , CASE WHEN X = 1 THEN CATE1_NO\r\n WHEN X = 2 THEN CATE2_NO\r\n WHEN X = 3 THEN CATE3_NO\r\n WHEN X = 4 THEN CATE4_NO\r\n ELSE CATE5_NO\r\n END CATE_NO\r\n FROM TB_CATE_4SRCH C4\r\n LEFT OUTER JOIN (\r\n SELECT 1 AS X\r\n UNION ALL\r\n SELECT 2 AS X\r\n UNION ALL\r\n SELECT 3 AS X\r\n UNION ALL\r\n SELECT 4 AS X\r\n UNION ALL\r\n SELECT 5 AS X\r\n ) B\r\n ON 1=1\r\n WHERE 1=1\r\n AND C4.SITE_CD = 'G000_10'\r\n AND C4.CATE_TYPE = 'G031_10'\r\n ORDER BY LEAF_CATE_NO\r\n , CATE_LVL\r\n ) C4SRCH\r\n INNER JOIN TB_COUPON_REFVAL CR\r\n ON C4SRCH.CATE_NO = CR.REF_VAL\r\n INNER JOIN TB_CATE_GOODS CG\r\n ON C4SRCH.LEAF_CATE_NO = CG.CATE_NO\r\n INNER JOIN GOODS_DATA GD\r\n ON CG.GOODS_CD = GD.GOODS_CD\r\n WHERE 1=1\r\n AND C4SRCH.CATE_NO IS NOT NULL\r\n AND CR.CPN_TARGET = 'G260_11' -- 카테고리\r\n AND CR.DEL_YN = 'N'\r\n UNION\r\n SELECT CR1.CPN_ID\r\n , GD.GOODS_CD\r\n , GD.CURR_PRICE\r\n FROM GOODS_DATA GD\r\n INNER JOIN TB_COUPON_REFVAL CR1\r\n ON GD.BRAND_CD = CR1.REF_VAL\r\n AND CR1.CPN_TARGET = 'G260_12' -- 브랜드\r\n AND CR1.DEL_YN = 'N'\r\n INNER JOIN TB_COUPON_REFVAL CR2\r\n ON GD.SUPPLY_COMP_CD = CR2.REF_VAL\r\n AND CR2.CPN_TARGET = 'G260_13' -- 공급업체\r\n AND CR2.DEL_YN = 'N'\r\n ) X\r\n LEFT OUTER JOIN TB_COUPON_REFVAL CR\r\n ON X.CPN_ID = CR.CPN_ID\r\n AND X.GOODS_CD = CR.REF_VAL\r\n AND CR.CPN_TARGET = 'G260_14' -- 쿠폰제외상품\r\n AND CR.DEL_YN = 'N'\r\n LEFT OUTER JOIN TB_COUPON_BAN_GOODS CBG -- 전체제외상품\r\n ON X.GOODS_CD = CBG.GOODS_CD\r\n AND CBG.DEL_YN = 'N'\r\n WHERE 1=1\r\n AND CR.CPN_ID IS NULL\r\n AND CBG.CPN_BAN_GOODS_SQ IS NULL\r\n ) Z\r\n ON Z.CPN_ID = CP.CPN_ID\r\n WHERE 1=1\r\n AND CP.SITE_CD = 'G000_10' -- 자사몰\r\n AND CP.CPN_TYPE = 'G230_10' -- 즉시할인\r\n AND CP.CPN_STAT = 'G232_11'\r\n AND CP.AVAIL_STDT < CURRENT_TIMESTAMP\r\n AND CP.AVAIL_EDDT > CURRENT_TIMESTAMP\r\n ORDER BY (CASE WHEN I_FRONT_GB IN ('P') THEN CP.DC_PVAL\r\n WHEN I_FRONT_GB IN ('M') THEN CP.DC_MVAL\r\n WHEN I_FRONT_GB IN ('A') THEN CP.DC_AVAL\r\n END) DESC\r\n ) Y\r\n LIMIT 1\r\n ;\r\n\r\n IF O_CURR_PRICE > O_DC_VAL THEN\r\n SET O_RET_VALUE = O_CURR_PRICE - O_DC_VAL;\r\n ELSE\r\n SET O_RET_VALUE = O_CURR_PRICE;\r\n END IF\r\n ;\r\n\r\n RETURN O_RET_VALUE;\r\nEND","parameters":[{"name":"Returns","description":null,"mode":"OUT","data_type":"int(10, 0)","custom_fields":{}},{"name":"I_GOODS_CD","description":null,"mode":"IN","data_type":"varchar(50)","custom_fields":{}},{"name":"I_FRONT_GB","description":null,"mode":"IN","data_type":"char(1)","custom_fields":{}}],"dependencies":null,"imported_at":"2021-11-11 16:37"};
|
