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;
----------------------------------------资产使用状态
--MODIFIED BY WRH 20140711:资产关键字直接取资产工作台界面的“资产关键字”
FUNCTION GET_FA_STATUS(P_ASSET_ID NUMBER) 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
L_DEPRN_AMOUNT NUMBER;
BEGIN
--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
--MODIFIED BY WRH 20140522
SELECT FS.YTD_DEPRN
INTO L_DEPRN_AMOUNTY
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_AMOUNTY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT FS2.YTD_DEPRN
INTO L_DEPRN_AMOUNTY
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_AMOUNTY;
END;
----------------------------------------------------
PROCEDURE MAIN(X_FLAG OUT NUMBER,
X_MSG OUT VARCHAR2,
P_FA_BOOK_TYPE IN VARCHAR2,
P_PERIOD_NAME IN VARCHAR2,
P_ASSET_NUMBER IN VARCHAR2,
P_ASSET_NAME IN VARCHAR2) IS
CURSOR FA_CATEGORY IS
SELECT DISTINCT SUBSTR(FA.ATTRIBUTE_CATEGORY_CODE, 1, 2) ATTRIBUTE_CATEGORY_CODE
FROM FA_ADDITIONS_V FA,
FA_CATEGORIES FACA,
FA_BOOKS FB,
FA.FA_DEPRN_PERIODS FDP,
GL_CODE_COMBINATIONS_KFV GCC,
FA_DISTRIBUTION_HISTORY DH
WHERE FA.ASSET_ID = FB.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FB.DATE_INEFFECTIVE IS NULL
AND FDP.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FA.ASSET_CATEGORY_ID = FACA.CATEGORY_ID
AND DH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND DH.ASSET_ID = FA.ASSET_ID
--and fdp.period_close_date is null
AND NVL(FB.DISABLED_FLAG, 'N') = 'N'
AND FDP.PERIOD_NAME = P_PERIOD_NAME
AND FB.BOOK_TYPE_CODE = P_FA_BOOK_TYPE
-- and faca.DESCRIPTION = nvl(p_type ,faca.DESCRIPTION )
AND FA.ASSET_NUMBER = NVL(P_ASSET_NUMBER, FA.ASSET_NUMBER)
AND FA.DESCRIPTION = NVL(P_ASSET_NAME, FA.DESCRIPTION)
ORDER BY SUBSTR(FA.ATTRIBUTE_CATEGORY_CODE, 1, 2);
CURSOR FA_DATA(P_ATTRIBUTE_CATEGORY_CODE VARCHAR2) IS
SELECT DISTINCT FB.BOOK_TYPE_CODE, --账簿
FA.ASSET_ID,
FA.ASSET_NUMBER ASSET_NUMBER, --资产编号
FA.SERIAL_NUMBER,
CUX_FA_DETAIL_001.GET_FA_STATUS(FA.ASSET_ID) FA_USER_STATUS, --使用状态
FA.TAG_NUMBER, --标签号
FA.DESCRIPTION FA_NAME, --资产名称
FA.ATTRIBUTE_CATEGORY_CODE FA_CODE, --资产类别
FAC.DESCRIPTION, --资产类别名称
DECODE(FA.ASSET_TYPE,
'CAPITALIZED',
'资本化',
'CIP',
'CIP',
'GROUP',
'组',
FA.ASSET_TYPE) ASSET_TYPE, --资产类型
FA.MODEL_NUMBER, --型号
-- fidv.VENDOR_NAME, --供应商
FA.MANUFACTURER_NAME VENDOR_NAME, --制造商
TO_CHAR(FB.DATE_PLACED_IN_SERVICE, 'YYYY-MM-DD') DATE_PLACED_IN_SERVICE, --启用日期
NVL(FA.CURRENT_UNITS, 0) QTY, --存放数量
NVL(FB.COST, 0) COST, --当前成本
FB.LIFE_IN_MONTHS, --使用年限（月）
NVL(FB.ORIGINAL_COST, 0) ORIGINAL_COST, --原值
NVL(FB.SALVAGE_VALUE, 0) SALVAGE_VALUE, --残值
NVL(FB.ADJUSTED_RECOVERABLE_COST, 0) ADJUSTED_RECOVERABLE_COST, --可收回成本
DECODE(FDP.PERIOD_NAME,
P_PERIOD_NAME,
NVL(FB.COST, 0),
NVL(FB.COST, 0) +
(SELECT SUM(FAR.COST_RETIRED) COST_RETIRED
FROM FA_RETIREMENTS FAR
WHERE 1 = 1
AND TO_NUMBER(TO_CHAR(FAR.DATE_RETIRED,
'yyyymm')) > =
FDP.FISCAL_YEAR || FDP.PERIOD_NUM
AND FAR.ASSET_ID = FA.ASSET_ID
AND FAR.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE)) -
FB.LIFE_IN_MONTHS -
FLOOR(MONTHS_BETWEEN(FDP.CALENDAR_PERIOD_CLOSE_DATE,
FB.DATE_PLACED_IN_SERVICE)) USED_LIFE, --已使用年限(月)
FLOOR(MONTHS_BETWEEN(FDP.CALENDAR_PERIOD_CLOSE_DATE,
FB.DATE_PLACED_IN_SERVICE)) SYQX, -- 剩余期限(月)
CUX_FA_DETAIL_001.GET_DEPRN_AMOUNT_CURR(FA.ASSET_ID,
FB.BOOK_TYPE_CODE,
FDP.PERIOD_NAME) DEPRN_AMOUNT_CURR, --月折旧额(本月折旧)
NVL(CUX_FA_DETAIL_001.GET_DEPRN_AMOUNT(FA.ASSET_ID,
FB.BOOK_TYPE_CODE,
FDP.PERIOD_NAME),
0) LEIJI_AMOUNT, --累计折旧
NVL(CUX_FA_DETAIL_001.GET_YTDDEPRN_AMOUNTY(FA.ASSET_ID,
FB.BOOK_TYPE_CODE,
FDP.PERIOD_NAME),
0) YTD_AMOUNT, --YTD折旧
DH.UNITS_ASSIGNED, --分配数量
PER.LAST_NAME, --管理人
GCC.SEGMENT3, --费用帐户
CUX_FND_COMMON_UTL.GET_FLEX_VALUE_DESCRIPTION('EWPT_COA_AC',
GCC.SEGMENT3) KMMC, --费用帐户中文描述
CUX_FND_COMMON_UTL.GET_FLEX_VALUE_DESCRIPTION('EWPT_COA_PT',
GCC.SEGMENT5) TYPES, --费用门户类中文描述
GCC.CONCATENATED_SEGMENTS,
(SELECT COV.DESCRIPTION
FROM FND_FLEX_VALUE_SETS COS,
FND_FLEX_VALUES_VL COV
WHERE 1 = 1
AND COV.FLEX_VALUE_SET_ID = COS.FLEX_VALUE_SET_ID
AND COS.FLEX_VALUE_SET_NAME = 'EWPT_FA_LOCATION2'
AND COV.ENABLED_FLAG = 'Y'
AND FL.SEGMENT2 = COV.FLEX_VALUE
AND ROWNUM < 2) LOCASEG, -- 存放地点
FB.DEPRN_METHOD_CODE, --折旧方法
FB.PRORATE_CONVENTION_CODE, --折旧惯例
(SELECT COV.DESCRIPTION
FROM FND_FLEX_VALUE_SETS COS,
FND_FLEX_VALUES_VL COV
WHERE 1 = 1
AND COV.FLEX_VALUE_SET_ID = COS.FLEX_VALUE_SET_ID
AND COS.FLEX_VALUE_SET_NAME = 'EWPT_COA_CC' --'EWPT_FA_LOCATION'
AND COV.ENABLED_FLAG = 'Y'
AND FL.SEGMENT1 = COV.FLEX_VALUE
AND ROWNUM < 2) LOCA, -- 部门
FLOOR(MONTHS_BETWEEN(TO_DATE(FDP.PERIOD_NAME,'YYYY-MM'),
TO_DATE(TO_CHAR(FB.DATE_PLACED_IN_SERVICE,'YYYY-MM'),'YYYY-MM'))) YY, --FOLLOW的已折期限（月）*/
FDP.PERIOD_NAME YUE --折旧月份
FROM FA_ADDITIONS_V FA,
FA_BOOKS FB,
FA.FA_DEPRN_PERIODS FDP,
FA.FA_CATEGORY_BOOKS FCB,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORIES FAC,
-- fa_deprn_summary fas,
FA_INVOICE_DETAILS_V FIDV,
PER_PEOPLE_F PER,
GL_CODE_COMBINATIONS_KFV GCC,
FA_LOCATIONS FL
WHERE FA.ASSET_ID = FB.ASSET_ID
-- and fa.asset_id = fas.asset_id
AND FA.ASSET_ID = FIDV.ASSET_ID(+)
-- and fdp.book_type_code = fas.book_type_code
--add by wrh 20140524
--AND FB.TRANSACTION_HEADER_ID_OUT IS NULL
--AND FB.DATE_INEFFECTIVE IS NULL
--end add 20140524
AND FDP.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FCB.CATEGORY_ID = FAC.CATEGORY_ID
AND NVL(FB.DISABLED_FLAG, 'N') = 'N'
AND DH.ASSET_ID = FA.ASSET_ID
AND DH.DATE_INEFFECTIVE IS NULL
AND DH.ASSIGNED_TO = PER.PERSON_ID(+)
AND DH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND DH.LOCATION_ID = FL.LOCATION_ID(+)
AND FDP.PERIOD_NAME = P_PERIOD_NAME
AND to_date(TO_CHAR(FB.DATE_PLACED_IN_SERVICE, 'YYYY-MM'), 'YYYY-MM')
<= to_date(P_PERIOD_NAME, 'YYYY-MM') --ADD BY WRH 20140514:启用日期在查询期间内
--add by wrh 20140524
AND ((to_date(TO_CHAR(FB.DATE_INEFFECTIVE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
>= to_date(TO_CHAR(NVL(FDP.PERIOD_CLOSE_DATE,SYSDATE), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
AND (to_date(TO_CHAR(FB.DATE_EFFECTIVE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')--100038349这个资产取数取了2次
<= to_date(TO_CHAR(NVL(FDP.PERIOD_CLOSE_DATE,SYSDATE), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'))
AND FB.DATE_INEFFECTIVE IS NOT NULL)
OR
(to_date(TO_CHAR(FB.DATE_EFFECTIVE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
<= to_date(TO_CHAR(NVL(FDP.PERIOD_CLOSE_DATE,SYSDATE), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
AND FB.DATE_INEFFECTIVE IS NULL))
--end add 20140524
--add by wrh 20140514
AND to_date(TO_CHAR(nvl(FA.CREATION_DATE,FDP.PERIOD_CLOSE_DATE), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
<= to_date(TO_CHAR(nvl(FDP.PERIOD_CLOSE_DATE,SYSDATE), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
--end add 20140514
AND FB.BOOK_TYPE_CODE = P_FA_BOOK_TYPE
AND FDP.BOOK_TYPE_CODE = P_FA_BOOK_TYPE--100040907:2月份导入2月份的期间--ADD BY WRH 20140514
AND FA.ASSET_NUMBER = NVL(P_ASSET_NUMBER, FA.ASSET_NUMBER)
AND FA.DESCRIPTION = NVL(P_ASSET_NAME, FA.DESCRIPTION)
AND SUBSTR(FA.ATTRIBUTE_CATEGORY_CODE, 1, 2) =
P_ATTRIBUTE_CATEGORY_CODE
ORDER BY FA.ATTRIBUTE_CATEGORY_CODE, FA.ASSET_NUMBER;
COMPANYNAME VARCHAR2(150);
V_USER_NAME VARCHAR2(30);
V_GS_NAME_T VARCHAR2(150);
V_GS_NAME_F VARCHAR2(150);
--小计--
L_SUBTOTAL_MOUNT_AMOUNTY NUMBER := 0;
L_SUBTOTAL_DEPRN_AMOUNTY NUMBER := 0;
L_SUBTOTAL_DEPRN_AMOUNT NUMBER := 0;
L_SUBTOTAL_COST NUMBER := 0;
L_SUBTOTAL_ORIGINAL_COST NUMBER := 0;
L_SUBTOTAL_SALVAGE_VALUE NUMBER := 0;
L_SUBTOTAL_NET_COST NUMBER := 0;
--合计--
L_TOTAL_MOUNT_AMOUNTY NUMBER := 0;
L_TOTAL_DEPRN_AMOUNTY NUMBER := 0;
L_TOTAL_DEPRN_AMOUNT NUMBER := 0;
L_TOTAL_COST NUMBER := 0;
L_TOTAL_ORIGINAL_COST NUMBER := 0;
L_TOTAL_SALVAGE_VALUE NUMBER := 0;
L_TOTAL_NET_COST NUMBER := 0;
BEGIN
BEGIN
SELECT FU.USER_NAME
INTO V_USER_NAME
FROM FND_USER FU
WHERE FU.USER_ID = FND_PROFILE.VALUE('USER_ID');
EXCEPTION
WHEN OTHERS THEN
LOG('取USER_NAME' || SQLERRM);
END;
BEGIN
NULL;
END;
OUTPUT('<?xml version = ''' || '1.0' || ''' encoding = ' || '''UTF-8' ||
'''?>');
OUTPUT('<MODULE1>');
OUTPUT('<PRINT>' || XML_FORMAT(V_USER_NAME) || '</PRINT>');
OUTPUT('<PRINTD>' || TO_CHAR(SYSDATE, 'YYYY-MM-DD') || '</PRINTD>');
OUTPUT('<qj>' || XML_FORMAT(P_PERIOD_NAME) || '</qj>');
OUTPUT('<FA>' || XML_FORMAT(P_FA_BOOK_TYPE) || '</FA>');
FOR REC IN FA_CATEGORY LOOP
FOR R1 IN FA_DATA(REC.ATTRIBUTE_CATEGORY_CODE) LOOP
OUTPUT('<LIST_G_SORT_B>');
OUTPUT('<FAB>' || XML_FORMAT(R1.BOOK_TYPE_CODE) || '</FAB>');
OUTPUT('<asset_number>' || XML_FORMAT(R1.ASSET_NUMBER) ||
'</asset_number>');
OUTPUT('<SERIAL_NUMBER>' || XML_FORMAT(R1.SERIAL_NUMBER) ||
'</SERIAL_NUMBER>');
OUTPUT('<SHIYZT>' || XML_FORMAT(R1.FA_USER_STATUS) || '</SHIYZT>');
OUTPUT('<fa_name>' || XML_FORMAT(R1.FA_NAME) || '</fa_name>');
OUTPUT('<FALB>' || XML_FORMAT(R1.FA_CODE) || '</FALB>');
OUTPUT('<FALBMC>' || XML_FORMAT(R1.DESCRIPTION) || '</FALBMC>');
OUTPUT('<FALX>' || XML_FORMAT(R1.ASSET_TYPE) || '</FALX>');
OUTPUT('<FAXH>' || XML_FORMAT(R1.MODEL_NUMBER) || '</FAXH>');
OUTPUT('<FAVN>' || R1.VENDOR_NAME || '</FAVN>');
OUTPUT('<FAQYRQ>' || XML_FORMAT(R1.DATE_PLACED_IN_SERVICE) ||
'</FAQYRQ>');
OUTPUT('<FAQTY>' || R1.QTY || '</FAQTY>');
OUTPUT('<FACOST>' || XML_FORMAT(R1.COST) || '</FACOST>');
OUTPUT('<SM>' || R1.LIFE_IN_MONTHS || '</SM>');
OUTPUT('<ZJGL>' || R1.PRORATE_CONVENTION_CODE || '</ZJGL>');
OUTPUT('<FF>' || R1.DEPRN_METHOD_CODE || '</FF>');
OUTPUT('<YZ>' || XML_FORMAT(R1.ORIGINAL_COST) || '</YZ>');
OUTPUT('<MH>' || XML_FORMAT(R1.TYPES) || '</MH>');
OUTPUT('<CZ>' || XML_FORMAT(R1.SALVAGE_VALUE) || '</CZ>');
OUTPUT('<XHCB>' || XML_FORMAT(R1.ADJUSTED_RECOVERABLE_COST) ||
'</XHCB>');
OUTPUT('<ZMJZ>' || XML_FORMAT(NVL((R1.COST - R1.LEIJI_AMOUNT), 0)) ||
'</ZMJZ>');
-- output('<YJSHIYQX>' || '' || '</YJSHIYQX>');
IF R1.PRORATE_CONVENTION_CODE = 'FOLLOW' THEN
IF R1.LIFE_IN_MONTHS < R1.YY THEN
OUTPUT('<YY>' || R1.LIFE_IN_MONTHS || '</YY>'); --已折期限（月）
OUTPUT('<SYQX>' || 0 || '</SYQX>'); --剩余期限（月）
ELSE
IF R1.YY > 0 THEN
OUTPUT('<YY>' || R1.YY || '</YY>');
OUTPUT('<SYQX>' || (R1.LIFE_IN_MONTHS - R1.YY) || '</SYQX>');
ELSE
OUTPUT('<YY>' || 0 || '</YY>');
OUTPUT('<SYQX>' || R1.LIFE_IN_MONTHS || '</SYQX>');
END IF;
END IF;
ELSIF R1.PRORATE_CONVENTION_CODE = 'CURRENT' THEN
IF R1.LIFE_IN_MONTHS <= R1.YY THEN
OUTPUT('<YY>' || R1.LIFE_IN_MONTHS || '</YY>'); --已折期限（月）
OUTPUT('<SYQX>' || 0 || '</SYQX>'); --剩余期限（月）
ELSE
IF R1.YY + 1 > 0 THEN ---
OUTPUT('<YY>' || (R1.YY + 1) || '</YY>');
OUTPUT('<SYQX>' || (R1.LIFE_IN_MONTHS - (R1.YY+1)) || '</SYQX>');
ELSE
OUTPUT('<YY>' || 0 || '</YY>');
OUTPUT('<SYQX>' || R1.LIFE_IN_MONTHS || '</SYQX>');
END IF;
END IF;
END IF;
OUTPUT('<YUE>' || XML_FORMAT(R1.YUE) || '</YUE>');
OUTPUT('<YUEZJ>' || XML_FORMAT(R1.DEPRN_AMOUNT_CURR) || '</YUEZJ>'); OUTPUT('<LJZJ>' || XML_FORMAT(R1.LEIJI_AMOUNT) || '</LJZJ>'); OUTPUT('<YTDZJ>' || XML_FORMAT(R1.YTD_AMOUNT) || '</YTDZJ>'); OUTPUT('<FPSL>' || XML_FORMAT(R1.UNITS_ASSIGNED) || '</FPSL>'); OUTPUT('<GLR>' || XML_FORMAT(R1.LAST_NAME) || '</GLR>'); OUTPUT('<KM>' || XML_FORMAT(R1.KMMC) || '</KM>'); OUTPUT('<DID>' || XML_FORMAT(R1.LOCASEG) || '</DID>'); OUTPUT('<BM>' || XML_FORMAT(R1.LOCA) || '</BM>'); OUTPUT('<ZJYUE>' || XML_FORMAT(R1.YUE) || '</ZJYUE>'); OUTPUT('</LIST_G_SORT_B>');
---计算小计值
L_SUBTOTAL_COST := L_SUBTOTAL_COST + R1.COST; L_SUBTOTAL_ORIGINAL_COST := L_SUBTOTAL_ORIGINAL_COST + R1.ORIGINAL_COST; L_SUBTOTAL_SALVAGE_VALUE := L_SUBTOTAL_SALVAGE_VALUE + R1.SALVAGE_VALUE;
L_SUBTOTAL_MOUNT_AMOUNTY := L_SUBTOTAL_MOUNT_AMOUNTY + --月折旧额(本月折旧)
R1.DEPRN_AMOUNT_CURR; L_SUBTOTAL_DEPRN_AMOUNTY := L_SUBTOTAL_DEPRN_AMOUNTY + --YTD折旧
R1.YTD_AMOUNT; L_SUBTOTAL_DEPRN_AMOUNT := L_SUBTOTAL_DEPRN_AMOUNT + --累计折旧
R1.LEIJI_AMOUNT; L_SUBTOTAL_NET_COST := L_SUBTOTAL_NET_COST + NVL((R1.COST - R1.LEIJI_AMOUNT), 0); --资产净额
---计算合计值
L_TOTAL_COST := L_TOTAL_COST + R1.COST; L_TOTAL_ORIGINAL_COST := L_TOTAL_ORIGINAL_COST + R1.ORIGINAL_COST; L_TOTAL_SALVAGE_VALUE := L_TOTAL_SALVAGE_VALUE + R1.SALVAGE_VALUE;
L_TOTAL_MOUNT_AMOUNTY := L_TOTAL_MOUNT_AMOUNTY + --月折旧额(本月折旧)
R1.DEPRN_AMOUNT_CURR; L_TOTAL_DEPRN_AMOUNTY := L_TOTAL_DEPRN_AMOUNTY + R1.YTD_AMOUNT; L_TOTAL_DEPRN_AMOUNT := L_TOTAL_DEPRN_AMOUNT + R1.LEIJI_AMOUNT; L_TOTAL_NET_COST := L_TOTAL_NET_COST + (R1.COST - R1.LEIJI_AMOUNT); --资产净额
END LOOP;
OUTPUT('<LIST_G_SORT_B>');
OUTPUT('<FAB>' || '小计' || '</FAB>'); OUTPUT('<asset_number>' || '' || '</asset_number>'); OUTPUT('<SHIYZT>' || '' || '</SHIYZT>'); OUTPUT('<fa_name>' || '' || '</fa_name>'); OUTPUT('<FALB>' || '' --xml_format(R1.fa_code)
|| '</FALB>'); OUTPUT('<FALBMC>' || '' --xml_format(R1.DESCRIPTION)
|| '</FALBMC>'); OUTPUT('<FALX>' || '' --xml_format(R1.asset_type)
|| '</FALX>'); OUTPUT('<FAXH>' || '' -- xml_format(R1.MODEL_NUMBER)
|| '</FAXH>'); OUTPUT('<FAVN>' || '' --R1.VENDOR_NAME
|| '</FAVN>'); OUTPUT('<FAQYRQ>' || '' --xml_format(R1.date_placed_in_service)
|| '</FAQYRQ>'); OUTPUT('<FAQTY>' || '' -- R1.QTY
|| '</FAQTY>'); OUTPUT('<FACOST>' || L_SUBTOTAL_COST --xml_format(R1.cost)
|| '</FACOST>'); OUTPUT('<SM>' || '' -- R1.life_in_months
|| '</SM>'); OUTPUT('<FF>' || '' -- R1.deprn_method_code
|| '</FF>'); OUTPUT('<YZ>' || L_SUBTOTAL_ORIGINAL_COST --xml_format(R1.original_cost)
|| '</YZ>'); OUTPUT('<CZ>' || L_SUBTOTAL_SALVAGE_VALUE --xml_format(R1.salvage_value)
|| '</CZ>'); OUTPUT('<XHCB>' || '' --xml_format(R1.adjusted_recoverable_cost)
|| '</XHCB>'); OUTPUT('<ZMJZ>' || L_SUBTOTAL_NET_COST --xml_format(R1.net_cost)
|| '</ZMJZ>'); OUTPUT('<YJSHIYQX>' || '' --xml_format(R1.used_life)
|| '</YJSHIYQX>'); OUTPUT('<SYQX>' || '' --xml_format(R1.SYQX)
|| '</SYQX>'); OUTPUT('<YUE>' || '' || '</YUE>'); OUTPUT('<YUEZJ>' || L_SUBTOTAL_MOUNT_AMOUNTY --xml_format(R1.deprn_amount_curr)
|| '</YUEZJ>'); OUTPUT('<LJZJ>' || L_SUBTOTAL_DEPRN_AMOUNT -- xml_format(R1.LEIJI_amount)
|| '</LJZJ>'); OUTPUT('<YTDZJ>' || L_SUBTOTAL_DEPRN_AMOUNTY --xml_format(R1.YTD_amount)
|| '</YTDZJ>'); OUTPUT('<FPSL>' || '' -- xml_format(R1.units_assigned)
|| '</FPSL>'); OUTPUT('<GLR>' || '' --xml_format(R1.LAST_NAME)
|| '</GLR>'); OUTPUT('<KM>' || '' --xml_format(R1.KMMC)
|| '</KM>'); OUTPUT('<DID>' || '' --xml_format(R1.locaseg)
|| '</DID>'); OUTPUT('<BM>' || '' --xml_format(R1.loca)
|| '</BM>'); OUTPUT('<ZJYUE>' || '' --xml_format(R1.YUE)
|| '</ZJYUE>'); OUTPUT('</LIST_G_SORT_B>');
--清零---
L_SUBTOTAL_COST := 0; L_SUBTOTAL_ORIGINAL_COST := 0; L_SUBTOTAL_SALVAGE_VALUE := 0; L_SUBTOTAL_MOUNT_AMOUNTY := 0; L_SUBTOTAL_DEPRN_AMOUNTY := 0; L_SUBTOTAL_DEPRN_AMOUNT := 0; L_SUBTOTAL_NET_COST := 0;
END LOOP;
OUTPUT('<LIST_G_SORT_B>');
OUTPUT('<FAB>' || '合计' || '</FAB>'); OUTPUT('<asset_number>' || '' || '</asset_number>'); OUTPUT('<SHIYZT>' || '' || '</SHIYZT>'); OUTPUT('<fa_name>' || '' || '</fa_name>'); OUTPUT('<FALB>' || '' --xml_format(R1.fa_code)
|| '</FALB>'); OUTPUT('<FALBMC>' || '' --xml_format(R1.DESCRIPTION)
|| '</FALBMC>'); OUTPUT('<FALX>' || '' --xml_format(R1.asset_type)
|| '</FALX>'); OUTPUT('<FAXH>' || '' -- xml_format(R1.MODEL_NUMBER)
|| '</FAXH>'); OUTPUT('<FAVN>' || '' --R1.VENDOR_NAME
|| '</FAVN>'); OUTPUT('<FAQYRQ>' || '' --xml_format(R1.date_placed_in_service)
|| '</FAQYRQ>'); OUTPUT('<FAQTY>' || '' -- R1.QTY
|| '</FAQTY>'); OUTPUT('<FACOST>' || L_TOTAL_COST --xml_format(R1.cost)
|| '</FACOST>'); OUTPUT('<SM>' || '' -- R1.life_in_months
|| '</SM>'); OUTPUT('<FF>' || '' -- R1.deprn_method_code
|| '</FF>'); OUTPUT('<YZ>' || L_TOTAL_ORIGINAL_COST --xml_format(R1.original_cost)
|| '</YZ>'); OUTPUT('<CZ>' || L_TOTAL_SALVAGE_VALUE --xml_format(R1.salvage_value)
|| '</CZ>'); OUTPUT('<XHCB>' || '' --xml_format(R1.adjusted_recoverable_cost)
|| '</XHCB>'); OUTPUT('<ZMJZ>' || L_TOTAL_NET_COST --xml_format(R1.net_cost)
|| '</ZMJZ>'); OUTPUT('<YJSHIYQX>' || '' --xml_format(R1.used_life)
|| '</YJSHIYQX>'); OUTPUT('<SYQX>' || '' --xml_format(R1.SYQX)
|| '</SYQX>'); OUTPUT('<YUE>' || '' || '</YUE>'); OUTPUT('<YUEZJ>' || L_TOTAL_MOUNT_AMOUNTY --xml_format(R1.deprn_amount_curr)
|| '</YUEZJ>'); OUTPUT('<LJZJ>' || L_TOTAL_DEPRN_AMOUNT -- xml_format(R1.LEIJI_amount)
|| '</LJZJ>'); OUTPUT('<YTDZJ>' || L_TOTAL_DEPRN_AMOUNTY --xml_format(R1.YTD_amount)
|| '</YTDZJ>'); OUTPUT('<FPSL>' || '' -- xml_format(R1.units_assigned)
|| '</FPSL>'); OUTPUT('<GLR>' || '' --xml_format(R1.LAST_NAME)
|| '</GLR>'); OUTPUT('<KM>' || '' --xml_format(R1.KMMC)
|| '</KM>'); OUTPUT('<DID>' || '' --xml_format(R1.locaseg)
|| '</DID>'); OUTPUT('<BM>' || '' --xml_format(R1.loca)
|| '</BM>'); OUTPUT('<ZJYUE>' || '' --xml_format(R1.YUE)
|| '</ZJYUE>'); OUTPUT('</LIST_G_SORT_B>');
---清零---
L_TOTAL_MOUNT_AMOUNTY := 0; L_TOTAL_DEPRN_AMOUNTY := 0; L_TOTAL_DEPRN_AMOUNT := 0; L_TOTAL_COST := 0; L_TOTAL_SALVAGE_VALUE := 0; L_TOTAL_NET_COST := 0;
----------
OUTPUT('</MODULE1>');
EXCEPTION
WHEN OTHERS THEN LOG('固定资产明细表出错:' || SQLERRM); FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM || '代码行:' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END MAIN;
END CUX_FA_DETAIL_001;
![需求沟通中的艺术[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)