分类目录归档：EBS_FA_SQL

EBS_FA_SQL

资产事务处理过程SQL

留下评论

SELECT FL.DESCRIPTION              事务处理描述,
       FT.TRANSACTION_TYPE_CODE    事务处理代码,
       FT.TRANSACTION_DATE_ENTERED 事务处理时间,
       FAA.ASSET_NUMBER            资产编号,
       FB.ORIGINAL_COST            资产原值,
       FFV.DESCRIPTION             机构,
       FAT.DESCRIPTION             资产说明

  FROM FA_TRANSACTION_HEADERS  FT,
       FA_LOOKUPS_VL           FL,
       FA_ADDITIONS_B          FAA,
       FA_ADDITIONS_TL         FAT,
       FA_BOOKS_V              FB,
       FA_DISTRIBUTION_HISTORY FD,
       GL_CODE_COMBINATIONS    GCC,
       FND_FLEX_VALUES_VL      FFV,
       FND_FLEX_VALUE_SETS     FFVS
 WHERE 1 = 1
   AND FAA.ASSET_ID = FT.ASSET_ID
   AND FAT.ASSET_ID = FAA.ASSET_ID
   AND FB.ASSET_ID = FAA.ASSET_ID
   AND FD.ASSET_ID = FB.ASSET_ID
   AND FL.LOOKUP_CODE = FT.TRANSACTION_TYPE_CODE
   AND FL.LOOKUP_TYPE = 'FAXOLTRX'
   AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
   AND FFV.ENABLED_FLAG = 'Y'
   AND FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
   AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_COMPANY' –COA机构段
   AND GCC.CODE_COMBINATION_ID = FD.CODE_COMBINATION_ID
   AND GCC.SEGMENT1 = FFV.FLEX_VALUE
— AND TO_CHAR(FT.TRANSACTION_DATE_ENTERED, 'YYYY-MM-DD') BETWEEN
—    '2014-12-31' AND '2014-12-31'
–AND FT.TRANSACTION_TYPE_CODE = 'ADDITION'
 ORDER BY FFV.FLEX_VALUE, FT.TRANSACTION_DATE_ENTERED ASC;

EBS_FA_SQL

资产类别信息

留下评论

SELECT FCV.SEGMENT1 || '.' || FCV.SEGMENT2 || '.' || FCV.SEGMENT3 资产类别,
       FCV.DESCRIPTION 类别描述,
       FCB.BOOK_TYPE_CODE 资产账簿,
       GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' ||
       GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' ||
       GCC.SEGMENT7 || '.' || 0 成本科目,
       GCC1.SEGMENT1 || '.' || GCC1.SEGMENT2 || '.' || GCC1.SEGMENT3 || '.' ||
       GCC1.SEGMENT4 || '.' || GCC1.SEGMENT5 || '.' || GCC1.SEGMENT6 || '.' ||
       GCC1.SEGMENT7 || '.' || 0 资产结算科目,
       GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.' || GCC2.SEGMENT3 || '.' ||
       GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.' || GCC2.SEGMENT6 || '.' ||
       GCC2.SEGMENT7 || '.' || 0 折旧费用科目,
       GCC3.SEGMENT1 || '.' || GCC3.SEGMENT2 || '.' || GCC3.SEGMENT3 || '.' ||
       GCC3.SEGMENT4 || '.' || GCC3.SEGMENT5 || '.' || GCC3.SEGMENT6 || '.' ||
       GCC3.SEGMENT7 || '.' || 0 累计折旧科目,
       GCC4.SEGMENT1 || '.' || GCC4.SEGMENT2 || '.' || GCC4.SEGMENT3 || '.' ||
       GCC4.SEGMENT4 || '.' || GCC4.SEGMENT5 || '.' || GCC4.SEGMENT6 || '.' ||
       GCC4.SEGMENT7 || '.' || 0 附加费用科目,
       GCC5.SEGMENT1 || '.' || GCC5.SEGMENT2 || '.' || GCC5.SEGMENT3 || '.' ||
       GCC5.SEGMENT4 || '.' || GCC5.SEGMENT5 || '.' || GCC5.SEGMENT6 || '.' ||
       GCC5.SEGMENT7 || '.' || 0 附加准备金科目

  FROM FA_CATEGORIES_VL     FCV,
       FA_CATEGORY_BOOKS    FCB,
       GL_CODE_COMBINATIONS GCC,
       GL_CODE_COMBINATIONS GCC1,
       GL_CODE_COMBINATIONS GCC2,
       GL_CODE_COMBINATIONS GCC3,
       GL_CODE_COMBINATIONS GCC4,
       GL_CODE_COMBINATIONS GCC5

 WHERE 1 = 1
   AND FCV.CATEGORY_ID = FCB.CATEGORY_ID
   AND FCV.ENABLED_FLAG = 'Y'
   AND FCB.ASSET_COST_ACCOUNT_CCID = GCC.CODE_COMBINATION_ID
   AND FCB.ASSET_CLEARING_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID
   AND FCB.DEPRN_EXPENSE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID
   AND FCB.BONUS_RESERVE_ACCT_CCID = GCC3.CODE_COMBINATION_ID
   AND FCB.BONUS_EXPENSE_ACCOUNT_CCID = GCC4.CODE_COMBINATION_ID
   AND FCB.BONUS_RESERVE_ACCT_CCID = GCC5.CODE_COMBINATION_ID;