XSJT项目记录

差旅费报销单 

--- 手工去掉 其他费用税额  4.42 后 后台修改  相关字段   ;  本位币核定不含税额；本位币不含税额 ；核定税额； 原币核定不含税金额；原币不含税金额；

 select  * from   T_BC_TravelAccountBill  where  fnumber='CLBX-01001-2511-000001'

 select  *  from  T_BC_TravelAccountBillEntry  where   FBILLID ='+AJKfeFFSzuqHsW+kyhC6cVwA7w='


--- 手工去掉 其他费用税额  4.42 后 后台修改  相关字段   ;  本位币核定不含税额；本位币不含税额 ；核定税额； 原币核定不含税金额；原币不含税金额；

update   T_BC_TravelAccountBillEntry  set  FAMOUNTAPPROVEDWITHOUTTAX= FAMOUNTAPPROVEDWITHOUTTAX+4.42,FAMOUNTWITHOUTTAX= FAMOUNTWITHOUTTAX+4.42 , FTAXAPPROVED=FTAXAPPROVED-4.42,  
            
FAMOUNTAPPROVEDORIWITHOUTTAX=FAMOUNTAPPROVEDORIWITHOUTTAX+4.42, FAMOUNTORIWITHOUTTAX=FAMOUNTORIWITHOUTTAX+4.42,FOTHEREXPENSETAX=0,CFORITAX=CFORITAX-4.42
   where   FBILLID ='+AJKfeFFSzuqHsW+kyhC6cVwA7w='

查询收票应付报账借方 核算项目表异常数据
 查询收票应付报账贷方 核算项目表异常数据

---- 查询收票应付报账借方 核算项目表异常数据
 select * from  CT_ZCH_JxfpAsstltems
 select      jxfp.fnumber,  jxfpAsstltems.* ,  jxfp.fbizdate    from CT_ZCH_JxfpAsstltems  as   jxfpAsstltems     inner join   CT_ZCH_Jxfp   jxfp  on jxfp.fid=jxfpAsstltems.FPARENTID   where       CFFROMNUMBER is null  or  CFFROMID is null  or  CFENTRYPARENTID  is null  order by FBIZDATE  desc 

 ---- 查询收票应付报账贷方 核算项目表异常数据
 select * from  CT_ZCH_JxfpAsstltemsDebit
 select      jxfp.fnumber,  jxfpAsstltems.* ,  jxfp.fbizdate    from CT_ZCH_JxfpAsstltemsDebit  as   jxfpAsstltems     inner join   CT_ZCH_Jxfp   jxfp  on jxfp.fid=jxfpAsstltems.FPARENTID   where       CFFROMNUMBER is null  or  CFFROMID is null  or  CFENTRYPARENTID  is null  order by FBIZDATE  desc 

  sql 查找收款单分录对方科目 选择的数据 不是当前公司的记录

---- sql 查找收款单分录对方科目 选择的数据 不是当前公司的记录
 
select   bill.fnumber  收款单编码 , account.fid  科目id    from    T_CAS_ReceivingBill  bill  inner join   T_CAS_ReceivingBillEntry  entry  on  entry.FRECEIVINGBILLID=bill.fid     inner  join   T_BD_AccountView account  on   account.fid=entry.FOPPACCOUNTID    where  account.FCOMPANYID!=bill.FCOMPANYID

 
---- sql 查找付款单分录对方科目 选择的数据 不是当前公司的记录
 
select   bill.fnumber  付款单编码 , account.fid  科目id  ,    billCompany.FNAME_l2 单据所属公司  ,  accountunit.FNAME_l2  科目所属公司     from    T_CAS_PaymentBill  bill  inner join   T_CAS_PaymentBillEntry  entry  on  entry.FPAYMENTBILLID=bill.fid     inner  join   T_BD_AccountView account  on   account.fid=entry.FOPPACCOUNTID     inner join      T_ORG_BaseUnit  accountunit on accountunit.fid= account.FCOMPANYID    inner join  T_ORG_BaseUnit  billCompany  on  billCompany.fid=  bill.FCOMPANYID    where  account.FCOMPANYID!=bill.FCOMPANYID

