资产卡片套打选不到当期使用期间数二开指导
最近客户支持时，有客户问到资产卡片套打时，选不到当前最新的使用期间数，我在这里给大家分享一下配置方案。文章很短，配置较为简单，脚本较长，直接复制执行即可，无需关注细节，配有截图
，配置大概两分钟左右，如果疑问，可在帖子下方留言。
第一步 执行脚本，需要区分SQL和ORACLE两种数据库。
SQL：
--TO SQL SERVER:
CREATE VIEW V_FA_USEPERIODCOUNT AS
SELECT CASE
WHEN (MIN(D.FCLEARDEPRPOLICY) = '2' AND
MIN(C.FASSETCURSTATUS) = '4') THEN
(MIN(F.FUSEDPERIODS) + COUNT(1))
ELSE
((MIN(F.FUSEDPERIODS) + COUNT(1)) - 1)
END fusedeprperiodscur,
C.FASSETID,
B1.FACCTPOLICYID
FROM T_FA_FINANCE F
INNER JOIN T_FA_CARD C
ON (F.FALTERID = C.FALTERID AND C.FISNEWREC = '2')
INNER JOIN (SELECT FORGID,
FACCTPOLICYID,
CONVERT(FLOAT,
MAX(CASE FKEY
WHEN 'StartYear' THEN
FVALUE
ELSE
0
END)) fstartyear,
CONVERT(FLOAT,
MAX(CASE FKEY
WHEN 'StartPeriod' THEN
FVALUE
ELSE
0
END)) fstartperiod
FROM T_BAS_SYSTEMPROFILE
WHERE (FCATEGORY = 'FA' AND (FORGID > 0))
GROUP BY FORGID, FACCTPOLICYID) st
ON ST.FORGID = C.FOWNERORGID
INNER JOIN T_FA_BALANCE B1
ON (((C.FASSETID = B1.FASSETID AND F.FACCTPOLICYID = B1.FACCTPOLICYID) AND
ST.FACCTPOLICYID = B1.FACCTPOLICYID) AND
((B1.FYEAR > ST.FSTARTYEAR) OR
(B1.FYEAR = ST.FSTARTYEAR AND (B1.FPERIOD >= ST.FSTARTPERIOD))))
INNER JOIN T_FA_BALANCE B2
ON ((B1.FASSETID = B2.FASSETID AND B2.FACCTPOLICYID = B1.FACCTPOLICYID) AND
B2.FISNEWREC = '2')
INNER JOIN T_FA_ACCTPOLICY P
ON B1.FACCTPOLICYID = P.FACCTPOLICYID
INNER JOIN T_FA_ACCTPOLICYASSET E
ON (P.FACCTPOLICYID = E.FACCTPOLICYID AND
E.FASSETTYPEID = C.FASSETTYPEID)
INNER JOIN T_FA_DEPRPOLICY D
ON D.FPOLICYID = E.FDEPRPOLICYID
WHERE (B1.FYEARPERIOD <= B2.FYEARPERIOD)
GROUP BY C.FASSETID, B1.FACCTPOLICYID, F.FFINANCEID;
go
CREATE VIEW V_FA_LASTBALANCENEW AS
SELECT V.*, C.FUSEDEPRPERIODSCUR
FROM V_FA_LASTBALANCE V
INNER JOIN V_FA_USEPERIODCOUNT C
ON (V.FASSETID = C.FASSETID AND C.FACCTPOLICYID = V.FACCTPOLICYID);
ORA：
--TO ORACLE:
CREATE VIEW V_FA_USEPERIODCOUNT AS
SELECT CASE
WHEN (MIN(D.FCLEARDEPRPOLICY) = '2' AND
MIN(C.FASSETCURSTATUS) = '4') THEN
(MIN(F.FUSEDPERIODS) + COUNT(1))
ELSE
((MIN(F.FUSEDPERIODS) + COUNT(1)) - 1)
END FUSEDEPRPERIODSCUR,
C.FASSETID,
B1.FACCTPOLICYID
FROM T_FA_FINANCE F
INNER JOIN T_FA_CARD C
ON (F.FALTERID = C.FALTERID AND C.FISNEWREC = '2')
INNER JOIN (SELECT FORGID,
FACCTPOLICYID,
TO_NUMBER(MAX(DECODE(FKEY, 'StartYear', FVALUE, 0))) FSTARTYEAR,
TO_NUMBER(MAX(DECODE(FKEY, 'StartPeriod', FVALUE, 0))) FSTARTPERIOD
FROM T_BAS_SYSTEMPROFILE
WHERE (FCATEGORY = 'FA' AND (FORGID > 0))
GROUP BY FORGID, FACCTPOLICYID) ST
ON ST.FORGID = C.FOWNERORGID
INNER JOIN T_FA_BALANCE B1
ON (((C.FASSETID = B1.FASSETID AND F.FACCTPOLICYID = B1.FACCTPOLICYID) AND
ST.FACCTPOLICYID = B1.FACCTPOLICYID) AND
((B1.FYEAR > ST.FSTARTYEAR) OR
(B1.FYEAR = ST.FSTARTYEAR AND (B1.FPERIOD >= ST.FSTARTPERIOD))))
INNER JOIN T_FA_BALANCE B2
ON ((B1.FASSETID = B2.FASSETID AND B2.FACCTPOLICYID = B1.FACCTPOLICYID) AND
B2.FISNEWREC = '2')
INNER JOIN T_FA_ACCTPOLICY P
ON B1.FACCTPOLICYID = P.FACCTPOLICYID
INNER JOIN T_FA_ACCTPOLICYASSET E
ON (P.FACCTPOLICYID = E.FACCTPOLICYID AND
E.FASSETTYPEID = C.FASSETTYPEID)
INNER JOIN T_FA_DEPRPOLICY D
ON D.FPOLICYID = E.FDEPRPOLICYID
WHERE (B1.FYEARPERIOD <= B2.FYEARPERIOD)
GROUP BY C.FASSETID, B1.FACCTPOLICYID, F.FFINANCEID with read only;
CREATE VIEW V_FA_LASTBALANCENEW AS
SELECT V.*, C.FUSEDEPRPERIODSCUR
FROM V_FA_LASTBALANCE V
INNER JOIN V_FA_USEPERIODCOUNT C
ON (V.FASSETID = C.FASSETID AND C.FACCTPOLICYID = V.FACCTPOLICYID) with read only;
第二步 打开BOS设计器， 扩展最新余额表信息基础资料，修改单据头对应视图为V_FA_LastBalanceNEW
第三步 添加字段-使用期间数，配置如图，保存
重新打开套打设计器，查看数据源，这个时候，我们就可以选到最新使用期间数了
资产卡片套打选不到当期使用期间数二开指导
本文2024-09-23 02:22:06发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-151952.html
