医废危废项目

医废危废项目

 医废危废项目

--外购入库单
select  *  from  CT_HW_TempInWarehsBill  inBill  inner join CT_HW_TempInWarehsBillEntry inBillentry   on inBillentry.FParentID =inBill.fid-- 将 入库单的单价同步到处置出库单上   金额处置出库单上的金额重算。
merge into CT_HW_TempIssueBillEntry  a using (    
select inBillentry.fid   billEntryid  ,inBillentry.CFPrice from  CT_HW_TempInWarehsBill  inBill  inner join CT_HW_TempInWarehsBillEntry inBillentry   on inBillentry.FParentID =inBill.fid) t  on  (t.billEntryid=a.CFTempInEntryId) when matched then  update set  a.CFPrice=t.CFPrice, a.CFAmount=round(a.CFUsed*t.CFPrice,3);--处置出库单
select  *  from  CT_HW_TempIssueBill outBill  inner join CT_HW_TempIssueBillEntry outBillentry   on outBillentry.FParentID =outBill.fid  -- 处置出库单的单价 和 入库单的单价一致
select   inBillentry.CFPrice  入库单价,  outBillentry.CFPrice  出库单价,   outBillentry.CFAmount  出库金额    from  CT_HW_TempInWarehsBill  inBill  inner join CT_HW_TempInWarehsBillEntry inBillentry   on inBillentry.FParentID =inBill.fid inner join CT_HW_TempIssueBillEntry  outBillentry   on  inBillentry.fid=outBillentry.CFTempInEntryId

 -------汇总 暂存入库单上分录金额合计 填充到单据表头金额字段上

          merge into   CT_HW_TempInWarehsBill  a   using (select  sum(CFAmount)  sumAmount ,  min(FParentID)  FParentID  from     CT_HW_TempInWarehsBillEntry    group by  FParentID)  ton  ( a.fid=t.FParentID ) when matched  then update set  a.CFAmt=t.sumAmount;

-------更新危废开票申请单上的 开票分录 金额等字段数据

   备份表

Select *  Into  CT_HW_HWReqBillEntrybak   FROM CT_HW_HWReqBillEntry Select *  Into  CT_HW_HWReqBillbak   FROM CT_HW_HWReqBill 

--危废开票申请 不含税单价UPDATE  CT_HW_HWReqBillEntry    SET     CFPrice=  Convert(decimal(28,8),CFTaxPrice/1.06)   -- ！这里税额先写死了  --金额 =不含税单价*数量UPDATE  CT_HW_HWReqBillEntry    SET     CFAmount=  Convert(decimal(28,2),CFPrice*CFQty)  --税额=开票金额-不含税金额UPDATE  CT_HW_HWReqBillEntry    SET     CFTaxAmount= CFInvoicedAmt- CFAmount--更新危废开票单上的 不含税金额合计  税额合计merge into CT_HW_HWReqBill  a  using (   SELECT  min(FParentID) ParentID  ,  sum(CFAmount) Amount  ,  sum(CFTaxAmount) TaxAmount   FROM   CT_HW_HWReqBillEntry  group  by  FParentID) t  on (t.ParentID=a.fid)  when  matched then UPDATE  SET  a.CFTotalAmount=t.Amount ,a.CFTotalTax=t.TaxAmount ;  

 ----- 医废合同  

     select   *  from CT_MW_MWContract  where  CFContractStatu=1   and   datediff(dd, GETDATE(),CFLeffe)<=20   and  cfcontractStatu=1 and  cfauto=0 and  cfinvalidAfterClose=0      select   *  from CT_MW_MWContract  where  CFContractStatu=1   and   datediff(dd, GETDATE(),CFLeffe)<=20   and  cfcontractStatu=1 and ( cfauto=0 or  cfauto is null) and  (cfinvalidAfterClose=0   or  cfinvalidAfterClose is null)   -- 初始化的合同数据  存在自动续签是null  或者 失效后关闭是 null的数据   把是null的数据置为0 未勾选select   *  from CT_MW_MWContract  where   cfauto is null  or   cfinvalidAfterClose is nullUPDATE      CT_MW_MWContract   SET  cfauto=0    WHERE   cfauto is nullUPDATE      CT_MW_MWContract   SET  cfinvalidAfterClose=0    WHERE   cfinvalidAfterClose is null

 ------ 将 处置出库单上分录上的单价 金额  更新到收入确认单分录上    ，将收入确认单分录上的 金额字段汇总合计到表头上

  select * from CT_HW_RevenueAffirmEntry where fparentid ='hdAAAAAwHw5AFFEx'select * from CT_HW_TempIssueBillentry   where fparentid ='hdAAAAAvC0lfxlUe'-- 将 处置出库单上分录上的单价 金额  更新到收入确认单分录上    (处置出库单分录上的 单价 金额  计算出不含税的  更新到对应的收入确认单上)  税率目前写死了置为0.6merge into  CT_HW_RevenueAffirmEntry  a   using  (   select   FID, CFPrice,  CFAmount  from  CT_HW_TempIssueBillentry )  t  on ( t.fid=a.CFIssueEntryId )  when  matched  then update  set a.CFPrice=  Convert(decimal(28,8),t.CFPrice/1.06),a.CFAmount=a.CFUsed* Convert(decimal(28,8),t.CFPrice/1.06);--更新收入确认单表头金额字段merge  into   CT_HW_RevenueAffirm  a  using  (   select    min(FParentID)  fid ,   sum(CFAmount)  sumAmt   from      CT_HW_RevenueAffirmEntry   group by FParentID  ) t  on    (a.fid=t.fid )   when  matched  then  UPDATE      SET   a.CFAmount=t.sumAmt  ; 

