XinYu_RZMES/.svn/pristine/ae/ae5a3d3c205b0455779919784b0b60e6a5ab2071.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="Order Project A">

    <query id="UIA020020.LOV.SpecStlGrd.find" desc=" " fetchSize="10">
        <![CDATA[
SELECT SM_CFNM AS SPEC_STL_GRD_DESC, SM_CD AS SPEC_STL_GRD
FROM TBZ00_COMMCD
WHERE LG_CD = 'A02001'
AND USED_YN = 'Y'
ORDER BY CD_SEQ
        ]]>
    </query>


    <query id="UIA020020.LOV.DlvtGrpTp.find" desc=" " fetchSize="10">
        <![CDATA[
SELECT SM_CFNM AS DLVT_GRP_TP_DESC, SM_CD AS DLVT_GRP_TP
FROM TBZ00_COMMCD
WHERE LG_CD = 'A02002'
AND USED_YN = 'Y'
ORDER BY CD_SEQ
        ]]>
    </query>


    <!-- query id="UIA020020.LOV.ElmtSeq.find" desc=" " fetchSize="10">
        <![CDATA[
SELECT ELMT_DESC||'('||TO_CHAR(TERM_DAYS,'FM990.0')||')' AS ELMT_DESC, ELMT_SEQ
FROM TBA02_DLVT_ELMT
WHERE 1 = 1
ORDER BY ELMT_SEQ
        ]]>
    </query -->


    <query id="UIA020020.List.DlvtGroup.find" desc=" " fetchSize="10">
        <![CDATA[
SELECT A.SPEC_STL_GRD
     , A.DLVT_GRP_TP
     , A.DSP_SEQ
     , NVL(B.SUM_TERM_DAYS,0) AS SUM_TERM_DAYS
     , A.REMARKS
FROM TBA02_DLVT_GRP A
   ,(SELECT X.SPEC_STL_GRD, X.DLVT_GRP_TP, SUM(X.TERM_DAYS) AS SUM_TERM_DAYS
     FROM TBA02_DLVT_DTL X
     WHERE X.SPEC_STL_GRD LIKE :1||'%'
     AND X.DLVT_GRP_TP LIKE :2||'%'
     GROUP BY X.SPEC_STL_GRD, X.DLVT_GRP_TP) B
WHERE A.SPEC_STL_GRD = B.SPEC_STL_GRD(+)
AND A.DLVT_GRP_TP = B.DLVT_GRP_TP(+)
AND A.SPEC_STL_GRD LIKE :3||'%'
AND A.DLVT_GRP_TP LIKE :4||'%'
ORDER BY A.SPEC_STL_GRD, A.DSP_SEQ
        ]]>
    </query>


    <query id="UIA020020.List.DlvtDetail.find" desc=" " fetchSize="100">
        <![CDATA[
SELECT DECODE(B.ELMT_SEQ,NULL,'N','Y')                                                   AS CHK2
     , A.ELMT_SEQ                                                                        AS ELMT_SEQ2
     , A.ELMT_DESC                                                                       AS ELMT_DESC2
     , A.DEFAULT_SEQ                                                                     AS DEFAULT_SEQ2
     , A.SPEC_STL_GRD                                                                    AS SPEC_STL_GRD2
     , A.DLVT_GRP_TP                                                                     AS DLVT_GRP_TP2
     , CASE WHEN (B.TERM_DAYS IS NULL OR A.TERM_DAYS = B.TERM_DAYS) THEN A.TERM_DAYS
                                                                    ELSE B.TERM_DAYS END AS TERM_DAYS2
     , B.REG_ID                                                                          AS REG_ID2
     , TO_CHAR(TO_DATE(B.REG_DTIME,'YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS')          AS REG_DTIME2
     , B.MOD_ID                                                                          AS MOD_ID2
     , TO_CHAR(TO_DATE(B.MOD_DTIME,'YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS')          AS MOD_DTIME2
     , B.TERM_DAYS_DESC                                                                  AS TERM_DAYS_DESC2
FROM(SELECT *
     FROM TBA02_DLVT_ELMT X,(SELECT :1 AS SPEC_STL_GRD, :2 AS DLVT_GRP_TP FROM DUAL) Y) A
   , TBA02_DLVT_DTL B
WHERE 1 = 1
AND A.ELMT_SEQ = B.ELMT_SEQ(+)
AND A.SPEC_STL_GRD = B.SPEC_STL_GRD(+)
AND A.DLVT_GRP_TP = B.DLVT_GRP_TP(+)
ORDER BY A.DEFAULT_SEQ, A.ELMT_SEQ
        ]]>
    </query>


    <query id="UIA020020.List.DlvtOrder.find" desc=" " fetchSize="100">
        <![CDATA[
SELECT X.ORD_NO          AS ORD_NO3
     , X.ORD_SEQ         AS ORD_SEQ3
     , X.ORD_DLVT_SEQ    AS ORD_DLVT_SEQ3
     , X.SPEC_STL_GRD    AS SPEC_STL_GRD3
     , X.DLVT_GRP_TP     AS DLVT_GRP_TP3
     , Z.SM_CFNM         AS DLVT_GRP_TP_DESC3
     , X.DLVT_DATE       AS DLVT_DATE3
     , X.REG_ID          AS REG_ID3
     , X.REG_DTIME       AS REG_DTIME3
FROM (SELECT A.ORD_NO
           , A.ORD_SEQ
           , A.ORD_DLVT_SEQ
           , A.SPEC_STL_GRD
           , A.DLVT_GRP_TP
           , A.DLVT_DATE
           , A.REG_ID
           , TO_CHAR(TO_DATE(A.REG_DTIME,'YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS') AS REG_DTIME
           , ROW_NUMBER() OVER (PARTITION BY A.ORD_NO, A.ORD_SEQ ORDER BY A.ORD_DLVT_SEQ DESC) AS RN
      FROM TBA02_DLVT_ORD A
      WHERE 1 = 1
      AND A.SPEC_STL_GRD = :1
      AND A.DLVT_GRP_TP = :2) X
    ,(SELECT SM_CFNM, SM_CD FROM TBZ00_COMMCD WHERE LG_CD = 'A02002') Z
WHERE X.DLVT_GRP_TP = Z.SM_CD(+)
AND X.RN = 1
ORDER BY X.ORD_NO, X.ORD_SEQ
        ]]>
    </query>


    <query id="UIA020020.List.DlvtGroup.insert" desc=" " fetchSize="10">
        <![CDATA[
INSERT INTO TBA02_DLVT_GRP (SPEC_STL_GRD, DLVT_GRP_TP, DSP_SEQ, REMARKS ) VALUES ( :1, :2, :3, :4 )
        ]]>
    </query>


    <query id="UIA020020.List.DlvtGroup.update" desc=" " fetchSize="10">
        <![CDATA[
UPDATE TBA02_DLVT_GRP
SET DSP_SEQ = :1
  , REMARKS = :2
WHERE SPEC_STL_GRD = :3
AND DLVT_GRP_TP = :4
        ]]>
    </query>


    <query id="UIA020020.List.DlvtGroup.delete" desc=" " fetchSize="10">
        <![CDATA[
DELETE FROM TBA02_DLVT_GRP WHERE SPEC_STL_GRD = :1 AND DLVT_GRP_TP = :2
        ]]>
    </query>


    <query id="UIA020020.List.DlvtDetail.insert" desc=" " fetchSize="10">
        <![CDATA[
INSERT INTO TBA02_DLVT_DTL (SPEC_STL_GRD, DLVT_GRP_TP, ELMT_SEQ, TERM_DAYS, TERM_DAYS_DESC, REG_ID, REG_DTIME, MOD_ID, MOD_DTIME )
       VALUES ( :1, :2, :3, :4, :5, :6, TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'), :7, TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') )
        ]]>
    </query>


    <query id="UIA020020.List.DlvtDetail.update" desc=" " fetchSize="10">
        <![CDATA[
UPDATE TBA02_DLVT_DTL
SET TERM_DAYS = :1
  , TERM_DAYS_DESC = :2
  , MOD_ID = :3
  , MOD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
WHERE SPEC_STL_GRD = :4
AND DLVT_GRP_TP = :5
AND ELMT_SEQ = :6
        ]]>
    </query>


    <query id="UIA020020.List.DlvtDetail.delete" desc=" " fetchSize="10">
        <![CDATA[
DELETE FROM TBA02_DLVT_DTL WHERE SPEC_STL_GRD = :1 AND DLVT_GRP_TP = :2 AND ELMT_SEQ = :3
        ]]>
    </query>


</queryMap>