问题记录

最新推荐文章于 2021-01-27 09:29:49 发布

艳阳天_.

最新推荐文章于 2021-01-27 09:29:49 发布

阅读量544

点赞数

分类专栏：项目问题记录

本文链接：https://blog.csdn.net/MZ199290/article/details/106057291

版权

项目问题记录专栏收录该内容

7 篇文章 0 订阅

订阅专栏

// 结算单中付款单养户承担的亏损数据错误修改

 select * from T_CAS_ReceivingBillEntry where CFCONTRACTNUMBER ='103001-1801-0054'

   update T_CAS_ReceivingBillEntry set  FCONTRACTENTRYSEQ=1 where CFCONTRACTNUMBER ='103001-1801-0054'

//标准入库单来源单据id

select * from T_IM_PurInWarehsBill where FSOURCEBILLID='10YAAALrwTxZ6SuP'

//二开采购入库单的状态修改
select * from CT_DUC_OutStoragetz where fid ='10YAAALrwTxZ6SuP'

//联营收货单修改标准入库单被删除的的单据状态为暂存

select * from CT_DUC_JointRecriptmz where fid ='n+FsRyCORAGYgpX4cEuZ4U36moo=' and  CFOTHERINBILLID not in ( select  fid from T_IM_PurInWarehsBill  )

//联营收货单修改标准入库单被删除的的单据状态为 暂存
   update   CT_DUC_JointRecriptmz  set  CFSTATUS=1 , CFAUDITTIME=null,FAUDITORID=null,CFBZNUMBER=null where fid=( select  fid from CT_DUC_JointRecriptmz where fid ='n+FsRyCORAGYgpX4cEuZ4U36moo=' and  CFOTHERINBILLID not in ( select  fid from T_IM_PurInWarehsBill  )  )

update CT_DUC_OutStoragetz set CFSTATUS='1' where fid ='10YAAALrwTxZ6SuP'

//二开的入库单生成的标准入库单错误地把批次带到标准入库单上，实际上这个物料是没有启用批次管理的

代码清楚掉物料的入库单，然后在库存余额表 alrt+i 重算即时余额

select * from T_IM_PurInWarehsEntry where FParentID='10YAAAL0Oq54MGHj'

update T_IM_PurInWarehsEntry set FLOT=null where FParentID='10YAAAL0Oq54MGHj' and fid=''

//胴体验收单选择上拉生成的时候，代码bug 财务组织没有赋值上去，导致生成应付单的时候报错，

财务组织规律上等于单据的控制单元。

update CT_DUC_CarcassAcceptanceReport set CFCOMPANYORGID=FCONTROLUNITID where FBIZDATE >= to_date('2020-05-11','yyyy-MM-dd')

//联营发货单-应付单（是否生成应收标志，某人改了流程，去掉了应付单自动提交，自动审核节点，这个反写标志不再反写了，后台更新）

   update  CT_DUC_SendOrdermz   set   CFOTHERBILL='1'    where  fid   in (select FSRCOBJECTID from T_BOT_Relation  where FBOTMAPPINGID='gwvHwqO6S8mtVdAO5Dl3VQRRIsQ=')  and  CFOTHERBILL='0'

// 查询联营发货生成的销售出库单合同号没有携带过去的数据

   --  发货单生成的销售出库单 合同号没携带的数据更新
  
  select *
    from CT_DUC_SendOrdermz mz
   inner join T_IM_SaleIssueBill sale
      on sale.FNUMBER = mz.CFSALENUMBER
     and mz.CFCONTRACTNUMBER is not null
     and sale.CFKDTEXTFIELD is null


  merge into T_IM_SaleIssueBill a
  using (select mz.CFCONTRACTNUMBER, mz.CFSALENUMBER
           from CT_DUC_SendOrdermz mz
          inner join T_IM_SaleIssueBill sale
             on sale.FNUMBER = mz.CFSALENUMBER
            and mz.CFCONTRACTNUMBER is not null
            and sale.CFKDTEXTFIELD is null
         
         ) t
  on (a.fnumber = t.CFSALENUMBER)
  when matched then
    update set a.CFKDTEXTFIELD = t.CFCONTRACTNUMBER

//派车计划与生成的胴体验收单之前的关系，每一行派车计划分录生成一张胴体验收单

  select  paicheentry.FSEQ,  paicheentry.CFCATCHAMT 派车抓鸭费，  paicheentry.CFACTUALEXECUTQTY 实际执行数量 ,   report.CFTHELASTSEQ 上游分录号 ,  report.CFQUANTITY 胴体只数 , report.CFCATCHAMT 胴体抓鸭费   from   CT_DUC_TransportConfirmEntry   paicheentry  inner join   CT_DUC_CarcassAcceptanceReport report  on   paicheentry.FSEQ=report.CFTHELASTSEQ  where paicheentry.fparentid='luNY3HrxQKmuswmxYZZE+iGZvlM=' and  report.CFTHELASTNUMBER=(select FNUMBER  from CT_DUC_TransportConfirm where fid ='luNY3HrxQKmuswmxYZZE+iGZvlM=' )   order by paicheentry.FSEQ

// 根据派车计划与胴体验收单的关系修复派车计划上的实际执行数量（根据派车计划单据内码）

/修复派车计划抓鸭费（根据派车计划单据内码）

//修复派车计划是否是关闭状态、

 
   --先把错误的这张抓禽派车计划的 抓禽费和实际执行数量置为null 行状态置为关闭 
  update    CT_DUC_TransportConfirmEntry   set   CFACTUALEXECUTQTY = null  ， CFCATCHAMT = null  ， CFISCLOSED='0'  where fparentid='UuZk58ZdSBSaIp6vTFfjDSGZvlM='   
                
           
           
   --根据抓禽派车计划和胴体验收单的关联关系 修复数据        
    merge into   CT_DUC_TransportConfirmEntry   a  using (     
          select paicheentry.fparentid,
                       paicheentry.FSEQ,
                       paicheentry.CFCATCHAMT 派车抓鸭费， paicheentry.CFACTUALEXECUTQTY 实际执行数量,
                       report.CFTHELASTSEQ    上游分录号,
                       report.CFQUANTITY      胴体只数,
                       report.CFCATCHAMT      胴体抓鸭费
                  from CT_DUC_TransportConfirmEntry paicheentry
                 inner join CT_DUC_CarcassAcceptanceReport report
                    on paicheentry.FSEQ = report.CFTHELASTSEQ
                 where paicheentry.fparentid = 'UuZk58ZdSBSaIp6vTFfjDSGZvlM='
                   and report.CFTHELASTNUMBER =
                       (select FNUMBER
                          from CT_DUC_TransportConfirm
                         where fid = 'UuZk58ZdSBSaIp6vTFfjDSGZvlM=')
                 order by paicheentry.FSEQ    ) t  on (  t.fparentid=a.fparentid and a.FSEQ = t.FSEQ    ) 
                 when matched then update set  a.CFACTUALEXECUTQTY = t.胴体只数 ,a.CFCATCHAMT=t.胴体抓鸭费
                   
 
   --  1 是未关闭
   update    CT_DUC_TransportConfirmEntry   set    CFISCLOSED='1'  where fparentid='UuZk58ZdSBSaIp6vTFfjDSGZvlM='  and  CFACTUALEXECUTQTY is   null

//标准的采购入库单年度和期间不对的数据

