/*******************************************************************************************************
    1. 프로시져명 : MWIPWORKDAT_INT5
    2. 제목(기능) : 조립 완제품 구성 LOT(파렛트 담기)
    3. 작  성  일 : 2009-08-14 오후 6:38:08
    4. 작  성  자 : 김영환
*******************************************************************************************************/
PROCEDURE MWIPWORKDAT_INT5
(
    IN_LANG             IN          VARCHAR2,   -- 언어
    IN_FACT             IN          VARCHAR2,   -- Factory
    IN_LINE             IN          VARCHAR2,   -- 라인 코드
    IN_ENG_NO           IN          VARCHAR2,   -- 엔진번호(000000000^000000001^000000002^000000003^)
    IN_ATMT             IN          VARCHAR2,   -- AT/MT
    IN_ALC              IN          VARCHAR2,   -- ALC_CD
    IN_QTY              IN          VARCHAR2,   -- 완제품 구성 수량
    IN_USER             IN          VARCHAR2,  
    OUT_CUR             OUT         T_CURSOR
)
AS
    VI_COUNT            INT;
    VI_LOOP             INT;
    VI_POS              INT;
    VI_IN_ENG_NO        VARCHAR2(200);
    VI_POS_ENG_NO       MWIPWORKDAT.ENG_NO%TYPE;
   
    VI_LOT_NO_BLOCK     MWIPWORKDAT.LOT_NO%TYPE;
    VI_LOT_NO_HEAD      MWIPWORKDAT.LOT_NO%TYPE;
    VI_LOT_NO_CRANK     MWIPWORKDAT.LOT_NO%TYPE;
   
    VI_PROD_LOT_NO      MWIPWORKDAT.PROD_LOT_NO%TYPE;
    VI_WORK_STS         MWIPWORKDAT.WORK_STS%TYPE;
       
BEGIN
   BEGIN
      VI_IN_ENG_NO := IN_ENG_NO;
                 
      -- 1. 완성품 LOTNO를 찾는다(파렛트LOTNO)       
      VI_PROD_LOT_NO := DSINFO.FN_POP_NUMBERING('PRD', IN_FACT, IN_LINE);   
         
      -- 없을 경우 에러
      IF VI_PROD_LOT_NO IS NULL THEN
          ACTIVE_YN := 'N';
          RAISE_MSG := COMMON.ERR_MSG(IN_LANG, 10013); 
          RAISE RAISE_EXT;
      END IF;
      
      WHILE LENGTH(VI_IN_ENG_NO) > 0 LOOP
         VI_POS          :=  INSTR(VI_IN_ENG_NO,'^');
         VI_POS_ENG_NO   := SUBSTR(VI_IN_ENG_NO, 1, VI_POS - 1);
         VI_IN_ENG_NO    := SUBSTR(VI_IN_ENG_NO,    VI_POS + 1);
     
         -- 2. 현재 진행이 가능한지 대상품을 찾는다.
         SELECT COUNT(*) INTO VI_COUNT
           FROM MWIPWORKDAT
          WHERE FACT_CD = IN_FACT
            AND LINE_CD = IN_LINE
            AND WORK_STS IN ('E', 'W')
            AND ENG_NO = VI_POS_ENG_NO;
           
         -- 2.1 1개가 아니면 에러 발생     
         IF VI_COUNT <> 1 THEN
             ACTIVE_YN := 'N';
             RAISE_MSG := COMMON.ERR_MSG(IN_LANG, 10014);   -- 처리할 대상이 없습니다.
             RAISE RAISE_EXT;
         END IF;
        
         -- 3. 엔진번호로 각각의 LOTNO를 찾는다.(블럭, 헤드, 클랭크)           
         SELECT BLK_LOT_NO,      HED_LOT_NO,     CRK_LOT_NO,      WORK_STS     
           INTO VI_LOT_NO_BLOCK, VI_LOT_NO_HEAD, VI_LOT_NO_CRANK, VI_WORK_STS
           FROM MWIPWORKDAT
          WHERE FACT_CD = IN_FACT
            AND LINE_CD = IN_LINE
            AND WORK_STS IN ('E', 'W')
            AND ENG_NO = VI_POS_ENG_NO;
        
         -- 2009-11-20 오후 3:51:45 / KYH / 조립정상완료만 처리한다(반입돼 재작업은 해당 사항 없음)
         IF VI_WORK_STS = 'E' OR VI_WORK_STS = 'W' THEN        
            -- 4. 블럭LOTNO를(엔진번호) 상태를 변경시킨다(WORK_STS사관(B), PROD_LOT_NO, PREV_LINE_CD, UPDATEDATE, UPDATEUSER)
            UPDATE MWIPWORKDAT
               SET WORK_STS      = 'B'
                 , PROD_LOT_NO   = VI_PROD_LOT_NO
                 , PREV_LINE_CD  = IN_LINE
                 , UPDATEDATE    = SYSDATE
                 , UPDATEUSER    = IN_USER
             WHERE FACT_CD = IN_FACT
               AND LINE_CD = IN_LINE
               AND WORK_STS = VI_WORK_STS
               AND ENG_NO = VI_POS_ENG_NO;          
                
            ---- 2010-02-23 오전 7:43:20 / KYH / 조립완료에서 완료처리('0') 하므로 여기서는 제외시킨다
            ---- 5. 헤드, 클랭크 LOTNO는 LOT수량을 0으로 변경한다(KILL)
            --UPDATE MWIPWORKDAT
            --   SET LOT_QTY       = 0
            --     , UPDATEDATE    = SYSDATE
            --     , UPDATEUSER    = IN_USER
            -- WHERE FACT_CD = IN_FACT
            --   AND LOT_NO  = VI_LOT_NO_HEAD;
            --  
            --UPDATE MWIPWORKDAT
            --   SET LOT_QTY       = 0
            --     , UPDATEDATE    = SYSDATE
            --     , UPDATEUSER    = IN_USER
            -- WHERE FACT_CD = IN_FACT
            --   AND LOT_NO  = VI_LOT_NO_CRANK;    
         END IF;
        
      END LOOP ;
     
      -- 6. MWIPSHIPLOT에 INSERT(한 건만 INSERT)
      INSERT INTO MWIPSHIPLOT (FACT_CD, PROD_LOT_NO,    ALC_CD, MAT_ID, AT_MT,   SHIP_QTY, SHIP_YN, INPUTDATE, INPUTUSER)
                       VALUES (IN_FACT, VI_PROD_LOT_NO, IN_ALC, IN_ALC, IN_ATMT, IN_QTY,   'N',     SYSDATE,   IN_USER);
                        
       ACTIVE_YN := 'Y';
--       RAISE_MSG := COMMON.ERR_MSG(IN_LANG, 10016);    -- 성공적으로 처리 하였습니다.
       RAISE_MSG := VI_PROD_LOT_NO;   -- 완제품 PALLETNO
   EXCEPTION
       WHEN RAISE_EXT        THEN ROLLBACK;
           ACTIVE_YN := 'N';
       WHEN NO_DATA_FOUND    THEN ROLLBACK;
           ACTIVE_YN := 'N';                        
       WHEN OTHERS           THEN ROLLBACK;
           ACTIVE_YN := 'N';    
   END;   

   OPEN  OUT_CUR FOR
   SELECT ACTIVE_YN       AS ACTIVE_YN
        , RAISE_MSG       AS RAISE_MSG
   FROM DUAL;   
  
END MWIPWORKDAT_INT5;


>>> 패키지 소스... 그닥 예뻐 보이진 않네...