CREATE OR REPLACE PROCEDURE proc_update_saleorder IS BEGIN update T_SD_saleorderentry --更改分录资料 set (CFdkdescen, cfmanufacturemno, cfmanufacturemname, cfmanufacturename, cfcategoryen, cfcategorycn, cfleadfreestatus, cfrohsstatus, cfciq, cfciqinfo, cfminimunorder, cfpackagequantaty, cfeccn, CFLogRemark, -- cfnetweight, fdiscount, cfccc, cfhkel) = (select CFdkdescen, cfmanufacturemno, cfmanufacturemname, cfmanufacturename, cfcategoryen, cfcategorycn, cfleadfreestatus, cfrohsstatus, cfciq, cfciqinfo, cfminimunorder, cfpackagequantaty, cfeccn, CFLogisticNote, -- fnetweight, 0, Case When cfccc= 10 Then cfccc Else 20 End , Case When cfhkel=10 Then cfhkel Else 20 End from t_BD_material where T_SD_saleorderentry.FMaterialID = t_BD_material.fid) where fparentid in (select fid from T_SD_saleorder where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null); update T_SD_saleorderentry --更改分录中财务确认 set CFFinVerify=1 where fparentid in (select fid from T_SD_saleorder where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null and (FSettlementTypeId='wYkAAAAAK1XpayuO' or FSettlementTypeId='wYkAAAAAK1bpayuO' or FSettlementTypeId='wYkAAAAPK1LpayuO')); update T_SD_SaleOrderEntry set fprice = ROUND(FTaxPrice / 1.13/(100-nvl(fdiscount,0))*100, 6), FActualPrice = ROUND(FTaxPrice / 1.13, 6), FActualTaxPrice = FTaxPrice, FTaxRate = 13, Ftax = fTaxamount - ROUND(ROUND(FTaxPrice / 1.13, 5)* fqty,2) ,--us 价格精度为5位 #225 FLocalTax = fTaxamount - ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225 FLocalTaxAmount = Ftaxamount, Famount = ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225 FLocalAmount = ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225 Fprepayment = Ftaxamount, FUnPrereceivedAmount = Ftaxamount, FPrepaymentRate =100, Ftaxprice =ROUND(FTaxPrice/(100-nvl(fdiscount,0))*100, 5), --us 价格精度为5位 #225 Fdiscountamount= ROUND(ROUND(FTaxPrice/(100-nvl(fdiscount,0))*100, 5) * fqty*nvl(fdiscount,0)/100,2),--us 价格精度为5位 #225 FDeliveryCustomerID = (SELECT FOrderCustomerID FROM T_SD_SALEORDER WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FReceiveCustomerID = (SELECT FOrderCustomerID FROM T_SD_SALEORDER WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FBaseStatus = '4', FDiscountCondition = '1', FTotalUnReturnBaseQty = fqty, FTotalUnShipBaseQty = fqty, FIsBySaleOrder = '1', FunOrderedQty = fqty, FTotalunProductQty = fqty, FTotalBaseunProductQty = fqty, CFwebOriginalQty = fqty, fsenddate = (SELECT fbizdate+1 FROM T_SD_SALEORDER WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),----将美国时间的后一天作为发货日期。 fdeliverydate = (SELECT fbizdate+1 FROM T_SD_SALEORDER WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid)--,----将美国时间的后一天作为交货日期。 -- cfwuliaokaipiaomc=(select max(a.fdescription_l2) from T_BD_materialgroup a left join T_BD_material b on a.fid=b.fmaterialgroupid ---物料开票名称修改成物料类别备注 -- where T_SD_saleorderentry.fmaterialid=b.fid) where fparentid in (select fid from T_SD_saleorder where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null); -- update T_SD_SaleOrder ---当发票类型为普通且财务联系人公司为空时，把财务联系人做为发票抬头 -- set CFInvoiceHeadLine2 = cffinanceperson -- where CFInvoiceType=0 and CFFinanceCom is null and fbasestatus = 1 -- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; -- update T_SD_SaleOrder ---当发票类型为普通且财务联系人公司不为空时，把公司做为发票抬头 -- set CFInvoiceHeadLine2 = CFFinanceCom -- where CFInvoiceType=0 and CFFinanceCom is not null and fbasestatus = 1 -- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; --update T_SD_SaleOrder ---当客户为账期客户时，结算方式默认成account ，订单类型默认成业务赊销 fsettlementtypeid,cfsobiztype -- set fsettlementtypeid = 'wYkAAAAPK1LpayuO', cfsobiztype=20 -- where fordercustomerid in (select fid from T_BD_customer where fiscredited =1) --and fbasestatus = 1 -- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; update T_SD_SaleOrder set fdescription = (select CFSoNote from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid), cfshuihao = (select FtxregisterNo from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据税号 cfzhucedizhi = (select substr(Faddress,0,50) from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据注册地址 --cfInvoiceHeadLine2 = (select max(CFInvoiceHeadLine) from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据发票抬头 cfgudingdianhua = (select max(fphone) from t_bd_customercompanyinfo where t_sd_saleorder.fordercustomerid=t_BD_customercompanyinfo.fcustomerid and t_sd_saleorder.FControlUnitID = t_bd_customercompanyinfo.FControlUnitID),--修改成主数据固定电话 cfkaihuyinhang = (select max(T_BD_CustomerCompanyBank.fbank) from T_BD_CustomerCompanyBank left join t_bd_customercompanyinfo on T_BD_CustomerCompanyBank.FCustomerCompanyInfoID= t_bd_customercompanyinfo.fid where t_sd_saleorder.fordercustomerid=t_BD_customercompanyinfo.fcustomerid),--修改成主数据最新开户银行 cfyinhangzhanghao = (select max(T_BD_CustomerCompanyBank.FBankAccount) from T_BD_CustomerCompanyBank left join t_bd_customercompanyinfo on T_BD_CustomerCompanyBank.FCustomerCompanyInfoID= t_bd_customercompanyinfo.fid where t_sd_saleorder.fordercustomerid=t_BD_customercompanyinfo.fcustomerid),--修改成主数据最新银行账号 cfdeliveryProvince = (select fid from (select fid,customerId from t_bd_province inner join ( select provinceName,customerId from ( select replace(replace(trim(cfprovince),'省',''),'市','') provinceName,customerId from ( select linkMan.cfprovince,saleInfo.Fcustomerid customerId, case when linkMan.cfmastertype='Logistic' then 1 else 0 end as isMain from t_bd_customerlinkman linkMan inner join t_bd_customersaleinfo saleInfo on linkMan.Fcustomersaleid = saleInfo.fid where linkMan.cfislog = 1 )order by isMain desc ) )custProvince on t_bd_province.fname_l2 = custProvince.provinceName ) province where t_sd_saleorder.fordercustomerid=province.customerId and rownum=1), cfdeliverycity = (select fdescription_l2 from (select fdescription_l2,customerId from t_bd_city inner join ( select cityName,customerId from ( select replace(replace(trim(cfcity),'省',''),'市','') cityName,customerId from ( select linkMan.cfcity,saleInfo.Fcustomerid customerId, case when linkMan.cfmastertype='Logistic' then 1 else 0 end as isMain from t_bd_customerlinkman linkMan inner join t_bd_customersaleinfo saleInfo on linkMan.Fcustomersaleid = saleInfo.fid where linkMan.cfislog = 1 )order by isMain desc ) )custCity on t_bd_city.fname_l2 = custCity.cityName ) city where t_sd_saleorder.fordercustomerid=city.customerId and rownum=1) where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; update T_SD_SaleOrder set fsalepersonid = (select cfcspersonid from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid) ----修改销售员为客户主数据中对应的客服人员 where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null and fsalepersonid is null; ---更新ContinousStrip数据 update T_SD_SaleOrder set (CFContinousStripFlag ,CFDateCodeFlag ,CFDateCodeMonths ,CFPreference , CFLotCodeFlag ,CFCertComplianceFlag ,CFAcceptPartialOrderFlag)= (select CFContinousStripFlag,CFDateCodeFlag , Case When CFDateCodeFlag=1 Then CFDateCodeMonths Else null End CFDateCodeMonths, Case When CFDateCodeFlag=1 Then CFPreference Else '0' End CFPreference , CFLotCodeFlag ,CFCertComplianceFlag ,CFAcceptPartialOrderFlag from T_BD_CustomerSaleInfo where FCustomerID = t_sd_saleorder.fordercustomerid and FControlUnitID = t_sd_saleorder.fcontrolunitid) where exists (select 1 from T_BD_CustomerSaleInfo where FCustomerID = t_sd_saleorder.fordercustomerid) and fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; update T_SD_SaleOrder set FSaleGroupID = 'wYkAAAAAKz4iBwBD', FPrepaymentRate = 100, cfbusinessPerson = cflogisticPerson, cfbusinessNumber = cflogisticNumber, cfbusinessEmail = cflogisticEmail, cfbusinessMobile = cflogisticMobile,-- added by luyl 业务联系人手机号，取物流联系人手机号 cfordertimechina = ( case ------主要是把美国那边冬令制和夏令制的时间还原成中国的北京时间 when fbizdate>= to_date('2014-11-03','yyyy-mm-dd') and fbizdate <= to_date('2015-03-07','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') when fbizdate>= to_date('2015-11-02','yyyy-mm-dd') and fbizdate <= to_date('2016-03-12','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') when fbizdate>= to_date('2016-11-07','yyyy-mm-dd') and fbizdate <= to_date('2017-03-11','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') when fbizdate>= to_date('2017-11-06','yyyy-mm-dd') and fbizdate <= to_date('2018-03-10','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') when fbizdate>= to_date('2018-11-05','yyyy-mm-dd') and fbizdate <= to_date('2019-03-09','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') when fbizdate>= to_date('2019-11-04','yyyy-mm-dd') and fbizdate <= to_date('2020-03-07','yyyy-mm-dd') then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss') else to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date) +NUMTODSINTERVAL(14,'hour'),'yyyy-mm-dd hh24:mi:ss') end ), fbizdate= ( case ------对订单日期做更改。 when to_timestamp(cfordertimechina,'yyyy-mm-dd hh24:mi:ssxff') is null then fcreatetime else to_timestamp(cfordertimechina,'yyyy-mm-dd hh24:mi:ssxff') end ), fpaymenttypeid = '91f078d7-fb90-4827-83e2-3538237b67a06BCA0AB5', CFUpdateSOWebDatetime = sysdate, CFFINVERIFY = (case when FSettlementTypeId='wYkAAAAAK1XpayuO' or FSettlementTypeId='wYkAAAAAK1bpayuO' or FSettlementTypeId='wYkAAAAPK1LpayuO' or FSettlementTypeId = 'wYkAAAFocILpayuO'--微信支付的ID在prod和Uat环境不同,所以此存储过程不能通用 then 1 else 0 end), --更改主表的财务确认 FTotalTaxAmount = (select sum(FTaxAmount) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FLocalTotalTaxAmount = (select sum(FLocalTaxAmount) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), Fprepayment = (select sum(FTaxAmount) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FTotalTax = (select sum(FTax) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FTotalAmount = (select sum(FAmount) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid), FLocalTotalAmount = (select sum(FLocalAmount) from T_SD_SaleOrderEntry WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid) where fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null; commit; END proc_update_saleorder;