修改支出合同线下已付款金额

 ---更新分录已付款金额
update    CT_ZCH_ZchtEntry   set    CFSFJE= CFFKJE,   CFYSQJE=CFFKJE ,    CFWSQJE=0  ,  CFYFKJE= CFFKJE    where     fparentid =(   select   fid  from  CT_ZCH_Zcht  where  fnumber='ZCHT-01001-2407-000017'   )  and   CFFKSM  LIKE '%已付%' 

 --已付款金额  已开票金额   更新
merge  into   CT_ZCH_Zcht   a    using  (   select   min(  bill.fid )  fid  ,    sum( entry.cfyfkje ) sumyfkje     from    CT_ZCH_Zcht   bill  inner join   CT_ZCH_ZchtEntry  entry  on  entry.fparentid=bill.fid  group  by   bill.fid   )  t on  (  a.fid=t.fid   and   a.fnumber='ZCHT-01001-2407-000038'   )   when  matched  then  update set   a.cfyfkje  =t.sumyfkje ,  CFYKPJE =t.sumyfkje
     
     
--未付款金额  未开票金额  更新
merge  into   CT_ZCH_Zcht   a    using  (   select   min(  bill.fid )  fid  ,    sum( CFWSQJE ) sumWSQJE     from    CT_ZCH_Zcht   bill  inner join   CT_ZCH_ZchtEntry  entry  on  entry.fparentid=bill.fid  group  by   bill.fid         )  t on  (  a.fid=t.fid   and  a.fnumber='ZCHT-01001-2407-000038'  )    when  matched  then  update set   a.cfwfkje  =t.sumWSQJE  ,  CFWKPJE =t.sumWSQJE

 

映射科目维护查找重复的数据

 SELECT CFACCVIEWID , CFCOSTTYPEID ,CFTYPEID  ,  COUNT(*)   FROM CT_BA_BillRelationEntry   where   FPARENTID ='o5ki+/TBTaWRg76COlTJI8InbdM='
 
GROUP BY   CFACCVIEWID , CFCOSTTYPEID ,CFTYPEID   HAVING COUNT(*) > 1 

支出合同将分录的未付款金额合计更新至表头字段

merge  into   CT_ZCH_Zcht   a    using  (   select   min(  bill.fid )  fid  ,    sum( CFWSQJE ) sumWSQJE     from    CT_ZCH_Zcht   bill  inner join   CT_ZCH_ZchtEntry  entry  on  entry.fparentid=bill.fid  group  by   bill.fid         )  t on  (  a.fid=t.fid  )


when  matched  then  update set   a.cfwfkje  =t.sumWSQJE

处理影像收款单   影像异常单据， 单据上传了影像，影像系统内能看到，但是点击查看  提示无影像

核心原因是 单据影像映射表 存在多个记录，代码只会获取一条，而且影响状态字段为2 的记录

select * from T_WSC_BillImageMap   where   FBILLID =(   select fid  from T_CAS_ReceivingBill where  fnumber='SKDJ-01001-2402-000252'  )

删除其他记录  只保留一条。

--- 辅助账初始化 也在科目余额这张表中

 
--- 辅助账初始化 也在科目余额这张表中
select    * from  T_GL_ASSISTBALANCE  where  FASSISTGRPID  = (   select  fid  from T_BD_AssistantHG where   CFITEMSID =  (     select   fid  from CT_CUS_Items   where   FNAME_L2  ='绿源车辆停保中心项目'  )    )
 
and   FORGUNITID='////8P//7//QMAAAAAAAT8znrtQ='

查找在流程中还是暂存状态的单据

select   *  from   T_WFR_Assign  assign   inner join  T_WFR_ProcInst  inst   on assign.FPROCINSTID=inst.FPROCINSTID    where   FBIZOBJID  in   (    select   fid   from    CT_ZCH_Fksq    where   CFDJZT='1'   )  and   inst.FSTATE='open.running'




 --- 修复二开付款申请单 进入提交进入流程单据，单据状态依然是暂存的错误数据
