/*------------------------------------------------------------------------------
-- 개체 이름 : FOS.MATERIALSELLER
-- 만든 날짜 : 2009-03-06 오후 3:44:23
-- 마지막으로 수정한 날짜 : 2011-03-22 오후 1:42:35
-- 상태 : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE FORCE VIEW FOS.MATERIALSELLER
( CATERINGCODE, MESSROOMCODE, MATERIALCODE, STARTDAY, 
  PRESELLERCODE, SELLERCODE, REGISTERID, REGISTERDATE, 
  MODIFIERID, MODIFIERDATE )
AS
select b.cateringCode,
       b.messroomCode,
       c.item_cd,
       '20001010' startDay,
       '100001' preSellerCode,
       '100001' sellerCode,
       '' registerId,
       '' registerDate,
       '' modifierId,
       '' modifierDate
from   fan.fzb002t a,
       smallMessroom b,
       material_customer c --MATERIAL_CUSTOMER
where  a.cust_no=b.store_cust_no
and    a.KUNPG=c.customer
and    b.store_type='301'
--and    to_char(sysdate, 'YYYYMMDD') between APP_STA_DT and APP_END_DT --기간관리 안함 2009년 3월 27일 김윤혜주임 확인
and    decode((select 'Y'
                from   material_customer d
                where  d.customer= a.KUNPG
--              and    to_char(sysdate, 'YYYYMMDD') between d.APP_STA_DT and d.APP_END_DT --기간관리 안함 2009년 3월 27일 김윤혜주임 확인
                and    rownum=1), 'Y', 'Y', 'N')='Y'
union all
select e.cateringCode,
       e.messroomCode,
       a.item_cd,
       '20001010' startDay,
       '100001' preSellerCode,
       '100001' sellerCode,
       '' registerId,
       '' registerDate,
       '' modifierId,
       '' modifierDate
from   epro.item a,
       epro.item_oper_org b,
       industryinfo c,
       fan.fzb002t d,
       smallMessroom e
where  a.ITEM_SEQ=b.ITEM_SEQ
and    b.oper_org_cd = '3000'
and    b.apprv_yn = 'Y'
and    b.PRICE_GRD=c.PRICE_GRD
and    c.industry=d.industry
and    d.cust_no=e.store_cust_no
and    e.store_type='301'
and    decode((select 'Y'
                from   material_customer k
                where  k.customer= d.KUNPG
                --and    to_char(sysdate, 'YYYYMMDD') between k.APP_STA_DT and k.APP_END_DT --기간관리 안함 2009년 3월 27일 김윤혜주임 확인
                and    rownum=1), 'Y', 'Y', 'N')='N'
union all
select CATERINGCODE,
       MESSROOMCODE,
       MATERIALCODE,
       STARTDAY,
       PRESELLERCODE,
       SELLERCODE,
       REGISTERID,
       '' REGISTERDATE,
       MODIFIERID,
       '' MODIFIERDATE
from   materialseller02
;

COMMENT ON COLUMN FOS.MATERIALSELLER.CATERINGCODE IS '회사';

COMMENT ON COLUMN FOS.MATERIALSELLER.MESSROOMCODE IS '사업장';

COMMENT ON COLUMN FOS.MATERIALSELLER.MATERIALCODE IS '품목';

COMMENT ON COLUMN FOS.MATERIALSELLER.STARTDAY IS '시작일';