OA集成K3供应商存储过程逻辑

USE [AIS20191118113253]

GO

/****** Object:   StoredProcedure [dbo].[pro_supplier]    Script Date: 12/20/2019 15:23:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[pro_supplier]

@FParentID int,--供应商分类ID

@FName varchar(20),--供应商名称

@FAddress varchar(20),--供应商地址

@FContact varchar(20),--联系人

@FPhone varchar(20),-- 公司座机

@FMobilePhone varchar(20),--移动联系电话

@FFax   varchar(20),--传真

@FPostalCode   varchar(20),--邮编

@FEmail   varchar(20),--邮件地址

@FBank   varchar(20),--开户银行

@FAccount   varchar(20),--银行账号

@FTaxNum   varchar(20) --税务登记号

as

begin

--第一步：调用函数，生成新的供应商代码

DECLARE @FNumber VARCHAR(20);

DECLARE @FShortNumber VARCHAR(100);

DECLARE @FItemID int;

DECLARE @FLevel varchar(100);

DECLARE @FFullName varchar(100);

select @FNumber=dbo.fn_GetFnumber (@FParentID) from t_Supplier where FParentID=@FParentID

set @FShortNumber=reverse(SUBSTRING(reverse(@FNumber),1,charindex('.',reverse(@FNumber))-1));

select @FLevel = FLevel from t_item where FParentID=@FParentID

IF @FLevel<>2

  select @FFullName = (t2.供应商名称+'_'+t1.供应商名称+'_'+@FName)

  from v_supplier t1

  left join v_supplier   t2 on t1.供应商父级id=t2.供应商id   where t1.供应商id=@FParentID

ELSE

  select @FFullName = (供应商名称+'_'+@FName)

  from v_supplier where 供应商id=@FParentID

--print '6666--'+@FFullName;

--第二步：在t_item表中新增一条数据，将自动生成FItemID

INSERT INTO t_Item

   (FItemClassID,

    FParentID,

    FLevel,

    FName,

    FNumber,

    FShortNumber,

    FFullNumber,

    FFullName,

    FDetail,

    FDeleted)

VALUES (8,@FParentID,2,@FName,@FNumber,@FShortNumber,@FNumber,@FFullName,1,0)

select @FItemID = FItemID from t_Item where FNumber = @FNumber

--第三步：在t_Supplier中新增一条数据，插入时本表FItemID值与前表生成的FItemID值相同

DECLARE @FID varchar(100);

select @FID = max(FID)+1 from t_supplier

INSERT INTO t_Supplier

  (FHelpCode,--默认null

  FShortName,--默认null

  FAddress ,--供应商地址

  FStatus ,--默认值1072

  FRegionID,--默认0

  FTrade,--默认0

  FContact ,--联系人

  FPhone ,-- 联系人座机

  FMobilePhone ,--联系人手机

  FFax,--传真

  FPostalCode ,--邮编

  FEmail ,--邮箱

  FBank   ,--开户行

  FAccount ,--银行账号

  FTaxNum ,--税务登记号

  FValueAddRate ,--增值税率

  FCountry ,--默认null

  FProvinceID ,--默认0

  Fcorperate,--默认null

  FDiscount,--默认' 0 '

  FTypeID,--默认0

  FPOMode,--默认0

  FVMIStockID,--默认0

  FStockIDAssignee,--默认0

  FBr,--默认0

  FRegmark,--默认null

  FLicence,--默认null

  FCyID,--默认0

  FSetID,--默认0

  FAPAccountID,--默认0

  FPreAcctID,--默认0

  FOtherAPAcctID,--默认0

  FPayTaxAcctID,--默认0

  FARAccountID,--默认0

  FPreARAcctID,--默认0

  FOtherARAcctID,--默认0

  FfavorPolicy,--默认null

  Fdepartment,--默认0

  Femployee,--默认0

  FlastTradeAmount,--默认' 0 '

  FlastRPAmount,--默认' 0 '

  FmaxDealAmount,--默认' 0 '

  FminForeReceiveRate,--默认' 1 '

  FCreditDays,--默认0

  FNameEN,--默认null

  FAddrEn,--默认null

  FCIQCode,--默认null

  FRegion,--默认0

  FManageType,--默认0

  FRegsterDate,--默认null

  FAbateDate ,--失效日期

  FSupplyGrade,--默认0

  FSupplyType,--默认0

  FCompanyType,--默认0

  FAutoCreateMR,--默认0

  FAutoValidateOrderFlag,--默认0

  FSupplierCoroutineFlag,--默认0

  FAPFrozenFlag,--默认0

  F_101,--默认null

  FShortNumber ,--默认为供应商代码后3位-短代码

  FNumber,--供应商代码

  FName,-- 供应商名称

  FParentID,--取t_item表的FItemID

  FItemID,

  FID,

  FClassTypeID,

  FBillNo,

  FBiller,

  FBillDate

  )

  VALUES

  (NULL,NULL,@FAddress,1072,0,0,@FContact,@FPhone,@FMobilePhone,@FFax,@FPostalCode,@FEmail,@FBank,@FAccount,@FTaxNum,'17',NULL,0,NULL,' 0 ',0,0,0,0,0,NULL,NULL,0,0,0,0,0,0,0,0,0,NULL,0,0,'0 ','0 ','0 ','1 ',0,NULL,NULL,NULL,0,0,NULL,'2100 - 12 - 31 00 :00 :00 ',0,0,0,0,0,0,0,NULL,@FShortNumber,@FNumber,@FName,@FParentID,@FItemID,@FID,1007301,@FNumber,16719,GETDATE())