update   CT_ZCH_Fksq  set    CFDJZT='2'  where   fid   in  (     
select  FBIZOBJID  from   T_WFR_Assign  assign   inner join  T_WFR_ProcInst  inst   on assign.FPROCINSTID=inst.FPROCINSTID    where   FBIZOBJID  in   (    select   fid   from    CT_ZCH_Fksq    where  CFDJZT='1'    )  and   inst.FSTATE='open.running'     and    assign.FBIZOPERATION='ActionSubmit'  )   and   CFDJZT='1'  

查询合同、付款申请、付款单 反写数据sql

---保存=10,已提交=11,已审批=12,已收款=14,已付款=15,审批中=6,已审核=8


select    zcht.fnumber 支出合同编码 ,    zchtentry.CFSFJE 支出合同分录实付金额 , zchtentry.CFWFJE 支出合同分录未付金额 ,    bill.fnumber  付款申请单编码 ,   entry.fseq 付款申请分录序号, entry.CFSQJE 付款申请分录申请金额,   entry.CFCDJE  冲抵金额 ,    entry.CFZFJE 付款申请分录支付金额,   entry.CFSfje 付款申请分录实付金额,   case    paymentBill.FBillStatus  when 15  then
'已付款' when 12 then '已审批'  when 10 then '保存'  when 11  then '已提交'   when 8  then '已审核'   END AS result    , bill.CFHASCONTRACT  是否有合同  ,     paymentBill.fnumber  付款单编码 ,    paymentBill.FBIZDATE  付款单业务日期 ,   payentry.FActualAmt   付款分录实付金额   from  CT_ZCH_Fksq  bill   inner join   CT_ZCH_FksqEntry  entry  on  entry.fparentid=bill.fid  inner join  T_CAS_PaymentBillEntry  payentry on  payentry.FSourceBillEntryId =entry.fid   inner join T_CAS_PaymentBill  paymentBill  on   paymentBill.fid=payentry.FPAYMENTBILLID      left  join    CT_ZCH_ZchtEntry   zchtentry  on  zchtentry.fid=entry.CFZCHTENTRYID   left  join      CT_ZCH_Zcht  zcht  on  zcht.fid=  zchtentry.fparentid   order  by    bill.fnumber ,  entry.fseq

更新付款申请单  收款方F7不为空，  收款方文本为空的数据 

select   entry.*  ,    bill.fbizdate   from  CT_ZCH_FksqEntry   entry  inner join  CT_ZCH_Fksq   bill   on  bill.fid=   entry.fparentid   where  CFSKFID  is  not  null  and  CFPAYEE  is null

   --- 更新付款申请单  收款方F7不为空，  收款方文本为空的数据
   update     CT_ZCH_FksqEntry   set   CFPAYEE= (    select    fname_l2  from T_BD_Supplier where fid   =  CFSKFID    )   where  CFSKFID  is  not  null  and  CFPAYEE  is null

无合同付款申请单  付款账号更新 

---- 无合同付款申请单  付款账号更新

update    CT_ZCH_Fksq   set   CFPAYACCOUNTTXT  =  (      
 select  FBANKACCOUNTNUMBER from T_BD_AccountBanks   where  fid=   CFPAYACCOUNTID )   where   CFPAYACCOUNTID is  not  null 

  更新付款申请单   银行F7不为空，  银行 方文本为空的数据 

-- 更新付款申请单   银行F7不为空，  银行 方文本为空的数据
   update     CT_ZCH_FksqEntry   set  CFSKYH = (    select    fname_l2  from  T_BE_BEBank where fid   =  CFRECEIVEBANKID)   where   CFRECEIVEBANKID  is  not  null  and  CFSKYH   is null

 sqlsever 截取业务日期

  substring( CONVERT(varchar(100), FBizDate , 23),1,7 )=substring('@yearMonth',1,7)  

 oracle 截取业务日期
 

