合计小计---CUX_FA_DETAIL_001(多层循环小计合计参考包)

最新推荐文章于 2021-05-05 16:03:18 发布

花椰菜1110

最新推荐文章于 2021-05-05 16:03:18 发布

阅读量995

点赞数

分类专栏： PLSQL Report 文章标签： sql 报表

本文链接：https://blog.csdn.net/ruihua1021/article/details/41113907

版权

这是一个Oracle包体CUX_FA_DETAIL_001的实现，用于计算资产的折旧金额，包括本期折旧、累计折旧和YTD折旧。函数GET_DEPRN_AMOUNT Curry获取指定条件下的折旧金额，考虑了当月最大折旧和上月最大折旧。此外，还涉及资产使用状态的判断，但被注释掉了。包体还提供了计算小计和合计的功能，用于报表展示。

摘要由CSDN通过智能技术生成

CREATE OR REPLACE PACKAGE BODY CUX_FA_DETAIL_001 IS

PROCEDURE OUTPUT(P_TEXT IN VARCHAR2) IS
BEGIN
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_TEXT);

END OUTPUT;
PROCEDURE LOG(P_TEXT IN VARCHAR2) IS
BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG, P_TEXT);
END LOG;
FUNCTION XML_FORMAT(P_XML IN VARCHAR2) RETURN VARCHAR2 IS

BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(P_XML, '&', ';;'),
                                           '<',
                                           ';'),
                                   '>',
                                   ' ;'),
                           '"',
                           ';'),
                   '''',
                   ';');

END XML_FORMAT;
---------------------------------------- 本期折旧
FUNCTION GET_DEPRN_AMOUNT_CURR(P_ASSET_ID       NUMBER,
                                 P_BOOK_TYPE_CODE VARCHAR2,
                                 P_PERIOD_NAME    VARCHAR2) RETURN NUMBER IS
    L_DEPRN_AMOUNT NUMBER;
BEGIN
    SELECT DEPRN_AMOUNT
      INTO L_DEPRN_AMOUNT
      FROM FA_FINANCIAL_INQUIRY_DEPRN_V
     WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
       AND ASSET_ID = P_ASSET_ID
       AND PERIOD_ENTERED = P_PERIOD_NAME;

    RETURN L_DEPRN_AMOUNT;
EXCEPTION
    WHEN OTHERS THEN
      RETURN 0;
END GET_DEPRN_AMOUNT_CURR;
----------------------------------------资产使用状态

/*FUNCTION GET_FA_STATUS(P_ASSET_ID       NUMBER,
                         P_BOOK_TYPE_CODE VARCHAR2,
                         P_PERIOD_NAME    VARCHAR2) RETURN VARCHAR2 IS

    L_ASSET_KEY_DISP VARCHAR2(30);
    L_COUNT          NUMBER := 0;

BEGIN
    \*SELECT *
      --INTO L_COUNT
      FROM FA_FINANCIAL_INQUIRY_COST_V FFC
     WHERE FFC.BOOK_TYPE_CODE = 'SZ_10_FA2'
       AND FFC.ASSET_ID = 100027952
       AND (SELECT FFIC.PERIOD_EFFECTIVE
              FROM FA_FINANCIAL_INQUIRY_COST_V FFIC
             WHERE FFIC.BOOK_TYPE_CODE = 'SZ_10_FA2'
               AND FFIC.ASSET_ID = 100027952
               AND FFIC.TRANSACTION_TYPE IN ('Full Retirement', '完全报废')) <= '2014-06'
       AND FFC.TRANSACTION_TYPE IN ('Full Retirement', '完全报废')*\
    SELECT COUNT(*)
      INTO L_COUNT
      FROM FA_FINANCIAL_INQUIRY_COST_V FFC
     WHERE FFC.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
       AND FFC.ASSET_ID = P_ASSET_ID
       AND FFC.PERIOD_EFFECTIVE = P_PERIOD_NAME
       AND FFC.TRANSACTION_TYPE IN ('Full Retirement', '完全报废');
    IF L_COUNT > 0 THEN
      L_ASSET_KEY_DISP := '完全报废';
    ELSE
      L_ASSET_KEY_DISP := '在使用';
    END IF;

    RETURN L_ASSET_KEY_DISP;

EXCEPTION
    WHEN OTHERS THEN
      L_ASSET_KEY_DISP := NULL;
END GET_FA_STATUS;*/

--MODIFIED BY WRH 20140711:资产关键字直接取资产工作台界面的“资产关键字”
FUNCTION GET_FA_STATUS(P_ASSET_ID       NUMBER/*,
                         P_BOOK_TYPE_CODE VARCHAR2,
                         P_PERIOD_NAME    VARCHAR2*/) RETURN VARCHAR2 IS
       L_ASSET_KEY_DISP VARCHAR2(30);
    L_COUNT          NUMBER := 0;

BEGIN

    SELECT DECODE(FAK.SEGMENT1,01,'01-使用中',02,'02-未使用',03,'03-出租',04,'04-不需用')
      INTO L_ASSET_KEY_DISP
      FROM FA_ADDITIONS_V FA,
           FA_ASSET_KEYWORDS FAK
     WHERE FA.ASSET_KEY_CCID = FAK.CODE_COMBINATION_ID
       AND FA.ASSET_ID = P_ASSET_ID;

    RETURN L_ASSET_KEY_DISP;

EXCEPTION
    WHEN OTHERS THEN
      L_ASSET_KEY_DISP := NULL;
END GET_FA_STATUS;
--END MODIFIED 20140711

--*****累计折旧：取查询月份的当月最大折旧，如果当月没有，就取上月最大****--
FUNCTION GET_DEPRN_AMOUNT(X_ASSET_ID       VARCHAR2,
                            X_BOOK_TYPE_CODE VARCHAR2,
                            X_PERIOD_NAME    VARCHAR2) RETURN NUMBER IS
/*FUNCTION GET_DEPRN_AMOUNT(P_ASSET_ID       VARCHAR2,
                            P_BOOK_TYPE_CODE VARCHAR2,
                            P_PERIOD_NAME    VARCHAR2) RETURN NUMBER IS*/
    L_DEPRN_AMOUNT NUMBER;
BEGIN
    /*SELECT NVL(FS.DEPRN_RESERVE,
               (SELECT MAX(FS2.DEPRN_RESERVE) DEPRN_RESERVE
                  FROM FA_DEPRN_SUMMARY FS2
                 WHERE FS2.ASSET_ID = P_ASSET_ID
                   AND FS2.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE)) --fs.deprn_reserve
      INTO L_DEPRN_AMOUNT
      FROM FA_DEPRN_PERIODS DP, FA_DEPRN_SUMMARY FS
     WHERE DP.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
       AND DP.PERIOD_NAME = P_PERIOD_NAME
       AND DP.BOOK_TYPE_CODE = FS.BOOK_TYPE_CODE
       AND DP.PERIOD_COUNTER = FS.PERIOD_COUNTER
          --and dp.period_counter = fs.period_counter(+)--WRH 20140301
       AND FS.ASSET_ID = P_ASSET_ID
       AND FS.DEPRN_RUN_DATE =
           (SELECT MAX(FDS.DEPRN_RUN_DATE)
              FROM FA_DEPRN_SUMMARY FDS
             WHERE FDS.ASSET_ID = P_ASSET_ID
                  --and (trunc(fds.deprn_run_date) between dp.CALENDAR_PERIOD_OPEN_DATE and dp.CALENDAR_PERIOD_CLOSE_DATE )
               AND FDS.PERIOD_COUNTER = DP.PERIOD_COUNTER
                  --and fds.period_counter(+) = dp.period_counter--WRH 20140301
               AND FDS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE);*/--WRH 20140303
     SELECT FS.DEPRN_RESERVE
       INTO L_DEPRN_AMOUNT
       FROM FA_DEPRN_SUMMARY FS
      WHERE FS.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE
        --AND TO_char(FS.DEPRN_RUN_DATE,'YYYY-MM') = P_PERIOD_NAME
        AND FS.ASSET_ID = X_ASSET_ID
        AND to_char(FS.PERIOD_COUNTER) =
             (SELECT fde.Period_Counter
                FROM fa_deprn_periods fde
               WHERE FDe.Period_Counter = fs.period_counter
                 AND FDe.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE
                 AND fde.period_name = X_PERIOD_NAME
                 );
    RETURN L_DEPRN_AMOUNT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       SELECT FS2.DEPRN_RESERVE
         INTO L_DEPRN_AMOUNT
         FROM FA_DEPRN_SUMMARY FS2
        WHERE FS2.ASSET_ID = X_ASSET_ID
          AND FS2.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE
          AND to_char(FS2.PERIOD_COUNTER) =
             (SELECT max(fds.Period_Counter)
                FROM FA_DEPRN_SUMMARY fds
               WHERE fds.asset_id = FS2.ASSET_ID
                 AND fds.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE
                 );
        RETURN L_DEPRN_AMOUNT;
END GET_DEPRN_AMOUNT;
----------------------------------------------------------------YTD折旧
FUNCTION GET_YTDDEPRN_AMOUNTY(X_ASSET_ID       VARCHAR2,
                                X_BOOK_TYPE_CODE VARCHAR2,
                                X_PERIOD_NAME    VARCHAR2) RETURN NUMBER IS
    L_DEPRN_AMOUNTY NUMBER;
BEGIN
    /*SELECT FS.YTD_DEPRN
      INTO L_DEPRN_AMOUNTY
      FROM FA_DEPRN_PERIODS DP, FA_DEPRN_SUMMARY FS
     WHERE 1 = 1
       AND DP.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
       AND DP.PERIOD_NAME = P_PERIOD_NAME
       AND DP.BOOK_TYPE_CODE = FS.BOOK_TYPE_CODE
       AND DP.PERIOD_COUNTER = FS.PERIOD_COUNTER
          --and dp.period_counter = fs.period_counter(+)--WRH 20140301
       AND FS.ASSET_ID = P_ASSET_ID
       AND FS.DEPRN_RUN_DATE =
           (SELECT MAX(FDS.DEPRN_RUN_DATE)
              FROM FA_DEPRN_SUMMARY FDS<