TianHua/Piolot_RepotForm_PeiHao
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Piolot_RepotForm_PeiHao/最新合同模板sql.sql

519 lines
23 KiB
MySQL
Raw Permalink Normal View History

0
2025-03-14 10:00:24 +08:00
ALTER PROC [dbo].[GetTempModelData]
(
@FID NVARCHAR(100),
@FType NVARCHAR(100)
)
AS
DECLARE @FitemName NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @YFitemName NVARCHAR(1000) --ԭֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @SJFP NVARCHAR(1000) --˰<EFBFBD>𼰷<EFBFBD>Ʊ
DECLARE @GPhone NVARCHAR(1000) --<EFBFBD>̶<EFBFBD><EFBFBD>绰
DECLARE @THDate NVARCHAR(1000) --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @YFDate NVARCHAR(1000) --ԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @YFDateYear NVARCHAR(1000) -- ԭ<><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @YFDateMonth NVARCHAR(1000) -- ԭ<><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @YFDateDay NVARCHAR(1000) -- ԭ<><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @SYFitemName NVARCHAR(1000) --Sԭֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @SYFDate NVARCHAR(1000) --Sԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @SYFDateYear NVARCHAR(1000) -- Sԭ<53><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @SYFDateMonth NVARCHAR(1000) -- Sԭ<53><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @SYFDateDay NVARCHAR(1000) -- Sԭ<53><D4AD><EFBFBD><EFBFBD> <20><>
DECLARE @FContractFPhone NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FContract NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FCustAddress NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FPaymentMethod NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FProductionCycle2 NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FProductionCycle NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FContractNo NVARCHAR(1000) --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
DECLARE @FCustName NVARCHAR(1000) --<EFBFBD>׷<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @FDate NVARCHAR(1000) -- <20><><EFBFBD><EFBFBD>
DECLARE @FDateYear NVARCHAR(1000) -- <20><><EFBFBD><EFBFBD> <20><>
DECLARE @FDateMonth NVARCHAR(1000) -- <20><><EFBFBD><EFBFBD> <20><>
DECLARE @FDateDay NVARCHAR(1000) -- <20><><EFBFBD><EFBFBD> <20><>
DECLARE @CreatedName NVARCHAR(1000) -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @FTaxAmountText NVARCHAR(1000)
DECLARE @FTaxAmount NVARCHAR(1000)
DECLARE @FItem NVARCHAR(1000)
DECLARE @FAddressAndPhone NVARCHAR(1000)
DECLARE @FDeliveryDate NVARCHAR(1000)
DECLARE @FlegalRepresentative NVARCHAR(1000)
DECLARE @FBank NVARCHAR(1000)
DECLARE @FBankAccount NVARCHAR(1000)
DECLARE @FFax NVARCHAR(1000)
DECLARE @FPhone NVARCHAR(1000)
DECLARE @FEMAIL NVARCHAR(1000)
DECLARE @FTax NVARCHAR(1000)
DECLARE @FItemData NVARCHAR(1000)
DECLARE @FJHAddressFContractPhone NVARCHAR(1000)
DECLARE @FAProjectManager NVARCHAR(1000)
DECLARE @FAProjectPhone NVARCHAR(1000)
DECLARE @FBProjectManager NVARCHAR(1000)
DECLARE @FBProjectPhone NVARCHAR(1000)
DECLARE @FDeliveryTime NVARCHAR(1000)
DECLARE @FSHADDRESS NVARCHAR(1000)
DECLARE @FProjectDeadline NVARCHAR(1000)
DECLARE @FSettleMethod NVARCHAR(1000)
DECLARE @FProductionAddress NVARCHAR(1000)
DECLARE @FPeriod NVARCHAR(1000)
DECLARE @FJHAddress NVARCHAR(1000)
DECLARE @FWarrantyPeriod NVARCHAR(1000)
DECLARE @FAddress NVARCHAR(1000)
DECLARE @FINFORMATION NVARCHAR(1000)
DECLARE @FStartDate NVARCHAR(1000)
DECLARE @FEndDate NVARCHAR(1000)
DECLARE @FSaleId NVARCHAR(1000)
DECLARE @FMONTH NVARCHAR(1000) --<EFBFBD>ʱ<EFBFBD><EFBFBD>·<EFBFBD>
DECLARE @FMONTHYear NVARCHAR(1000) --<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @Projectarea NVARCHAR(1000) --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @Province NVARCHAR(1000) --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʡ<EFBFBD><EFBFBD>
DECLARE @VPAYMENTMETHOD NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD>㷽ʽ
DECLARE @CreatedPhone NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˵绰
DECLARE @FDELIVERYCONDITIONS NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @HFPaymentMethod NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD>ʽ F_PaymentMethod
DECLARE @FNOTE NVARCHAR(1000)--<EFBFBD><EFBFBD>ע<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ
DECLARE @FSALEORGID NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>֯
DECLARE @SX01 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX02 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX03 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX04 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX05 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX06 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX07 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @SX08 NVARCHAR(1000)--SX<EFBFBD><EFBFBD>ͬ
DECLARE @TH_001 NVARCHAR(1000)--<EFBFBD><EFBFBD>˾<EFBFBD><EFBFBD>
DECLARE @TH_002 NVARCHAR(1000)--<EFBFBD><EFBFBD>ַ
DECLARE @TH_003 NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @TH_004 NVARCHAR(1000)--<EFBFBD>绰
DECLARE @TH_005 NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @TH_006 NVARCHAR(1000)--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DECLARE @TH_007 NVARCHAR(1000)--<EFBFBD>˺<EFBFBD>
DECLARE @TH_008 NVARCHAR(1000)--˰<EFBFBD><EFBFBD>
--100302 <EFBFBD>麣<EFBFBD><EFBFBD>ŵ
--100303 <EFBFBD>麣<EFBFBD><EFBFBD>ŵ
--100305 <EFBFBD>人<EFBFBD><EFBFBD>ŵ
--438223 <EFBFBD>㶫̼<EFBFBD><EFBFBD>
--438224 <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ŵ
--533896 <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
SELECT top 1
@VPAYMENTMETHOD = A.F_VPAYMENTMETHOD,
@FDELIVERYCONDITIONS = (CASE WHEN A.F_DELIVERYCONDITIONS = '' THEN ''
ELSE SUBSTRING(A.F_DELIVERYCONDITIONS, CHARINDEX(',',A.F_DELIVERYCONDITIONS) + 1, LEN(A.F_DELIVERYCONDITIONS)) END),
@CreatedPhone = P.FPhone,
@TH_001 =(CASE WHEN A.FSALEORGID=100302 THEN '<EFBFBD>麣<EFBFBD><EFBFBD>ŵ<EFBFBD>Ƽ<EFBFBD><EFBFBD>ɷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>޹<EFBFBD>˾'
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '<EFBFBD>人<EFBFBD><EFBFBD>ŵ<EFBFBD>Ƽ<EFBFBD><EFBFBD><EFBFBD>չ<EFBFBD><EFBFBD><EFBFBD>޹<EFBFBD>˾'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_002 =(CASE WHEN A.FSALEORGID=100302 THEN '<EFBFBD>麣<EFBFBD><EFBFBD><EFBFBD>ƼҸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>º<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƽ<EFBFBD><EFBFBD><EFBFBD>·15<EFBFBD><EFBFBD>'
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '<EFBFBD>人<EFBFBD><EFBFBD><EFBFBD>ü<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ض<EFBFBD>·28<EFBFBD><EFBFBD>4<EFBFBD><EFBFBD>¥A501'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_003 =(CASE WHEN A.FSALEORGID=100302 THEN '<EFBFBD>'
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN ''
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_004 =(CASE WHEN A.FSALEORGID=100302 THEN '0756-3629690' --<EFBFBD>绰
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '027-87508171'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_005 =(CASE WHEN A.FSALEORGID=100302 THEN '0756-3629670' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN ''
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_006 =(CASE WHEN A.FSALEORGID=100302 THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>麣<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӫҵ<EFBFBD><EFBFBD>'
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>йɷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>޹<EFBFBD>˾<EFBFBD>人<EFBFBD><EFBFBD><EFBFBD>ü<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>֧<EFBFBD><EFBFBD>'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_007 =(CASE WHEN A.FSALEORGID=100302 THEN '2002 0207 19100 0780 51' --<EFBFBD>˺<EFBFBD>
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '1279 1590 4410 401'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@TH_008 =(CASE WHEN A.FSALEORGID=100302 THEN '91321191588407302A' --˰<EFBFBD><EFBFBD>
WHEN A.FSALEORGID=100303 THEN ''
WHEN A.FSALEORGID=100305 THEN '91420100MA4F4JX60A'
WHEN A.FSALEORGID=438223 THEN ''
WHEN A.FSALEORGID=438224 THEN ''
WHEN A.FSALEORGID=533896 THEN ''
ELSE '' END),
@SX01 = A.F_VRYF_Text_ne11,
@SX02 = A.F_VRYF_Text_6oq,
@SX03 = A.F_VRYF_Text_pzg,
@SX04 = A.F_VRYF_Text_795,
@SX05 = A.F_VRYF_Remarks_43c,
@SX06 = A.F_VRYF_Remarks_43c_s58,
@SX07 = A.F_VRYF_Remarks_43c_9uj,
@SX08 = A.F_VRYF_Remarks_43c_bgx,
@HFPaymentMethod = A.F_PaymentMethod,
@FStartDate=F_TGKSRQ,
@FEndDate =F_TGJSRQ,
@FNOTE = A.FNOTE,
@FAddress=F_VRYF_Text_fg2,
@FJHAddress =F_Shippingaddress,
@FContractNo= F_contractnumber,
@FTaxAmount=FORMAT(b.FBillAllAmount, 'N2') ,
@FDate=convert(nvarchar(10), A.FDATE ,23),
@FDateYear=Year(A.FDATE),--<EFBFBD><EFBFBD>
@FDateMonth=Month(A.FDATE),--<EFBFBD><EFBFBD>
@FDateDay=DAY(A.FDATE),--<EFBFBD><EFBFBD>
@FCustName=isnull(C.FNAME,''),
@FItem=isnull(D.FNAME,''),
@FAddressAndPhone=CONCAT( e.FADDRESS,' ',FLinkMan,' ',FLinkPhone),
@FlegalRepresentative=isnull(e.FLegalPerson,''),
@FBank=isnull(L.FINVOICEBANKNAME,''),
@FBankAccount=isnull(L.FINVOICEBANKACCOUNT,''),
@FTax=isnull(e.FTAXREGISTERCODE,''),
@FFax=isnull(e.FFAX,''),
@FTaxAmountText=dbo.ConvertAmountToChineseWords(b.FBillAllAmount),
@FEMAIL=isnull(g.FEMAIL,''),
@FProductionCycle= F_SCZQ,
@FProductionCycle2 =F_Deliverymethod,
@FPaymentMethod =ISNULL(N.FNAME+',',''),
@FCustAddress=e.FADDRESS,
@FContract=H.FNAME,
@FitemName =H.FNAME,
@FContractFPhone = g.FTEL,
@FPhone = e.FTEL,
@FitemName= I.FNAME,
@FItemData = A.F_VRYF_Text_fg2,
@FJHAddressFContractPhone = F_Shippingaddress,
@FAProjectManager = a.F_ProjectManager ,
@FAProjectPhone = a.F_Contactnumber ,
@FBProjectManager = a.F_VRYF_TEXT_UKY ,
@FBProjectPhone = a.F_VRYF__Contactnumber_dvn ,
@FDeliveryTime=F_FHSJ,
@FProjectDeadline = F_ProjectDeadli ,
@FPeriod = J.FDATAVALUE ,
@FWarrantyPeriod = J.FDATAVALUE,
@FMONTH = FLOOR(A.F_MONTH),
@FMONTHYear = FLOOR(A.F_MONTH/12),
@FSaleId = K.FNAME,
@FSALEORGID = Q.FNAME,
@Projectarea = (CASE WHEN O.F_PROJECTFIELD = 1 THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>¥<EFBFBD><EFBFBD>'
WHEN O.F_PROJECTFIELD = 2 THEN 'ҽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
WHEN O.F_PROJECTFIELD = 3 THEN 'ѧУ'
WHEN O.F_PROJECTFIELD = 4 THEN '<EFBFBD><EFBFBD><EFBFBD>鿵<EFBFBD><EFBFBD>'
WHEN O.F_PROJECTFIELD = 5 THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
WHEN O.F_PROJECTFIELD = 6 THEN '<EFBFBD><EFBFBD>Դ'
WHEN O.F_PROJECTFIELD = 7 THEN '<EFBFBD><EFBFBD>ҵ'
WHEN O.F_PROJECTFIELD = 8 THEN '<EFBFBD><EFBFBD>ͨ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
END ),
@Province = O.F_Province,
@FINFORMATION = A.F_INFORMATION,
@CreatedName = P.FNAME,
@GPhone = P.F_GPhone,
@THDate = A.F_THDate,
@SJFP = A.F_SJFP
FROM T_SAL_ORDER A
INNER JOIN T_SAL_ORDERFIN B ON A.FID = B.FID
--INNER JOIN T_SAL_ORDERENTRY BB ON A.FID = BB.FID
LEFT JOIN T_BD_CUSTOMER_L C ON C.FCUSTID =A.FCUSTID AND C.FLOCALEID =2052
LEFT JOIN T_BD_CUSTOMER E ON E.FCUSTID =A.FCUSTID
LEFT JOIN T_BAS_PREBDONE_L D ON D.FID = A.F_projectname AND D.FLocaleID =2052
LEFT JOIN T_BD_CUSTBANK F ON F.FCUSTID = E.FCUSTID
LEFT JOIN T_BD_CUSTOMER_F L ON L.FCUSTID = E.FCUSTID
LEFT JOIN T_BD_COMMONCONTACT g on g.FCOMPANY = e.FCUSTID and g.FISDEFAULTCONTACT =1
LEFT JOIN T_BD_COMMONCONTACT_L H on H.FCONTACTID =G.FCONTACTID AND H.FLOCALEID =2052
LEFT JOIN T_BAS_PREBDONE_L I ON I.FID = A.F_projectname
LEFT JOIN V_ASSISTANTDATA J ON J.FENTRYID=A.F_ZBQ
left join V_BD_SALESMAN_L K ON K.FID = A.FSALERID
LEFT JOIN T_SAL_ORDERPLAN M ON M.FID =A.FID AND M.FSEQ = 1
LEFT JOIN T_BD_RecCondition_L N on N.FID = M.F_RecConditionId
LEFT JOIN T_BAS_PREBDTHREE O on O.FID = A.F_BON
LEFT JOIN T_SEC_USER P on A.FCreatorId = P.FUSERID
LEFT JOIN T_ORG_ORGANIZATIONS_L Q on Q.FORGID = A.FSALEORGID
--LEFT JOIN (Select B.FDATE,A.FENTRYID From T_SAL_ORDERENTRY_LK A
--JOIN T_SAL_ORDER B on A.FSBILLID = B.FID) P on P.FENTRYID = BB.FENTRYID
WHERE A.FID =@FID;
--2025/01/09 <EFBFBD>Ķ<EFBFBD> --<EFBFBD><EFBFBD>
SELECT top 1
@YFitemName = F_CONTRACTNUMBER,
@YFDate = convert(nvarchar(10), FDATE ,23),
@YFDateYear=Year(FDATE),--<EFBFBD><EFBFBD>
@YFDateMonth=Month(FDATE),--<EFBFBD><EFBFBD>
@YFDateDay=DAY(FDATE)--<EFBFBD><EFBFBD>
From T_SAL_ORDER Where FID =
(SELECT top 1 FSBILLID From
T_SAL_ORDERENTRY A
JOIN T_SAL_ORDERENTRY_LK B
on A.FENTRYID = B.FENTRYID
Where A.FID = @FID AND B.FSTABLENAME = 'T_SAL_ORDERENTRY')
--2025/01/09 <EFBFBD>Ķ<EFBFBD> --<EFBFBD><EFBFBD>
--2025/01/12 <EFBFBD>Ķ<EFBFBD> --<EFBFBD><EFBFBD>
SELECT top 1
@SYFitemName = F_CONTRACTNUMBER,
@SYFDate = convert(nvarchar(10), FDATE ,23),
@SYFDateYear=Year(FDATE),--<EFBFBD><EFBFBD>
@SYFDateMonth=Month(FDATE),--<EFBFBD><EFBFBD>
@SYFDateDay=DAY(FDATE)--<EFBFBD><EFBFBD>
From T_SAL_ORDER Where F_CONTRACTNUMBER =
(Select top 1 F_ZHTH From T_SAL_ORDER Where FID = @FID)
--2025/01/12 <EFBFBD>Ķ<EFBFBD> --<EFBFBD><EFBFBD>
;with t as(
SELECT A.FID,
CONCAT(RANK()OVER(PARTITION BY A.FID ORDER BY A.FSEQ) ,'<EFBFBD><EFBFBD>',B.FDESCRIPTION,' ',CAST( a.F_day AS REAL),'<EFBFBD><EFBFBD>',' ',
CAST( A.FRECADVANCERATE AS REAL),'%') AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
FROM T_SAL_ORDERPLAN A
INNER JOIN T_SAL_ORDER C ON C.FID = A.FID
INNER JOIN T_BD_RecCondition_L b on a.F_RecConditionId = b.fid
WHERE a.fid =@fid
)
,
H AS(
SELECT FID, -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
STUFF(
(
SELECT ','+ CONVERT(NVARCHAR(100), <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,120) -- Ҫƴ<D2AA>ӵ<EFBFBD><D3B5><EFBFBD>
FROM t a WHERE b.FID = a.FID FOR XML PATH('')
),1 ,1, '') <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
FROM t b
GROUP BY FID
)
SELECT @FSettleMethod =<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> FROM H
--@VPAYMENTMETHOD
select 'TH_001' as 'Key' ,isnull(@TH_001,'') as 'VALUE'
union all
select 'TH_002' as 'Key' ,isnull(@TH_002,'') as 'VALUE'
union all
select 'TH_003' as 'Key' ,isnull(@TH_003,'') as 'VALUE'
union all
select 'TH_004' as 'Key' ,isnull(@TH_004,'') as 'VALUE'
union all
select 'TH_005' as 'Key' ,isnull(@TH_005,'') as 'VALUE'
union all
select 'TH_006' as 'Key' ,isnull(@TH_006,'') as 'VALUE'
union all
select 'TH_007' as 'Key' ,isnull(@TH_007,'') as 'VALUE'
union all
select 'TH_008' as 'Key' ,isnull(@TH_008,'') as 'VALUE'
union all
select 'F_VRYF_Text_ne11' as 'Key' ,isnull(@SX01,'') as 'VALUE'
union all
select 'F_VRYF_Text_ne11' as 'Key' ,isnull(@SX01,'') as 'VALUE'
union all
select 'F_VRYF_Text_6oq' as 'Key' ,isnull(@SX02,'') as 'VALUE'
union all
select 'F_VRYF_Text_pzg' as 'Key' ,isnull(@SX03,'') as 'VALUE'
union all
select 'F_VRYF_Text_795' as 'Key' ,isnull(@SX04,'') as 'VALUE'
union all
select 'F_VRYF_Remarks_43c' as 'Key' ,isnull(@SX05,'') as 'VALUE'
union all
select 'F_VRYF_Remarks_43c_s58' as 'Key' ,isnull(@SX06,'') as 'VALUE'
union all
select 'F_VRYF_Remarks_43c_9uj' as 'Key' ,isnull(@SX07,'') as 'VALUE'
union all
select 'F_VRYF_Remarks_43c_bgx' as 'Key' ,isnull(@SX08,'') as 'VALUE'
union all
select 'FSaleId' as 'Key' ,isnull(@FSaleId,'') as 'VALUE'
union all
select 'FSALEORGID' as 'Key' ,isnull(@FSALEORGID,'') as 'VALUE'
union all
select 'SJFP' as 'Key' ,isnull(@SJFP,'') as 'VALUE'
union all
select 'THDate' as 'Key' ,isnull(@THDate,'') as 'VALUE'
union all
select 'GPhone' as 'Key' ,isnull(@GPhone,'') as 'VALUE'
union all
select 'FNOTE' as 'Key' ,@FNOTE as 'VALUE'
union all
select 'YFContractNo' as 'Key' ,@YFitemName as 'VALUE'
union all
select 'YFDate' as 'Key' ,isnull(@YFDate,'') as 'VALUE'
union all
select 'YFDateYear' as 'Key' ,isnull(@YFDateYear,'') as 'VALUE'
union all
select 'YFDateMonth' as 'Key' ,isnull(@YFDateMonth,'') as 'VALUE'
union all
select 'YFDateDay' as 'Key' ,isnull(@YFDateDay,'') as 'VALUE'
union all
select 'SYFContractNo' as 'Key' ,@SYFitemName as 'VALUE'
union all
select 'SYFDate' as 'Key' ,isnull(@SYFDate,'') as 'VALUE'
union all
select 'SYFDateYear' as 'Key' ,isnull(@SYFDateYear,'') as 'VALUE'
union all
select 'SYFDateMonth' as 'Key' ,isnull(@SYFDateMonth,'') as 'VALUE'
union all
select 'SYFDateDay' as 'Key' ,isnull(@SYFDateDay,'') as 'VALUE'
union all
select 'Province' as 'Key' ,Isnull(@Province,'') as 'VALUE'
union all
select 'VPAYMENTMETHOD' as 'Key' ,Isnull(@VPAYMENTMETHOD,'') as 'VALUE'
union all
select 'Projectarea' as 'Key' ,Isnull(@Projectarea,'') as 'VALUE'
union all
select 'FMONTH' as 'Key' ,@FMONTH as 'VALUE'
union all
select 'FStartDate' as 'Key' ,isnull(@FStartDate,'') as 'VALUE'
union all
select 'FEndDate' as 'Key' ,isnull(@FEndDate,'') as 'VALUE'
union all
select 'FAddress' as 'Key' ,isnull(@FAddress,'') as 'VALUE'
union all
select 'FWarrantyPeriod' as 'Key' ,isnull(@FWarrantyPeriod,'') as 'VALUE'
union all
select 'HFPaymentMethod' as 'Key' ,@HFPaymentMethod as 'VALUE'
union all
select 'FDELIVERYCONDITIONS' as 'Key' ,@FDELIVERYCONDITIONS as 'VALUE'
union all
select 'CreatedName' as 'Key' ,@CreatedName as 'VALUE'
union all
select 'CreatedPhone' as 'Key',@CreatedPhone as 'VALUE'
union all
select 'FINFORMATION' as 'Key' ,Isnull(@FINFORMATION,'') as 'VALUE'
union all
select 'FMONTHYear' as 'Key' ,Isnull(@FMONTHYear,'') as 'VALUE'
union all
select 'FJHAddress' as 'Key' ,isnull(@FJHAddress,'') as 'VALUE'
union all
select 'FPeriod' as 'Key' ,isnull(@FPeriod,'') as 'VALUE'
union all
select 'FItemName' as 'Key' ,isnull(@FitemName,'') as 'VALUE'
UNION ALL
select 'FSettleMethod' as 'Key' ,isnull(@FSettleMethod,'') as 'VALUE'
UNION ALL
select 'FProjectDeadline' as 'Key' ,@FProjectDeadline as 'VALUE'
UNION ALL
select 'FDeliveryTime' as 'Key' ,@FDeliveryTime as 'VALUE'
UNION ALL
select 'FAProjectManager' as 'Key' ,@FAProjectManager as 'VALUE'
UNION ALL
select 'FAProjectPhone' as 'Key' ,@FAProjectPhone as 'VALUE'
UNION ALL
select 'FBProjectManager' as 'Key' ,@FBProjectManager as 'VALUE'
UNION ALL
select 'FBProjectPhone' as 'Key' ,@FBProjectPhone as 'VALUE'
UNION ALL
select 'FJHAddressFContractPhone' as 'Key' ,@FJHAddressFContractPhone as 'VALUE'
UNION ALL
select 'FItemData' as 'Key' ,@FItemData as 'VALUE'
UNION ALL
select 'FitemName' as 'Key' ,@FitemName as 'VALUE'
UNION ALL
select 'FFax' as 'Key' ,@FFax as 'VALUE'
UNION ALL
select 'FContractPhone' as 'Key' ,@FContractFPhone as 'VALUE'
UNION ALL
select 'FContract' as 'Key' ,@FContract as 'VALUE'
UNION ALL
select 'FCustAddress' as 'Key' ,@FCustAddress as 'VALUE'
UNION ALL
select 'FPaymentMethod' as 'Key' ,@FPaymentMethod as 'VALUE'
UNION ALL
select 'FProductionCycle2' as 'Key' ,@FProductionCycle2 as 'VALUE'
UNION ALL
select 'FProductionCycle' as 'Key' ,@FProductionCycle as 'VALUE'
UNION ALL
select 'FContractNo' as 'Key' ,@FContractNo as 'VALUE'
UNION ALL
select 'FCustName' as 'Key' ,@FCustName as 'VALUE'
UNION ALL
select 'FDate' as 'Key' ,@FDate as 'VALUE'
UNION ALL
select 'FDateYear' as 'Key' ,@FDateYear as 'VALUE'
UNION ALL
select 'FDateMonth' as 'Key' ,@FDateMonth as 'VALUE'
UNION ALL
select 'FDateDay' as 'Key' ,@FDateDay as 'VALUE'
UNION ALL
select 'FTaxAmountText' as 'Key' ,@FTaxAmountText as 'VALUE'
UNION ALL
select 'FTaxAmount' as 'Key' ,@FTaxAmount as 'VALUE'
UNION ALL
select 'FItem' as 'Key' ,@FItem as 'VALUE'
UNION ALL
select 'FAddressAndPhone' as '@FAddressAndPhone' ,@FCustName as 'VALUE'
UNION ALL
select 'FDeliveryDate' as 'Key' ,@FDeliveryDate as 'VALUE'
UNION ALL
select 'FlegalRepresentative' as 'Key' ,@FlegalRepresentative as 'VALUE'
UNION ALL
select 'FBank' as 'Key' ,@FBank as 'VALUE'
UNION ALL
select 'FBankAccount' as 'Key' ,@FBankAccount as 'VALUE'
UNION ALL
select 'FTax' as 'Key' ,@FTax as 'VALUE'
UNION ALL
select 'FPhone' as 'Key' ,@FPhone as 'VALUE'
UNION ALL
select 'FEmial' as 'Key' ,@FEMAIL as 'VALUE'
UNION ALL
select 'FEMAIL' as 'Key' ,@FEMAIL as 'VALUE'
SELECT fseq,B.fname,b.FSPECIFICATION,C.FNAME,FLOOR(A.FQTY),
FORMAT(ROUND( D.FTAXPRICE,2), 'N2'), FORMAT(D.FALLAMOUNT_LC, 'N2')
,A.FNOTE
FROM T_SAL_ORDERENTRY A
INNER JOIN T_BD_MATERIAL_L B ON B.FMATERIALID = A.FMATERIALID AND B.FLOCALEID =2052
INNER JOIN T_BD_UNIT_L C ON C.FUNITID =A.FUNITID AND C.FLOCALEID =2052
INNER JOIN T_SAL_ORDERENTRY_F D ON D.FENTRYID =A.FENTRYID
WHERE A.FID =@FID AND A.FMRPCLOSESTATUS = 'A'
SELECT rank()over(partition by a.fid ,a.F_Projecttype order by a.fseq desc) as 'fseq',B.fname,b.FSPECIFICATION,E.F_103,FLOOR(A.FQTY),C.FNAME,
FORMAT(ROUND( D.FTAXPRICE,2), 'N2'), FORMAT(D.FALLAMOUNT_LC, 'N2')
,A.FNOTE,F_Projecttype as 'FType',
FORMAT(ROUND( SUM(D.FALLAMOUNT_LC)OVER(PARTITION BY A.FID ),2),'N2') AS 'FALLAMOUNT',
FORMAT(ROUND( SUM(D.FALLAMOUNT_LC)OVER(PARTITION BY A.FID,F_Projecttype) ,2), 'N2') AS 'FALLAMOUNT2'
FROM T_SAL_ORDERENTRY A
INNER JOIN T_BD_MATERIAL_L B ON B.FMATERIALID = A.FMATERIALID AND B.FLOCALEID =2052
INNER JOIN T_BD_MATERIAL E ON E.FMATERIALID = A.FMATERIALID
INNER JOIN T_BD_UNIT_L C ON C.FUNITID =A.FUNITID AND C.FLOCALEID =2052
INNER JOIN T_SAL_ORDERENTRY_F D ON D.FENTRYID =A.FENTRYID
WHERE A.FID =@FID AND A.FMRPCLOSESTATUS = 'A'
ORDER BY a.F_Projecttype ,a.fseq DESC
Select ROW_NUMBER() OVER (ORDER BY B.FRETURNTYPE) AS ID,CASE WHEN B.FRETURNTYPE = 'SEND' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' ELSE '<EFBFBD>˻<EFBFBD>' END as 'FRETURNTYPE'
,C.FSPECIFICATION,D.FNAME,FLOOR(B.FQTY),FORMAT(E.FTAXPRICE,'N2'),FORMAT(E.FALLAMOUNT_LC,'N2') From T_SAL_ORDER A JOIN T_SAL_ORDERENTRY B
on A.FID = B.FID INNER JOIN T_BD_MATERIAL_L C ON C.FMATERIALID = B.FMATERIALID AND C.FLOCALEID =2052 INNER JOIN T_BD_UNIT_L D ON D.FUNITID =B.FUNITID
AND D.FLOCALEID =2052 INNER JOIN T_SAL_ORDERENTRY_F E ON E.FENTRYID =B.FENTRYID Where A.FID = @FID AND B.FMRPCLOSESTATUS = 'A'
SELECT fseq,b.FSPECIFICATION,C.FNAME,FLOOR(A.FQTY),
FORMAT(ROUND( D.FTAXPRICE,2), 'N2'), FORMAT(D.FALLAMOUNT_LC, 'N2')
FROM T_SAL_ORDERENTRY A
INNER JOIN T_BD_MATERIAL_L B ON B.FMATERIALID = A.FMATERIALID AND B.FLOCALEID =2052
INNER JOIN T_BD_UNIT_L C ON C.FUNITID =A.FUNITID AND C.FLOCALEID =2052
INNER JOIN T_SAL_ORDERENTRY_F D ON D.FENTRYID =A.FENTRYID
WHERE A.FID =@FID AND A.FMRPCLOSESTATUS = 'A'
--EXEC GetTempModelData @FID = 101521,@FType = 'HH'
--Select * From T_TempModelPath
--Update T_TempModelPath set FTempPath = 'D:\ģ<><C4A3>\SXϵͳģ<CDB3><C4A3>.docx' Where FKeyId = 'SX'
--Update T_SAL_ORDER set FDocumentStatus = 'D' Where FBILLNO = 'PL-XSDD20250100011'
--Select * From V_BD_SALESMAN_L Where FNAME = '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
Reference in New Issue Copy Permalink