select   SUBSTRING('2023-02-01', 1, 7)   ,   SUBSTRING(to_char(  FBizDate,'yyyy-MM-dd'), 1, 7)   from   CT_DM_LowConsumablesOW

  --- 更新财务账单应收金额字段小数位 大于2位数据  四舍五入保留2位
  update CT_ACC_AccountBillEntry set CFAmt=round(CFAmt,2) where    floor(CFAmt*100)/100<>CFAmt  


 --- 更新财务账单欠收金额字段小数位 大于2位数据  四舍五入保留2位
  update CT_ACC_AccountBillEntry set CFDebt=round(CFDebt,2) where    floor(CFDebt*100)/100<>CFDebt   


  --- 应收账单
  select  * from      CT_ACC_ReceiveBillEntry   where floor(CFDebt*100)/100<>CFDebt  

  --- 更新应收账单  应收金额字段小数位 大于2位数据  四舍五入保留2位
  update CT_ACC_ReceiveBillEntry set CFAmt=round(CFAmt,2) where    floor(CFAmt*100)/100<>CFAmt  


 --- 更新应收账单欠收金额字段小数位 大于2位数据  四舍五入保留2位
  update CT_ACC_ReceiveBillEntry set CFDebt=round(CFDebt,2) where    floor(CFDebt*100)/100<>CFDebt   

---按照项目统计房源的    套内面积  可出租面积  已出租面积合计

select  min(a.FName_l2) 项目 ,  sum(b.CFInsideArea) 套内面积 , sum(b.CFRentArea) 可出租面积  ,  sum( b.CFRentedArea ) 已出租面积     from   CT_BAS_Room  as  b  inner join   CT_BAS_Project    as a  on  a.fid=b.CFProjectID    where b.CFProjectID='RsoAAAAMf8AuV5QY'

select  *   from   CT_ACC_ReceiveBillEntry  where  fparentid in (   select fid   from  CT_ACC_ReceiveBill  where   CFRentContractID in (  select fid from   CT_LEA_RentContract  where FNumber   in ('ZLHT-202206-000084','ZLHT-202206-000090','ZLHT-202206-000085','ZLHT-202206-000086','ZLHT-202206-000087','ZLHT-202206-000088','ZLHT-202206-000089','ZLHT-202206-000091','ZLHT-202206-000092','ZLHT-202206-000093','ZLHT-202206-000094','ZLHT-202206-000095','ZLHT-202206-000096','ZLHT-202206-000097','ZLHT-202206-000098','ZLHT-202206-000099','ZLHT-202206-000100','ZLHT-202206-000101','ZLHT-202206-000102','ZLHT-202206-000103' ) )  )

 修复botp管理关系  以凭证为列

 //应付单-凭证
	     public  void  updateBotp(   ) throws BOSException, SQLException, ClassNotFoundException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
     
    	NianJinCollection billBaseCollection =NianJinFactory.getRemoteInstance().getNianJinCollection(" where   CREATETIME >='2024-08-22'    " );
    	for (int i = 0; i < billBaseCollection.size(); i++) {
    		NianJinInfo billBaseInfo =  billBaseCollection.get(i);  
    	    if(null== billBaseInfo.getPzNumber() )  continue;
    		String sourceBillId= billBaseInfo.getId().toString() ;
 
    		String destBillId ="";                                                                                //期间是8月份
    	    String  sql="  select  fid from   T_GL_Voucher  where  fnumber='"+billBaseInfo.getPzNumber() +"' and   FPERIODID='////8P//7//QMAAAAAAkT4I4jEw='    ";
     	    IRowSet executeSQL = SQLExecutorFactory.getRemoteInstance(sql).executeSQL();
	    	while(executeSQL.next()) { 
	    		destBillId = executeSQL.getString("fid");
	    	}
 
	    	 if(!StringUtils.isEmpty(sourceBillId)) {
		        String botMapping = getMaping (    sourceBillId, destBillId  );
	    	    addnewBotp( sourceBillId,  destBillId, botMapping,  billBaseInfo.getBizDate(),"系统_SheBao-凭证 ") ; 
		     }else {
		    	 System.out.println("来源单据为空"+sourceBillId);
		      }
    	}
    }
	

 查询凭证 选择 某个核算项目 数据

