XinYu_RZMES/.svn/pristine/a5/a532b3d7122e0c7919cb67c98359235bc42b261d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="板坯外销查询">
	<query id="UIG080010_01.SELECT" desc="板坯外销查询" fetchSize="10">
        <![CDATA[
         SELECT '' CHK
			       ,A.SLAB_NO
			       ,A.STL_GRD
			       ,J.L2_SCH_NO
			       ,SUBSTR(A.SLAB_NO,1,10) CHARGE_NO
			       ,A.SLAB_THK
			       ,A.SLAB_WTH
			       ,A.SLAB_LEN
			       ,A.SLAB_WGT
			       ,A.WEIGHT_WGT  --20130217
			       ,A.CUR_LOAD_LOC
			       --,A.PLT_TRS_CAR_NO
			      --,TO_CHAR(SYSDATE,'YYYYMMDD') PLT_TRS_INVOICE_NO
			      --,A.PLT_PLANT_ARRV_LOC
			       ,PLT_TRS_DTIME
			       ,A.EXTSHAPE_GRD
			       ,A.SLAB_INGR_DEC_GRD
			       ,B.CRK_CD1
			       ,C.CRK_CD2
			       ,D.CRK_CD3
			       ,E.CRK_CD4
			       ,TO_CHAR(SYSDATE,'YYYYMMDD') SYS_DATE
			       ,decode(a.SLAB_OUT_TP,'Y','允许出库','不允许出库') SLAB_OUT_TP
			      FROM TBG02_SLAB_COMM A
			           ,TBD01_SLABDN_OBJ2 J
			     ,(SELECT SM_CD, SM_CD||SM_CFNM AS CRK_CD1 FROM TBZ00_COMMCD WHERE LG_CD = 'G03004') B
			     ,(SELECT SM_CD, SM_CD||SM_CFNM AS CRK_CD2 FROM TBZ00_COMMCD WHERE LG_CD = 'G03004') C
			     ,(SELECT SM_CD, SM_CD||SM_CFNM AS CRK_CD3 FROM TBZ00_COMMCD WHERE LG_CD = 'G03004') D
			     ,(SELECT SM_CD, SM_CD||SM_CFNM AS CRK_CD4 FROM TBZ00_COMMCD WHERE LG_CD = 'G03004') E    
			      WHERE 1 = 1
			    AND A.CRK_CD1 = B.SM_CD(+)
			      AND A.CRK_CD2 = C.SM_CD(+)
			    AND A.CRK_CD3 = D.SM_CD(+)
			      AND A.CRK_CD4 = E.SM_CD(+)
			      --AND  (A.CUR_LOAD_LOC LIKE 'L%' OR A.CUR_LOAD_LOC LIKE 'H%')   --在库
			      AND  A.SLAB_RT IN ('P','Z')    --去向
			      AND  (A.CUR_PROG_CD = 'RGB' or A.CUR_PROG_CD = 'RFA')    --物料进程
			      AND  A.SLAB_STAT = '2'         --物料状态
			      AND  A.PLT_TRS_CAR_NO IS  NULL --移送货单号
			      AND A.DSN_SLAB_NO = J.DSN_SLAB_NO(+)   
			      AND A.SLAB_NO LIKE :1||'%'
         ]]>
    </query>
    <query id="UIG080010_02.CAL" desc="外销保存操作 " fetchSize="10">
    <![CDATA[
    		{CALL MES_LG_KC.SENDLIST_BP(?,?,?,?,?,?,?,?)}
    ]]>
    </query>
    
    <query id="UIG080010_04.CAL" desc="发送电子出门证 " fetchSize="10">
    <![CDATA[
    		{CALL MES_LG_KC.SENDPASSPOT(?,?,?)}
    ]]>
    </query>
    
    <query id="UIG080010_03.CAL" desc="外销退回保存操作 " fetchSize="10">
    <![CDATA[
    		{CALL MES_LG_KC.BACKLIST_BP(?,?,?,?)}
    ]]>
    </query>
    
    <query id="UIG080010_03.SELECT" desc="外销明细查询操作 " fetchSize="10">
    <![CDATA[
    SELECT 
     '' CHK,       --选择
     T.PACTNO,     --合同号
     T.PLANVEHICLEID,--发运号
     T.LISTNUMBER, -- 明细编号
     T.SERIALNUMBER ,--板坯号
     T.BUTTRESS,     --垛位
     T.PICKSTORAGETIME, --发运时间
     T.PICKSTORAGEMAN, --挑库人
     T.CLASSORDER,    --班次
     T.CLASSTEAM ,    --班组
     T.TRAINWAGON ,   --车辆号
     T.THICK ,    --厚度
     T.WIDTH ,    --宽度
     T.LENGTH ,   --长度
     decode(t.weight,0.000,t1.SLAB_WGT/1000,t.weight) WEIGHT ,   --重量
     T.STOVENO,   --炉号
     T.INCEPTCORPNAME, --收货单位名称
     T.STEEL ,  --牌号
     T.BUYERCODE, --订货单位
     T.GBZL ,     --过磅重量
     T.CXLY,      --产线来源
     T.CCMID   --连铸号
   FROM 
     kcg_turnoffsendlist@xgcx T,tbg02_slab_comm t1
   WHERE 1=1
   AND T.SERIALNUMBER=T1.SLAB_NO(+)
   AND TO_CHAR(T.PICKSTORAGETIME,'yyyymmdd')  LIKE :1||'%'
   AND T.PLANVEHICLEID LIKE :2||'%'
   AND T.TRAINWAGON LIKE :3||'%'
   AND T.SERIALNUMBER LIKE :4||'%'
   AND T.CXLY='4001LGX' 
   --SUBSTR(T.PICKSTORAGETIME,1,10)
    ]]>
    </query>
    <query id="UIG080010_04.SELECT" desc="外销排车计划查询 " fetchSize="10">
    <![CDATA[
       SELECT            '' CHK,
                         B.INCEPTCORPNAME,--收货单位
                         C.STATIONNAME,--到站   
                         A.ASKPLAN_ID,--发运号
                         A.PACTINDEXDETAIL,--计划号
                         A.SENDWEIGHT SENDQUANTITY,--发运车数
                         TO_CHAR(A.PLANDATE, 'YY-MM-DD') PLANDATE,--排车日期
                         A.WEIGHT,--排车量
                         A.STEELCODE,--牌号
                         A.TRANSNO VECIHLENO,--车牌号
                         A.PACTNO,--合同号
                         G.NAME_ TRANSTYPE,--发运类型
                         H.NAME_ TRAINTYPE--运输类型
                         FROM SEL_ASKVEHICLEPLANINFL@xgcx A,
                         SEL_INCEPTCORPINFO@xgcx     B,
                         SEL_STATIONINFO@xgcx        C,
                         SCM_BASE_INFO@xgcx          G,
                         SCM_BASE_INFO@xgcx         H
                         WHERE A.INCEPTCORPCODE = B.INCEPTCORPCODE
                         AND A.STATIONCODE = C.STATIONCODE
                         AND A.CARAFFIRM NOT IN ('4', '5', '0')
                         AND A.TRANSTYPE = G.ID_(+)
                         AND A.TRAINTYPE = H.ID_(+) 
                         AND TO_CHAR(A.PLANDATE, 'yyyymmdd') LIKE :1 ||'%'
                         AND A.PL='4001LGX'
                         ORDER BY ASKPLAN_ID
     ]]>
    </query>
    
    <query id="UIG080010_05.SELECT" desc="外销排车计划查询 " fetchSize="10">
    <![CDATA[
       SELECT            PLANVEHICLEID,
                         PLD,--产线
                         LISTNUMBER,--明细编码   
                         TRAINWAGON,--车号
                         WEIGHT,--磅重
                         MEMO,--退回备注
                         AFFIRM_FLAG --审核状态
                         FROM kc_turnoffsend_apply@xgcx 
                         WHERE PLANVEHICLEID = ?
                         AND LISTNUMBER = ?
                         AND PLD='4001LGX'
                         AND ISVALID = '1'
     ]]>
    </query>
    
    <query id="UIG080010_05.INSERT" desc="外销排车计划查询 " fetchSize="10">
    <![CDATA[
    	insert into kc_turnoffsend_apply@xgcx (
    			PLANVEHICLEID,
    			PLD,
    			LISTNUMBER,
                TRAINWAGON,
                PICKSTORAGEMAN,
                PICKSTORAGETIME,
                WEIGHT,
                MEMO,
                CREATE_MAN,
                CREATE_TIME,
                ISVALID) 
       			  SELECT 
             PLANVEHICLEID,
              CXLY,
              SERIALNUMBER,
              TRAINWAGON,
              PICKSTORAGEMAN,
              PICKSTORAGETIME,
              GBZL,
              ?,
              ?,
              sysdate,
              '1' ISVALID
             FROM kcg_turnoffsendlist@xgcx 
             WHERE SERIALNUMBER = ?
             AND PLANVEHICLEID = ?
             AND CXLY='4001LGX'
     ]]>
    </query>
    
</queryMap>