|
1
|
CREATE OR REPLACE PROCEDURE proc_update_saleorder IS
|
|
2
|
BEGIN
|
|
3
|
update T_SD_saleorderentry --更改分录资料
|
|
4
|
set (CFdkdescen,
|
|
5
|
cfmanufacturemno,
|
|
6
|
cfmanufacturemname,
|
|
7
|
cfmanufacturename,
|
|
8
|
cfcategoryen,
|
|
9
|
cfcategorycn,
|
|
10
|
cfleadfreestatus,
|
|
11
|
cfrohsstatus,
|
|
12
|
cfciq,
|
|
13
|
cfciqinfo,
|
|
14
|
cfminimunorder,
|
|
15
|
cfpackagequantaty,
|
|
16
|
cfeccn,
|
|
17
|
CFLogRemark,
|
|
18
|
-- cfnetweight,
|
|
19
|
fdiscount,
|
|
20
|
cfccc,
|
|
21
|
cfhkel) =
|
|
22
|
(select CFdkdescen,
|
|
23
|
cfmanufacturemno,
|
|
24
|
cfmanufacturemname,
|
|
25
|
cfmanufacturename,
|
|
26
|
cfcategoryen,
|
|
27
|
cfcategorycn,
|
|
28
|
cfleadfreestatus,
|
|
29
|
cfrohsstatus,
|
|
30
|
cfciq,
|
|
31
|
cfciqinfo,
|
|
32
|
cfminimunorder,
|
|
33
|
cfpackagequantaty,
|
|
34
|
cfeccn,
|
|
35
|
CFLogisticNote,
|
|
36
|
-- fnetweight,
|
|
37
|
0,
|
|
38
|
Case When cfccc= 10 Then cfccc Else 20 End ,
|
|
39
|
Case When cfhkel=10 Then cfhkel Else 20 End
|
|
40
|
from t_BD_material
|
|
41
|
where T_SD_saleorderentry.FMaterialID = t_BD_material.fid)
|
|
42
|
where fparentid in (select fid
|
|
43
|
from T_SD_saleorder
|
|
44
|
where fbasestatus = 1
|
|
45
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null);
|
|
46
|
|
|
47
|
update T_SD_saleorderentry --更改分录中财务确认
|
|
48
|
set CFFinVerify=1
|
|
49
|
where fparentid in (select fid
|
|
50
|
from T_SD_saleorder
|
|
51
|
where fbasestatus = 1
|
|
52
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null
|
|
53
|
and (FSettlementTypeId='wYkAAAAAK1XpayuO' or FSettlementTypeId='wYkAAAAAK1bpayuO' or FSettlementTypeId='wYkAAAAPK1LpayuO'));
|
|
54
|
|
|
55
|
update T_SD_SaleOrderEntry
|
|
56
|
set fprice = ROUND(FTaxPrice / 1.13/(100-nvl(fdiscount,0))*100, 6),
|
|
57
|
|
|
58
|
FActualPrice = ROUND(FTaxPrice / 1.13, 6),
|
|
59
|
FActualTaxPrice = FTaxPrice,
|
|
60
|
FTaxRate = 13,
|
|
61
|
Ftax = fTaxamount - ROUND(ROUND(FTaxPrice / 1.13, 5)* fqty,2) ,--us 价格精度为5位 #225
|
|
62
|
FLocalTax = fTaxamount - ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225
|
|
63
|
FLocalTaxAmount = Ftaxamount,
|
|
64
|
Famount = ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225
|
|
65
|
|
|
66
|
FLocalAmount = ROUND(ROUND(FTaxPrice / 1.13, 5) * fqty,2),--us 价格精度为5位 #225
|
|
67
|
Fprepayment = Ftaxamount,
|
|
68
|
FUnPrereceivedAmount = Ftaxamount,
|
|
69
|
FPrepaymentRate =100,
|
|
70
|
Ftaxprice =ROUND(FTaxPrice/(100-nvl(fdiscount,0))*100, 5), --us 价格精度为5位 #225
|
|
71
|
Fdiscountamount= ROUND(ROUND(FTaxPrice/(100-nvl(fdiscount,0))*100, 5) * fqty*nvl(fdiscount,0)/100,2),--us 价格精度为5位 #225
|
|
72
|
FDeliveryCustomerID =
|
|
73
|
(SELECT FOrderCustomerID
|
|
74
|
FROM T_SD_SALEORDER
|
|
75
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
76
|
FReceiveCustomerID =
|
|
77
|
(SELECT FOrderCustomerID
|
|
78
|
FROM T_SD_SALEORDER
|
|
79
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
80
|
FBaseStatus = '4',
|
|
81
|
FDiscountCondition = '1',
|
|
82
|
FTotalUnReturnBaseQty = fqty,
|
|
83
|
FTotalUnShipBaseQty = fqty,
|
|
84
|
FIsBySaleOrder = '1',
|
|
85
|
FunOrderedQty = fqty,
|
|
86
|
FTotalunProductQty = fqty,
|
|
87
|
FTotalBaseunProductQty = fqty,
|
|
88
|
CFwebOriginalQty = fqty,
|
|
89
|
fsenddate =
|
|
90
|
(SELECT fbizdate+1
|
|
91
|
FROM T_SD_SALEORDER
|
|
92
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),----将美国时间的后一天作为发货日期。
|
|
93
|
fdeliverydate = (SELECT fbizdate+1
|
|
94
|
FROM T_SD_SALEORDER
|
|
95
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid)--,----将美国时间的后一天作为交货日期。
|
|
96
|
-- cfwuliaokaipiaomc=(select max(a.fdescription_l2) from T_BD_materialgroup a left join T_BD_material b on a.fid=b.fmaterialgroupid ---物料开票名称修改成物料类别备注
|
|
97
|
-- where T_SD_saleorderentry.fmaterialid=b.fid)
|
|
98
|
where fparentid in (select fid
|
|
99
|
from T_SD_saleorder
|
|
100
|
where fbasestatus = 1
|
|
101
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null);
|
|
102
|
|
|
103
|
-- update T_SD_SaleOrder ---当发票类型为普通且财务联系人公司为空时，把财务联系人做为发票抬头
|
|
104
|
-- set CFInvoiceHeadLine2 = cffinanceperson
|
|
105
|
-- where CFInvoiceType=0 and CFFinanceCom is null and fbasestatus = 1
|
|
106
|
-- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
107
|
|
|
108
|
-- update T_SD_SaleOrder ---当发票类型为普通且财务联系人公司不为空时，把公司做为发票抬头
|
|
109
|
-- set CFInvoiceHeadLine2 = CFFinanceCom
|
|
110
|
-- where CFInvoiceType=0 and CFFinanceCom is not null and fbasestatus = 1
|
|
111
|
-- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
112
|
|
|
113
|
|
|
114
|
--update T_SD_SaleOrder ---当客户为账期客户时，结算方式默认成account ，订单类型默认成业务赊销 fsettlementtypeid,cfsobiztype
|
|
115
|
-- set fsettlementtypeid = 'wYkAAAAPK1LpayuO', cfsobiztype=20
|
|
116
|
-- where fordercustomerid in (select fid from T_BD_customer where fiscredited =1)
|
|
117
|
--and fbasestatus = 1
|
|
118
|
-- and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
119
|
|
|
120
|
update T_SD_SaleOrder
|
|
121
|
set
|
|
122
|
fdescription = (select CFSoNote from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),
|
|
123
|
cfshuihao = (select FtxregisterNo from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据税号
|
|
124
|
cfzhucedizhi = (select substr(Faddress,0,50) from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据注册地址
|
|
125
|
--cfInvoiceHeadLine2 = (select max(CFInvoiceHeadLine) from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid),--修改成主数据发票抬头
|
|
126
|
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),--修改成主数据固定电话
|
|
127
|
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
|
|
128
|
where t_sd_saleorder.fordercustomerid=t_BD_customercompanyinfo.fcustomerid),--修改成主数据最新开户银行
|
|
129
|
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
|
|
130
|
where t_sd_saleorder.fordercustomerid=t_BD_customercompanyinfo.fcustomerid),--修改成主数据最新银行账号
|
|
131
|
|
|
132
|
cfdeliveryProvince = (select fid from (select fid,customerId from t_bd_province
|
|
133
|
inner join
|
|
134
|
(
|
|
135
|
select provinceName,customerId from
|
|
136
|
(
|
|
137
|
select replace(replace(trim(cfprovince),'省',''),'市','') provinceName,customerId from (
|
|
138
|
select linkMan.cfprovince,saleInfo.Fcustomerid customerId, case when linkMan.cfmastertype='Logistic' then 1 else 0 end as isMain
|
|
139
|
from t_bd_customerlinkman linkMan
|
|
140
|
inner join t_bd_customersaleinfo saleInfo on linkMan.Fcustomersaleid = saleInfo.fid
|
|
141
|
where linkMan.cfislog = 1
|
|
142
|
)order by isMain desc
|
|
143
|
)
|
|
144
|
)custProvince on t_bd_province.fname_l2 = custProvince.provinceName
|
|
145
|
) province where t_sd_saleorder.fordercustomerid=province.customerId and rownum=1),
|
|
146
|
cfdeliverycity = (select fdescription_l2 from (select fdescription_l2,customerId from t_bd_city
|
|
147
|
inner join
|
|
148
|
(
|
|
149
|
select cityName,customerId from
|
|
150
|
(
|
|
151
|
select replace(replace(trim(cfcity),'省',''),'市','') cityName,customerId from (
|
|
152
|
select linkMan.cfcity,saleInfo.Fcustomerid customerId, case when linkMan.cfmastertype='Logistic' then 1 else 0 end as isMain
|
|
153
|
from t_bd_customerlinkman linkMan
|
|
154
|
inner join t_bd_customersaleinfo saleInfo on linkMan.Fcustomersaleid = saleInfo.fid
|
|
155
|
where linkMan.cfislog = 1
|
|
156
|
)order by isMain desc
|
|
157
|
)
|
|
158
|
)custCity on t_bd_city.fname_l2 = custCity.cityName
|
|
159
|
) city where t_sd_saleorder.fordercustomerid=city.customerId and rownum=1)
|
|
160
|
|
|
161
|
where fbasestatus = 1
|
|
162
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
163
|
|
|
164
|
update T_SD_SaleOrder
|
|
165
|
set
|
|
166
|
fsalepersonid = (select cfcspersonid from t_bd_customer where t_sd_saleorder.fordercustomerid=t_BD_customer.fid) ----修改销售员为客户主数据中对应的客服人员
|
|
167
|
where fbasestatus = 1
|
|
168
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null and fsalepersonid is null;
|
|
169
|
|
|
170
|
---更新ContinousStrip数据
|
|
171
|
update T_SD_SaleOrder
|
|
172
|
set (CFContinousStripFlag ,CFDateCodeFlag ,CFDateCodeMonths ,CFPreference , CFLotCodeFlag ,CFCertComplianceFlag ,CFAcceptPartialOrderFlag)=
|
|
173
|
(select CFContinousStripFlag,CFDateCodeFlag ,
|
|
174
|
Case When CFDateCodeFlag=1 Then CFDateCodeMonths Else null End CFDateCodeMonths,
|
|
175
|
Case When CFDateCodeFlag=1 Then CFPreference Else '0' End CFPreference ,
|
|
176
|
CFLotCodeFlag ,CFCertComplianceFlag ,CFAcceptPartialOrderFlag
|
|
177
|
from T_BD_CustomerSaleInfo where FCustomerID
|
|
178
|
= t_sd_saleorder.fordercustomerid and FControlUnitID = t_sd_saleorder.fcontrolunitid) where exists (select 1 from T_BD_CustomerSaleInfo where FCustomerID
|
|
179
|
= t_sd_saleorder.fordercustomerid) and
|
|
180
|
fbasestatus = 1 and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
181
|
|
|
182
|
|
|
183
|
|
|
184
|
update T_SD_SaleOrder
|
|
185
|
set FSaleGroupID = 'wYkAAAAAKz4iBwBD',
|
|
186
|
FPrepaymentRate = 100,
|
|
187
|
cfbusinessPerson = cflogisticPerson,
|
|
188
|
cfbusinessNumber = cflogisticNumber,
|
|
189
|
cfbusinessEmail = cflogisticEmail,
|
|
190
|
cfbusinessMobile = cflogisticMobile,-- added by luyl 业务联系人手机号，取物流联系人手机号
|
|
191
|
cfordertimechina =
|
|
192
|
(
|
|
193
|
case ------主要是把美国那边冬令制和夏令制的时间还原成中国的北京时间
|
|
194
|
when fbizdate>= to_date('2014-11-03','yyyy-mm-dd') and fbizdate <= to_date('2015-03-07','yyyy-mm-dd')
|
|
195
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
196
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
197
|
when fbizdate>= to_date('2015-11-02','yyyy-mm-dd') and fbizdate <= to_date('2016-03-12','yyyy-mm-dd')
|
|
198
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
199
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
200
|
when fbizdate>= to_date('2016-11-07','yyyy-mm-dd') and fbizdate <= to_date('2017-03-11','yyyy-mm-dd')
|
|
201
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
202
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
203
|
when fbizdate>= to_date('2017-11-06','yyyy-mm-dd') and fbizdate <= to_date('2018-03-10','yyyy-mm-dd')
|
|
204
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
205
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
206
|
when fbizdate>= to_date('2018-11-05','yyyy-mm-dd') and fbizdate <= to_date('2019-03-09','yyyy-mm-dd')
|
|
207
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
208
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
209
|
when fbizdate>= to_date('2019-11-04','yyyy-mm-dd') and fbizdate <= to_date('2020-03-07','yyyy-mm-dd')
|
|
210
|
then to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
211
|
+NUMTODSINTERVAL(12,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
212
|
else to_char(cast(to_timestamp(cfordertimeus,'yyyy-mm-dd hh24:mi:ssxff') as date)
|
|
213
|
+NUMTODSINTERVAL(14,'hour'),'yyyy-mm-dd hh24:mi:ss')
|
|
214
|
end
|
|
215
|
),
|
|
216
|
|
|
217
|
fbizdate=
|
|
218
|
(
|
|
219
|
case ------对订单日期做更改。
|
|
220
|
when to_timestamp(cfordertimechina,'yyyy-mm-dd hh24:mi:ssxff') is null
|
|
221
|
then fcreatetime
|
|
222
|
else to_timestamp(cfordertimechina,'yyyy-mm-dd hh24:mi:ssxff')
|
|
223
|
end
|
|
224
|
),
|
|
225
|
|
|
226
|
fpaymenttypeid = '91f078d7-fb90-4827-83e2-3538237b67a06BCA0AB5',
|
|
227
|
CFUpdateSOWebDatetime = sysdate,
|
|
228
|
CFFINVERIFY = (case when FSettlementTypeId='wYkAAAAAK1XpayuO' or FSettlementTypeId='wYkAAAAAK1bpayuO' or FSettlementTypeId='wYkAAAAPK1LpayuO'
|
|
229
|
or FSettlementTypeId = 'wYkAAAFocILpayuO'--微信支付的ID在prod和Uat环境不同,所以此存储过程不能通用
|
|
230
|
then 1 else 0 end), --更改主表的财务确认
|
|
231
|
|
|
232
|
FTotalTaxAmount =
|
|
233
|
(select sum(FTaxAmount)
|
|
234
|
from T_SD_SaleOrderEntry
|
|
235
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
236
|
FLocalTotalTaxAmount =
|
|
237
|
(select sum(FLocalTaxAmount)
|
|
238
|
from T_SD_SaleOrderEntry
|
|
239
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
240
|
Fprepayment =
|
|
241
|
(select sum(FTaxAmount)
|
|
242
|
from T_SD_SaleOrderEntry
|
|
243
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
244
|
FTotalTax =
|
|
245
|
(select sum(FTax)
|
|
246
|
from T_SD_SaleOrderEntry
|
|
247
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
248
|
FTotalAmount =
|
|
249
|
(select sum(FAmount)
|
|
250
|
from T_SD_SaleOrderEntry
|
|
251
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid),
|
|
252
|
FLocalTotalAmount =
|
|
253
|
(select sum(FLocalAmount)
|
|
254
|
from T_SD_SaleOrderEntry
|
|
255
|
WHERE T_SD_SALEORDER.FID = T_SD_SaleOrderEntry.FParentid)
|
|
256
|
where fbasestatus = 1
|
|
257
|
and fnumber not like 'SO%' and fnumber not like '%_A' and CFUpdateSOWebDatetime is null;
|
|
258
|
|
|
259
|
|
|
260
|
|
|
261
|
commit;
|
|
262
|
|
|
263
|
END proc_update_saleorder;
|