CREATE procedure [dbo].[eosp_CreateEPOS_CARD](@cC_NO VarChar(30),@cC_DATE DateTime,@sTATUS Char(1),@lAST_PAY_TIME DateTime,@oRDER_NUMBER VarChar(12),@pAN VarChar(19),@eXPIREDDATE Char(10),@cVV2 VarChar(3),@cHMOBILE VarChar(15),@cALLMOBILE VarChar(15),@cHIDNUM NVarChar(30),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_CARD] (CC_NO, CC_DATE, STATUS, LAST_PAY_TIME, ORDER_NUMBER, PAN, EXPIREDDATE, CVV2, CHMOBILE, CALLMOBILE, CHIDNUM, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@cC_NO, @cC_DATE, @sTATUS, @lAST_PAY_TIME, @oRDER_NUMBER, @pAN, @eXPIREDDATE, @cVV2, @cHMOBILE, @cALLMOBILE, @cHIDNUM, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOcreate procedure [dbo].[eosp_CreateEPOS_CHECKACCOUNT_HEAD](@tRANS_ID Int,@s_POSTIME NVarChar(14),@r_POSTIME NVarChar(14),@s_POSID Int,@r_POSID Int,@r_TRANSTIME NVarChar(8),@r_TRANSDATE NVarChar(8),@r_RETCODE NVarChar(7),@s_TERMINALID NVarChar(8),@r_TERMINALID NVarChar(8),@s_MERCHANTID NVarChar(15),@r_MERCHANTID NVarChar(15),@r_MERCHANTNAME NVarChar(40),@s_BATCHNO Int,@r_BATCHNO Int,@s_TERMINALFLAG NChar(1),@r_TERMINALFLAG NChar(1),@r_COMMENTRES NVarChar(100),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_CHECKACCOUNT_HEAD] (TRANS_ID, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_BATCHNO, R_BATCHNO, S_TERMINALFLAG, R_TERMINALFLAG, R_COMMENTRES, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_BATCHNO, @r_BATCHNO, @s_TERMINALFLAG, @r_TERMINALFLAG, @r_COMMENTRES, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOcreate procedure [dbo].[eosp_CreateEPOS_CHECKACCOUNT_LINE](@tRANS_ID Int,@cHECK_ID Int,@lINE_TYPE NChar(1),@pAYMENT_ID Int,@oRDER_NUMBER NVarChar(8),@mERCHANTID NVarChar(15),@tERMINALID NVarChar(8),@bATCHNO Int,@tRANSDATE NVarChar(8),@tRANSTIME NVarChar(8),@pAN NVarChar(19),@tRANSAMOUNT Decimal(13,2),@cURRCODE NVarChar(3),@sYSTEMREFCODE NVarChar(12),@sERIALNO Int,@aUTHORIZECODE NVarChar(6),@pRODUCTCODE NVarChar(6),@dIVIDEDMONTHS NVarChar(2),@rEFUNDFLG NChar(1),@cHECKFLG NChar(1),@fILLER NVarChar(36),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_CHECKACCOUNT_LINE] (TRANS_ID, CHECK_ID, LINE_TYPE, PAYMENT_ID, ORDER_NUMBER, MERCHANTID, TERMINALID, BATCHNO, TRANSDATE, TRANSTIME, PAN, TRANSAMOUNT, CURRCODE, SYSTEMREFCODE, SERIALNO, AUTHORIZECODE, PRODUCTCODE, DIVIDEDMONTHS, REFUNDFLG, CHECKFLG, FILLER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @cHECK_ID, @lINE_TYPE, @pAYMENT_ID, @oRDER_NUMBER, @mERCHANTID, @tERMINALID, @bATCHNO, @tRANSDATE, @tRANSTIME, @pAN, @tRANSAMOUNT, @cURRCODE, @sYSTEMREFCODE, @sERIALNO, @aUTHORIZECODE, @pRODUCTCODE, @dIVIDEDMONTHS, @rEFUNDFLG, @cHECKFLG, @fILLER, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOCREATE procedure [dbo].[eosp_CreateEPOS_TRANS_HEADER](@iP_ADDRESS NVarChar(20),@s_TRANS_DATE DateTime,@tRANS_TYPE NVarChar(50),@rESULT NChar(1),@r_TRANS_DATE DateTime,@eRROR_MSG NVarChar(80),@sXML_BODY VarChar(4000),@rXML_BODY VarChar(4000),@mODULE_NAME NVarChar(50),@fILE_NAME NVarChar(50),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asbegindeclare @sXML_BODYXML xmldeclare @rXML_BODYXML xmlSET @sXML_BODYXML = CONVERT(XML, @sXML_BODY)SET @rXML_BODYXML = CONVERT(XML, @rXML_BODY)insert into [EPOS_TRANS_HEADER] (IP_ADDRESS, S_TRANS_DATE, TRANS_TYPE, RESULT, R_TRANS_DATE, ERROR_MSG, SXML_BODY, RXML_BODY, MODULE_NAME, FILE_NAME, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@iP_ADDRESS, @s_TRANS_DATE, @tRANS_TYPE, @rESULT, @r_TRANS_DATE, @eRROR_MSG, @sXML_BODYXML,@rXML_BODYXML, @mODULE_NAME, @fILE_NAME, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityend GOCREATE procedure [dbo].[eosp_CreateEPOS_TRANS_LOGIN](@tRANS_ID Int,@tRANS_TYPE NVarChar(10),@s_POSTIME NVarChar(14),@r_POSTIME NVarChar(14),@s_POSID Int,@r_POSID NVarChar(6),@r_TRANSTIME NVarChar(8),@r_TRANSDATE NVarChar(8),@r_RETCODE NVarChar(7),@s_TERMINALID NVarChar(8),@r_TERMINALID NVarChar(8),@s_MERCHANTID NVarChar(15),@r_MERCHANTID NVarChar(15),@r_MERCHANTNAME NVarChar(40),@s_PASSWORD NVarChar(8),@s_NEWPASSWORD NVarChar(8),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_TRANS_LOGIN] (TRANS_ID, TRANS_TYPE, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_PASSWORD, S_NEWPASSWORD, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @tRANS_TYPE, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_PASSWORD, @s_NEWPASSWORD, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOcreate procedure [dbo].[eosp_CreateEPOS_TRANS_PAYMENT](@tRANS_ID Int,@oRDER_NUMBER NVarChar(12),@tRANS_TYPE NVarChar(20),@rESULT NChar(1),@rETURN_CONFIRM NChar(1),@rETURN_CON_DATE DateTime,@rETURN_CON_BY NVarChar(30),@s_PAN NVarChar(19),@r_PAN NVarChar(19),@s_PROCESSCODE NVarChar(6),@r_PROCESSCODE NVarChar(6),@s_TRANSAMOUNT Decimal(13,2),@r_TRANSAMOUNT Decimal(13,2),@s_POSTIME NVarChar(14),@r_POSTIME NVarChar(14),@s_POSID Int,@r_POSID NVarChar(6),@r_TRANSTIME NVarChar(8),@r_TRANSDATE NVarChar(8),@s_EXPIREDDATE NVarChar(4),@r_EXPIREDDATE NVarChar(4),@s_AUTHORIZECODE NVarChar(6),@r_AUTHORIZECODE NVarChar(6),@s_ORGPOSID Int,@r_ORGPOSID Int,@r_RETCODE NVarChar(7),@s_TERMINALID NVarChar(8),@r_TERMINALID NVarChar(8),@s_MERCHANTID NVarChar(15),@r_MERCHANTID NVarChar(15),@r_MERCHANTNAME NVarChar(40),@r_COMMENTRES NVarChar(100),@s_CURRCODE NVarChar(3),@r_CURRCODE NVarChar(3),@s_CHIDNUM NVarChar(18),@r_CHIDNUM NVarChar(18),@s_CHMOBILE NVarChar(15),@r_CHMOBILE NVarChar(15),@s_BATCHNO Int,@r_BATCHNO Int,@s_DIVIDEDNUM NVarChar(2),@s_PRODUCTTYPE NVarChar(2),@r_DIVIDEDFEE Decimal(13,2),@r_TOTALAMT Decimal(13,2),@r_DIVIDEDAMT Decimal(13,2),@s_CVV2 NVarChar(3),@r_CVV2 NVarChar(3),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_TRANS_PAYMENT] (TRANS_ID, ORDER_NUMBER, TRANS_TYPE, RESULT, RETURN_CONFIRM, RETURN_CON_DATE, RETURN_CON_BY, S_PAN, R_PAN, S_PROCESSCODE, R_PROCESSCODE, S_TRANSAMOUNT, R_TRANSAMOUNT, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, S_EXPIREDDATE, R_EXPIREDDATE, S_AUTHORIZECODE, R_AUTHORIZECODE, S_ORGPOSID, R_ORGPOSID, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, R_COMMENTRES, S_CURRCODE, R_CURRCODE, S_CHIDNUM, R_CHIDNUM, S_CHMOBILE, R_CHMOBILE, S_BATCHNO, R_BATCHNO, S_DIVIDEDNUM, S_PRODUCTTYPE, R_DIVIDEDFEE, R_TOTALAMT, R_DIVIDEDAMT, S_CVV2, R_CVV2, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @oRDER_NUMBER, @tRANS_TYPE, @rESULT, @rETURN_CONFIRM, @rETURN_CON_DATE, @rETURN_CON_BY, @s_PAN, @r_PAN, @s_PROCESSCODE, @r_PROCESSCODE, @s_TRANSAMOUNT, @r_TRANSAMOUNT, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @s_EXPIREDDATE, @r_EXPIREDDATE, @s_AUTHORIZECODE, @r_AUTHORIZECODE, @s_ORGPOSID, @r_ORGPOSID, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @r_COMMENTRES, @s_CURRCODE, @r_CURRCODE, @s_CHIDNUM, @r_CHIDNUM, @s_CHMOBILE, @r_CHMOBILE, @s_BATCHNO, @r_BATCHNO, @s_DIVIDEDNUM, @s_PRODUCTTYPE, @r_DIVIDEDFEE, @r_TOTALAMT, @r_DIVIDEDAMT, @s_CVV2, @r_CVV2, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOcreate procedure [dbo].[eosp_CreateEPOS_TRANS_SETTLEMENT](@tRANS_ID Int,@s_POSTIME NVarChar(14),@s_POSID Int,@r_POSID NVarChar(6),@r_TRANSTIME NVarChar(8),@r_TRANSDATE NVarChar(8),@r_RETCODE NVarChar(7),@rESULT Char(1),@s_TERMINALID NVarChar(8),@r_TERMINALID NVarChar(8),@s_MERCHANTID NVarChar(15),@r_MERCHANTID NVarChar(15),@r_MERCHANTNAME NVarChar(40),@s_BATCHNO Int,@r_BATCHNO Int,@s_TOTALTRSCNT Int,@r_TOTALTRSCNT Int,@s_SIGN NChar(1),@r_SIGN NChar(1),@s_TRASUMAMT Decimal(13,2),@r_TRASUMAMT Decimal(13,2),@r_SUCCESSFLAG NChar(1),@r_COMMENTRES NVarChar(100),@rESERVED NVarChar(100),@cREATION_DATE DateTime,@cREATED_BY NVarChar(50),@lAST_UPDATE_DATE DateTime,@lAST_UPDATED_BY NVarChar(50))asinsert into [EPOS_TRANS_SETTLEMENT] (TRANS_ID, S_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, RESULT, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_BATCHNO, R_BATCHNO, S_TOTALTRSCNT, R_TOTALTRSCNT, S_SIGN, R_SIGN, S_TRASUMAMT, R_TRASUMAMT, R_SUCCESSFLAG, R_COMMENTRES, RESERVED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @s_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @rESULT, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_BATCHNO, @r_BATCHNO, @s_TOTALTRSCNT, @r_TOTALTRSCNT, @s_SIGN, @r_SIGN, @s_TRASUMAMT, @r_TRASUMAMT, @r_SUCCESSFLAG, @r_COMMENTRES, @rESERVED, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)return @@identityGOcreate procedure [dbo].[eosp_DeleteEPOS_CARD](@cC_NO VarChar(30)) AS DELETE FROM [EPOS_CARD] WHERE CC_NO=@cC_NOGOcreate procedure [dbo].[eosp_DeleteEPOS_CHECKACCOUNT_HEAD](@s_POSID Int) AS DELETE FROM [EPOS_CHECKACCOUNT_HEAD] WHERE S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_DeleteEPOS_CHECKACCOUNT_LINE](@cHECK_ID Int, @lINE_TYPE NChar(1)) AS DELETE FROM [EPOS_CHECKACCOUNT_LINE] WHERE CHECK_ID=@cHECK_ID AND LINE_TYPE=@lINE_TYPEGOcreate procedure [dbo].[eosp_DeleteEPOS_TRANS_HEADER](@tRANS_ID Int) AS DELETE FROM [EPOS_TRANS_HEADER] WHERE TRANS_ID=@tRANS_IDGOcreate procedure [dbo].[eosp_DeleteEPOS_TRANS_LOGIN](@s_POSID Int) AS DELETE FROM [EPOS_TRANS_LOGIN] WHERE S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_DeleteEPOS_TRANS_PAYMENT](@s_POSID Int) AS DELETE FROM [EPOS_TRANS_PAYMENT] WHERE S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_DeleteEPOS_TRANS_SETTLEMENT](@s_POSID Int) AS DELETE FROM [EPOS_TRANS_SETTLEMENT] WHERE S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_UpdateEPOS_CARD](@cC_NO VarChar(30), @cC_DATE DateTime, @sTATUS Char(1), @lAST_PAY_TIME DateTime, @oRDER_NUMBER VarChar(12), @pAN VarChar(19), @eXPIREDDATE Char(10), @cVV2 VarChar(3), @cHMOBILE VarChar(15), @cALLMOBILE VarChar(15), @cHIDNUM NVarChar(30), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asupdate [EPOS_CARD] set CC_DATE=@cC_DATE, STATUS=@sTATUS, LAST_PAY_TIME=@lAST_PAY_TIME, ORDER_NUMBER=@oRDER_NUMBER, PAN=@pAN, EXPIREDDATE=@eXPIREDDATE, CVV2=@cVV2, CHMOBILE=@cHMOBILE, CALLMOBILE=@cALLMOBILE, CHIDNUM=@cHIDNUM, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where CC_NO=@cC_NOGOcreate procedure [dbo].[eosp_UpdateEPOS_CHECKACCOUNT_HEAD](@s_POSID Int, @tRANS_ID Int, @s_POSTIME NVarChar(14), @r_POSTIME NVarChar(14), @r_POSID Int, @r_TRANSTIME NVarChar(8), @r_TRANSDATE NVarChar(8), @r_RETCODE NVarChar(7), @s_TERMINALID NVarChar(8), @r_TERMINALID NVarChar(8), @s_MERCHANTID NVarChar(15), @r_MERCHANTID NVarChar(15), @r_MERCHANTNAME NVarChar(40), @s_BATCHNO Int, @r_BATCHNO Int, @s_TERMINALFLAG NChar(1), @r_TERMINALFLAG NChar(1), @r_COMMENTRES NVarChar(100), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asupdate [EPOS_CHECKACCOUNT_HEAD] set TRANS_ID=@tRANS_ID, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, S_BATCHNO=@s_BATCHNO, R_BATCHNO=@r_BATCHNO, S_TERMINALFLAG=@s_TERMINALFLAG, R_TERMINALFLAG=@r_TERMINALFLAG, R_COMMENTRES=@r_COMMENTRES, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_UpdateEPOS_CHECKACCOUNT_LINE](@cHECK_ID Int, @lINE_TYPE NChar(1), @tRANS_ID Int, @pAYMENT_ID Int, @oRDER_NUMBER NVarChar(8), @mERCHANTID NVarChar(15), @tERMINALID NVarChar(8), @bATCHNO Int, @tRANSDATE NVarChar(8), @tRANSTIME NVarChar(8), @pAN NVarChar(19), @tRANSAMOUNT Decimal(13,2), @cURRCODE NVarChar(3), @sYSTEMREFCODE NVarChar(12), @sERIALNO Int, @aUTHORIZECODE NVarChar(6), @pRODUCTCODE NVarChar(6), @dIVIDEDMONTHS NVarChar(2), @rEFUNDFLG NChar(1), @cHECKFLG NChar(1), @fILLER NVarChar(36), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asupdate [EPOS_CHECKACCOUNT_LINE] set TRANS_ID=@tRANS_ID, PAYMENT_ID=@pAYMENT_ID, ORDER_NUMBER=@oRDER_NUMBER, MERCHANTID=@mERCHANTID, TERMINALID=@tERMINALID, BATCHNO=@bATCHNO, TRANSDATE=@tRANSDATE, TRANSTIME=@tRANSTIME, PAN=@pAN, TRANSAMOUNT=@tRANSAMOUNT, CURRCODE=@cURRCODE, SYSTEMREFCODE=@sYSTEMREFCODE, SERIALNO=@sERIALNO, AUTHORIZECODE=@aUTHORIZECODE, PRODUCTCODE=@pRODUCTCODE, DIVIDEDMONTHS=@dIVIDEDMONTHS, REFUNDFLG=@rEFUNDFLG, CHECKFLG=@cHECKFLG, FILLER=@fILLER, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where CHECK_ID=@cHECK_ID and LINE_TYPE=@lINE_TYPEGOCREATE procedure [dbo].[eosp_UpdateEPOS_TRANS_HEADER](@tRANS_ID Int, @iP_ADDRESS NVarChar(20), @s_TRANS_DATE DateTime, @tRANS_TYPE NVarChar(50), @rESULT NChar(1), @r_TRANS_DATE DateTime, @eRROR_MSG NVarChar(80), @sXML_BODY VarChar(4000), @rXML_BODY VarChar(4000), @mODULE_NAME NVarChar(50), @fILE_NAME NVarChar(50), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asbegindeclare @sXML_BODYXML xmldeclare @rXML_BODYXML xmlSET @sXML_BODYXML = CONVERT(XML, @sXML_BODY)SET @rXML_BODYXML = CONVERT(XML, @rXML_BODY)update [EPOS_TRANS_HEADER] set IP_ADDRESS=@iP_ADDRESS, S_TRANS_DATE=@s_TRANS_DATE, TRANS_TYPE=@tRANS_TYPE, RESULT=@rESULT, R_TRANS_DATE=@r_TRANS_DATE, ERROR_MSG=@eRROR_MSG, SXML_BODY=@sXML_BODYXML, RXML_BODY=@rXML_BODYXML, MODULE_NAME=@mODULE_NAME, FILE_NAME=@fILE_NAME, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where TRANS_ID=@tRANS_IDendGOCREATE procedure [dbo].[eosp_UpdateEPOS_TRANS_LOGIN](@s_POSID Int, @tRANS_ID Int, @tRANS_TYPE NVarChar(10), @s_POSTIME NVarChar(14), @r_POSTIME NVarChar(14), @r_POSID NVarChar(6), @r_TRANSTIME NVarChar(8), @r_TRANSDATE NVarChar(8), @r_RETCODE NVarChar(7), @s_TERMINALID NVarChar(8), @r_TERMINALID NVarChar(8), @s_MERCHANTID NVarChar(15), @r_MERCHANTID NVarChar(15), @r_MERCHANTNAME NVarChar(40), @s_PASSWORD NVarChar(8), @s_NEWPASSWORD NVarChar(8), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asupdate [EPOS_TRANS_LOGIN] set TRANS_ID=@tRANS_ID, TRANS_TYPE=@tRANS_TYPE, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, S_PASSWORD=@s_PASSWORD, S_NEWPASSWORD=@s_NEWPASSWORD, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where S_POSID=@s_POSIDGOcreate procedure [dbo].[eosp_UpdateEPOS_TRANS_PAYMENT](@s_POSID Int, @tRANS_ID Int, @oRDER_NUMBER NVarChar(12), @tRANS_TYPE NVarChar(20), @rESULT NChar(1), @rETURN_CONFIRM NChar(1), @rETURN_CON_DATE DateTime, @rETURN_CON_BY NVarChar(30), @s_PAN NVarChar(19), @r_PAN NVarChar(19), @s_PROCESSCODE NVarChar(6), @r_PROCESSCODE NVarChar(6), @s_TRANSAMOUNT Decimal(13,2), @r_TRANSAMOUNT Decimal(13,2), @s_POSTIME NVarChar(14), @r_POSTIME NVarChar(14), @r_POSID NVarChar(6), @r_TRANSTIME NVarChar(8), @r_TRANSDATE NVarChar(8), @s_EXPIREDDATE NVarChar(4), @r_EXPIREDDATE NVarChar(4), @s_AUTHORIZECODE NVarChar(6), @r_AUTHORIZECODE NVarChar(6), @s_ORGPOSID Int, @r_ORGPOSID Int, @r_RETCODE NVarChar(7), @s_TERMINALID NVarChar(8), @r_TERMINALID NVarChar(8), @s_MERCHANTID NVarChar(15), @r_MERCHANTID NVarChar(15), @r_MERCHANTNAME NVarChar(40), @r_COMMENTRES NVarChar(100), @s_CURRCODE NVarChar(3), @r_CURRCODE NVarChar(3), @s_CHIDNUM NVarChar(18), @r_CHIDNUM NVarChar(18), @s_CHMOBILE NVarChar(15), @r_CHMOBILE NVarChar(15), @s_BATCHNO Int, @r_BATCHNO Int, @s_DIVIDEDNUM NVarChar(2), @s_PRODUCTTYPE NVarChar(2), @r_DIVIDEDFEE Decimal(13,2), @r_TOTALAMT Decimal(13,2), @r_DIVIDEDAMT Decimal(13,2), @s_CVV2 NVarChar(3), @r_CVV2 NVarChar(3), @cREATION_DATE DateTime, @cREATED_BY NVarChar(50), @lAST_UPDATE_DATE DateTime, @lAST_UPDATED_BY NVarChar(50))asupdate [EPOS_TRANS_PAYMENT] set TRANS_ID=@tRANS_ID, ORDER_NUMBER=@oRDER_NUMBER, TRANS_TYPE=@tRANS_TYPE, RESULT=@rESULT, RETURN_CONFIRM=@rETURN_CONFIRM, RETURN_CON_DATE=@rETURN_CON_DATE, RETURN_CON_BY=@rETURN_CON_BY, S_PAN=@s_PAN, R_PAN=@r_PAN, S_PROCESSCODE=@s_PROCESSCODE, R_PROCESSCODE=@r_PROCESSCODE, S_TRANSAMOUNT=@s_TRANSAMOUNT, R_TRANSAMOUNT=@r_TRANSAMOUNT, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, S_EXPIREDDATE=@s_EXPIREDDATE, R_EXPIREDDATE=@r_EXPIREDDATE, S_AUTHORIZECODE=@s_AUTHORIZECODE, R_AUTHORIZECODE=@r_AUTHORIZECODE, S_ORGPOSID=@s_ORGPOSID, R_ORGPOSID=@r_ORGPOSID, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, R_COMMENTRES=@r_COMMENTRES, S_CURRCODE=@s_CURRCODE, R_CURRCODE=@r_CURRCODE, S_CHIDNUM=@s_CHIDNUM, R_CHIDNUM=@r_CHIDNUM, S_CHMOBILE=@s_CHMOBILE, R_CHMOBILE=@r_CHMOBILE, S_BATCHNO=@s_BATCHNO, R_BATCHNO=@r_BATCHNO, S_DIVIDEDNUM=@s_DIVIDEDNUM, S_PRODUCTTYPE=@s_PRODUCTTYPE, R_DIVIDEDFEE=@r_DIVIDEDFEE, R_TOTALAMT=@r_TOTALAMT, R_DIVIDEDAMT=@r_DIVIDEDAMT, S_CVV2=@s_CVV2, R_CVV2=@r_CVV2, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY where S_POSID=@s_POSIDGOCREATE PROCEDURE [dbo].[usp_GetPaymentOrder]-- Add the parameters for the stored procedure here@orderNOnvarchar(50),@dateFromdatetime,@dateTodatetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SELECT dbo.EPOS_CARD.ROW_ID, dbo.EPOS_CARD.CC_NO, dbo.EPOS_CARD.CC_DATE, dbo.EPOS_CARD.STATUS, dbo.EPOS_CARD.LAST_PAY_TIME, dbo.EPOS_CARD.ORDER_NUMBER, dbo.EPOS_CARD.PAN, dbo.EPOS_CARD.EXPIREDDATE, dbo.EPOS_CARD.CVV2, dbo.EPOS_CARD.CHMOBILE, dbo.EPOS_CARD.CALLMOBILE, dbo.EPOS_CARD.CHIDNUM, dbo.EPOS_CARD.CREATION_DATE, dbo.EPOS_CARD.CREATED_BY, dbo.EPOS_CARD.LAST_UPDATE_DATE, dbo.EPOS_CARD.LAST_UPDATED_BY,dbo.OrderH.hCreateDate,dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwner,dbo.OrderCredit.ocCardModthFROM dbo.EPOS_CARD INNER JOIN dbo.OrderH ON dbo.EPOS_CARD.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo WHERE (dbo.EPOS_CARD.STATUS = 'W') AND (dbo.OrderH.hStatus = 'WC') and(dbo.OrderCredit.ocActive = 'NEED_POS') AND (dbo.OrderCredit.ocAttribute1 <> 'D') AND (dbo.OrderH.hCustID IN (select pValue from parameter where pActive='Y' and pName='EPOS_CUST_ID'))And (dbo.OrderH.hSO = @orderNO or @orderNO='' ) and dbo.OrderH.hCreateDate>=@dateFromand dbo.OrderH.hCreateDate<@dateToENDGOCREATE PROCEDURE [dbo].[usp_GetPaymentOrderByRowID]@rowIDintASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SELECT dbo.EPOS_CARD.ROW_ID, dbo.EPOS_CARD.CC_NO, dbo.EPOS_CARD.CC_DATE, dbo.EPOS_CARD.STATUS, dbo.EPOS_CARD.LAST_PAY_TIME, dbo.EPOS_CARD.ORDER_NUMBER, dbo.EPOS_CARD.PAN, dbo.EPOS_CARD.EXPIREDDATE, dbo.EPOS_CARD.CVV2, dbo.EPOS_CARD.CHMOBILE, dbo.EPOS_CARD.CALLMOBILE, dbo.EPOS_CARD.CHIDNUM, dbo.EPOS_CARD.CREATION_DATE, dbo.EPOS_CARD.CREATED_BY, dbo.EPOS_CARD.LAST_UPDATE_DATE, dbo.EPOS_CARD.LAST_UPDATED_BY,dbo.OrderH.hCreateDate,dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwner,dbo.OrderCredit.ocCardModthFROM dbo.EPOS_CARD INNER JOIN dbo.OrderH ON dbo.EPOS_CARD.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo WHERE (dbo.EPOS_CARD.ROW_ID = @RowID)ENDGOCREATE PROCEDURE [dbo].[usp_GetReversalPayment]@orderNOnvarchar(50),@dateFromnvarchar(20),@dateTonvarchar(20)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SELECT dbo.EPOS_TRANS_PAYMENT.ROW_ID, dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER, dbo.EPOS_TRANS_PAYMENT.S_PAN,dbo.EPOS_TRANS_PAYMENT.S_TRANSAMOUNT,dbo.EPOS_TRANS_PAYMENT.S_EXPIREDDATE,dbo.EPOS_TRANS_PAYMENT.S_POSID,dbo.EPOS_TRANS_PAYMENT.S_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_CHMOBILE,dbo.EPOS_TRANS_PAYMENT.S_DIVIDEDNUM,dbo.OrderH.hCreateDate, dbo.OrderCredit.ocAttribute11, dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwnerFROM dbo.EPOS_TRANS_PAYMENT INNER JOINdbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSO INNER JOINdbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSoWHERE (dbo.OrderH.hStatus = 'WC') AND (dbo.OrderCredit.ocActive = 'NEED_SETTLE') AND (dbo.EPOS_TRANS_PAYMENT.TRANS_TYPE = 'PAYMENT') AND (dbo.EPOS_TRANS_PAYMENT.RESULT = 'S') AND (dbo.OrderCredit.ocAttribute1 <> 'D') and (dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = @orderNO or @orderNO = '')and dbo.OrderCredit.ocAttribute11<@dateTo and dbo.OrderCredit.ocAttribute11>=@dateFromENDGOCREATE PROCEDURE [dbo].[usp_GetReversalPaymentByRowID]@rowID intASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT dbo.EPOS_TRANS_PAYMENT.ROW_ID, dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER, dbo.EPOS_TRANS_PAYMENT.S_PAN,dbo.EPOS_TRANS_PAYMENT.S_TRANSAMOUNT,dbo.EPOS_TRANS_PAYMENT.S_EXPIREDDATE,dbo.EPOS_TRANS_PAYMENT.S_POSID,dbo.EPOS_TRANS_PAYMENT.S_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_CHMOBILE,dbo.EPOS_TRANS_PAYMENT.S_DIVIDEDNUM,dbo.EPOS_TRANS_PAYMENT.S_CVV2,dbo.EPOS_TRANS_PAYMENT.R_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_AUTHORIZECODE,dbo.EPOS_TRANS_PAYMENT.R_AUTHORIZECODE,dbo.EPOS_TRANS_PAYMENT.S_BATCHNO,dbo.OrderH.hCreateDate, dbo.OrderCredit.ocAttribute11, dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwnerFROM dbo.EPOS_TRANS_PAYMENT INNER JOINdbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSO INNER JOINdbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSoWHERE dbo.EPOS_TRANS_PAYMENT.ROW_ID=@rowIDENDGOCREATE PROCEDURE [dbo].[usp_CheckReversalPayment]@rowID int,@resultMsgnvarchar(100) outputASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @OrderNumber nvarchar(50)declare @CREATION_DATE nvarchar(50)select @OrderNumber = ORDER_NUMBER from EPOS_TRANS_PAYMENT where ROW_ID=@rowIDselect @CREATION_DATE = CREATION_DATE from EPOS_TRANS_PAYMENT where TRANS_TYPE = 'REVERSAL' and RESULT ='S'and ORDER_NUMBER = @OrderNumberif @CREATION_DATE is not nullset @resultMsg = '订单'+@OrderNumber+'在'+ +'时间已成功取消刷卡，请选择其他记录取消刷卡。'elseset @resultMsg = ''ENDGOCREATE PROCEDURE [dbo].[usp_GetSettledPayment]@batchNo nvarchar(50),@beginDate nvarchar(20),@endDate nvarchar(20),@successFlag char(1)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT S_BATCHNO, S_TOTALTRSCNT, S_TRASUMAMT, S_POSTIME, R_SUCCESSFLAG,R_COMMENTRESFROM dbo.EPOS_TRANS_SETTLEMENTwhere (S_BATCHNO=@batchNo or @batchNo='') and (R_SUCCESSFLAG=@successFlag or @successFlag='') and cast(left(S_POSTIME,8) as datetime) >=@beginDate andcast(left(S_POSTIME,8) as datetime) <@endDateENDGOCREATE PROCEDURE [dbo].[usp_GetSettlePayment]@batchNO nvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT S_BATCHNO,COUNT(S_BATCHNO) AS S_TOTALTRSCNT, SUM(S_TRANSAMOUNT) AS S_TRASUMAMT,'' AS S_POSTIME, '' AS R_SUCCESSFLAG, '' AS R_COMMENTRESfrom (select S_BATCHNO, case TRANS_TYPE WHEN 'REVERSAL' THEN -S_TRANSAMOUNT ELSE S_TRANSAMOUNT END AS S_TRANSAMOUNTfrom EPOS_TRANS_PAYMENTWHERE (RESULT = 'S') and (S_BATCHNO=@batchNO or @batchNO='') and S_BATCHNO not in (select S_BATCHNO from EPOS_TRANS_SETTLEMENT)) as subtableGROUP BY S_BATCHNOENDGOCREATE PROCEDURE [dbo].[usp_GetSettlePaymentDetail]@batchNO nvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT dbo.EPOS_TRANS_PAYMENT.R_BATCHNO, dbo.EPOS_TRANS_PAYMENT.TRANS_TYPE, dbo.OrderH.hSO, dbo.OrderH.hCreateDate, dbo.OrderH.hSum, dbo.EPOS_TRANS_PAYMENT.R_POSTIMEFROM dbo.EPOS_TRANS_PAYMENT INNER JOINdbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSOWHERE dbo.EPOS_TRANS_PAYMENT.R_BATCHNO = @batchNOENDGOCREATE PROCEDURE [dbo].[usp_GetCheckAccount]@batchNo nvarchar(50),@beginDate nvarchar(20),@endDate nvarchar(20)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT S_BATCHNO, S_TOTALTRSCNT, S_TRASUMAMT, S_POSTIME, R_SUCCESSFLAG,R_COMMENTRESFROM dbo.EPOS_TRANS_SETTLEMENTwhere R_SUCCESSFLAG='C' AND (S_BATCHNO=@batchNo or @batchNo='') and cast(left(S_POSTIME,8) as datetime) >=@beginDate andcast(left(S_POSTIME,8) as datetime) <@endDateENDGOCREATE PROCEDURE [dbo].[usp_GetCheckAccountDetail]@batchNo nvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT TRANS_ID, ORDER_NUMBER,S_BATCHNO, R_TRANSDATE, R_TRANSTIME, S_PAN, R_TRANSAMOUNT, R_CURRCODE, S_DIVIDEDNUM, CASE TRANS_TYPE WHEN 'RETURN' THEN 'Y' ELSE 'N' END AS TRANS_TYPE,S_TERMINALID, S_MERCHANTID,S_AUTHORIZECODE,S_POSID,R_AUTHORIZECODEFROM dbo.EPOS_TRANS_PAYMENTWHERE S_BATCHNO=@batchNoorder by TRANS_IDENDGOCREATE PROCEDURE [dbo].[usp_GetCheckAccountViewDetail] @batchNo nvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;SELECT 0 as [NO],dbo.EPOS_CHECKACCOUNT_HEAD.CHECK_ID, dbo.EPOS_CHECKACCOUNT_HEAD.S_BATCHNO, dbo.EPOS_CHECKACCOUNT_HEAD.R_RETCODE, dbo.EPOS_CHECKACCOUNT_LINE.ORDER_NUMBER, dbo.EPOS_CHECKACCOUNT_LINE.MERCHANTID, dbo.EPOS_CHECKACCOUNT_LINE.TERMINALID, dbo.EPOS_CHECKACCOUNT_LINE.TRANSDATE, dbo.EPOS_CHECKACCOUNT_LINE.TRANSTIME, dbo.EPOS_CHECKACCOUNT_LINE.PAN, dbo.EPOS_CHECKACCOUNT_LINE.TRANSAMOUNT, dbo.EPOS_CHECKACCOUNT_LINE.CURRCODE, dbo.EPOS_CHECKACCOUNT_LINE.SYSTEMREFCODE, dbo.EPOS_CHECKACCOUNT_LINE.SERIALNO, dbo.EPOS_CHECKACCOUNT_LINE.AUTHORIZECODE, dbo.EPOS_CHECKACCOUNT_LINE.PRODUCTCODE, dbo.EPOS_CHECKACCOUNT_LINE.DIVIDEDMONTHS, dbo.EPOS_CHECKACCOUNT_LINE.REFUNDFLG, dbo.EPOS_CHECKACCOUNT_LINE.CHECKFLG, dbo.EPOS_CHECKACCOUNT_LINE.FILLERFROM dbo.EPOS_CHECKACCOUNT_HEAD INNER JOIN dbo.EPOS_CHECKACCOUNT_LINE ON dbo.EPOS_CHECKACCOUNT_HEAD.CHECK_ID = dbo.EPOS_CHECKACCOUNT_LINE.CHECK_IDWHERE (dbo.EPOS_CHECKACCOUNT_HEAD.S_BATCHNO = @batchNo)ENDGOcreate procedure [dbo].[usp_CreateImportEPOS_CARD](@CREATED_BY VarChar(50))asDECLARE @DateFrom varchar(10)DECLARE @DateTo varchar(10)DECLARE @CallID varchar(20)DECLARE @CallerID varchar(20)DECLARE @CalleeID varchar(20) DECLARE @CardNO varchar(50)DECLARE @CertificateNO varchar(50)DECLARE @UsefulLife varchar(50)DECLARE @CVV2 varchar(50) DECLARE @RegisterPhone varchar(50) DECLARE @OrderNO varchar(50)DECLARE @Create_Date datetime DECLARE @STATUS varchar(1)DECLARE @tempEPOS_CARD table(CallID varchar(32) NOT NULL,CallerID varchar(20) NOT NULL DEFAULT (''),CalleeID varchar(20) NOT NULL DEFAULT (''),CardNO varchar(50) NOT NULL DEFAULT (''),CardType varchar(50) NOT NULL DEFAULT (''),CertificateNO varchar(50) NOT NULL DEFAULT (''),CertificateType varchar(50) NOT NULL DEFAULT (''),UsefulLife varchar(50) NOT NULL DEFAULT (''),CVV2 varchar(50) NOT NULL DEFAULT (''),RegisterPhone varchar(50) NOT NULL DEFAULT (''),OrderNO varchar(50) NOT NULL DEFAULT (''),Create_Date datetime NOT NULL DEFAULT (getdate()))--SET @CREATED_BY='Robbie'SET @DateFrom=convert(varchar(10),DATEADD(month,-1,getdate()),121)SET @DateTo=convert(varchar(10),getdate(),121)--PRINT @DateFrom--PRINT @DateTo--将最近一个月的数据放入临时表INSERT INTO @tempEPOS_CARD EXEC CCDB..usp_QueryCreditCard_IVRToB2C_ePOSDECLARE MyCursor CURSOR FORSELECT CallID,CallerID,CalleeID,CardNO,CertificateNO,UsefulLife,CVV2,RegisterPhone,OrderNO,Create_Date FROM @tempEPOS_CARD WHERE Create_Date>=@DateFrom and Create_Date<=@DateTo OPEN MyCursorFETCH NEXT FROM MyCursorINTO @CallID,@CallerID,@CalleeID,@CardNO,@CertificateNO,@UsefulLife,@CVV2,@RegisterPhone,@OrderNO,@Create_Date WHILE @@FETCH_STATUS = 0BEGIN--CallID CC_NO Call NO --Create_Date CC_DATE CallCenter呼入时间 --OrderNO ORDER_NUMBER DMS订单号码 --CardNO PAN 信用卡号码 --UsefulLife EXPIREDDATE 有效期 --CVV2 CVV2 CVV2 --RegisterPhone CHMOBILE 注册号码 --CallerID CALLMOBILE 主叫号码 --CertificateNO CHIDNUM 证件号码 --将一个月来没有导过的call信息 INSERT INTO EPOS_CARD--并检查每个新导入的call信息中的订单之前是否有成功刷卡的记录,--如有 本条新增记录的status置为’ F’;否则检查之前是否有待刷卡的记录,--如有则将之前该订单的status置为’D’IF NOT exists(SELECT 1 FROM EPOS_CARD WHERE CC_NO=@CallID)BEGIN SET @STATUS='W'IF exists(SELECT 1 FROM EPOS_CARD WHERE ORDER_NUMBER=@OrderNO and STATUS='S') BEGINSET @STATUS='F'END ELSE IF exists(SELECT 1 FROM EPOS_CARD WHERE ORDER_NUMBER=@OrderNO and STATUS='W')BEGINUPDATE EPOS_CARD SET STATUS='D' WHERE ORDER_NUMBER=@OrderNO and STATUS='W'END ELSEBEGIN PRINT ' 'END INSERT INTO EPOS_CARD(CC_NO,CC_DATE,[STATUS],LAST_PAY_TIME,ORDER_NUMBER, PAN,EXPIREDDATE,CVV2,CHMOBILE,CALLMOBILE, CHIDNUM,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY)VALUES(@CallID,@Create_Date,@STATUS,'1900-01-01',@OrderNO, @CardNO,@UsefulLife,@CVV2,@RegisterPhone,@CallerID, @CertificateNO,getdate(),@CREATED_BY,getdate(),@CREATED_BY)END --PRINT 'Contact Name: ' + @CallerID + ' ' + @CalleeIDFETCH NEXT FROM MyCursorINTO @CallID,@CallerID,@CalleeID,@CardNO,@CertificateNO, @UsefulLife,@CVV2,@RegisterPhone,@OrderNO, @Create_Date ENDCLOSE MyCursorDEALLOCATE MyCursorGOCREATE PROCEDURE [dbo].[usp_CheckEPOS_CARDPaymentReturn]@rowID int,@resultMsgnvarchar(200) outputASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @OrderNumber varchar(50)declare @CREATION_DATE varchar(50)declare @CANCEL_DATE varchar(50)declare @BatchNo varchar(50)set @resultMsg =''SELECT @OrderNumber = ORDER_NUMBER,@BatchNo=S_BATCHNOFROM EPOS_TRANS_PAYMENT WHERE ROW_ID=@rowIDSELECT @CREATION_DATE = CREATION_DATE FROM EPOS_TRANS_PAYMENT WHERE TRANS_TYPE='RETURN' AND RESULT='S' AND ORDER_NUMBER =@OrderNumberif @CREATION_DATE is not null or @CREATION_DATE=''set @resultMsg = '订单'+@OrderNumber+'在'+ @CREATION_DATE +'时间已成功取消刷卡，请选择其他记录取消刷卡。'elseBEGIN SELECT @CANCEL_DATE =S_POSTIME FROM EPOS_TRANS_PAYMENT WHERE TRANS_TYPE= 'REVERSAL' AND RESULT='S' AND ORDER_NUMBER = @OrderNumberif @CANCEL_DATE is not null or @CANCEL_DATE=''set @resultMsg = '订单'+@OrderNumber+'在'+ @CREATION_DATE +'时间已成功取消刷卡，请选择其他记录取消刷卡。'elseBEGINif exists(SELECT 1 FROM EPOS_TRANS_PAYMENT WHERE TRANS_TYPE='PAYMENT' AND RESULT='S' AND ORDER_NUMBER =@OrderNumber)BEGIN if not exists(SELECT 1 FROM EPOS_TRANS_SETTLEMENT WHERE S_BATCHNO=@BatchNo AND RESULT='S')set @resultMsg = '订单'+@OrderNumber+'在'+ +'刷卡还未结算,请选择其它记录退款。'ENDelseset @resultMsg = '订单'+@OrderNumber+'在'+ +'未做过刷卡,请选择其它记录退款。'ENDENDENDGOCREATE PROCEDURE [dbo].[usp_GetEPOS_CARDQuery]@OrderNovarchar(50), @CardStautsvarchar(50), @DateFromvarchar(50), @DateEndvarchar(50)ASBEGINdeclare @exeSql varchar(8000)--W: 等待刷卡;--S: 刷卡成功;--E: 刷卡失败;--D: 信息失效(未刷卡)--F: 已刷卡set @exeSql = 'SELECT EPOS_CARD.CC_NO,EPOS_CARD.CC_DATE,EPOS_CARD.STATUS,PARAMETER.pDescription AS STATUS_CN, EPOS_CARD.LAST_PAY_TIME,EPOS_CARD.ORDER_NUMBER,EPOS_CARD.PAN,EPOS_CARD.EXPIREDDATE,EPOS_CARD.CVV2, EPOS_CARD.CHMOBILE,EPOS_CARD.CALLMOBILE,EPOS_CARD.CHIDNUM,EPOS_CARD.CREATION_DATE,EPOS_CARD.CREATED_BY, EPOS_CARD.LAST_UPDATE_DATE,EPOS_CARD.LAST_UPDATED_BY FROM EPOS_CARD LEFT JOIN PARAMETER ON EPOS_CARD.STATUS=PARAMETER.pValueWHERE PARAMETER.pName=''EPOS_CARD_STATUS'' 'if(@OrderNo<>'')set @exeSql=@exeSql+' AND ORDER_NUMBER='''+@OrderNo+''''if(@CardStauts<>'')set @exeSql=@exeSql+' AND STATUS='''+@CardStauts+''''if(@DateFrom<>'')set @exeSql=@exeSql+' AND CC_DATE>=cast('''+@DateFrom+''' as datetime)'if(@DateEnd<>'')set @exeSql=@exeSql+' AND CC_DATE<cast('''+@DateEnd+''' as datetime)'print @exeSqlexec(@exeSql)ENDGOCREATE PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnByPosID]@PosID intASBEGINdeclare @exeSql varchar(8000)set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner, Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO, Credit.ocAttribute13,PAY.RETURN_CONFIRM,PAY.RETURN_CON_DATE, PAY.S_PAN,PAY.S_EXPIREDDATE,PAY.S_CHIDNUM,PAY.S_CHMOBILE,PAY.S_CVV2, PAY.S_AUTHORIZECODE,PAY.R_AUTHORIZECODE,PAY.S_TRANSAMOUNT,Credit.ocCardModth FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit WHERE PAY.ORDER_NUMBER=ORD.HSO AND PAY.ORDER_NUMBER =Credit.ocSO AND Credit.OCATTRIBUTE1<>''D'' AND (PAY.S_BATCHNO IN(SELECT S_BATCHNO FROM EPOS_TRANS_SETTLEMENT WHERE S_TRASUMAMT>0 AND RESULT=''S'') )'if(@PosID>0) set @exeSql=@exeSql+' AND PAY.S_POSID =' + cast(@PosID as varchar)--print @exeSqlexec(@exeSql)END GOCREATE PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnConfirmQuery]@OrderNovarchar(50),@DateFromvarchar(10),@DateEnd varchar(10),@ConfirmStatus varchar(50)ASBEGIN/*@OrderNovarchar(50), -- 订单号@DateFromvarchar(10), -- 退款开始日期@Datend varchar(10), -- 退款结束日期 @ConfirmStatus varchar(50) -- 确认状态*/declare @exeSql varchar(8000)set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner, Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO, Credit.ocAttribute13, case PAY.RETURN_CONFIRM when ''Y'' then ''已确认'' else ''未确认'' end as RETURN_CONFIRM, PAY.RETURN_CON_DATE FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit WHERE PAY.ORDER_NUMBER=ORD.HSO AND PAY.ORDER_NUMBER =Credit.ocSO AND PAY.TRANS_TYPE=''RETURN'' AND PAY.RESULT=''S'' AND Credit.OCATTRIBUTE1<>''D'' 'if(@OrderNo<>'')set @exeSql=@exeSql+' AND PAY.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''if(@DateFrom<>'')set @exeSql=@exeSql+' AND cast(Credit.ocAttribute13 as datetime)>=cast('''+ @DateFrom + ''' as datetime)'if(@DateEnd<>'')set @exeSql=@exeSql+' AND cast(Credit.ocAttribute13 as datetime)<cast('''+ @DateEnd + ''' as datetime)'if(@ConfirmStatus<>'')set @exeSql=@exeSql+' AND PAY.RETURN_CONFIRM='''+@ConfirmStatus+''''--print @exeSqlexec(@exeSql)END GOCREATE PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnQuery]@OrderNovarchar(50),@DateFromvarchar(10),@DateEndvarchar(10),@ocActive varchar(50)ASBEGIN/*@OrderNovarchar(50), -- 订单号@DateFromvarchar(10), -- 结算开始日期@Datend varchar(10), -- 结算结束日期 @ocActive varchar(50) -- 结算状态*/declare @exeSql varchar(8000)set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner, Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO, Credit.ocAttribute13,PAY.RETURN_CONFIRM,PAY.RETURN_CON_DATE FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit WHERE PAY.ORDER_NUMBER=ORD.HSO AND PAY.ORDER_NUMBER =Credit.ocSO AND Credit.OCATTRIBUTE1<>''D'' AND (PAY.S_BATCHNO IN(SELECT S_BATCHNO FROM EPOS_TRANS_SETTLEMENT WHERE S_TRASUMAMT>0 AND RESULT=''S'') )'--AND ORD.hstatus in(''WM'',''NS'',''PS'') if(@OrderNo<>'')set @exeSql=@exeSql+' AND PAY.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''if(@DateFrom<>'')set @exeSql=@exeSql+' AND cast(Credit.ocAttribute14 as datetime)>=cast('''+ @DateFrom + ''' as datetime)'if(@DateEnd<>'')set @exeSql=@exeSql+' AND cast(Credit.ocAttribute14 as datetime)<cast('''+ @DateEnd + ''' as datetime)'if(@ocActive<>'')set @exeSql=@exeSql+' AND Credit.ocActive='''+@ocActive+''''--print @exeSqlexec(@exeSql)END GOCREATE PROCEDURE [dbo].[usp_GetEPOS_TRANSQuery]@OrderNovarchar(50),@DateFromvarchar(50),@DateEndvarchar(50),@BacthNovarchar(50),@TransTypevarchar(50),@TransStatusvarchar(50)ASBEGINdeclare @exeSql varchar(8000)--W: 等待刷卡;--S: 刷卡成功;--E: 刷卡失败;--D: 信息失效(未刷卡)--F: 已刷卡set @exeSql = 'SELECT OrderH.HSO,OrderH.hCreateDate,OrderCredit.ocCardOwner, EPOS_TRANS_PAYMENT.S_POSTIME,EPOS_TRANS_PAYMENT.TRANS_TYPE, PARAMETER.pDescription AS TRANS_TYPE_CN, EPOS_TRANS_PAYMENT.RESULT, CASE EPOS_TRANS_PAYMENT.RESULT WHEN ''E'' THEN ''错误'' WHEN ''S'' THEN ''成功'' ELSE '''' END as RESULT_CN, EPOS_TRANS_PAYMENT.S_BATCHNO FROM OrderH inner join OrderCredit ON OrderH.HSO =OrderCredit.ocSO inner join EPOS_TRANS_PAYMENT ON EPOS_TRANS_PAYMENT.ORDER_NUMBER = OrderH.HSO LEFT JOIN PARAMETER ON EPOS_TRANS_PAYMENT.TRANS_TYPE=PARAMETER.pValueWHERE PARAMETER.pName=''EPOS_TRANS_TYPE'' 'if(@OrderNo<>'')set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''if(@DateFrom<>'')set @exeSql=@exeSql+' AND cast(EPOS_TRANS_PAYMENT.S_POSTIME as datetime)>=cast('''+ @DateFrom+''' as datetime) 'if(@DateEnd<>'')set @exeSql=@exeSql+' AND cast(EPOS_TRANS_PAYMENT.S_POSTIME as datetime)<cast('''+ @DateEnd+''' as datetime)'if(@BacthNo<>'')set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.S_BATCHNO LIKE ''%'+@BacthNo+'%'''if(@TransType<>'')set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.TRANS_TYPE='''+@TransType+''''if(@TransStatus<>'')set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.RESULT='''+@TransStatus+''''--print @exeSqlexec(@exeSql)ENDGOCREATE PROCEDURE [dbo].[usp_UpdateConfirmEPOS_TRANS_PAYMENT](@sPOSID Int, @sRETURN_CONFIRM varchar(50),@sRETURN_CON_BY NVarChar(50))asupdate [EPOS_TRANS_PAYMENT] set RETURN_CONFIRM=@sRETURN_CONFIRM, RETURN_CON_BY=@sRETURN_CON_BY,RETURN_CON_DATE=getdate() where S_POSID=@sPOSIDGO