select    unit.fnumber 公司编码,  unit.fname_l2  公司,   voucher.fnumber as 凭证号  , hg.FDISPLAYNAMEGROUP_L2 as 辅助账组合名称  from   T_GL_Voucher  voucher  inner join  T_GL_VoucherEntry  voucherEntry  on   voucherEntry.FBillID=voucher.fid  inner join  T_BD_AsstAccount  assAccount  on  assAccount.fid=voucherEntry.FCAA

inner join  T_GL_VoucherAssistRecord  assAccountRecord   on   assAccountRecord.FENTRYID=voucherEntry.fid  inner join  T_BD_AssistantHG  hg  on   assAccountRecord.FASSGRPID=hg.fid

inner join  T_ORG_BaseUnit  as unit on  unit.fid= voucher.FCOMPANYID   

where    assAccount.FNAME_L2 LIKE '%项目%'    and    hg.FDISPLAYNAMEGROUP_L2  LIKE '%01-999%'   and    voucher.FCOMPANYID='////8P//7//QMAAAAAAAT8znrtQ=' 

查询 选择到 未分配到的项目 凭证数据 

--查询 选择到 未分配到的项目 凭证数据
select    hg.fid 辅助账横表ID , hg.FCONTROLUNITID  辅助账横表控制单元 ,  hg.CFITEMSID 项目id ,   items.fnumber as 项目编码 ,   unit.fid 公司ID ,   unit.fnumber 公司编码,  unit.fname_l2  公司,   voucher.fnumber as 凭证号  , hg.FDISPLAYNAMEGROUP_L2 as 辅助账组合名称  from   T_GL_Voucher  voucher  inner join  T_GL_VoucherEntry  voucherEntry  on   voucherEntry.FBillID=voucher.fid  inner join  T_BD_AsstAccount  assAccount  on  assAccount.fid=voucherEntry.FCAA

inner join  T_GL_VoucherAssistRecord  assAccountRecord   on   assAccountRecord.FENTRYID=voucherEntry.fid  inner join  T_BD_AssistantHG  hg  on   assAccountRecord.FASSGRPID=hg.fid

inner join  T_ORG_BaseUnit  as unit on  unit.fid= voucher.FCOMPANYID   

inner join  CT_CUS_Items  items  on  items.fid=  hg.CFITEMSID

where    assAccount.FNAME_L2 LIKE '%项目%'   

-- and    hg.FDISPLAYNAMEGROUP_L2  LIKE '%01-999%'             
--and    voucher.FCOMPANYID='////8P//7//QMAAAAAAAT8znrtQ='   
and  items.CFTARGETCOMPANYIDS  not  like CONCAT(CONCAT('%', unit.fid ),'%')

 费用报销单 金额字段 相关修改SQL

select * from T_BC_BizAccountBillEntry where fid ='O3dQIZSDSqaZHRsF9xMPsvIGLxM='


 select   *   from   T_BC_BIZACCOUNTBILL  where   fnumber='FYBX-01128-2408-000002' 


 update  T_BC_BIZACCOUNTBILL   set   FAMOUNT=1000,FAMOUNTAPPROVED=1000, FAMOUNTENCASHED=1000   where   fnumber='FYBX-01128-2408-000002' 


update   T_BC_BizAccountBillEntry   set       FAMOUNT=1000,   FAMOUNTAPPROVED=1000,  FAMOUNTORI=1000,  FAMOUNTAPPROVEDORI=1000,  FAMOUNTWITHOUTTAX=1000,    FAMOUNTAPPROVEDWITHOUTTAX=1000,   FAMOUNTAPPROVEDORIWITHOUTTAX=1000,   FAMOUNTORIWITHOUTTAX=1000   where fid ='O3dQIZSDSqaZHRsF9xMPsvIGLxM='

