新增文章

文章标题分类内容

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】 删除

if exists (select * from sys.views where object_id = OBJECT_ID(N'dbo.a_ICClassLinkEntry'))
              drop     view                                         dbo.a_ICClassLinkEntry
   go

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】a_ICClassLinkEntry 新单 下推字段

create view dbo.a_ICClassLinkEntry
   as 
   select  
       b1.FName_CHS  FSourClassType
      ,b2.FName_CHS  FDestClassType
      
      ,(case when a1.FSourPage = isnull(a2.FPage,'') then '' else convert(varchar(10),isnull(a2.FPage,'')) end) FSrPage
      ,isnull(a2.FCaption_CHS,'')  FSrCaption
      
      ,isnull(a2.FTableName,'') +'.'+ isnull(a2.FFieldName,'')  FSrField
      
      ,dbo.aBillNo( convert(varchar(10),isnull(a2.FPage,''))+'.' ,'0' ,2 ,isnull(a2.FTabIndex,'') )  FSrTab
      
      ,(case when a1.FDestPage = isnull(a3.FPage,'') then '' else convert(varchar(10),isnull(a3.FPage,'')) end) FDePage
      ,isnull(a3.FCaption_CHS,'')  FDeCaption
      
      ,isnull(a3.FTableName,'') +'.'+ isnull(a3.FFieldName,'')      FDeField
      ,dbo.aBillNo( convert(varchar(10),isnull(a3.FPage,''))+'.' ,'0' ,2 ,isnull(a3.FTabIndex,'') )  FDeTab
      
      ,a1.FSourClassTypeID 
      ,a1.FSourPage
      ,a1.FSourFKey 
      
      ,a1.FDestClassTypeID 
      ,a1.FDestPage
      ,a1.FDestFKey 
      
      ,a1.FISUserDefine
      ,a1.FAllowModified
      
     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ,'insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( '
      
          + convert(varchar(30),a1.FISUserDefine)
      +','+ convert(varchar(30),a1.FAllowModified) +'  '
      
      +','+ convert(varchar(30),a1.FSourClassTypeID) +','+ convert(varchar(30),a1.FSourPage) +','''+ a1.FSourFKey +'''' + dbo.aSpace( a1.FSourFKey,'18')
      +','+ convert(varchar(30),a1.FDestClassTypeID) +','+ convert(varchar(30),a1.FDestPage) +','''+ a1.FDestFKey +'''' + dbo.aSpace( a1.FDestFKey,'18')
      
      +')  -- '
      +'【 '+ convert(varchar(30),a1.FSourPage) +'  '+ a1.FSourFKey + dbo.aSpace( a1.FSourFKey,'18') +' '+ isnull(a2.FCaption_CHS,'') + dbo.aSpace( isnull(a2.FCaption_CHS,''),'15') +' '+ isnull(a2.FFieldName,'') + dbo.aSpace( isnull(a2.FFieldName,''),'18')  +' 】'
      +'【 '+ convert(varchar(30),a1.FDestPage) +'  '+ a1.FDestFKey + dbo.aSpace( a1.FDestFKey,'18') +' '+ isnull(a3.FCaption_CHS,'') + dbo.aSpace( isnull(a3.FCaption_CHS,''),'15') +' '+ isnull(a3.FFieldName,'') + dbo.aSpace( isnull(a3.FFieldName,''),'18')  +' 】'
     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       FTxt
      
      ,a1.FIsCheck
      ,a1.FDoAction
      
      ,a1.FBeforeFormula
      ,a1.FAfterFormula
      ,a1.FCheckStatCol
      ,a1.FHaveCheckCol
      
      ,a1.FIsGroup
      ,a1.FIsFilter
      ,a1.FControl
      ,a1.FRedNeg
      ,a1.FSourTypeID
      ,a1.FDestTypeID
      
      
   from      ICClassLinkEntry  a1
   left join ICClassTableInfo  a2 on a1.FSourClassTypeID = a2.FClassTypeID and a1.FSourFKey = a2.FKey
   left join ICClassTableInfo  a3 on a1.FDestClassTypeID = a3.FClassTypeID and a1.FDestFKey = a3.FKey
   
   left join ICClassType  b1 on a1.FSourClassTypeID = b1.FID 
   left join ICClassType  b2 on a1.FDestClassTypeID = b2.FID 
   
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 调用：

Exec a_Select 'ICClassType'         ,@where = 'FSourClassTypeID = 1006001 '
   
   Exec a_Select 'a_ICClassLink'       ,@where = 'FSourClassTypeID = 1006001 '

Exec a_Select 'a_ICClassLinkEntry'  ,@where = 'FSourClassTypeID = 1006001 '
                                       ,@Order = 'FDestClassTypeID,FSrTab'

Exec a_Select 'a_ICClassLinkEntry'  ,@where = 'FSourClassTypeID = 210001201 and FDestClassTypeID = 210001401 '
                                       ,@Order = 'FDestClassTypeID,FSrTab'
                                       
                                       
   insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( 0,0  ,1006001,1,'FNumber'           ,1006002,1,'FSourceBillNo_CN'  )  -- 【 1  FNumber            单据编号        FNumber            】【 1  FSourceBillNo_CN   付款申请单号    FSourceBillNo_CN   】
   insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( 0,1  ,1006001,1,'FDate'             ,1006002,1,'FDate'             )  -- 【 1  FDate              日期            FDate              】【 1  FDate              日期            FDate              】
   insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( 0,0  ,1006001,1,'FPayInside_CN'     ,1006002,1,'FPayInside_CN'     )  -- 【 1  FPayInside_CN      内部结算        FPayInside_CN      】【 1  FPayInside_CN      内部结算        FPayInside_CN      】
   insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( 0,0  ,1006001,1,'FRP_CN'            ,1006002,1,'FRP_CN'            )  -- 【 1  FRP_CN             结算类型        FRP_CN             】【 1  FRP_CN             结算类型        FRP_CN             】
   insert ICClassLinkEntry( FISUserDefine,FAllowModified,FSourClassTypeID,FSourPage,FSourFKey,FDestClassTypeID,FDestPage,FDestFKey ) values( 0,0  ,1006001,1,'FItemClassID'      ,1006002,1,'FItemClassID'      )  -- 【 1  FItemClassID       收款单位类别    FItemClassID       】【 1  FItemClassID       收款单位类别    FItemClassID       】

*/
go