17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE EA_MARKET_INTERFACE.P_GetCustProductInfo_Active(
RESULT OUT SYS_REFCURSOR, --结果集临时表名描述
CUST_CODE IN VARCHAR2 --客户代码
)
IS
SQLS VARCHAR2(1000);
BEGIN
SQLS:='CREATE GLOBAL TEMPORARY TABLE VT_RESULT(
PRODUCT_CODE VARCHAR(20)
,PRODUCT_NAME VARCHAR(60)
,PRODUCT_KIND VARCHAR(50)
,PRODUCT_STATUS VARCHAR(100))';
EXECUTE IMMEDIATE SQLS;
COMMIT; --建立临时表
SQLS:='INSERT INTO VT_RESULT
SELECT COALESCE(A.PRODUCT_CODE,'')
,COALESCE(B.PRODUCT_NAME,'')
,COALESCE(C.PRODUCT_CLASS_NAME,'')
,COALESCE(B.PRODUCT_STATUS,'')
FROM CUST_PROD_MATCH A
INNER JOIN PRODUCT_DEF B ON A.PRODUCT_CODE=B.PRODUCT_CODE
INNER JOIN PRODUCT_CLASS C ON B.PRODUCT_CLASS_CODE=C.PRODUCT_CLASS_CODE
LEFT JOIN (SELECT PRODUCT_CODE,COUNT(1) AS RESOURCE_COUNT
FROM EA_MARKET.MS_PRODUCT_RESOURCE
GROUP BY PRODUCT_CODE
) D ON B.PRODUCT_CODE=D.PRODUCT_CODE
WHERE A.SRC_CUST_CODE='||CUST_CODE||'AND A.PRODUCT_CLASS_CODE=01';
EXECUTE IMMEDIATE SQLS;
COMMIT; --插入新的数据到临时表
--更新产品状态
UPDATE VT_RESULT A
SET PRODUCT_STATUS=(SELECT B.CODE_NOTE
FROM E_MMS.V_D_CODE_NOTE B
WHERE B.GROUP_KEY = 'PRODUCT_STATUS'
AND B.INTF_ID = 1
AND A.PRODUCT_STATUS = B.CODE_VAL);
END;