差旅费报销单 之前有实现功能， 普通发票清空税额 税率 有些问题，   提交的时候标准校验不通过  待优化

select  * from   T_BC_TravelAccountBillEntry    where    FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w='



select  * from   T_BC_TravelAccountBill  where   fnumber ='CLBX-01075-2504-000002'



select  * from   T_BC_TravelAccountBillEntry    where    FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w=' and  fseq=1

---- 差旅费报销单不含税金额  
update   T_BC_TravelAccountBillEntry   set    FAMOUNTAPPROVEDWITHOUTTAX ='2791.13'  , FAMOUNTWITHOUTTAX='2791.13',FAMOUNTAPPROVEDORIWITHOUTTAX='2791.13',FAMOUNTORIWITHOUTTAX='2791.13'  where   FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w='    and  fseq=2

----差旅费报销分录 其他费用
update   T_BC_TravelAccountBillEntry   set    FOTHEREXPENSEWITHOUTTAX='0' ,   FOTHEREXPENSETAX='0'   where   FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w='    and  fseq=2

---- 差旅费报销单税额
update   T_BC_TravelAccountBillEntry   set    FTAXAPPROVED  ='139.87'  , CFORITAX ='139.87'   where   FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w='   and  fseq=2  




update   T_BC_TravelAccountBillEntry   set    FAMOUNTAPPROVEDWITHOUTTAX ='698.11'  , FAMOUNTWITHOUTTAX='698.11',FAMOUNTAPPROVEDORIWITHOUTTAX='698.11',FAMOUNTORIWITHOUTTAX='698.11'  where   FBILLID ='Zm23ck52TuCM+czL82AEvcVwA7w='  and  fseq=1

供应商相关问题

SELECT
      supplier.fname_l2 AS 供应商名称,
      supplierBank.FBANK AS 开户银行,
      supplierBank.FBANKACCOUNT AS 银行账号,
      unit.fname_l2 AS 财务组织
  FROM T_BD_SupplierCompanyBank supplierBank
  INNER JOIN T_BD_SupplierCompanyInfo supplierCompany
      ON supplierCompany.fid = supplierBank.FSUPPLIERCOMPANYINFOID
  INNER JOIN T_ORG_BaseUnit unit
      ON unit.fid = supplierCompany.FCOMORGID
  INNER JOIN T_BD_Supplier supplier
      ON supplier.fid = supplierCompany.FSUPPLIERID
  WHERE supplier.FBROWSEGROUPID = 'OBa2OiDvTNOpLxoVxrUJ73olaaI='
    AND EXISTS (
        -- 子查询：同一公司 + 同一供应商，有多个不同账号
        SELECT 1
        FROM T_BD_SupplierCompanyBank sb
        INNER JOIN T_BD_SupplierCompanyInfo sc ON sc.fid = sb.FSUPPLIERCOMPANYINFOID
        WHERE sc.FCOMORGID = supplierCompany.FCOMORGID           -- 同一公司
          AND sc.FSUPPLIERID = supplierCompany.FSUPPLIERID       -- 同一供应商
        GROUP BY sc.FCOMORGID, sc.FSUPPLIERID
        HAVING COUNT(DISTINCT sb.FBANKACCOUNT) > 1
    )
  ORDER BY unit.fname_l2, supplier.fname_l2, supplierBank.FBANKACCOUNT