//查找由外购入库单生成的标准入库单的 年 和期间不对的数据
 select * from T_IM_PurInWarehsBill where  FSOURCEBILLID  in   (      select   fid  from CT_DUC_OutStoragetz       ) and  FYEAR='2199'  and FPeriod='1'

 select    *  from T_IM_PurInWarehsBill   where  FNUMBER  in (        select  CFBznumber    from CT_DUC_OutStoragetz   )    and  FYEAR='2199'
  
//修改
 update  T_IM_PurInWarehsBill  set     FYEAR='2020' , FPeriod='5' where  FSOURCEBILLID  in   (      select   fid  from CT_DUC_OutStoragetz       ) and  FYEAR='2199'   and FPeriod='1'
 
  select    *  from T_IM_PurInWarehsBill   where   FYEAR='2199'
 

   select   *    from CT_DUC_OutStoragetz   where   CFContractID =(  select  fid  from  T_DUC_BreedingContract   where FNumber ='103009-200328-0002' )



//查找由联营收货单生成的标准入库单的 年 和期间不对的数据
 select * from T_IM_PurInWarehsBill where  FSOURCEBILLID  in   (      select   fid  from CT_DUC_JointRecriptmz       ) and  FYEAR='2199'  and FPeriod='1'

 select    *  from T_IM_PurInWarehsBill   where  FNUMBER  in (        select  CFBznumber    from  CT_DUC_JointRecriptmz )    and  FYEAR='2199' and FPeriod='1'


   update  T_IM_PurInWarehsBill set     FYEAR='2020' , FPeriod='5'  where  FNUMBER  in (        select  CFBznumber    from  CT_DUC_JointRecriptmz )    and  FYEAR='2199' and FPeriod='1'

//联营发货单

//联营发货单 生成的标准出库单  年 和期间不对的数据
 select * from  T_IM_SaleIssueBill  where  FSOURCEBILLID  in   (      select   fid  from CT_DUC_SendOrdermz       ) and  FYEAR='2199'  and FPeriod='1'

 update T_IM_SaleIssueBill  set     FYEAR='2020' , FPeriod='5'   where  FSOURCEBILLID  in   (      select   fid  from CT_DUC_SendOrdermz       ) and  FYEAR='2199'  and FPeriod='1'

//批次问题

//查询出由二开入库单生成的标准入库单 物料没有启用批次 含有批次的错误入库单
 select  a.fid,  a.FSTORAGEORGUNITID  ， a.FMATERIALID  ,  a.flot, b.FIsLotNumber  from T_IM_PurInWarehsEntry    a   inner join    T_BD_MaterialInventory   b on  ( a.FSTORAGEORGUNITID=b.forgunit  and a.FMATERIALID=b.FMaterialID )       and  a.FSOURCEBILLENTRYID in (  select  fid  from CT_DUC_OutStoragetzEntry      )  and   a.flot is  not  null  and  FISLOTNUMBER=0


// 把不应该含有批次的数据 清空批次
update T_IM_PurInWarehsEntry set FLOT=null  where   fid  in  ( select   a.fid  from T_IM_PurInWarehsEntry    a   inner join    T_BD_MaterialInventory   b on  ( a.FSTORAGEORGUNITID=b.forgunit  and a.FMATERIALID=b.FMaterialID )    and  a.FSOURCEBILLENTRYID in (  select  fid  from CT_DUC_OutStoragetzEntry      )  and   a.flot is  not  null  and  FISLOTNUMBER=0                          )



//查询二开入库单 不应该含有批次的数据  这里用的是二开单据分录里面的采购组织 基本上等于库存组织
 select  a.fid,  a.CFPURCHASEORGUNITI ， a.CFMATERIALID  ,  a.CFLOT, b.FIsLotNumber  from CT_DUC_OutStoragetzEntry    a   inner join    T_BD_MaterialInventory   b on  ( a.CFPURCHASEORGUNITI=b.forgunit  and a.CFMATERIALID=b.FMaterialID )    and   a.CFLOT is  not  null  and  FISLOTNUMBER=0

update CT_DUC_OutStoragetzEntry set  CFLOT=null  where   fid  in (
 select  a.fid   from CT_DUC_OutStoragetzEntry    a   inner join    T_BD_MaterialInventory   b on  ( a.CFPURCHASEORGUNITI=b.forgunit  and a.CFMATERIALID=b.FMaterialID )    and   a.CFLOT is  not  null  and  FISLOTNUMBER=0
)

 //宿迁桂柳食品    长江桂柳睢宁   灵璧桂柳睢宁  连云港市桂柳食品  河南正友牧业   桂柳蚌埠食品  河南泰森食品

// 用查找的结果集中的数据去批量更细：把分录中的库存组织字段赋值给采购组织

update   CT_DUC_JointRecriptmzEntry  t set  t.CFPURCHASEORGUNITI=(
        select   CFSTORAGEORGID  from    CT_DUC_JointRecriptmzEntry  b  inner join CT_DUC_JointRecriptmz a  on  a.fid=b.FPARENTID
        where t.fid=b.fid
)

//更新应付单的采购组织为空的数据


             UPDATE T_AP_OtherBill t  SET   t.FPURORGID =(

                select c.CFPURCHASEORGUNITI from  CT_DUC_JointRecriptmzEntry c inner join  
               T_AP_OtherBillentry    b    on c.fid=b.FSOURCEBILLENTRYID    inner join T_AP_OtherBill  a on a.fid=b.fparentid  
                where t.fid=a.fid
                 ) where    t.FPURORGID is null

--更新联营发货单的运费类型根据仓库名字判断，仓库名字带苗的是苗带料的运费类型是料

update    CT_DUC_SendOrdermzEntry  set CFYUNFEITYPESTR='苗' where fid in  (
select t.fid from  (
 select  b.*  ,(  select FNAME_L2  from T_DB_WAREHOUSE where fid = b.CFSTOREID )  storeName  from CT_DUC_SendOrdermz  a  inner join  CT_DUC_SendOrdermzEntry  b  on a.fid=b.fparentid   
)  t       where   STORENAME   like '%苗%'  and CFYUNFEITYPESTR is null 

)

-- 查找发货单没有推到应收单的数据

-- 查找发货单没有推到应收单的数据

 select  person.FNAME_L2 人员, a.FNUMBER, unit.FNAME_L2  业务组织  from    CT_DUC_SendOrdermz a  inner  join  T_ORG_BaseUnit unit on unit.fid=a.CFSTORAGEORGID   inner join T_BD_Person person on person.fid=a.CFPERSONID

  where  a.fid  not in(   select    FSRCOBJECTID  from T_BOT_Relation   where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='FC910EF3'   )

--胴体单重复提交导致产生重复入库单数据

select a.FBIZDATE 业务日期 , person.FNAME_L2 创建人 , a.FNUMBER 胴体验收单编码 , b.FNAME_L2 库存组织  from (  select  *  from CT_DUC_CarcassAcceptanceReport where fid in  (
select  FSRCOBJECTID from T_BOT_Relation a
where (a.FSRCENTITYID,a.FDESTENTITYID ,a.FSRCOBJECTID) in (select FSRCENTITYID,FDESTENTITYID ,FSRCOBJECTID from T_BOT_Relation group by FSRCENTITYID,FDESTENTITYID ,FSRCOBJECTID  having count(*) > 1) and FSRCENTITYID='E65FED21'  and  FDESTENTITYID='4DFA9A8A' 
  )  )a   inner join   T_ORG_BaseUnit  b   on b.fid=a.CFSTOREORGID  inner join    T_PM_User person on   person.fid=a.FCREATORID

