资产卡片套打选不到当期使用期间数二开指导

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-23浏览：2

最近客户支持时，有客户问到资产卡片套打时，选不到当前最新的使用期间数，我在这里给大家分享一下配置方案。文章很短，配置较为简单，脚本较长，直接复制执行即可，无需关注细节，配有截图
，配置大概两分钟左右，如果疑问，可在帖子下方留言。
第一步执行脚本，需要区分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; goCREATE 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