SELECT
      supplier.fname_l2 AS 供应商名称,
      supplierBank.FBANK AS 开户银行,
      supplierBank.FBANKACCOUNT AS 银行账号,
      unit.fname_l2 AS 财务组织
  FROM T_BD_SupplierCompanyBank supplierBank
  INNER JOIN T_BD_SupplierCompanyInfo supplierCompany
      ON supplierCompany.fid = supplierBank.FSUPPLIERCOMPANYINFOID
  INNER JOIN T_ORG_BaseUnit unit
      ON unit.fid = supplierCompany.FCOMORGID
  INNER JOIN T_BD_Supplier supplier
      ON supplier.fid = supplierCompany.FSUPPLIERID
  WHERE supplier.FBROWSEGROUPID = 'OBa2OiDvTNOpLxoVxrUJ73olaaI='
    AND supplier.fname_l2 IN (
        -- 子查询：找出有多个不同银行账号的供应商
        SELECT s.fname_l2
        FROM T_BD_SupplierCompanyBank sb
        INNER JOIN T_BD_SupplierCompanyInfo sc ON sc.fid = sb.FSUPPLIERCOMPANYINFOID
        INNER JOIN T_BD_Supplier s ON s.fid = sc.FSUPPLIERID
        WHERE s.FBROWSEGROUPID = 'OBa2OiDvTNOpLxoVxrUJ73olaaI='
        GROUP BY s.fname_l2
        HAVING COUNT(DISTINCT sb.FBANKACCOUNT) > 1
    )
  ORDER BY supplier.fname_l2, supplierBank.FBANKACCOUNT ,  unit.fname_l2

botp 插入  开票应收 正  与 负的 关系表

 -- 找出所有配对的记录，一一对应显示FOPERATORID
SELECT 
    pos.FID as 正数发票ID,
    neg.FID as 负数冲红ID,
    pos.CFCUSTOMERID as 客户ID,
    pos.CFSUMAMOUNT as 正数金额,
    neg.CFSUMAMOUNT as 负数金额,
    ABS(pos.CFSUMAMOUNT) as 配对金额,
    pos.*,  -- 正数发票所有字段
    neg.*   -- 负数冲红所有字段
FROM 
    (SELECT * FROM CT_RM_GenericInvoiceBill WHERE CFSUMAMOUNT > 0) pos
    JOIN 
    (SELECT * FROM CT_RM_GenericInvoiceBill WHERE CFSUMAMOUNT < 0) neg
    ON pos.CFCUSTOMERID = neg.CFCUSTOMERID 
    AND pos.CFSUMAMOUNT = -neg.CFSUMAMOUNT
ORDER BY pos.CFCUSTOMERID, pos.CFSUMAMOUNT 

 
 

-- 直接插入配对关系
INSERT INTO T_BOT_Relation (
    FID,
    FSrcEntityID,
    FDestEntityID,
    FSrcObjectID,
    FDestObjectID,
    FDate,
    FOperatorID,
    FBOTMappingID,
    FisEffected,
    FType
)
SELECT 
    newbosid('59302EC6') as FID,           -- 使用newbosid函数生成ID
    '6CA99BA5' as FSrcEntityID,            -- 源实体ID
    '6CA99BA5' as FDestEntityID,           -- 目标实体ID
    pos.FID as FSrcObjectID,               -- 正数发票ID作为源对象
    neg.FID as FDestObjectID,              -- 负数发票ID作为目标对象
    SYSDATE as FDate,                      -- 当前系统时间
    'mz' as FOperatorID,                   -- 操作人
    NULL as FBOTMappingID,                 -- 转换规则
    1 as FisEffected,                      -- 是否生效
    1 as FType                             -- 类型
FROM 
    (SELECT * FROM CT_RM_GenericInvoiceBill WHERE CFSUMAMOUNT > 0) pos
    JOIN 
    (SELECT * FROM CT_RM_GenericInvoiceBill WHERE CFSUMAMOUNT < 0) neg
    ON pos.CFCUSTOMERID = neg.CFCUSTOMERID 
    AND pos.CFSUMAMOUNT = -neg.CFSUMAMOUNT
WHERE NOT EXISTS (
    SELECT 1 FROM T_BOT_Relation r 
    WHERE r.FSrcObjectID = pos.FID 
      AND r.FDestObjectID = neg.FID
) 

