Ticket #1: enterprise_20110428.txt

--itÉ̳ǜš²úƷϵÁÐÊÓÍŒ
CREATE OR REPLACE FORCE VIEW "V_PDL_PRODUCT_VARIANCE" ("ID", "NAME", "SHORT_NAME", "BRAND_ID", "MAJOR_PRODUCT_ID", "CREATE_DATE")
AS
  SELECT v.id,
    v.name,
    v.short_name,
    v.brand_id,
    v.major_product_id,
    v.create_date
  FROM product.pdl_product_variance v;
  
--²úÆ·¿âž³selectÈšÏÞžøÉ̳Ç
--GRANT SELECT ON product.pdl_product_variance TO CYP_NW_APP£»

--itÉ̳ÇÖØœš²úÆ·ÊÓÍŒ
CREATE OR REPLACE FORCE VIEW "V_PDL_PRODUCT" ("ID", "TYPE_ID", "NAME", "SHORT_NAME", "BRAND_NAME", "SMALLTYPE_ID", "VARIANCE_ID", "PRICE_CONFIG", "ORDER_KEY", "CREATE_DATE", "REVIEW_STATUS", "PRICE", "HOT_NEW", "PUB_URL", "FORUM_PATH", "PIC_PATH", "MID_PATH", "BIG_PIC_PATH", "LAST_COUNT_ORDER")
AS
  SELECT p.ID,
    p.type_id,
    p.NAME,
    p.short_name,
    b.name,
    b.parent_id,
    p.variance_id,
    p.price_config,
    p.order_key,
    p.create_date,
    3 AS REVIEW_STATUS,
    p.price,
    p.HOT_NEW,
    p.PUB_URL,
    '/'
    || b.forum_dir
    ||'/f'
    || p.forum_id AS forum_path,
    pic.index_path,
    pic.mid_path AS MID_PATH,
    pic.big_path AS BIG_PIC_PATH,
    p.last_count_order
  FROM pdl_product p,
    pdl_product_type b,
    pdl_temp_product_pic pic
  WHERE p.id           =pic.product_id(+)
  AND b.id             = p.type_id
  AND p.status         =1
  AND p.REVIEW_STATUS IN(2,3);