-- //查询联营收货单产生的入库单已被删除的数据

  select   *   from   CT_DUC_JointRecriptmz    where    CFOTHERINBILLID   not in  ( select   fid  from T_IM_PurInWarehsBill )

--//修改标准入库单被删除的联营收货单的状态和清除标准入库单编码字段

   update    CT_DUC_JointRecriptmz  set CFSTATUS=1 , CFBZNUMBER=null  where fid in  (    select  fid  from   CT_DUC_JointRecriptmz    where    CFOTHERINBILLID   not in  ( select   fid  from T_IM_PurInWarehsBill )

--经销商运费公里数模板数据重复，和删除重复数据保留一条不重复的，更新经销商的实际公里数

该语法有问题，id匹配不到的数据要更新的字段置为了null

--更新经销商的运费公里数-------------------------------------------------
update CT_DUC_SendOrdermzEntry t  set t.CFActKilometers =(

  
     select  CFKILOMETERS from   ( select a.CFFARMERID, b.CFSALENUMBER ,a.CFUPDATE  , b.fid entryid from CT_DUC_SendOrdermz a    inner join CT_DUC_SendOrdermzEntry b  on a.fid = b.fparentid )   s                 
        inner join    
       (select * from CT_DUC_FreightUnitTwo b   inner join CT_DUC_FreightUnitTwoE1 c  on b.fid=c.fparentid    ) y

       on ( s.CFFARMERID=y.CFCUSTOMERID and  s.CFSALENUMBER=y.cfnum   and  to_char( s.CFUPDATE, 'yyyy-mm-dd')=to_char(y.CFSEEDDATE, 'yyyy-mm-dd')    )

       where s.entryid=t.fid
)

-----------------------------------------------------------------------------------------------------------------------------------------------------------
//查询运费公里数维护经销商（暂用） 重复数据
 select * from CT_DUC_FreightUnitTwoE1 a
where (a.CFCUSTOMERID,a.CFSEEDDATE,CFKILOMETERS,cfnum) in (select CFCUSTOMERID ,CFSEEDDATE, CFKILOMETERS  ,cfnum from CT_DUC_FreightUnitTwoE1 group by CFCUSTOMERID,CFSEEDDATE,CFKILOMETERS   ,cfnum having count(*) > 1) 
 
 //删除运费公里数维护经销商（暂用） 重复数据 保存一条不重复的
delete from CT_DUC_FreightUnitTwoE1  a
where (a.CFCUSTOMERID,a.CFSEEDDATE,a.CFKILOMETERS, a.cfnum) in (select  CFCUSTOMERID ,CFSEEDDATE, CFKILOMETERS ,cfnum from CT_DUC_FreightUnitTwoE1 group by CFCUSTOMERID ,CFSEEDDATE, CFKILOMETERS,cfnum having count(*) > 1)
and rowid not in (select min(rowid) from CT_DUC_FreightUnitTwoE1 group by CFCUSTOMERID ,CFSEEDDATE, CFKILOMETERS ,cfnum having count(*)>1)

// 查找养殖户运费公里数维护表重复的记录（同一库存组织下，养殖户和供应商同名字的存在重复记录）

select j.*,org.FNAME_L2 from  (
 
select  *  from    (    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFCUSTOMERNAME,b.CFSUPPLYNAME,  b.CFSUPPLYNUMBERID   from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )   t
where (t.CFSTORAGEORGID,t.CFCUSTOMERID,t.CFSUPPLYNUMBERID) in (select CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID  from ((    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFCUSTOMERNAME,b.CFSUPPLYNAME,  b.CFSUPPLYNUMBERID   from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )  ) group by  CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID having count(*) > 1)    order by t.CFCUSTOMERNAME   

) j inner  join  T_ORG_BaseUnit org   on org.fid=j.CFSTORAGEORGID

-- 养殖户联营发货单分录上根据公里数维护表更新实际公里数（养殖户，供应商，库存组织）

-- 养殖户 联营发货单分录上根据公里数维护表 更新实际公里数（养殖户，供应商，库存组织）
 merge into  CT_DUC_SendOrdermzEntry a  using  (    select *
    from (select a.CFFARMERID, b.CFSupplierID, b.fid entryid,a.CFStorageOrgID
            from CT_DUC_SendOrdermz a
           inner join CT_DUC_SendOrdermzEntry b
              on a.fid = b.fparentid) s
   inner join (select *
                 from CT_DUC_FreightUnit b
                inner join CT_DUC_FreightUnitE1 c
                   on b.fid = c.fparentid) y
  
      on (s.CFFARMERID = y.CFCUSTOMERID and
         s.CFSupplierID = y.cfsupplynumberid and s.CFStorageOrgID=y.cfstorageorgid)   ) t

 on (  a.fid=t.entryid   )  when matched then update set  a.CFActKilometers=t.CFKilometers

--河南陈州华英组织和河南正友组织（鸡饲料固定运费单价CFPRICE，目前这两个鸡饲料公司）
--运费公里数模板数据查重同一库存组织下，养殖户和供应商同名字和单价一样的存在重复记录



--河南陈州华英组织 和河南正友组织 （鸡饲料固定运费单价CFPRICE，目前这两个鸡饲料公司）
--运费公里数模板数据查重 同一库存组织下 ，养殖户和供应商同名字 一样的存在重复记录 
select j.*,org.FNAME_L2 from  (
 
select  *  from    (    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFCUSTOMERNAME,b.CFSUPPLYNAME,  b.CFSUPPLYNUMBERID from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )   t
where (t.CFSTORAGEORGID,t.CFCUSTOMERID,t.CFSUPPLYNUMBERID ) in (select CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID  from ((    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFSUPPLYNUMBERID   from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )  ) group by  CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID  having count(*) > 1)    order by t.CFCUSTOMERNAME   
 
) j inner  join  T_ORG_BaseUnit org   on org.fid=j.CFSTORAGEORGID   and  j.CFSTORAGEORGID in  ('10YAAAGUkUHM567U','10YAAADdsufM567U') 





--河南陈州华英组织 和河南正友组织 （鸡饲料固定运费单价CFPRICE，目前这两个鸡饲料公司）
--运费公里数模板数据查重 同一库存组织下 ，养殖户和供应商同名字和单价一样的存在重复记录 
select j.*,org.FNAME_L2 from  (
 
select  *  from    (    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFCUSTOMERNAME,b.CFSUPPLYNAME,  b.CFSUPPLYNUMBERID , b.CFPRICE from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )   t
where (t.CFSTORAGEORGID,t.CFCUSTOMERID,t.CFSUPPLYNUMBERID,t.CFPRICE) in (select CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID ,CFPRICE from ((    select  a.CFSTORAGEORGID,b.CFCUSTOMERID, b.CFSUPPLYNUMBERID ,b.CFPRICE  from CT_DUC_FreightUnit  a inner join   CT_DUC_FreightUnitE1 b on   a.fid=b.fparentid    )  ) group by  CFSTORAGEORGID,CFCUSTOMERID,CFSUPPLYNUMBERID,CFPRICE having count(*) > 1)    order by t.CFCUSTOMERNAME   
 
) j inner  join  T_ORG_BaseUnit org   on org.fid=j.CFSTORAGEORGID

--河南陈州华英组织和河南正友组织（鸡饲料固定运费单价CFPRICE，目前这两个鸡饲料公司）
--根据运费单价维护表数据匹配发货单中的供应商和养殖户赋值运费单价

-- 将河南陈州华英组织和河南正友组织联营发货单上的运费单价和运费小计更新到销售出库单上


 
--河南陈州华英组织 和河南正友组织 （鸡饲料固定运费单价CFPRICE，目前这两个鸡饲料公司）     
--根据运费单价维护表数据 匹配发货单中的供应商和养殖户 赋值运费单价  计算运费小计赋值  不包含数量是负数的计算
merge into  CT_DUC_SendOrdermzEntry a  using   (  select  * from   (

    (select   b.CFCUSTOMERID CUSTOMERID ,b.CFSUPPLYNUMBERID   SUPPLYNUMBERID  ,b.CFPRICE PRICE , a.CFStorageOrgID StorageOrgID  from   CT_DUC_FreightUnit a inner join CT_DUC_FreightUnitE1 b on a.fid=b.fparentid  ) Freight  inner join (   select  d.fid  entryid, c.CFFarmerID FarmerID, d.CFSupplierID  SupplierID ,d.CFSaleNumber  saleNumber  from    CT_DUC_SendOrdermz  c  inner join  CT_DUC_SendOrdermzEntry  d   on d.fparentid=c.fid  ) mz  on  ( mz.FarmerID=Freight.CUSTOMERID and mz.SupplierID=Freight.SUPPLYNUMBERID and  Freight.StorageOrgID in  ('10YAAAGUkUHM567U','10YAAADdsufM567U') and  mz.saleNumber>0 )
    
   )   )  t  on ( a.fid=t.ENTRYID   ) when  matched   then update set   a.CFTonfreight=t.PRICE ,a.CFTotalfreight=ROUND(a.CFSaleNumber*t.PRICE,2)
     
 


-- 将河南陈州华英组织 和河南正友组织 联营发货单上的 运费单价和运费小计更新到销售出库单上
   merge into  T_IM_SaleIssueEntry  a  using  (select * from CT_DUC_SendOrdermzEntry  where CFSALEORGUNITID in ( '10YAAAGUkUHM567U','10YAAADdsufM567U')  )  t on (t.fid=a.FSOURCEBILLENTRYID) 
   when  matched then update set  a.cfTonfreight=t.CFTonfreight ,a.CFTotalfreight=t.CFTotalfreight 

 

--  河南陈州华英组织 和河南正友组织 销售出库单上数量为负数的运费单价和运费小计 更新为0
   merge into  T_IM_SaleIssueEntry  a  using  (select * from CT_DUC_SendOrdermzEntry  where CFSALEORGUNITID in ( '10YAAAGUkUHM567U','10YAAADdsufM567U') and CFSaleNumber<0 )  t on (t.fid=a.FSOURCEBILLENTRYID    ) 
   when  matched then update set  a.cfTonfreight=0 ,a.CFTotalfreight=0 
 
--联营发货单  数量为负数的运费单价和运费小计 更新为0
   update   CT_DUC_SendOrdermzEntry  set  CFTonfreight=0,  CFTotalfreight=0  where CFSALEORGUNITID in ( '10YAAAGUkUHM567U','10YAAADdsufM567U') and CFSaleNumber<0

//--审核状态的毛鸭联营收货单产生的入库单是保存状态的数据

--审核状态的毛鸭联营收货单产生的入库单是保存状态的数据
   select  a.FBizDate 业务日期 , a.FNUMBER 采购入库单编码 ,unit.fname_l2 库存组织 ,  a.FStorageOrgUnitID   from T_IM_PurInWarehsBill  a  inner join T_IM_PurInWarehsEntry  b on a.fid=b.fparentid   inner join  T_ORG_BaseUnit unit on unit.fid=a.FStorageOrgUnitID where a.FBASESTATUS=1  and  b.FMaterialID='10YAAAAJel1ECefw'  and a.FNUMBER in (    select  CFBZNUMBER   from   CT_DUC_JointRecriptmz  where   CFSTATUS=3    ) order by a.FBizDate

//通过botp关联表根据胴体验收单的业务日期同步到联营收货单的业务日期使两个单据的业务日期保持一致

该语法有问题，id匹配不到的数据业务日期置为了null

//通过botp关联表   根据胴体验收单的业务日期 同步到联营收货单的业务日期 使两个单据的业务日期保持一致
   select  botp.FSRCOBJECTID,  botp.FDESTOBJECTID,  report.fbizdate as roportdate ,mz.fbizdate  asmzdate from  T_BOT_Relation botp 
   inner join CT_DUC_CarcassAcceptanceReport report on  report.fid= botp.FSRCOBJECTID inner join    CT_DUC_JointRecriptmz  mz  on  mz.fid=botp.FDESTOBJECTID  where  botp.FSRCENTITYID='E65FED21'  and  botp.FDESTENTITYID='4DFA9A8A' 


--错误的更新语句 要么返回过多的子查询，要么把值更新为了null
   update  CT_DUC_JointRecriptmz mz set mz.fbizdate=(   
   select   report.fbizdate  from  T_BOT_Relation botp 
   inner join CT_DUC_CarcassAcceptanceReport report on  report.fid= botp.FSRCOBJECTID inner join    CT_DUC_JointRecriptmz  mz  on  mz.fid=botp.FDESTOBJECTID  where  botp.FSRCENTITYID='E65FED21'  and  botp.FDESTENTITYID='4DFA9A8A'  and  mz.fid=report.fid    
     )


--正确的更新
   update  CT_DUC_JointRecriptmz mz set mz.fbizdate=(   
     select   t.fbizdate1  from  (
 select botp.FDESTOBJECTID  reportid  ,  botp.FDESTOBJECTID  mzid  ,  report.fbizdate fbizdate1  , mz.fbizdate   fbizdate2    from  T_BOT_Relation botp 
   inner join CT_DUC_CarcassAcceptanceReport report on  report.fid= botp.FSRCOBJECTID inner join    CT_DUC_JointRecriptmz  mz  on  mz.fid=botp.FDESTOBJECTID  where  botp.FSRCENTITYID='E65FED21'  and  botp.FDESTENTITYID='4DFA9A8A'
     )t   where t.reportid=mz.fid

   )

//更新标准的采购入库业务日期和联营收货单业务日期保持一致

该语法有问题，id匹配不到的数据业务日期置为了null

//更新标准的采购入库业务日期和联营收货单业务日期保持一致


update T_IM_PurInWarehsBill  t set t.fbizdate=(
       select  y.date1 from  ( 
        select   mz.CFOTHERINBILLID as CFOTHERINBILLID , mz.fbizdate as date1, purchbill.fid as purchfid ,purchbill.fbizdate as bizdate2  from  CT_DUC_JointRecriptmz  mz inner join T_IM_PurInWarehsBill purchbill on purchbill.fid=mz.CFOTHERINBILLID ) y

 where y.CFOTHERINBILLID =t.fid
 )

以后更新语句一律采用merge into语法

 --更新标准的采购入库业务日期和联营收货单业务日期保持一致
 merge into T_IM_PurInWarehsBill a  using (  select CFOTHERINBILLID, fbizdate from  CT_DUC_JointRecriptmz ) t
 
 on  ( a.fid=t.CFOTHERINBILLID ) when matched then update set a.fbizdate=t.fbizdate

-- 没有下游单据的应付单应付金额不等于未结算金额的数据

-- 没有下游单据的应付单   应付金额不等于未结算金额的数据
select a.FNUMBER,a.FBIZDATE ,unit.FNAME_L2,unit.FNUMBER,a.FAMOUNTLOCAL 应付金额 , a.FUNVERIFYAMOUNTLOCAL 未结算金额本位币 from T_AP_OtherBill a  inner join T_ORG_BaseUnit unit on unit.fid=a.FCOMPANYID  where a.fid not in (   select FSrcObjectID  from  T_BOT_Relation    where FSrcEntityID='48DA3A71'      ) and  unit.FNUMBER='1010901' and   a.FUNVERIFYAMOUNT!= a.FAMOUNT and    a.FUNVERIFYAMOUNTLOCAL!=a.FAMOUNTLOCAL

----联营发货单的车次根据导入的联营发货单单据号和车次信息批量更新

--联营发货单的车次 根据导入的联营发货单单据号和车次信息批量更新  
 merge into CT_DUC_SendOrdermzEntry a  using (  select   a.fnumber , c.CFCARLOT ,b.fid as entryid  from CT_DUC_SendOrdermz a    inner join CT_DUC_SendOrdermzEntry b  on a.fid = b.fparentid    inner join     CT_DUC_FreightlotE1  c  on  c.CFNUMBER=a.FNUMBER  ) t on  (a.fid=t.entryid)   when matched then update set  a.CFTRAINNUMBER=t.CFCARLOT


--删除联营发货单的车次导入的模板中车次重复数据
delete from CT_DUC_FreightlotE1 a
where (a.cFNUMBER,a.CFCARLOT) in (select cFNUMBER,CFCARLOT from CT_DUC_FreightlotE1 group by cFNUMBER,CFCARLOT  having count(1) > 1)
and rowid not in (select min(rowid) from CT_DUC_FreightlotE1 group by cFNUMBER,CFCARLOT having count(1)>1)

--查询联营发货单生成的应付单金额不相等的数据

 select *  from  (

 select  person.FNAME_L2 人员, a.FNUMBER 发货单编码 , unit.FNAME_L2  业务组织 ,(select  sum(CFSaleAmount)  from  CT_DUC_SendOrdermzEntry  where fparentid=a.fid) 发货单金额     , otherbill.FNUMBER  应收单编码,  otherbill.FAmountLocal  应收单金额  from     CT_DUC_SendOrdermz a  inner  join  T_ORG_BaseUnit unit on unit.fid=a.CFSTORAGEORGID   inner join T_BD_Person person on person.fid=a.CFPERSONID
inner join    T_BOT_Relation  botp on botp.FSRCOBJECTID=a.fid   inner join    T_AR_OtherBill otherbill  on  otherbill.fid=botp.FDESTOBJECTID
 where  a.fid   in(   select    FSRCOBJECTID  from T_BOT_Relation   where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='FC910EF3'   )   


) t  where t.发货单金额!=t.应收单金额

--查询联营发货单重复生成应付单的数据

 --查询联营发货单重复生成应付单的数据
select    botp.FOPERATORID 操作id, botp.FDATE 日期 ,mz.fnumber 发货单编码 , otherBill.fnumber 应收单编码 , unit.FNAME_L2  销售组织   from T_BOT_Relation botp inner join CT_DUC_SendOrdermz mz on mz.fid=botp.FSRCOBJECTID inner join T_AR_OtherBill otherBill on otherBill.fid=botp.FDESTOBJECTID   inner join T_ORG_BaseUnit unit on unit.fid= mz.CFStorageOrgID
where FSRCOBJECTID  in (select  FSRCOBJECTID from T_BOT_Relation      where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='FC910EF3'   group       by FSRCOBJECTID having count(FSRCOBJECTID) > 1    )

--查询二开采购入库单重复生成应收单的数据

--查询二开采购入库单重复生成应收单的数据
 select    botp.FOPERATORID 操作id, botp.FDATE 日期 ,mz.fnumber 二开入库单编码 , otherBill.fnumber 应付单编码 , unit.FNAME_L2  销售组织   from T_BOT_Relation botp inner join CT_DUC_OutStoragetz mz on mz.fid=botp.FSRCOBJECTID inner join T_AP_OtherBill otherBill on otherBill.fid=botp.FDESTOBJECTID   inner join T_ORG_BaseUnit unit on unit.fid= mz.CFStorageOrgID
where FSRCOBJECTID  in (select  FSRCOBJECTID from T_BOT_Relation   where   FSRCENTITYID='59E92B8F'  and  FDESTENTITYID='48DA3A71'  group   by FSRCOBJECTID having count(FSRCOBJECTID) > 1 )

--查询没有上有单据的标准入库单（按编码过滤不一定非常准确）

--查询没有上有单据的标准入库单
    select  bill.FNUMBER , unit.fname_l2 from  T_IM_PurInWarehsBill  bill inner join  T_ORG_BaseUnit unit on unit.fid=bill.FSTORAGEORGUNITID   where  unit.FNUMBER LIKE '1030%'  and   bill.fid not in (    select   FDESTOBJECTID    from T_BOT_Relation   where   FDESTENTITYID='783061E3'   )

-- 联营发货单分录入库价格有值的，更新单位实际成本为入库价格，实际成本=入库价格*数量

--联营发货单单上有入库价格的分录把单位实际成本和实际成本更新到销售出库单上


 -- 联营发货单 分录入库价格有值的，更新单位实际成本为入库价格，实际成本=入库价格*数量
 merge into CT_DUC_SendOrdermzEntry a using (    select   *  from   CT_DUC_SendOrdermzEntry   where   CFStoragePrice>0   )  t
on ( a.fid=t.fid ) when matched then update set a.CFCostPrice=t.CFStoragePrice,a.CFCostAmount=round(a.CFStoragePrice*a.CFSaleNumber,2);



 --联营发货单单上有入库价格的分录把单位实际成本 和实际成本更新到销售出库单上
 merge into  T_IM_SaleIssueEntry  a using (    select   *  from   CT_DUC_SendOrdermzEntry   where   CFStoragePrice>0   )  t
on ( a.FSourceBillEntryID=t.fid ) when matched then update set a.FUnitActualCost=t.CFStoragePrice,a.FActualCost=t.CFCostAmount;

--查找由发货单生成的出库单出库单分录来源单据分录内码是 null 的数据

--查找联营发货单没生成出库单的数据


 --查找由发货单生成的出库单 出库单分录来源单据分录内码 是 null 的数据
 select * from  (
 (select b.FSourceBillEntryID  salesourceEntryId , a.fid  salebillId from   t_Im_Saleissuebill  a inner join  T_IM_SaleIssueEntry b on a.fid=b.fparentid ) t1 inner join  
 ( select  mz.cfsalebillid  salebillid ,  mzentry.fid sendOrdermzEntryId  from  CT_DUC_SendOrdermz  mz  inner join CT_DUC_SendOrdermzEntry mzentry  on mz.fid=mzentry.fparentid)  t2
 on t1.salebillId=t2.salebillid)  where  t1.salesourceEntryId not in ( select fid  from CT_DUC_SendOrdermzEntry )
 
  --查找联营发货单没生成出库单的数据
 select *from CT_DUC_SendOrdermz mz where   CFSTATUS=3  and   ( mz.cfsalenumber is null or mz.cfsalebillid is null  )

-- 联营发货单更新单位实际成本=销售价格，实际成本=销售金额

-- 联营发货单生成的销售出库单更新单位实际成本=销售价格，实际成本=销售金额

 -- 联营发货单  更新单位实际成本=销售价格，实际成本=销售金额
 merge into CT_DUC_SendOrdermzEntry a using (  select *  from   CT_DUC_SendOrdermzEntry   )  t
on ( a.fid=t.fid ) when matched then update set a.CFCostPrice=t.CFSalePrice,a.CFCostAmount=CFSaleAmount 

 -- 联营发货单生成的销售出库单   更新单位实际成本=销售价格，实际成本=销售金额
  merge into  T_IM_SaleIssueEntry  a using (    select   *  from   CT_DUC_SendOrdermzEntry    )  t
on ( a.FSourceBillEntryID=t.fid ) when matched then update set a.FUnitActualCost=t.CFCostPrice,a.FActualCost=t.CFCostAmount

-- 查询二开发货单是否生成标注出库单


-- 查询二开发货单是否生成标注出库单
 SELECT * FROM   T_IM_PurInWarehsBill  where FSOURCEBILLID='10YAAAMmDrLt1GRX'

--查出发货单 -凭证-标准出库单

  --查出发货单 -凭证-标准出库单
    select * from       (  select sendorder.fid as 发货单Id, sendorder.CFSaleBillId as 标准出库单Id  from CT_DUC_SendOrdermz  sendorder   )  mz    inner join 
          (  select   FSRCOBJECTID, FDESTOBJECTID   from T_BOT_Relation   where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='2652E01E' ) botp  on mz.发货单Id=botp.    FSRCOBJECTID
          inner join (    select   v.fid  as 凭证Id  ,v.FNUMBER  as 凭证编码  from T_GL_Voucher v    ) voucher on voucher.凭证Id=botp.FDESTOBJECTID

             
-- 更新发货单上的凭证编码

merge into CT_DUC_SendOrdermz a  using (         select * from       (  select sendorder.fid as 发货单Id, sendorder.CFSaleBillId as 标准出库单Id  from CT_DUC_SendOrdermz  sendorder   )  mz    inner join 
          (  select   FSRCOBJECTID, FDESTOBJECTID   from T_BOT_Relation   where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='2652E01E' ) botp  on mz.发货单Id=botp.    FSRCOBJECTID
          inner join (    select   v.fid  as 凭证Id  ,v.FNUMBER  as 凭证编码  from T_GL_Voucher v    ) voucher on voucher.凭证Id=botp.FDESTOBJECTID        ) t

on ( a.fid=t.发货单Id ) when matched then update set a.CFVOUCHERNUMBER=t.凭证编码


--发货单生成的凭证id 更新到标准出库单上 
 merge into  T_IM_SaleIssueBill  a  using (         select * from       (  select sendorder.fid as 发货单Id, sendorder.CFSaleBillId as 标准出库单Id  from CT_DUC_SendOrdermz  sendorder   )  mz    inner join 
          (  select   FSRCOBJECTID, FDESTOBJECTID   from T_BOT_Relation   where   FSRCENTITYID='EDD46457'  and  FDESTENTITYID='2652E01E' ) botp  on mz.发货单Id=botp.    FSRCOBJECTID
          inner join (    select   v.fid  as 凭证Id  ,v.FNUMBER  as 凭证编码  from T_GL_Voucher v    ) voucher on voucher.凭证Id=botp.FDESTOBJECTID        ) t

on ( a.fid=t.标准出库单Id  ) when matched then update set a.FVoucherID=t.凭证Id

-- 宿迁更新运费单价运费小计到联营发货单上销售数量>0 非退库业务

 
 
-- 宿迁 更新运费单价 运费小计到联营发货单上  销售数量>0  非退库业务
merge into CT_DUC_SendOrdermzEntry a
using (select *
         from (
              
               (select b.CFCUSTOMERID     CUSTOMERID,
                       b.CFSUPPLYNUMBERID SUPPLYNUMBERID,
                       b.CFPRICE          PRICE,
                       a.CFStorageOrgID   StorageOrgID
                  from CT_DUC_FreightUnit a
                 inner join CT_DUC_FreightUnitE1 b
                    on a.fid = b.fparentid) Freight inner join
               (select d.fid          entryid,
                       c.CFFarmerID   FarmerID,
                       d.CFSupplierID SupplierID,
                       d.CFSaleNumber saleNumber,
                       c.cfstorageorgid
                  from CT_DUC_SendOrdermz c
                 inner join CT_DUC_SendOrdermzEntry d
                    on d.fparentid = c.fid) mz
               on(mz.FarmerID = Freight.CUSTOMERID and
                  mz.SupplierID = Freight.SUPPLYNUMBERID and
                  Freight.StorageOrgID =
                    '10YAAAAEbdvM567U'   and Freight.StorageOrgID =mz.cfstorageorgid     and
                  mz.saleNumber > 0)
              
              )) t
on (a.fid = t.ENTRYID)
when matched then
  update
     set a.CFTonfreight   = t.PRICE,
         a.CFTotalfreight = ROUND(a.CFSaleNumber * t.PRICE, 2)
     
 
 
 
--  宿迁 联营发货单上的 运费单价和运费小计更新到销售出库单上  非退库业务
   merge into  T_IM_SaleIssueEntry  a  using  (select * from CT_DUC_SendOrdermzEntry  where CFSALEORGUNITID in ( '10YAAAAEbdvM567U' )  )  t on (t.fid=a.FSOURCEBILLENTRYID) 
   when  matched then update set  a.cfTonfreight=t.CFTonfreight ,a.CFTotalfreight=t.CFTotalfreight

 --这2个发货单实际公里数改成75
  update  CT_DUC_SendOrdermzEntry  set  CFACTKILOMETERS=75     where  fparentid in( select fid from  CT_DUC_SendOrdermz  where fnumber in( '103009-200529-0055','103009-200601-0018'  ) )  
 --这6个发货单把运费去掉
  update  CT_DUC_SendOrdermzEntry  set  CFTONFREIGHT=null,  CFTOTALFREIGHT=null    where  fparentid in( select fid from  CT_DUC_SendOrdermz  where fnumber in( '103004-200515-0002','103004-200511-0037','103004-200520-0002','103004-200601-0082','103004-200601-0084','103004-200602-0188'   ) )

 
--  更新到 销售出库单上
   merge into  T_IM_SaleIssueEntry  a  using  (select  b.fid  entryid ,b.CFTotalfreight  Totalfreight  ,b.CFTonfreight  Tonfreight  from CT_DUC_SendOrdermzEntry  b   inner join   CT_DUC_SendOrdermz  a on a.fid=b.fparentid   and    a.fnumber in( '103004-200515-0002','103004-200511-0037','103004-200520-0002','103004-200601-0082','103004-200601-0084','103004-200602-0188'   ))
  t on (t.entryid=a.FSOURCEBILLENTRYID) 
   when  matched then update set  a.cfTonfreight=t.Tonfreight ,a.CFTotalfreight=t.Totalfreight

-- 把收款单分录里面的委托合同号字段清空数据

-- 把收款单分录里面的委托合同号字段清空数据
update T_CAS_ReceivingBillEntry
   set CFCONTRACTNUMBER = null
 where FReceivingBillID in
       (select fid
          from T_CAS_ReceivingBill
         where FNUMBER in ('103001-2005-1453', '103001-2005-1398'))

-- 应收单这个单据去掉合同号信息

-- 应收单这个单据 去掉合同号信息
update T_AR_OtherBillentry
   set CFCONTRACTNUMBER = null
 where FParentID in
       (select fid from T_AR_OtherBill where fnumber = '103001-2005-08044')

--生成应收单但是状态是未审核，且没产生标注出库单的联营发货单


--生成应收单  但是状态是未审核，且没产生标注出库单的联营发货单
select  *   from  CT_DUC_SendOrdermz  where  CFOTHERBILL=1  and   CFSTATUS!=3

--修改联营发货单和销售出库单同步销售出库单上的运费

--修改联营发货单运费单价和运费小计
update CT_DUC_SendOrdermzEntry
   set CFTonfreight  =25,
          CFTotalfreight = ROUND(CFSaleNumber * 25, 2)
 where fparentid in
       (select fid
          from CT_DUC_SendOrdermz
         where fnumber in ( '103004-200505-0101' ))


--修改标准出库单运费单价和运费小计
 update T_IM_SaleIssueEntry   set    cfTonfreight=36.8 , CFTotalfreight=ROUND(FQty * 36.8, 2)  where   fparentid =  (  select fid from  T_IM_SaleIssueBill  where fnumber ='xsck-103004-2005-00663' )  
 
 --同步到标准出库单
merge into  T_IM_SaleIssueEntry  a  using  (select * from CT_DUC_SendOrdermzEntry   where fparentid in
       (select fid
          from CT_DUC_SendOrdermz
         where fnumber in ( '103004-200505-0070','103004-200505-0062','103004-200505-0089','103004-200505-0056','103004-200505-0056',
  '103004-200505-0042','103004-200531-0020','103004-200505-0026','103004-200505-0028','103004-200505-0113','103004-200505-0030',
  '103004-200505-0036','103004-200505-0108','103004-200505-0083','103004-200505-0024','103004-200505-0058','103004-200505-0053','103004-200505-0081',
  '103004-200505-0032','103004-200505-0076','103004-200505-0078','103004-200505-0051','103004-200505-0096','103004-200505-0086','103004-200505-0034','103004-200505-0044',
  '103004-200505-0040','103004-200505-0046','103004-200505-0092','103004-200505-0094','103004-200505-0110','103004-200505-0098','103004-200505-0072','103004-200505-0068',
  '103004-200505-0038','103004-200505-0066','103004-200505-0101')) )  t on (t.fid=a.FSOURCEBILLENTRYID) 
   when  matched then update set  a.cfTonfreight=t.CFTonfreight ,a.CFTotalfreight=t.CFTotalfreight

---查询销售出库单实际成本和成本为null的数据，为null的数据存货核算（出库核算）会报错

 select * from  T_IM_SaleIssueEntry  b  inner  join  T_IM_SaleIssueBill   a   on   a.fid=b.fparentid     where      FUnitActualCost   is null  or  FActualCost  is  null

--联营发货单成本和单位实际成本字段为null -- 销售出库单实际成本和成本为null的数据更新

--联营发货单 
update CT_DUC_SendOrdermzEntry
   set CFCostPrice = CFSALEPRICE, CFCostAmount = CFSALEAMOUNT
 where CFCostPrice is null
   or  CFCostAmount is null


--销售出库单  

  update T_IM_SaleIssueEntry
     set FUnitActualCost = FPRICE, FActualCost = FAMOUNT
   where FUnitActualCost is null
      or  FActualCost is null

--河南泰森赠品的单价和金额改为0

update T_IM_SaleIssueEntry
   set FSalePrice         = 0,
       FPrice             = 0,
       FTaxPrice          = 0,
       FActualPrice       = 0,
       FNonTaxAmount      = 0,
       FAmount            = 0,
       FLocalNonTaxAmount = 0,
       FLocalAmount       = 0
 where FIsPresent = 1
   and FStorageOrgUnitID = '10YAAAIzgxzM567U'
   and FBIZDATE between to_date('2020-05-01', 'yyyy-MM-dd') AND
       to_date('2020-05-31', 'yyyy-MM-dd')

-- 查询联营户发货单有合同号但是应收单没有合同号的

   --  查询联营户发货单有合同号  但是应收单没有合同号的
   select sendOrder.fid,
          sendOrder.CFCONTRACTNUMBER 合同单据号,
          otherBillEntry.fid         应收单分录Id
     from CT_DUC_SendOrdermz sendOrder
    inner join T_BOT_Relation botp
       on sendOrder.fid = botp.FSRCOBJECTID
    inner join T_AR_OtherBill otherBill
       on botp.FDESTOBJECTID = otherBill.fid
    inner join T_AR_OTHERBILLENTRY otherBillEntry
       on otherBillEntry.fparentid = otherBill.fid
    where sendOrder.CFCONTRACTNUMBER is not null
      and otherBillEntry.CFCONTRACTNUMBER is null
      and botp.FBOTMAPPINGID = 'gwvHwqO6S8mtVdAO5Dl3VQRRIsQ='
   
   -- 更新由联营发货单生成的应收单 应收单分录上没有合同号的单据  
    merge into T_AR_OTHERBILLENTRY a using
    (select sendOrder.fid,
                  sendOrder.CFCONTRACTNUMBER 合同单据号,
                  otherBillEntry.fid         应收单分录Id
             from CT_DUC_SendOrdermz sendOrder
            inner join T_BOT_Relation botp
               on sendOrder.fid = botp.FSRCOBJECTID
            inner join T_AR_OtherBill otherBill
               on botp.FDESTOBJECTID = otherBill.fid
            inner join T_AR_OTHERBILLENTRY otherBillEntry
               on otherBillEntry.fparentid = otherBill.fid
            where sendOrder.CFCONTRACTNUMBER is not null
              and otherBillEntry.CFCONTRACTNUMBER is null
              and botp.FBOTMAPPINGID = 'gwvHwqO6S8mtVdAO5Dl3VQRRIsQ=') t
    on(t.应收单分录ID = a.fid) when matched then update set a.CFCONTRACTNUMBER = t.合同单据号

--  发货单上的单据合同文本字段有值，但是合同F7字段无值的，数据修复
    update  CT_DUC_SendOrdermz    set   CFCONTRACTID=( select  fid from T_DUC_BreedingContract where FNUMBER =CFCONTRACTNUMBER )  where CFCONTRACTNUMBER  is not null  and CFCONTRACTID  is null

--更新收款单分录上的合同号 是空的 上游单据应收单合同
  --更新收款单分录上的合同号 是空的 上游单据应收单合同
 merge into T_CAS_RECEIVINGBILLENTRY a using
    (  select   distinct  otherBill.FNUMBER 应收单单据号,  otherBill.FID,  receBillEntry.fid    收款单分录Id,  otherBillEntey.CFCONTRACTNUMBER  应收单合同号 from   T_AR_OTHERBILLENTRY  otherBillEntey    inner join    T_AR_OTHERBILL otherBill     on  otherBillEntey.FPARENTID=  otherBill.fid
   inner join T_BOT_Relation botp  on  botp.FSRCOBJECTID=otherBill.fid   and   botp.FBOTMAPPINGID='dc0b50d4-0108-1000-e000-4422c0a83cf1045122C4'
   inner join T_CAS_RECEIVINGBILL receBill on   receBill.fid= botp.FDESTOBJECTID   inner  join  T_CAS_RECEIVINGBILLENTRY  receBillEntry  on
  receBillEntry.FRECEIVINGBILLID=receBill.fid  where otherBillEntey.CFCONTRACTNUMBER is not  null   and  receBillEntry.CFCONTRACTNUMBER  is null   ) t
    on(t.收款单分录ID = a.fid)  when matched then update set    a.CFCONTRACTNUMBER=t.应收单合同号

--更新外购入库单单据头合金金额是0 的单据


      select   sum(b.CFAmount),a.FNumber,a.fid  from  CT_DUC_OutStoragetz  a inner join    CT_DUC_OutStoragetzEntry  b
      on a.fid=b.fparentid     where a.CFAmout is null   group  by   a.FNumber  ,a.fid

--更新外购入库单  单据头合金金额是0 的单据
merge into CT_DUC_OutStoragetz a
using (select sum(b.CFAmount) summoney, a.FNumber, a.fid
         from CT_DUC_OutStoragetz a
        inner join CT_DUC_OutStoragetzEntry b
           on a.fid = b.fparentid
        where a.CFAmout is null
        group by a.FNumber, a.fid
       
       ) t
on (a.fid = t.fid)
when matched then
  update set a.CFAmout = t.summoney

-- 修改库存组织错误的合同单据

-- 修改库存组织错误的合同单据

--长江桂柳食品睢宁
  UPDATE       T_DUC_BreedingContract  set   CFSTROAGEORGID='10YAAAAAGSPM567U'   where  FCONTROLUNITID='10YAAAAAGSPM567U'  and  CFSTROAGEORGID!='10YAAAAAGSPM567U'

-- ；灵璧桂柳
  UPDATE       T_DUC_BreedingContract  set   CFSTROAGEORGID='10YAAAAAGTbM567U'   where  FCONTROLUNITID='10YAAAAAGTbM567U'   and  CFSTROAGEORGID!='10YAAAAAGTbM567U'
-- ； 宿迁桂柳食品
  UPDATE       T_DUC_BreedingContract  set   CFSTROAGEORGID='10YAAAAEbdvM567U'   where  FCONTROLUNITID='10YAAAAEbdvM567U'   and  CFSTROAGEORGID!='10YAAAAEbdvM567U'
-- ； 淮阳桂柳牧业
  UPDATE       T_DUC_BreedingContract  set   CFSTROAGEORGID='10YAAAGUkUHM567U'   where  FCONTROLUNITID='10YAAAGUkUHM567U'   and  CFSTROAGEORGID!='10YAAAGUkUHM567U'

 --批量下推导致联营发货单没有编码的，用对应的标注出库单的单号填充
  select *
    from CT_DUC_SendOrdermz
   where FNUMBER is null
     and CFSALENUMBER is not null

  update CT_DUC_SendOrdermz
     set FNUMBER = substr(CFSALENUMBER, -17)
   where FNUMBER is null
     and CFSALENUMBER is not null


--批量下推导致采购入库单没有编码的，用对应的标准入库单的单据号填充 标准入库单最后面为4位，为了不和二开的入库单numbe出现一致的可能，拼接0
 
   
  select substr(CFBZNUMBER, -16) || '0'
    from CT_DUC_OutStoragetz
   where FNUMBER is null
     and CFBZNUMBER is not null
           
         update    CT_DUC_OutStoragetz
            set FNUMBER = substr(CFBZNUMBER, -16) || '0'
          where FNUMBER is null
            and CFBZNUMBER is not null


-- 查询 联营发货单 基本计量单位（公斤）  和计量单位(吨)不同  但是数量和基本数量一样的错误数据  
select *
  from CT_DUC_SendOrdermzentry
 where CFAMOUNT = CFSALENUMBER
   and CFBASEUNIID != CFMATERIALUNITID
   and CFMATERIALUNITID = '10YAAAAARvVbglxX'
   and CFBASEUNIID = '10YAAAAARvdbglxX'

-- 查询 销售出库单 基本计量单位（公斤）  和计量单位(吨)不同  但是数量和基本数量一样的错误数据  
select *
  from T_IM_SaleIssueEntry
 where FQty = FBaseQty
   and FUnitID != FBaseUnitID
   and FUnitID = '10YAAAAARvVbglxX'
   and FBaseUnitID = '10YAAAAARvdbglxX'

--修改联营发货单 联营发货单 基本计量单位（公斤）  和计量单位(吨)不同  但是数量和基本数量一样的错误数据     
update CT_DUC_SendOrdermzentry
   set CFAMOUNT = CFSALENUMBER * 1000   where
                  CFBASEUNIID != CFMATERIALUNITID and
                  CFMATERIALUNITID = '10YAAAAARvVbglxX' and
                  CFBASEUNIID = '10YAAAAARvdbglxX'  and   CFAMOUNT = CFSALENUMBER 

-- 修改 销售出库单 基本计量单位（公斤）  和计量单位(吨)不同  但是数量和基本数量一样的错误数据  
update T_IM_SaleIssueEntry
   set FBASEQTY           = FQTY * 1000,
       FWRITTENOFFBASEQTY = FQTY * 1000,
       FDREWBASEQTY       = FQTY * 1000,
       FUNRETURNEDBASEQTY = FQTY * 1000,
       FUNDELIVERBASEQTY = FQTY * 1000,
       FUNINBASEQTY       = FQTY * 1000,
       FASSOCIATEBASEQTY  = FQTY * 1000,
       FUNSETTLEBASEQTY   = FQTY * 1000
 where FUNITID = '10YAAAAARvVbglxX'
   and FBASEUNITID = '10YAAAAARvdbglxX'
   and FQTY = FBASEQTY


--查找 由二开入库单生成的标准入库单 单位实际成本不是0  实际成本是0 的数据
select *
  from T_IM_PurInWarehsEntry
 where FUnitActualCost != 0
   and FActualCost = 0
   and FSOURCEBILLID in (select fid from CT_DUC_OutStoragetz)

--更新成本等数据
update T_IM_PurInWarehsEntry
   set FUNITSTANDARDCOST = FPRICE,
       FUNITACTUALCOST   = FPRICE,
       FUNITPURCHASECOST = FPRICE,
       FPURCHASECOST     = FAMOUNT,
       FSTANDARDCOST     = FAMOUNT,
       FACTUALCOST       = FAMOUNT
 where FUnitActualCost != 0
   and FActualCost = 0
   and FSOURCEBILLID in (select fid from CT_DUC_OutStoragetz)


--查找 由二联营收货单生成的标准入库单 单位实际成本不是0  实际成本是0 的数据
 select *
   from T_IM_PurInWarehsEntry
  where FUnitActualCost != 0
    and FActualCost = 0
    and FSOURCEBILLID in (select fid from CT_DUC_JointRecriptmz)

--料场维护表

merge into  CT_DUC_Materialsuppliere1  a  using  (  select FNAME_L2 ,fid  from T_BD_Supplier      ) t

  on ( t.fid=a.CFSUPPLYNUMBERID )  when matched then update set  a.CFSUPPLYNAME=t.FNAME_L2

--把 来源单据  是联营发货单的 核心单据id     FSaleOrderEntryID  置为空白
 
 merge into  T_IM_SaleIssueEntry  a using (    select   *  from   CT_DUC_SendOrdermzEntry )  t
on ( a.FSourceBillEntryID=t.fid ) when matched then update set a.FSaleOrderEntryID=null, FSaleOrderID=null

艳阳天_.

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
问题记录

// 结算单中付款单养户承担的亏损数据错误修改select * from T_CAS_ReceivingBillEntry where CFCONTRACTNUMBER ='103001-1804-0879'update T_CAS_ReceivingBillEntry set FCONTRACTENTRYSEQ=1 where CFCONTRACTNUMBER ='103001-1804-0879'...
复制链接

扫一扫