-- 插入配对关系：开票应收单作为源单，T_IM_MakeInvoice作为目标单
INSERT INTO T_BOT_Relation (
    FID,
    FSrcEntityID,
    FDestEntityID,
    FSrcObjectID,
    FDestObjectID,
    FDate,
    FOperatorID,
    FBOTMappingID,
    FisEffected,
    FType
)
SELECT 
    newbosid('59302EC6') as FID,              -- 使用源单的BOSTYPE生成ID
    '6CA99BA5' as FSrcEntityID,               -- 源实体ID（开票应收单的BOSTYPE）
    '412A6F98' as FDestEntityID,              -- 目标实体ID（T_IM_MakeInvoice的BOSTYPE）
    rib.FID as FSrcObjectID,                  -- 源对象ID：开票应收单的FID
    mi.FID as FDestObjectID,                  -- 目标对象ID：T_IM_MakeInvoice的FID
    SYSDATE as FDate,                         -- 当前系统时间
    'mz' as FOperatorID,                      -- 操作人
    NULL as FBOTMappingID,                    -- 转换规则
    1 as FisEffected,                         -- 是否生效
    1 as FType                                -- 类型
FROM 
    CT_RM_GenericInvoiceBill rib              -- 源表：开票应收单
    JOIN 
    T_IM_MakeInvoice mi                       -- 目标表：开票单
    ON rib.CFCUSTOMERID = mi.FCURRACCTCUSTOMERID  -- 客户相同
    AND rib.CFSUMAMOUNT = mi.FPRICETAXTOTAL       -- 金额相等（都是负的）
WHERE rib.CFSUMAMOUNT < 0 AND mi.FPRICETAXTOTAL < 0  -- 确保都是负数
    AND NOT EXISTS (  -- 避免重复插入
        SELECT 1 FROM T_BOT_Relation r 
        WHERE r.FSrcEntityID = '6CA99BA5' 
          AND r.FDestEntityID = '412A6F98'
          AND r.FSrcObjectID = rib.FID 
          AND r.FDestObjectID = mi.FID
    )

修复收票应付单据  辅助账核算异常问题

   select  * from      CT_ZCH_Jxfp  where  fnumber='HTFP-01075-2601-000012'      --  9E7Q0NhbRHCeP0MOyydZdjfGffw=     --   0yqMXR/DTpCNG2t0OUU3aI40neo=  01075-003   01-004178
                      
  
    select  * from    CT_ZCH_JxfpEntry  where   FPARENTID= (     select   fid  from      CT_ZCH_Jxfp  where  fnumber='HTFP-01075-2601-000012'   ) 

   ---对方核算项目
    select  * from    CT_ZCH_JxfpAsstltems    where   FPARENTID= (     select   fid  from      CT_ZCH_Jxfp  where  fnumber='HTFP-01075-2601-000012'   ) 


   --借方核算项目
     select  * from    CT_ZCH_JxfpAsstltemsDebit    where   FPARENTID= (     select   fid  from      CT_ZCH_Jxfp  where  fnumber='HTFP-01075-2601-000012'   ) 


     select  * from    CT_ZCH_JxfpAsstltems    where    

     select  * from    CT_ZCH_JxfpAsstltemsDebit   where  CFENTRYPARENTID LIKE '%jqg%'  or    CFFROMID  is  null

     select  * from    CT_ZCH_JxfpAsstltems    where  CFENTRYPARENTID LIKE '%jqg%'  or    CFFROMID  is  null


       

  select  * from      CT_ZCH_Jxfp   where  fid  in  (        select  FPARENTID     from    CT_ZCH_JxfpAsstltemsDebit   where  CFENTRYPARENTID LIKE '%jqg%'  or    CFFROMID  is  null     )      and  to_char(FBIZDATE,'yyyy-MM-dd')>=   '2026-01-01'

 
  select  * from      CT_ZCH_Jxfp   where  fid  in  (        select  FPARENTID     from    CT_ZCH_JxfpAsstltems   where  CFENTRYPARENTID LIKE '%jqg%'  or    CFFROMID  is  null     )      and  to_char(FBIZDATE,'yyyy-MM-dd')>=   '2026-01-01'


  select * from CT_ZCH_JxfpEntry where fid ='HklixaqXTBKSX0RRzvdQRmQyCsw='           D0DeqbqkQjaWL151PbIWLY40neo=              Gq9oYVVLRdWFpjheA93sC8znrtQ=