------------暂存入库单  新增时 出现   剩余处置数量默认是0 的错误单据， 手工修复数据

---- 查找处置出库单 分录 处置量是0的不正确的数据-------------------------------------------- select * from  CT_HW_TempIssueBillEntry  where CFUsed=0---将料坑的入库单 的数量  生成处置出库单分录的处置量   , 处置出库单的金额重算merge into   CT_HW_TempIssueBillEntry  a  using (  select * from CT_HW_TempInWarehsBillEntry    ) ton ( a.CFTempInEntryId=t.fid  and    a.CFUsed=0  and a.CFWarehouseID='hdAAAAAnuUi76fiu' ) when  matched then update set a.CFUsed=t.CFQty  ,a.CFResidue=t.CFQty  ,a.CFAmount=t.CFQty *a.CFPrice ;----  修复错误的危废收入确认单   处置量是0 的数据select * from CT_HW_RevenueAffirmEntry where fparentid ='hdAAAAAymetAFFEx'-- 将 处置出库单上分录上的单价 金额  更新到收入确认单分录上    (处置出库单分录上的 单价 金额  计算出不含税的  更新到对应的收入确认单上)  税率目前写死了置为0.6merge into  CT_HW_RevenueAffirmEntry  a   using  (   select   FID, CFPrice, CFUsed, CFAmount  from  CT_HW_TempIssueBillentry )  t  on ( t.fid=a.CFIssueEntryId and   a.CFUsed=0  )  when  matched  then update  set a.CFAmount=t.CFUsed*a.CFPrice ;--更新收入确认单表头金额字段merge  into   CT_HW_RevenueAffirm  a  using  (   select    min(FParentID)  fid ,   sum(CFAmount)  sumAmt   from      CT_HW_RevenueAffirmEntry   group by FParentID  ) t  on    (a.fid=t.fid )   when  matched  then  UPDATE      SET   a.CFAmount=t.sumAmt  ;

 select * from CT_HW_HWReqBill where fid ='hdAAAAAtAl/IC9jo'
