| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183 |
- <?xml version="1.0" encoding='UTF-8'?>
- <queryMap desc="">
-
- <query id="UIB040010_01.INITSELECT" desc="" fetchSize="10">
- <![CDATA[
- SELECT 'N' PRINT
- ,'N' CHK
- ,T1.SEND_TP
- ,XB_PACKAGE.GF_CUSTOMER_FIND(T1.CUST_CD) CUST_CD
- ,XB_PACKAGE.GF_CUSTOMER_FIND(T1.ORDCUST_CD) ORDCUST_CD
- ,T1.ORD_NO
- ,T1.ORD_SEQ
- ,XB_PACKAGE.GF_COMNNAME_FIND('A01007', T1.ORD_USE_TP) ORD_USE_TP
- ,T1.SPEC_ABBSYM
- ,T1.SPEC_STL_GRD
- ,T1.SHIP_DIRNO
- ,T1.INV_NO
- ,T1.CRET_NO
- ,T1.CRET_NO_DTIME
- ,T1.MOD_ID
- ,T1.ACC_TP
- ,T1.MOD_DTIME
- ,T1.PRT_NM
- ,T1.TRNF_DTIME
- ,TO_CHAR(SUM_WGT/1000 , 'FM9999.000') SUM_WGT
- ,SUM_CNT
- ,TK.CAR_GP
- ,T2.ORD_DEVLMT_DATE
- FROM TBB03_MS_COM T1 , TBA01_ORD_LINE T2 ,
- (SELECT SUM(T2.PAKG_ACT_WGT) SUM_WGT ,SUM(T2.PAKG_NUM) SUM_CNT ,T2.CRET_NO , T2.CAR_GP FROM TBB03_MS_PAKG T2 /*WHERE T1.CRET_NO = T2.CRET_NO*/ GROUP BY T2.CRET_NO , T2.CAR_GP) TK
- WHERE USED_TP = 'Y' --是否有效
- AND T1.CRET_NO = TK.CRET_NO
- AND T1.ORD_NO = T2.ORD_NO
- AND T1.ORD_SEQ = T2.ORD_SEQ
- AND T1.CRET_NO_DTIME BETWEEN ?||'0000' AND ?||'6000' --质保书生成时间
- AND T1.SHIP_DIRNO = DECODE(? , NULL , T1.SHIP_DIRNO , ?) --发货指示号
- AND T1.ORD_NO = DECODE(? , NULL , T1.ORD_NO , ?) --合同
- AND T1.ACC_TP = DECODE(? , NULL , T1.ACC_TP , ?) --是否审核
- ORDER BY T1.CRET_NO
- ]]>
- </query>
-
- <query id="UIB040010_HEAD.SELECT" desc="质保书表头信息">
- SELECT XB_PACKAGE.GF_CUSTOMER_FIND(T1.CUST_CD) CUST_CD
- ,XB_PACKAGE.GF_CUSTOMER_FIND(T1.ORDCUST_CD) ORDCUST_CD
- ,T1.ORD_NO
- -- ,XB_PACKAGE.GF_COMNNAME_FIND('A01007', T1.ORD_USE_TP) PROC_NM
- ,PKG_QUALITY_COMM.FIB040000('A01007' , T1.ORD_USE_TP , ?) PROC_NM
- ,T1.SPEC_ABBSYM
- ,T1.SPEC_STL_GRD
- ,T1.SHIP_DIRNO
- ,T1.TRNF_DTIME SHIP_CFM_DTIME --发货日期
- ,T1.INV_NO
- ,T2.CNT --总数量
- ,(CASE WHEN GREATEST(T2.WGT/1000 , 1) = 1 THEN 0||T2.WGT/1000
- ELSE TO_CHAR(T2.WGT/1000 , 'FM9999.000') END) WGT --总重量
- ,T1.CRET_NO
- ,T1.CRET_NO_DTIME
- ,T1.MOD_ID USERID
- ,T1.CRET_NO_RMK
- FROM TBB03_MS_COM T1
- ,(SELECT PA.CRET_NO
- ,SUM(PA.PAKG_NUM) CNT
- ,SUM(PA.PAKG_ACT_WGT) WGT
- FROM TBB03_MS_PAKG PA
- GROUP BY PA.CRET_NO
- ) T2
- WHERE T1.CRET_NO = T2.CRET_NO
- AND ROWNUM = 1
- AND T1.CRET_NO = ?
- </query>
-
- <query id="UIB040010_SIZE.SELECT" desc="尺寸信息">
- SELECT
- SUBSTR(PKG.COIL_NO , 1 , 10) COIL_NO
- ,PKG.COIL_PAKG_NO
- ,PKG.CHARGE_NO
- ,COM.SPEC_STL_GRD
- ,PKG.PAKG_THK --厚度
- ,PKG.PAKG_WTH --宽度
- ,PKG.PAKG_LEN --长度
- ,TO_CHAR(PKG.PAKG_ACT_WGT/1000 , 'FM99.000') PAKG_ACT_WGT --重量
- ,PKG.PAKG_NUM --数量
- ,DECODE(PKG.PROC_CD, 'JJ', 'L', 'CO', 'P', '') PROC_TP -- 分析区分
- ,PKG.CAR_GP --车号
- FROM TBB03_MS_COM COM , TBB03_MS_PAKG PKG
- WHERE COM.CRET_NO = PKG.CRET_NO
- AND COM.CRET_NO = ?
- </query>
-
- <query id="UIB040010_CHEM.SELECT" desc="成分">
- SELECT PAK.COIL_PAKG_NO
- ,PAK.CHARGE_NO
- ,REPLACE(CASE WHEN SEQ.COMP_YN = 'N' THEN SEQ.CHEM_CD
- WHEN SEQ.COMP_DETAIL = '复合元素' THEN SEQ.COMP_CAL
- ELSE SEQ.COMP_DETAIL
- END , '=' ) CHEM_CD
- ,WOR.CHEM_VAL
- FROM TBB03_MS_PAKG PAK
- ,TBB02_WORK_INGR WOR
- ,TBB01_SEQ_INGR SEQ
- ,TBB01_ORD_INGR ORD
- ,TBB03_MS_COM MCOM
- WHERE PAK.CRET_NO = ?
- AND PAK.CRET_NO = MCOM.CRET_NO
- AND WOR.CHARGE_NO = PAK.CHARGE_NO
- AND WOR.PROC_CD = PAK.PROC_CD
- AND WOR.CHEM_SEQ = PAK.CHEM_SEQ
- AND WOR.CHEM_CD = SEQ.CHEM_CD
- AND WOR.CHEM_CD = ORD.CHEM_CD
- AND ORD.DSN_KIND = '2' --DECODE(PKG_QUALITY_COMM.FIB010201(MCOM.ORD_NO , MCOM.ORD_SEQ) , '1' , '3' , '2')
- AND ORD.ORD_NO = MCOM.ORD_NO
- AND ORD.ORD_SEQ = MCOM.ORD_SEQ
- ORDER BY PAK.COIL_PAKG_NO , SEQ.COMP_YN , SEQ.DISPLAY_SEQ
- </query>
-
- <query id="UIB040010_SAMPLE.SELECT" desc="性能">
- SELECT PAKG.COIL_PAKG_NO
- ,SUBSTR(PAKG.COIL_NO , 1,10) COIL_NO
- ,PAKG.SMP_NO
- ,PAKG.SMP_CUT_LOC
- ,PAKG.PROCNM_CD MET_DEF ---交货状态
- ,(CASE WHEN WORY.QLTY_VAL_WK = 1 OR WORY.QLTY_UNIT_WK = 'Y' THEN '合格' ELSE TO_CHAR(WORY.QLTY_VAL_WK) END ) QLTY_VAL_WK
- ,DECODE(SUBSTR(SQLY.MS_QLTY_TEM,1,1) ,'0' , '-'||TO_NUMBER(SUBSTR(SQLY.MS_QLTY_TEM,2,3))
- ,'1' , TO_NUMBER(SUBSTR(SQLY.MS_QLTY_TEM,2,3))
- ,'2' , ' ') MS_QLTY_TEM -- 温度
- ,DECODE(SQLY.MS_QLTY_RP , NULL , ' ' , MS_QLTY_RP) MS_QLTY_RP --补充码
- ,(CASE WHEN SUBSTR(SQLY.QLTY_CD,1,3) = 'IMS' THEN 'IS' ELSE SUBSTR(SQLY.QLTY_CD, 1, 2) END) QLTY_CD
- ,SQLY.QLTY_CD_CFNM --中（英）文名称
- ,(CASE WHEN SUBSTR(SQLY.QLTY_CD , 1,2) = 'TE' AND SQLY.MS_QLTY_DIR = 'H' THEN 'T'
- WHEN SUBSTR(SQLY.QLTY_CD , 1,2) = 'TE' AND SQLY.MS_QLTY_DIR = 'Z' THEN 'L'
- WHEN SQLY.MS_QLTY_DIR IS NULL THEN ' '
- ELSE SQLY.MS_QLTY_DIR END) MS_QLTY_DIR
- FROM TBB03_MS_COM MCOM
- ,TBB03_MS_PAKG PAKG
- ,TBB02_WORK_QLTY WORY
- ,TBB01_SEQ_QLTY SQLY
- ,TBB01_ORD_QLTY ORDY
- WHERE MCOM.CRET_NO = PAKG.CRET_NO
- AND PAKG.SMP_NO = WORY.SMP_NO
- AND PAKG.SMP_CUT_LOC = WORY.SMP_CUT_LOC
- AND WORY.QLTY_CD = SQLY.QLTY_CD
- AND ORDY.ORD_NO = MCOM.ORD_NO
- AND ORDY.ORD_SEQ = MCOM.ORD_SEQ
- AND ORDY.DSN_KIND = PKG_QUALITY_COMM.FIB010202(MCOM.ORD_NO , MCOM.ORD_SEQ)
- AND ORDY.QLTY_CD = SQLY.QLTY_CD
- AND ORDY.QLTY_DEC_CD IS NULL
- AND MCOM.CRET_NO = ?
- </query>
-
- <query id="UIB040010_QLTY.SELECT" desc="针对性能类型为A3类型的">
- SELECT PAKG.COIL_PAKG_NO
- ,SUBSTR(REQY.QLTY_CD , 1,2) QLTY_CD
- ,REQY.QLTY_SEQ
- ,REQY.QLTY_VAL_WK
- ,SEQY.MS_QLTY_DIR
- FROM TBB03_MS_COM MCOM
- ,TBB03_MS_PAKG PAKG
- ,TBB02_REWORK_QLTY REQY
- ,TBB01_ORD_QLTY ORDY
- ,TBB01_SEQ_QLTY SEQY
- WHERE MCOM.ORD_NO = ORDY.ORD_NO
- AND MCOM.ORD_SEQ = ORDY.ORD_SEQ
- AND MCOM.QLTY_DSN_KIND = ORDY.DSN_KIND
- AND MCOM.CRET_NO = PAKG.CRET_NO
- AND REQY.QLTY_CD = ORDY.QLTY_CD
- AND REQY.SMP_NO = PAKG.SMP_NO
- AND REQY.SMP_CUT_LOC = PAKG.SMP_CUT_LOC
- AND ORDY.QLTY_TYPE = 'A3'
- AND SEQY.QLTY_CD = REQY.QLTY_CD
- AND MCOM.CRET_NO = ?
- </query>
-
- <query id="UIB040010_01.UPDATE" desc="审核">
- UPDATE TBB03_MS_COM T
- SET T.MOD_ID = ?
- ,T.ACC_TP = 'Y' --'Y'表示通过审核，否则为没有审核
- ,T.MOD_DTIME = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
- WHERE T.CRET_NO = ?
- AND ZL_HOST_DCS03.BANJA_CHECK_BOOK(?) = 'YY'
- AND T.ACC_TP = 'N'
- </query>
- </queryMap>
|
