--반품박스정보 조회
  PROCEDURE PROC_OPEN_BOX ( P_COMPCD       IN VARCHAR2
                           ,P_CENTCD       IN VARCHAR2
                           ,P_BOXNNO       IN VARCHAR2
                           ,P_RESULTSET   OUT SYS_REFCURSOR)
  IS
    V_BOXSTARTTIMES VARCHAR2(50);
    V_COMPCD      CO_BOX.COMPCD      %TYPE;
    V_CENTCD      CO_BOX.CENTCD      %TYPE;
    V_BOXNO       CO_BOX.BOXNO       %TYPE;
    V_OWNGB       CO_BOX.OWNGB       %TYPE;
    V_BRANDCD     CO_BOX.BRANDCD     %TYPE;
    V_SHOPCD      CO_BOX.SHOPCD      %TYPE;
    V_INSDD       SR_INSMST.INSDD    %TYPE;
  BEGIN
     --1번주석 04-4-C52116090263 값에 문제가 없습니다.
     --RAISE_APPLICATION_ERROR(-20001, P_COMPCD||'-'||P_CENTCD||'-'||P_BOXNNO);

     BEGIN
       SELECT OWNGB  , SHOPCD  , BRANDCD
         INTO V_OWNGB, V_SHOPCD, V_BRANDCD
         FROM CO_BOX
        WHERE COMPCD = P_COMPCD
          AND CENTCD = P_CENTCD
          AND BOXNO  = P_BOXNNO;
     EXCEPTION WHEN NO_DATA_FOUND THEN
       --2번주석 XX-X-XXXX 로 변수값들이 변하였습니다. 왜일까요?
       RAISE_APPLICATION_ERROR(-20001, P_COMPCD||'-'||P_CENTCD||'-'||P_BOXNNO);
     END;

     --매장브랜드 체크
     SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') INTO V_INSDD FROM DUAL;

     IF ( V_OWNGB = '4' ) THEN
        PRO_SHOPCHK(P_COMPCD, V_SHOPCD, V_BRANDCD, V_INSDD);
     END IF;

    SELECT TO_CHAR(systimestamp, 'yyyy-mm-dd hh24:mi:ss ff6') INTO V_BOXSTARTTIMES FROM DUAL;

    OPEN P_RESULTSET FOR
       SELECT '   ' CURRENTROW, A.BOXNO, A.OWNGB, D.CODENM OWNGBNM, A.BOXGB, A.SHOPCD, C.SHOPNM, A.BOXSTATEGB, A.AREACD,
              B.STYLECD, B.COLORCD, B.SIZECD, B.QTY RTNQTY, 0 INSQTY, -1*NVL(B.QTY,0) GAPQTY,
              B.BRANDCD SBRANDCD,
              CASE WHEN B.BRANDCD <> A.BRANDCD THEN '●' ELSE '' END NONBOXBRAND,
              A.AREACD, A.PALLETNO, F_AREA_USEGROUP(A.COMPCD, A.CENTCD, A.AREACD) AREAGROUP,
              V_BOXSTARTTIMES BOXSTARTTIMES,
              A.BRANDCD BXBRANDCD, E.CODENM BXBRANDNM,
              TO_BLOB('') BARCODE, TO_BLOB('') BARCODE1, TO_BLOB('') BARCODE2,
              B.STYLECD||B.COLORCD||B.SIZECD STYLECODE
              , DECODE(A.OWNGB, '4', NVL(F.BRANDCD, 'XXXXX'), A.BRANDCD) CHEBRANDCD
         FROM CO_BOX A, CO_BOX_SKU B, V_SHOPINFO_ALLCOMPANY C,
              ( SELECT * FROM SYSCOMMINFO_WMS WHERE COMPCD = P_COMPCD AND BUSIGB = 'NS' AND CODEGB = '1010' ) D,
              ( SELECT * FROM V_SYSCOMMINFO   WHERE COMPCD = P_COMPCD AND BUSIGB = 'PL' AND CODEGB = '0020' ) E
              , (SELECT *
                   FROM SMASHOPBRAND
                  WHERE COMPCD = P_COMPCD
                    AND TO_CHAR(SYSDATE,'YYYY-MM-DD') BETWEEN STARTDT AND NVL(ENDDT, '2099-12-31')) F
        WHERE A.COMPCD  = B.COMPCD AND A.CENTCD = B.CENTCD AND A.BOXNO = B.BOXNO
          AND A.OWNGB   = C.OWNGB  AND A.SHOPCD = C.SHOPCD
          AND A.OWNGB   = D.CODE(+)
          AND A.BRANDCD = E.CODE(+)
          AND A.COMPCD = F.COMPCD(+)
          AND A.SHOPCD  = F.SHOPCD(+)
          AND A.BRANDCD = F.BRANDCD(+)
          AND A.COMPCD  = P_COMPCD
          AND A.CENTCD  = P_CENTCD
          AND A.BOXGB   = 'B' --B반품
          AND A.BOXSTATEGB = 'C' --C생성 D폐기
          AND A.BOXNO = P_BOXNNO
       ORDER BY B.STYLECD, B.COLORCD, B.SIZECD;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20001, SQLERRM);
  END;