select * from CT_HW_HWReqBill where fid ='hdAAAAAtAl/IC9jo'-- 反写危废开票申请 凭证号
select  *   from   T_BOT_Relation  where   FSrcObjectID='hdAAAAAtAl/IC9jo'  merge into  CT_HW_HWReqBill  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_HW_HWReqBill reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--- 反写危废收款单 凭证号
select * from CT_HW_HWReceiveReq where fid ='hdAAAAAvtk4oY0C4'merge into  CT_HW_HWReceiveReq  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_HW_HWReceiveReq reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--反写危废收入确认单  凭证号反写
select * from CT_HW_RevenueAffirm where fid ='hdAAAAAvD/JAFFEx'merge into  CT_HW_RevenueAffirm  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_HW_RevenueAffirm reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--医废开票申请  凭证号反写select * from CT_MW_MWReqBill where fid ='hdAAAAAxGadAs1ny'
merge into  CT_MW_MWReqBill  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_MW_MWReqBill reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--医废收款单  凭证号反写
select * from CT_MW_MWReceiveReq where fid ='hdAAAAAw7IrVBKtu'merge into  CT_MW_MWReceiveReq  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_MW_MWReceiveReq reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--一医废收入确认单  凭证号反写
select * from CT_MW_RevenueAffirm where fid ='hdAAAAAv/0B/ERy2'merge into  CT_MW_RevenueAffirm  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_MW_RevenueAffirm reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;--一医废暂估收入确认单  凭证号反写
select * from CT_MW_TempRevenue where fid ='hdAAAAAv/1g9+uT/' merge into  CT_MW_TempRevenue  a  using  (     select  reqbill.fid , voucher.fnumber from   T_BOT_Relation  botp inner join  CT_MW_TempRevenue reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  t  on   t.fid=a.fid  when  matched   then  update set a.CFVoucherNumber=t.fnumber;

-----正常类型的  危废开票申请单  分录中必须要选择暂存入库单，  将分录中分合同 更新到表头中
select  a.fid, b.CFContactNumber  , ( select fid from CT_HW_HWContract where FNumber= b.CFContactNumber ) hwContractId  from CT_HW_HWReqBill   a  inner  join  CT_HW_HWReqBillEntrys2   b  on a.fid=b.fparentid   where  a.CFHwContractID  is null    and a.CFContractType=0  merge into  CT_HW_HWReqBill a  using (  select  distinct   a.fid, b.CFContactNumber  , ( select fid from CT_HW_HWContract where FNumber= b.CFContactNumber ) hwContractId  from CT_HW_HWReqBill   a  inner  join  CT_HW_HWReqBillEntrys2   b  on a.fid=b.fparentid   where  a.CFHwContractID  is null    and a.CFContractType=0      ) t   on  (a.fid=t.fid    )  when matched  then UPDATE   SET   a.CFHwContractID=t.hwContractId;

--- 查找暂存入库单 没有被这张开票单引用，却被反写这张开票单号select * from CT_HW_TempInWarehsBill where fid ='hdAAAAA5gC+z2FIW'select    CT_HW_HWReqBill.fnumber , inWarehs.CFWFKPSQ  from CT_HW_HWReqBillEntrys2   entry2  inner join    CT_HW_HWReqBill    on  CT_HW_HWReqBill.fid=entry2.fparentid   inner join CT_HW_TempInWarehsBill inWarehs   on  inWarehs.fid= entry2.CFTEMPINWAREHSBILL     where      CT_HW_HWReqBill.fnumber!=inWarehs.CFWFKPSQ -- 查找入库单单编码重复的单据select * from CT_HW_TempInWarehsBill 
where fnumber  in (select fnumber  from CT_HW_TempInWarehsBill group by fnumber having count(fnumber) > 1)

-------------------绿源

查找入库单是复制的单据  实际上没有生成凭证  ，但是把凭证号复制了过来

---查找入库单是复制的单据  实际上没有生成凭证  ，但是把凭证号复制了过来
select * from CT_HW_TempInWarehsBill  inbillwhere  fid  not  in(select  reqbill.fid  from   T_BOT_Relation  botp inner join  CT_HW_TempInWarehsBill reqbill  on  reqbill.fid=botp.FSrcObjectID  inner join T_GL_Voucher  voucher on botp.FDestObjectID=voucher.fid)  and   inbill.CFVoucherNumber  is  not  null

修改处置出库单 金额 不正确的单据

--- 处置出库单分录
select * from    CT_HW_TempIssueBillEntry    where fparentid ='hdAAAAB/bPNfxlUe'  and    CFAmount!= Convert(decimal(28,2),CFPrice*CFUsed )  select bill.fnumber 单据编码 ,entry.* from    CT_HW_TempIssueBillEntry  entry  inner join  CT_HW_TempIssueBill  bill  on  bill.fid= entry.fparentid where   CFAmount!= Convert(decimal(28,2),CFPrice*CFUsed )  and   entry.fid!='hdAAAAB7GDHPGEX0'---修正处置出库单 金额、税额 、不含税金额    这里的price 为含税单价update   CT_HW_TempIssueBillEntry   set  CFAmount=  Convert(decimal(28,2),CFPrice*CFUsed ),  CFExclTaxMoney = Convert( decimal(28,2),Convert(decimal(28,8),CFPrice/1.06)*CFUsed ),  CFTax=CFAmount-CFExclTaxMoney    where   CFAmount!= Convert(decimal(28,2),CFPrice*CFUsed )  and   fid!='hdAAAAB7GDHPGEX0'