style24.doc/산출물/3.설계/03.인터페이스/네이버/네이버_최저가/sql/getProductInfo.sql

SELECT A.PROD_CD
     , A.LIST_PRICE
     , A.PROD_PRICE
     , B.STYLECD AS TEXT
FROM (
      SELECT A.PROD_CD
           , A.LIST_PRICE
           , A.PROD_PRICE
      FROM TB_PRODUCT A
         , TB_PROD_STOCK B
         , TB_ITEMKIND_GROUP C
      WHERE A.PROD_CD = B.PROD_CD
      AND A.ITEMKIND_CD = C.ITEMKIND_CD
      AND A.PROD_STS_CD = '90' --승인 완료된 상품만
      AND B.STOCK_QTY > 0 --재고 있는 상품만
      --AND C.TGROUP_NM = 'Shoes'
      AND A.BRAND_CD IN ('P0PM','R0PR','P0PS')
      AND A.PROD_YY = '2018'
      AND EXISTS ( SELECT 1
                   FROM   TB_CATEGORY_PROD
                   WHERE  PROD_CD = A.PROD_CD )
     ) A
     , V_STYLE_INFO_ESHOP@ECS2ERP B
WHERE A.PROD_CD = B.STYLECD||B.COLOR

/*
SELECT A.PROD_CD    AS PROD_CD--상품코드
     , C.STYLECD
     , C.COLOR
     , A.LIST_PRICE AS LIST_PRICE --최초소비자가
     , A.PROD_PRICE AS PROD_PRICE --현재판매가
FROM   TB_PRODUCT A
     , TB_PROD_STOCK B
     , V_STYLE_INFO_ESHOP@ECS2ERP C
WHERE  A.PROD_CD = B.PROD_CD
AND A.PROD_CD = C.STYLECD||C.COLOR
--AND A.BRAND_CD NOT IN ('E001', 'D0DV') -- EXR, 델보 브랜드 제외
AND A.BRAND_CD IN ('R0MG','A0AR','S021')
--AND    (A.PROD_LSN_YN = 'Y' or (A.PROD_LSN_YN = 'N' AND A.BRAND_CD='M'))  
AND    A.PROD_STS_CD = '90' --승인 완료된 상품만
AND    B.STOCK_QTY > 0 --재고 있는 상품만
--전시된 상품만
AND    EXISTS (SELECT 1
               FROM   TB_CATEGORY_PROD
               WHERE  PROD_CD = A.PROD_CD
              )
ORDER BY A.PROD_CD
*/
/*
SELECT A.SUPPLY_PROD_CD    AS PROD_CD--상품코드
     , A.LIST_PRICE AS LIST_PRICE --최초소비자가
     , A.PROD_PRICE AS PROD_PRICE --현재판매가
FROM   TB_PRODUCT A
     , TB_PROD_STOCK B
WHERE  A.PROD_CD = B.PROD_CD
AND A.BRAND_CD IN ('R0MG','A0AR','S021')
AND    A.PROD_STS_CD = '90'
AND    B.STOCK_QTY > 0
AND    EXISTS (SELECT 1
               FROM   TB_CATEGORY_PROD
               WHERE  PROD_CD = A.PROD_CD
              )
ORDER BY PROD_CD
*/