存储过程样例

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE  [dbo].[SP_TMS_AUTO_BILLING] (
   @V_TMS_ORDER_ID              VARCHAR (32),                    --订单主ID
   @V_RETUREN_VALUE             VARCHAR (20)  output                --状态返回
                                            )
AS
   /***********************************************************************
   **  Stored Procedure Documentation
   **  Stored Procedure Name: SP_TMS_AUTO_BILLING
   **  Creation Date: 2010-3-1
   **  Author: 
   **  Program ID:  
   **  Input Parameters:

   **  Output Parameters:

   **  Return Status Values:
   **  Usage:
   **  Local Variables:
   **  Called By:
   **  Calls:
   **  Data Modifications:
   **  Updates:
   ************************************************************************/
	BEGIN

		DECLARE @V_OPERATION_ITEM VARCHAR (30)     ----订单上的费用协议操作项目
		DECLARE @V_SERVICE_TYPE VARCHAR (50)        ----订单上的服务类型
		DECLARE @V_CUSTOMER_CONTRACT VARCHAR(30)   ----订单上的费用协议号
		DECLARE @V_SERVICE_LEVEL VARCHAR(30)       ----订单上的服务时限
		DECLARE @V_CHARGE_UNIT VARCHAR(20)         ----订单上的计件单位
		DECLARE @V_CARGO_TYPE VARCHAR(50)          ----订单上的货物类型
		DECLARE @V_ESTIMATED_CARGO_PACKAGES NUMERIC   ---订单上的货物件数
		DECLARE @V_ESTIMATED_CARGO_WEIGHT NUMERIC     ---订单上的货物重量
		DECLARE @V_ESTIMATED_CARGO_CUBE NUMERIC       ---订单上的货物体积
		DECLARE @V_BILLING_OFFICE VARCHAR(50)         ---订单上的结算公司
		DECLARE @V_HOME_CURRENCY VARCHAR(3)           ---结算公司本位币
 			

		DECLARE @V_CHARGE_CODE VARCHAR(20)       ---客户计费协议费用项目中的费用代码
		DECLARE @V_CHARGE_ITEM_NAME VARCHAR(50)       ---客户计费协议费用项目中的费用名称
		DECLARE @V_RATES_NUMBER     VARCHAR(20)       ---客户计费协议费用项目中的费率号
		DECLARE @V_FRT_NAME_EN      VARCHAR(50)       ---费用英文名称
		
		DECLARE @V_CUST_OPERATION_ITEM_ID VARCHAR(50)    ----操作项的ID
		DECLARE @V_CUST_CONTRACT_ID VARCHAR(50)          ---计费协议ID
		DECLARE @V_CRM_CUST_ID VARCHAR(50)               ---客户ID

		DECLARE @V_CHARGE_QUANTITY NUMERIC               ----结算总量

		DECLARE @V_UNIT_PRICE NUMERIC                    ----费用单价
		DECLARE @V_CHARGE_CURRENCY VARCHAR(20)           ----费用币别
		DECLARE @V_CUST_CODE          VARCHAR(50)           ----客户代码
		DECLARE @V_EXCHANGE_RATE      NUMERIC            ----汇率

		DECLARE @V_CUST_INNER_OUTER   VARCHAR(50)        ----对内或者对外

		DECLARE @V_MIN_RATES NUMERIC                     ----计费协议里面的最低起运价
		DECLARE @V_AMOUNT NUMERIC                        ----费用表里面的amount
 
		---取去订单下需要的数据
		SELECT @V_SERVICE_TYPE = SERVICE_TYPE, @V_CUSTOMER_CONTRACT = CUSTOMER_CONTRACT, 
			   @V_SERVICE_LEVEL = SERVICE_LEVEL, @V_CHARGE_UNIT = CHARGE_UNIT,
			   @V_CARGO_TYPE = CARGO_TYPE, @V_ESTIMATED_CARGO_PACKAGES =ESTIMATED_CARGO_PACKAGES,
			   @V_ESTIMATED_CARGO_WEIGHT = ESTIMATED_CARGO_WEIGHT, @V_ESTIMATED_CARGO_CUBE = ESTIMATED_CARGO_CUBE,
			   @V_BILLING_OFFICE = BILLING_OFFICE, @V_HOME_CURRENCY = HOME_CURRENCY
		from TMS_ORDER
			LEFT JOIN SYS_OFFICE ON BILLING_OFFICE = OFFICE_CODE
		where TMS_ORDER_ID = @V_TMS_ORDER_ID
		
		---判断费用单位对应的结算总量
		IF(@V_CHARGE_UNIT = '件数')
			set @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_PACKAGES
		ELSE IF(@V_CHARGE_UNIT = '体积')
			SET @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_CUBE
		ELSE
			SET @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_WEIGHT 
		

		----将符合的费用查找出来
		DECLARE
			V_CHARGE_CURSOR CURSOR FOR
			SELECT CRM.CHARGE_CODE, CRM.CHARGE_ITEM_NAME, SFD.FRT_NAME_EN, 
				   CRM.RATES_NUMBER, 
				   CUOI.CUST_OPERATION_ITEM_ID, CUOI.CUST_CONTRACT_ID,
				   CC.CUST_CODE, CCC.CRM_CUST_ID, CC.CUST_INNER_OUTER
			FROM CRM_CUST_CHARGE_ITEM CRM 
			LEFT JOIN CRM_CUST_OPERATION_ITEM CUOI ON CRM.CUST_OPERATION_ITEM_ID = CUOI.CUST_OPERATION_ITEM_ID
			LEFT JOIN CRM_CUST_CONTRACT CCC ON CUOI.CUST_CONTRACT_ID = CCC.CUST_CONTRACT_ID
			LEFT JOIN CRM_CUST CC ON CC.CRM_CUST_ID = CCC.CRM_CUST_ID
			LEFT JOIN SB_FRT_DEF SFD ON SFD.FRT_CODE = CRM.CHARGE_CODE
			LEFT JOIN TMS_OPERATION_ITEM TOI ON CUOI.OPERATION_ITEM=TOI.OPERATION_ITEM
			WHERE CCC.CONTRACT_NUMBER = @V_CUSTOMER_CONTRACT
			AND CUOI.SERVICE_TYPE = @V_SERVICE_TYPE
			AND CUOI.SERVICE_PERIOD = @V_SERVICE_LEVEL
			AND CUOI.CARGO_TYPE = @V_CARGO_TYPE
			AND TOI.TMS_ORDER_ID = @V_TMS_ORDER_ID
			/**
			AND EXISTS (SELECT 1 FROM TMS_OPERATION_ITEM TOI 
							WHERE CUOI.OPERATION_ITEM = TOI.OPERATION_ITEM 
							AND TMS_ORDER_ID = @V_TMS_ORDER_ID
						)
			**/

		OPEN V_CHARGE_CURSOR

		FETCH NEXT FROM V_CHARGE_CURSOR 
				INTO @V_CHARGE_CODE, @V_CHARGE_ITEM_NAME, @V_FRT_NAME_EN, @V_RATES_NUMBER, 
					 @V_CUST_OPERATION_ITEM_ID, @V_CUST_CONTRACT_ID, @V_CUST_CODE, @V_CRM_CUST_ID,
					 @V_CUST_INNER_OUTER
		IF(@@FETCH_STATUS!=0)
			set @V_RETUREN_VALUE = '没有匹配操作项'
		WHILE(@@FETCH_STATUS = 0)
			BEGIN
				
				set @V_RETUREN_VALUE = '自动计费完成'
				---取出费率表中符合条件的记录
				SELECT @V_UNIT_PRICE = UNIT_PRICE, @V_CHARGE_CURRENCY = CHARGE_CURRENCY, @V_MIN_RATES = MIN_RATES
				FROM CRM_CUST_CHARGE_RATE
				WHERE CHARGE_UNIT = @V_CHARGE_UNIT
				AND CUST_CONTRACT_ID = @V_CUST_CONTRACT_ID
				AND RATES_NUMBER = @V_RATES_NUMBER
				AND CHARGE_UNIT_FROM < @V_CHARGE_QUANTITY
				AND CHARGE_UNIT_TO >= @V_CHARGE_QUANTITY
				
				---看是否有对应的协议汇率维护
				IF(@V_UNIT_PRICE IS NULL)
					PRINT 'NO DATA'
				ELSE
					BEGIN
					----查询汇率
					SELECT @V_EXCHANGE_RATE = EXCHANGE_RATE
						from CRM_CUST_EXCHANGERATE
					WHERE CRM_CUST_ID = @V_CRM_CUST_ID
					AND LOCAL_CURRENCY_CODE = @V_HOME_CURRENCY
					AND FOREIGN_CURRENCY_CODE = @V_CHARGE_CURRENCY
					
					---如果不存在取系统中的汇率
					IF(@V_EXCHANGE_RATE IS NULL)
						BEGIN
							IF(@V_CUST_INNER_OUTER = 'INTERIOR')   ----判断是对内的还是对外的汇率
								SELECT @V_EXCHANGE_RATE = RATE_IN
								FROM SB_RATE
								WHERE STANDARD_CUR_CODE = @V_HOME_CURRENCY
								AND ORIGINAL_CUR_CODE = @V_CHARGE_CURRENCY
							ELSE
								SELECT @V_EXCHANGE_RATE = RATE
								FROM SB_RATE
								WHERE STANDARD_CUR_CODE = @V_HOME_CURRENCY
								AND ORIGINAL_CUR_CODE = @V_CHARGE_CURRENCY
						END

					IF(@V_EXCHANGE_RATE IS NULL)
						BEGIN
							PRINT @V_HOME_CURRENCY
							print @V_CHARGE_CURRENCY
							PRINT 'SYS NOT EXCHANGE RATE'
							RETURN 1
						END

					print @V_EXCHANGE_RATE

					----判断费用amount是否比最低起运价低，如果低，就取最低起运价，否则却当前值
					SET @V_AMOUNT = @V_UNIT_PRICE*@V_CHARGE_QUANTITY
					IF(@V_AMOUNT IS NOT NULL AND @V_MIN_RATES IS NOT NULL)
						BEGIN
							IF(@V_AMOUNT<@V_MIN_RATES)
								SET @V_AMOUNT = @V_MIN_RATES
						END						
					
					----插入费用表
					INSERT INTO TMS_FREIGHT(TMS_FREIGHT_ID,
											TMS_ORDER_ID,
											FRT_CODE,
											FRT_NAME,
											FRT_NAME_CN,

											RP_IND,
											UNIT_PRICE,
											CHARGE_QUANTITY,
											CHARGE_UNIT,
											BILLING_STATION,
											
											CUST_CODE,
											CURRENCY,
											EXCHANGE_RATE,
											AMOUNT,
											LOCAL_CURRENCY,

											LOCAL_CURRENCY_AMOUNT,
											IS_SHARE,
											NEED_SHARE,
											IS_AUTO,
											IS_SETTLE,

											IS_REVICED,
											RECORD_VERSION
											
											)
					SELECT	NEWID(),
							@V_TMS_ORDER_ID,
							@V_CHARGE_CODE,
							@V_FRT_NAME_EN,
							@V_CHARGE_ITEM_NAME,

							'0',
							@V_UNIT_PRICE,
							@V_CHARGE_QUANTITY,
							@V_CHARGE_UNIT,
							@V_BILLING_OFFICE,

							@V_CUST_CODE,	
							@V_CHARGE_CURRENCY,
							@V_EXCHANGE_RATE,
							@V_AMOUNT,
							@V_HOME_CURRENCY,

							@V_AMOUNT*@V_EXCHANGE_RATE,
							0,
							0,
							1,
							0,

							0,		
							1
				END
				-----插入表结束		
							

				FETCH NEXT FROM V_CHARGE_CURSOR 
				INTO @V_CHARGE_CODE, @V_CHARGE_ITEM_NAME, @V_FRT_NAME_EN, @V_RATES_NUMBER, 
					 @V_CUST_OPERATION_ITEM_ID, @V_CUST_CONTRACT_ID, @V_CUST_CODE, @V_CRM_CUST_ID,
					 @V_CUST_INNER_OUTER
			END
			CLOSE V_CHARGE_CURSOR
			DEALLOCATE V_CHARGE_CURSOR
						
	END